Data Cleaning | ZeroToML

Garbage In, Garbage Out

A gradient boosted tree trained on clean data beats a neural network trained on dirty data, every time. Feature engineering starts with making the data trustworthy. This lesson is a systematic checklist for real-world datasets.

Handling Missing Values

First, understand why values are missing. The strategy depends on the mechanism:

MCARMissing Completely At Random. Dropout from a sensor malfunction. Safe to drop rows or impute.

MARMissing At Random. Age missing more often for younger users. Can be imputed using other features.

MNARMissing Not At Random. High-income users skip income field. Missingness carries information — add a binary indicator!

Python

import pandas as pd
import numpy as np
from sklearn.impute import SimpleImputer, KNNImputer

df = pd.read_csv("https://raw.githubusercontent.com/mwaskom/seaborn-data/master/titanic.csv")

# ── Step 1: Quantify
print(df.isnull().mean().sort_values(ascending=False).head(10))

# ── Step 2: Add missingness indicator for MNAR features
df["age_was_missing"] = df["age"].isnull().astype(int)

# ── Step 3: Drop if >50% missing (usually useless)
threshold = 0.5
cols_to_drop = df.columns[df.isnull().mean() > threshold]
df.drop(columns=cols_to_drop, inplace=True)

# ── Step 4: Imputation strategies
# Simple: mean / median / mode
simple = SimpleImputer(strategy="median")          # for numeric
df["age_imputed_median"] = simple.fit_transform(df[["age"]])

# KNN imputation — uses neighbours to fill (more accurate)
knn = KNNImputer(n_neighbors=5)
df_numeric = df.select_dtypes("number")
df_knn = pd.DataFrame(knn.fit_transform(df_numeric), columns=df_numeric.columns)

# Categorical: mode or dedicated category "Unknown"
df["embarked"] = df["embarked"].fillna(df["embarked"].mode()[0])

Duplicate Detection

Python

print(f"Exact duplicates: {df.duplicated().sum()}")
df.drop_duplicates(inplace=True)

# Near-duplicates (e.g., same passenger with slight variations)
# Check on a subset of identifying columns
key_cols = ["name", "age", "fare"]
near_dupes = df[df.duplicated(subset=key_cols, keep=False)]
print(f"Near-duplicates on key cols: {len(near_dupes)}")

Data Type Fixes

Python

import pandas as pd

df = pd.DataFrame({
    "price":       ["$1,200.50", "$850", "$2,300.00"],
    "date":        ["2024-01-15", "2024-03-02", "2024-11-20"],
    "is_premium":  ["yes", "no", "yes"],
    "category":    ["A", "B", "A"],
})

# String → numeric
df["price"] = df["price"].str.replace("[$,]", "", regex=True).astype(float)

# String → datetime
df["date"]  = pd.to_datetime(df["date"])
df["year"]  = df["date"].dt.year
df["month"] = df["date"].dt.month
df["dayofweek"] = df["date"].dt.dayofweek   # 0=Monday

# Binary string → int
df["is_premium"] = (df["is_premium"] == "yes").astype(int)

# Low-cardinality string → category (saves memory)
df["category"] = df["category"].astype("category")

Outlier Treatment

Python

import pandas as pd, numpy as np

np.random.seed(0)
data = pd.Series(np.random.normal(50, 10, 200).tolist() + [150, 200, -30])

# IQR winsorisation (clip, don't drop)
Q1, Q3 = data.quantile([0.25, 0.75])
IQR    = Q3 - Q1
lower  = Q1 - 1.5 * IQR
upper  = Q3 + 1.5 * IQR
data_clipped = data.clip(lower=lower, upper=upper)

# Log transform for right-skewed data (e.g., income, price)
data_log = np.log1p(data.clip(lower=0))    # log1p handles zeros: log(1+x)

# sklearn's RobustScaler handles outliers by scaling via median/IQR
from sklearn.preprocessing import RobustScaler
X = data.values.reshape(-1, 1)
X_scaled = RobustScaler().fit_transform(X)

💡

Clip, don't drop. Dropping outliers loses information and can bias your dataset. Clipping (Winsorisation) keeps the sample while limiting extreme influence. Log-transforming skewed features is often even better.

Consistency Checks

Python

# Impossible values
print(df[df["age"] < 0])              # negative age
print(df[df["fare"] < 0])             # negative fare
print(df[df["age"] > 120])            # unrealistic age

# Referential integrity: values in column that shouldn't exist
valid_classes = {1, 2, 3}
bad_rows = df[~df["pclass"].isin(valid_classes)]
print(f"Rows with invalid pclass: {len(bad_rows)}")

# Logical contradictions (if age < 18, can't be 'married')
# Flag them for investigation rather than auto-correcting

Summary

Understand WHY values are missing (MCAR/MAR/MNAR) before choosing imputation strategy.
Add binary missingness indicators for MNAR features — the missing-ness is signal.
Fix data types early: strings to numeric/datetime, low-cardinality strings to category.
Clip outliers (Winsorise); log-transform right-skewed features.
Always validate impossible values and logical contradictions.