Messy Real Data

The founder hands you a customer file with an apologetic smile. It's been typed by different staff, in different cafes, over three years. Welcome to the real job. Data scientists often say that eighty percent of the work is cleaning. Before you trust a single number, you have to audit the file.

• An apologetic smile
• Typed by many hands
• Audit before you trust

A data audit

Here's a five line audit that you should run on any new file. How many rows? Any duplicates? What's missing? Are the ranges sensible? What categories exist? Run it. Look at what we found. A hundred and twenty duplicate customers. Missing ages. An age of nine hundred and ninety nine. And look at the cities. Oslo appears in two spellings, Lagos has a stray space. Every one of these would have silently wrecked our analysis.

import pandas as pd

df = pd.read_csv("customers.csv")

print("Rows:", len(df))
print("Duplicate rows:", df.duplicated().sum())
print("Missing per column:")
print(df.isna().sum()[lambda s: s > 0].to_string())
print("Age range:", df["age"].min(), "to", df["age"].max())
print("Cities:", sorted(df["city"].unique()))

Rows: 6120
Duplicate rows: 120
Missing per column:
age    458
Age range: 16.0 to 999.0
Cities: [' Lagos', 'Cairo', 'Lagos', 'Lima', 'Oslo', 'ROME', 'Rome', 'Tokyo', 'oslo ']

Cleaning it up

Let's fix them in order. Drop the duplicates. Strip spaces, and fix the capitals in city names. Ages outside a sensible range become missing. Then fill the missing ages with the median, which ignores extreme values. Run it. Clean cities, sensible ages, and no duplicates. Note that dropping duplicates came first. Otherwise, duplicates would have affected the median.

import pandas as pd

df = pd.read_csv("customers.csv", parse_dates=["signup"])

df = df.drop_duplicates()
df["city"] = df["city"].str.strip().str.title()
df.loc[~df["age"].between(16, 90), "age"] = pd.NA
df["age"] = df["age"].fillna(df["age"].median())

print("Rows left:", len(df))
print("Cities:", sorted(df["city"].unique()))
print("Age range:", df["age"].min(), "to", df["age"].max())

Rows left: 6000
Cities: ['Cairo', 'Lagos', 'Lima', 'Oslo', 'Rome', 'Tokyo']
Age range: 16.0 to 78.0

Summary