Case Study — Cleaning a Real-World Sales Dataset¶
This case study walks through cleaning a messy sales dataset end to end. You'll encounter all the problems from this module: missing values, duplicates, bad types, and outliers — all in the same dataset.
Business Context¶
A retail company exports their sales data from a legacy ERP system every month. The export is messy: dates in multiple formats, currencies mixed with text, duplicate records from re-exports, and inconsistent category names. Your job is to clean it before it goes into the analytics dashboard.
The Raw Data¶
import pandas as pd
import numpy as np
# Simulate the messy raw data
raw_data = {
"order_id": ["O001", "O002", "O002", "O003", "O004", "O005", "O006", None, "O007"],
"customer_id": ["C001", "C003", "C003", "C002", "C004", "C005", "C001", "C008", "C002"],
"product": [" Wireless Headphones ", "Standing Desk", "Standing Desk",
"ANALYTICS BOOK", "yoga mat", "Coffee Maker", "wireless headphones",
"standing desk", "Analytics Book"],
"category": ["Electronics", "Furniture", "Furniture", "Books",
"sports", "Kitchen", "electronics", "Furniture", "Books"],
"quantity": ["2", "1", "1", "3", "abc", "2", "1", "1", "5"],
"unit_price": ["£149.00", "£389.00", "£389.00", "£49.99",
"£35.99", "£89.99", "£14900", "£389.00", "£49.99"],
"status": ["Completed", "completed", "completed", "PENDING",
"cancelled", "Completed", "completed", "pending", "completed"],
"order_date": ["2024-01-05", "2024-01-07", "2024-01-07", "09/01/2024",
"2024-01-11", "14-Jan-2024", "2024-01-14", "2024-01-18", "2024-01-20"],
"discount_pct": [None, 10, 10, None, 5, None, None, None, None],
}
df_raw = pd.DataFrame(raw_data)
print("RAW DATA:")
print(df_raw.to_string())
Problems visible immediately:
- Row 2: O002 appears twice (duplicate export)
- Row 4: quantity = "abc" — invalid
- Row 6: unit_price = "£14900" — likely a data entry error (extra zero), should be £149.00
- Row 7: order_id = None — critical field missing
- Dates in 3 different formats
- status and category in mixed case
- product with leading/trailing spaces and inconsistent casing
Step 1 — Audit¶
print(f"\nShape: {df_raw.shape}")
print(f"\nDtypes:\n{df_raw.dtypes}")
print(f"\nMissing values:\n{df_raw.isnull().sum()}")
print(f"\nDuplicate rows: {df_raw.duplicated().sum()}")
print(f"\nDuplicate order_ids: {df_raw.duplicated(subset=['order_id']).sum()}")
print(f"\nUnique statuses: {df_raw['status'].unique()}")
print(f"\nUnique categories: {df_raw['category'].unique()}")
Step 2 — Clean¶
def step1_drop_critical_nulls(df):
"""Remove rows missing order_id (cannot identify the record)."""
before = len(df)
df = df.dropna(subset=["order_id"])
print(f"Step 1 — drop critical nulls: {before} → {len(df)} rows")
return df
def step2_fix_dtypes(df):
"""Convert columns to correct types."""
df = df.copy()
# Dates — mixed format
df["order_date"] = pd.to_datetime(df["order_date"], dayfirst=True, errors="coerce")
# Quantity — coerce invalid to NaN
df["quantity"] = pd.to_numeric(df["quantity"], errors="coerce")
# Price — strip currency symbol and commas
df["unit_price"] = (
df["unit_price"]
.str.replace("£", "")
.str.replace(",", "")
.pipe(pd.to_numeric, errors="coerce")
)
print(f"Step 2 — fix dtypes: complete")
return df
def step3_standardise_strings(df):
"""Normalise text columns."""
df = df.copy()
df["status"] = df["status"].str.lower().str.strip()
df["category"] = df["category"].str.title().str.strip()
df["product"] = df["product"].str.strip().str.replace(r"\s+", " ", regex=True).str.title()
print(f"Step 3 — standardise strings: complete")
return df
def step4_handle_outliers(df):
"""Fix obvious data entry errors in unit_price."""
df = df.copy()
# Rule: if unit_price > 10x the median for the same category, divide by 10
median_by_cat = df.groupby("category")["unit_price"].median()
for category, median in median_by_cat.items():
mask = (df["category"] == category) & (df["unit_price"] > median * 10)
if mask.any():
print(f" Fixing {mask.sum()} likely entry error(s) in {category}")
df.loc[mask, "unit_price"] = df.loc[mask, "unit_price"] / 10
print(f"Step 4 — handle outliers: complete")
return df
def step5_handle_missing(df):
"""Apply business rules for missing values."""
df = df.copy()
df["discount_pct"] = df["discount_pct"].fillna(0)
df["quantity"] = df["quantity"].fillna(df["quantity"].median()).astype(int)
print(f"Step 5 — handle missing: complete")
return df
def step6_remove_duplicates(df):
"""Deduplicate on order_id, keeping first (same record)."""
before = len(df)
df = df.drop_duplicates(subset=["order_id"], keep="first").reset_index(drop=True)
print(f"Step 6 — remove duplicates: {before} → {len(df)} rows")
return df
def step7_add_calculated_columns(df):
"""Add derived analytical columns."""
df = df.copy()
df["total"] = df["quantity"] * df["unit_price"]
df["net_total"] = df["total"] * (1 - df["discount_pct"] / 100)
df["month"] = df["order_date"].dt.month_name()
return df
def clean_sales_data(df_raw):
"""Full cleaning pipeline."""
return (
df_raw
.pipe(step1_drop_critical_nulls)
.pipe(step2_fix_dtypes)
.pipe(step3_standardise_strings)
.pipe(step4_handle_outliers)
.pipe(step5_handle_missing)
.pipe(step6_remove_duplicates)
.pipe(step7_add_calculated_columns)
)
df_clean = clean_sales_data(df_raw)
print("\nCLEAN DATA:")
print(df_clean.to_string())
Step 3 — Validate¶
def validate_orders(df):
"""Assert all quality constraints."""
assert df["order_id"].duplicated().sum() == 0, "Duplicate order IDs"
assert df["order_id"].isnull().sum() == 0, "Null order IDs"
assert (df["quantity"] > 0).all(), "Non-positive quantities"
assert (df["unit_price"] > 0).all(), "Non-positive prices"
assert (df["unit_price"] <= 1000).all(), "Prices above £1,000 — check outliers"
assert df["order_date"].isnull().sum() == 0, "Null order dates"
assert set(df["status"].unique()).issubset({"completed", "pending", "cancelled", "refunded"})
print(f"✓ Validation passed — {len(df)} clean rows ready for analysis")
validate_orders(df_clean)
Before/After Comparison¶
print("BEFORE → AFTER")
print(f"Rows: {len(df_raw):>6} → {len(df_clean):>6}")
print(f"Duplicates: {df_raw.duplicated().sum():>6} → {df_clean.duplicated().sum():>6}")
print(f"Missing order_id: {df_raw['order_id'].isnull().sum():>6} → {df_clean['order_id'].isnull().sum():>6}")
print(f"Missing discount: {df_raw['discount_pct'].isnull().sum():>6} → {df_clean['discount_pct'].isnull().sum():>6}")