fix-excel-date-formats

Read a file /home/piotr/Documents/customers/customers.xlsx

Thinking

Loading and inspecting data

I’m thinking about how we should load the data first. I need to use code effectively, particularly with pandas to read an Excel file. The developer mentioned having to follow the exact inspection pattern, especially regarding the columns. I want to make sure I'm getting that right, so I’ll focus on using the correct function and parameters to achieve the desired pattern. It’s about making sure everything aligns perfectly!

</>Load customer data

import pandas as pd

df = pd.read_excel("/home/piotr/Documents/customers/customers.xlsx")

print(f"Shape: {df.shape}")
columns = df.columns.tolist()
print(f"Columns ({len(columns)}):", columns if len(columns) <= 100 else columns[:100] + ["..."])
df.head()

Shape: (530, 7)
Columns (7): ['customer_id', 'full_name', 'email', 'country', 'signup_date', 'plan', 'monthly_spend']

	customer_id	full_name	email	country	signup_date	plan	monthly_spend
0	CUST-0292	Anna Weber	anna.weber91@example.com	Poland	14/09/2024	Pro	49
1	CUST-0283	Mateo Kowalski	mateo.kowalski38@example.com	Poland	April 29, 2023	Basic	19
2	CUST-0082	Emma Andersen	emma.andersen58@example.com	United States	2024-09-13	Enterprise	199
3	CUST-0016	Nina Martin	nina.martin22@example.com	Italy	2023-08-13	Free	0
4	CUST-0472	Tomasz Rossi	tomasz.rossi70@example.com	Italy	2024-11-13	Free	0

The Excel file contains 530 customer records with 7 fields: customer identifier, name, email, country, signup date, subscription plan, and monthly spend. The preview shows mixed signup-date formats (for example, 14/09/2024, April 29, 2023, and 2024-09-13), while monthly spend is numeric and includes free-plan customers with zero spend.

show the different date formats in signup_date with counts

Thinking

Developing format counting code

I need to create code that counts formats and robustly classifies patterns. I think one table would work. I could define regex formats and display counts based on them. Dates might come through as timestamp types, but they could be read as strings in Excel. I want to offer an inferred format using regex to count occurrences, and I should probably distinguish between formats such as YYYY-MM-DD, DD/MM/YYYY, and Month D YYYY.

</>Count date formats

import re

def date_format(value):
    text = str(value).strip()
    if re.fullmatch(r"\d{4}-\d{2}-\d{2}", text):
        return "YYYY-MM-DD"
    if re.fullmatch(r"\d{2}/\d{2}/\d{4}", text):
        return "DD/MM/YYYY"
    if re.fullmatch(r"[A-Za-z]+ \d{1,2}, \d{4}", text):
        return "Month D, YYYY"
    return "Other / missing"

format_counts = (
    df["signup_date"]
    .map(date_format)
    .value_counts()
    .rename_axis("date_format")
    .reset_index(name="count")
)

format_counts

	date_format	count
0	YYYY-MM-DD	430
1	DD/MM/YYYY	50
2	Month D, YYYY	50

signup_date uses three formats across all 530 records:

YYYY-MM-DD: 430 records (81.1%)
DD/MM/YYYY: 50 records (9.4%)
Month D, YYYY: 50 records (9.4%)

The ISO-style YYYY-MM-DD format is dominant, but 100 records use alternative formats and would need normalization for consistent date analysis.

parse signup_date into a proper date column

Create a normalized datetime column while interpreting slash-formatted dates as day/month/year.

</>Parse signup dates

import pandas as pd

def parse_signup_date(value):
    text = str(value).strip()
    if "/" in text:
        return pd.to_datetime(text, format="%d/%m/%Y", errors="coerce")
    return pd.to_datetime(text, errors="coerce")

df["signup_date_parsed"] = df["signup_date"].map(parse_signup_date)

df[["signup_date", "signup_date_parsed"]].head()

	signup_date	signup_date_parsed
0	14/09/2024	2024-09-14
1	April 29, 2023	2023-04-29
2	2024-09-13	2024-09-13
3	2023-08-13	2023-08-13
4	2024-11-13	2024-11-13

signup_date_parsed has been added as a proper pandas datetime column. The displayed examples confirm that all three original formats are normalized consistently, including 14/09/2024 becoming 2024-09-14 and April 29, 2023 becoming 2023-04-29.

The original signup_date column remains unchanged for reference.

how many signup_date values failed to parse, and show the min and max date

Thinking

Clarifying output requirements

I need to compute an answer based on the current state, but it seems I don't have the right output ready. It sounds like I should focus on showing just one table to keep things clear, using aggregated named values. I really want to make sure it’s exactly what I need, so having that single table should be the goal here. I'll work on that to keep it precise!

Check parse failures and the date range in the new datetime column.

</>Summarize signup date parsing

summary = df["signup_date_parsed"].agg(
    failed_to_parse=lambda s: s.isna().sum(),
    min_date="min",
    max_date="max",
).to_frame().T

summary

	failed_to_parse	min_date	max_date
signup_date_parsed	0	2023-01-13 00:00:00	2025-01-13 00:00:00

All 530 signup_date values parsed successfully: 0 failures.

The parsed dates range from 2023-01-13 to 2025-01-13.