hanlde-missing-values-in-excel-with-python

piotrek / hanlde-missing-values-in-excel-with-python

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

Thinking

Examining code for inspections

I need to use code to load and inspect data, specifically with pandas for Excel files. I know that the exact inspection pattern must match the required columns or variables. It feels like I have to ensure every detail is considered for this task. I want to make sure everything is set up correctly so I can accurately process the inspection. Let's keep focused on ensuring the output aligns with what’s expected!

</>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 and 7 fields: customer ID, name, email, country, signup date, plan, and monthly spend. The preview shows multiple date formats and subscription tiers ranging from Free to Enterprise, with monthly-spend values such as 0, 19, 49, and 199 USD.

count missing values per column

</>Count missing values

missing_by_column = df.isna().sum().sort_values(ascending=False)
missing_by_column

full_name        14
email            14
monthly_spend    12
customer_id       0
country           0
signup_date       0
plan              0
dtype: int64

Missing values by column:

full_name: 14
email: 14
monthly_spend: 12
customer_id, country, signup_date, plan: 0

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

Thinking

Normalizing account strings

I’m considering how to deal with account strings, particularly those that might be "N/A" or "-". It seems like the first step should be counting these values without mutating the data too much. The user might want to calculate actual missing values, and I need to ensure that the search is case insensitive. I think I can use something like df.astype("string").str.strip().isin(["N/A", "-"]), or potentially check for isna. I'll need to make a count.

Count NaN, N/A, and - values as missing (after trimming whitespace):

</>Count missing values

missing_tokens = ["N/A", "-"]

missing_by_column = (
    df.isna()
    | df.astype("string").apply(lambda col: col.str.strip().str.upper().isin([x.upper() for x in missing_tokens]))
).sum().sort_values(ascending=False)

missing_by_column

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

Treating blank/NaN, N/A, and - as missing, the counts are:

Column	Missing values
`full_name`	21
`email`	21
`monthly_spend`	18
`customer_id`	0
`country`	0
`signup_date`	0
`plan`	0

full_name and email have the highest missingness, each affecting 21 of 530 records.

convert the - values to nulls and make monthly_spend numeric, then show the total spend

Thinking

Parsing data effectively

I need to address cell mutations and consider how to display a total. The user might be using a "-" to represent nulls, and I'm thinking about how to parse monthly data effectively. Should I use N/A for automatic parsing? When using read_excel, it likely defaults to NaN. I also want to ensure that the "total spend" reflects the accurate sum without any spaces or currency symbols. I'll need to replace those and convert to a numeric format without having to inspect further.

Replace hyphen placeholders with nulls, convert monthly_spend to numeric, and calculate the total:

</>Clean and sum spend

df = df.replace(r"^\s*-\s*$", pd.NA, regex=True)

df["monthly_spend"] = pd.to_numeric(df["monthly_spend"], errors="coerce")

total_spend = df["monthly_spend"].sum()
total_spend

np.float64(21013.0)

Hyphen (-) entries were converted to nulls across the dataset. monthly_spend was successfully converted to numeric; any non-numeric entries became null values.

The total monthly spend is 21,013 USD.