clean-messy-excel-data-with-ai-and-python

piotrek / clean-messy-excel-data-with-ai-and-python

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

</>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 workbook has been read into df with 530 customer records and 7 fields: customer identifier, name, email, country, signup date, subscription plan, and monthly spend. The preview shows mixed date formats in signup_date and plan values including Free, Basic, Pro, and Enterprise.

show types for each column

</>Inspect data types

df.dtypes

customer_id         str
full_name           str
email               str
country             str
signup_date         str
plan                str
monthly_spend    object
dtype: object

All seven columns are currently stored as non-numeric text-like values, except monthly_spend, which is object rather than a numeric type. In particular, signup_date remains text because the file contains mixed date formats.

how many rows are exact duplicates

</>Count duplicate rows

df.duplicated().sum()

np.int64(30)

There are 30 exact duplicate rows in the 530-row dataset.

count missing values per column, treating N/A and - as missing

Thinking

Calculating missing tokens

I need to write code to calculate any missing tokens. It sounds like I should start by stripping whitespace and considering any cases where values might be "N/A." I want to ensure this process is non-destructive and doesn’t mutate the original data. Getting those details right is essential, so I'll take my time to double-check everything as I code. Let's make this work smoothly!

This counts standard missing values plus N/A and - (including surrounding whitespace) in each column.

</>Count missing values

missing_counts = df.isna() | df.astype("string").apply(lambda col: col.str.strip().isin(["N/A", "-"]))
missing_counts.sum()

customer_id       0
full_name        21
email            21
country           0
signup_date       0
plan              0
monthly_spend    18
dtype: int64

Missing-value counts, treating both N/A and - as missing:

full_name: 21
email: 21
monthly_spend: 18
customer_id, country, signup_date, and plan: 0

remove duplicate rows

</>Remove duplicates and reset index

df = df.drop_duplicates().reset_index(drop=True)
df.shape

(500, 7)

30 duplicate rows were removed. The dataset now contains 500 unique rows across 7 columns.

replace the remaining - values with nulls and convert monthly_spend to numeric

</>Convert monthly spend

df = df.replace(r"^\s*-\s*$", pd.NA, regex=True)
df["monthly_spend"] = pd.to_numeric(df["monthly_spend"], errors="coerce")

df["monthly_spend"].dtype

dtype('float64')

All standalone - values have been replaced with nulls. monthly_spend is now stored as a numeric float64 column; any non-numeric values in that column were converted to nulls.

parse signup_date into a proper date column, handle mixed formats, treat slash dates as day-first, then show how many parsed and the min and max date

Thinking

Considering data execution

I need to execute a code while being careful to avoid overwriting the datetime. There are mixed format concerns with the pandas version, specifically using pd.to_datetime with format='mixed' and dayfirst true for pandas version 2 or newer. I should think about a fallback option that provides parsed counts and min/max dates. The original data might contain date serialized strings, so ending with a table is essential. I could produce a Series with the required statistics in one block.

</>Parse signup dates

df["signup_date"] = pd.to_datetime(
    df["signup_date"],
    format="mixed",
    dayfirst=True,
    errors="coerce",
)

pd.Series(
    {
        "parsed_dates": df["signup_date"].notna().sum(),
        "min_date": df["signup_date"].min(),
        "max_date": df["signup_date"].max(),
    }
)

parsed_dates                    500
min_date        2023-01-13 00:00:00
max_date        2025-01-13 00:00:00
dtype: object

signup_date has been converted to a proper datetime column using mixed-format parsing with slash-formatted dates interpreted day-first.

Parsed successfully: 500 dates
Earliest signup date: 13 January 2023
Latest signup date: 13 January 2025

standardize the country column to full country names

Thinking

Inspecting values before mutation

I think I need to inspect the values before performing any mutations. This way, I can ensure that I'm working with distinct values and also get a count of them. It's important to have a single piece of code to achieve this efficiently. I'll focus on developing that code to handle both displaying the unique values and counting them. This should help streamline the process moving forward!

First, inspect the current country labels and their frequencies so the standardization mapping is based on the values actually present.

</>Count countries

country_counts = df["country"].astype("string").str.strip().value_counts(dropna=False)
country_counts

country
Poland           147
Germany           85
France            60
Spain             42
United States     37
Italy             29
POLAND            17
poland            13
PL                10
FR                 9
france             8
spain              7
germany            6
ES                 6
IT                 5
GERMANY            5
Polska             5
italy              3
U.S.               3
USA                2
DE                 1
Name: count, dtype: int64[pyarrow]

The country column contains case variants and abbreviations for six countries. Standardize them to full names, including PL/Polska as Poland, DE as Germany, and USA/U.S. as United States.

</>Normalize country names

country_map = {
    "poland": "Poland", "pl": "Poland", "polska": "Poland",
    "germany": "Germany", "de": "Germany",
    "france": "France", "fr": "France",
    "spain": "Spain", "es": "Spain",
    "italy": "Italy", "it": "Italy",
    "united states": "United States", "usa": "United States", "u.s.": "United States",
}

normalized_country = df["country"].astype("string").str.strip().str.casefold()
df["country"] = normalized_country.map(country_map).fillna(df["country"].astype("string").str.strip())

df["country"].value_counts(dropna=False)

country
Poland           192
Germany           97
France            77
Spain             55
United States     42
Italy             37
Name: count, dtype: int64

All 500 records now use full country names with no remaining abbreviations or case variants:

Poland: 192
Germany: 97
France: 77
Spain: 55
United States: 42
Italy: 37

save the cleaned file as customers-clean.xlsx

</>Save cleaned data to Excel

output_path = "/home/piotr/Documents/customers/customers-clean.xlsx"
df.to_excel(output_path, index=False)
output_path

'/home/piotr/Documents/customers/customers-clean.xlsx'

The cleaned workbook was saved at:

/home/piotr/Documents/customers/customers-clean.xlsx

It contains 500 cleaned records, including standardized full country names.