The Dirty Data Problem

Audit Before You Clean

Auditing first gives you a complete map of the problem — the X-ray you take before surgery. Never clean a column you haven't measured.

import pandas as pd

df = pd.read_csv('sales_data.csv')
df.shape; df.info()

# Missing-value audit
null_report = pd.DataFrame({
  'missing': df.isnull().sum(),
  'pct': (df.isnull().mean()*100).round(2),
  'dtype': df.dtypes
}).query('missing > 0') \
  .sort_values('pct', ascending=False)

# Duplicate audit
df.duplicated().sum()
df['customer_id'].duplicated().sum()

Understanding WHY Data Is Missing

The mechanism determines the method. Diagnose why a value is absent before choosing how to fill it.

Missing-Value Treatment — Decision Tree

Drop Rows or Columns — dropna()

# Drop rows missing ANY value
df_clean = df.dropna()

# Drop only where SPECIFIC columns are missing
df_clean = df.dropna(subset=['age', 'purchase_amount'])

# Drop columns that are >50% empty
df_clean = df.dropna(axis=1, thresh=len(df)*0.5)

# ALWAYS log what you lost
print(f"Before: {len(df):,}  After: {len(df_clean):,}")

Fill with Statistics — fillna()

An Indian e-commerce set had 15.7% missing ages (MCAR). Median (34) beat the mean because age is right-skewed — and downstream model accuracy rose 4%.

# numeric → median (robust to skew)
df['age'].fillna(df['age'].median(),
              inplace=True)
# categorical → most frequent
df['city'].fillna(df['city'].mode()[0],
               inplace=True)
# group-wise → smarter than global
df['age'] = df.groupby('bracket')['age'] \
    .transform(lambda x: x.fillna(x.median()))

Interpolation — for Time Series

# straight-line between neighbours
df['temp'] = df['temp'].interpolate('linear')
# time-aware (respects unequal gaps)
df = df.set_index('timestamp')
df['temp'] = df['temp'].interpolate('time')
# carry last / next value
df['price'] = df['price'].ffill()  # stocks
df['price'] = df['price'].bfill()

Model-Based Imputation — KNN & Iterative

from sklearn.impute import KNNImputer
# average of k most-similar rows
imp = KNNImputer(n_neighbors=5,
                 weights='distance')
df[num] = imp.fit_transform(df[num])

from sklearn.impute import IterativeImputer
# regress each column on the others
it = IterativeImputer(max_iter=10,
                      random_state=42)
df[num] = it.fit_transform(df[num])

Removing Duplicates — Know the Three Types

Detecting & Dropping Duplicates

# inspect BEFORE you delete
df.duplicated().sum()
df[df.duplicated(keep=False)]     # all copies

# exact duplicates
df = df.drop_duplicates()          # keep first
df = df.drop_duplicates(keep='last')

# key-based: newest row per customer
df = (df.sort_values('order_date',
                     ascending=False)
        .drop_duplicates(
            subset=['customer_id'],
            keep='first'))

# fuzzy: normalise text first
df['city'] = df['city'].str.lower().str.strip()
df['city'] = df['city'].replace(city_map)

Validate the Clean Dataset

A cleaning step that silently introduces a new problem is worse than no cleaning at all. Prove your result with hard assertions.

# post-cleaning assertions
assert df.isnull().sum().sum() == 0
assert df.duplicated().sum()   == 0
assert df['age'].between(18,100).all()
assert df['purchase_amount'].ge(0).all()
assert df['rating'].between(1,5).all()

# reproducible summary report
report = {
  'rows_before': len(df_raw),
  'rows_after':  len(df),
  'nulls_remaining': df.isnull().sum().sum(),
  'dupes_remaining': df.duplicated().sum()}

A Production-Ready Cleaning Function

def clean_dataframe(df, id_col=None, date_col=None, verbose=True):
    df_clean = df.copy()                      # Rule 1: never touch the original
    report = {'rows_start': len(df_clean)}

    df_clean = df_clean.dropna(how='all').dropna(axis=1, how='all')   # empties
    df_clean = df_clean.dropna(axis=1, thresh=len(df_clean)*0.5)      # >50% cols

    for c in df_clean.select_dtypes('number').columns:       # numeric → median
        df_clean[c] = df_clean[c].fillna(df_clean[c].median())
    for c in df_clean.select_dtypes(['object','category']).columns:  # cat → mode
        df_clean[c] = df_clean[c].fillna(df_clean[c].mode()[0])

    if id_col and date_col:                            # keep most recent per key
        df_clean = (df_clean.sort_values(date_col, ascending=False)
                            .drop_duplicates(subset=[id_col], keep='first'))
    else:
        df_clean = df_clean.drop_duplicates()
    return df_clean, report

df_clean, report = clean_dataframe(df_raw, id_col='customer_id', date_col='order_date')

The #1 Silent Mistake — Imputation Leakage

Compute a median (or fit KNN) on the whole dataset and then split, and test-set information has already leaked into training. Your validation score looks great — and lies.

Leak-Proof Cleaning with a Pipeline

from sklearn.compose import ColumnTransformer
from sklearn.pipeline import Pipeline
from sklearn.impute import SimpleImputer
from sklearn.preprocessing import (
    StandardScaler, OneHotEncoder)

num = Pipeline([
  ('imp', SimpleImputer(strategy='median')),
  ('sc',  StandardScaler())])
cat = Pipeline([
  ('imp', SimpleImputer(strategy='most_frequent')),
  ('oh',  OneHotEncoder(handle_unknown='ignore'))])

pre = ColumnTransformer([
  ('num', num, num_cols),
  ('cat', cat, cat_cols)])

model = Pipeline([('pre', pre), ('clf', clf)])
model.fit(X_train, y_train)   # fit = train only ✓

Outlier-Aware Cleaning

A handful of extreme values silently wreck the mean, inflate variance and stretch every scaler. Detect them, then decide deliberately: cap, remove, or keep.

Common Pitfalls & Their Fixes

The Golden Rules — 1 to 4

The Golden Rules — 5 to 8 & Takeaway

🧹 End of tutorial · Press ← to review, or click Restart