Data Cleaning — Module 4.1

How This Lesson Fits the Module

Data Collection lands raw tables in storage. Data cleaning is the first transformation pass: fix types, remove duplicates, align formats, and enforce a schema so downstream labeling and modeling do not silently fail.

Models do not see “dirty” data—they see whatever you encoded. A string "1,234.50" in a numeric column becomes NaN or wrong features unless you clean deliberately. This lecture is Pandas engineering applied to ML reliability.

Learning Objectives

By the end of this lesson, students should be able to:

Profile a DataFrame for duplicates, dtype mismatches, and inconsistent categories.
Remove or merge duplicate rows with domain-aware keys.
Coerce types safely (astype, to_datetime, to_numeric).
Normalize text, units, and categorical labels for consistent training.
Separate cleaning rules that must be fit on training data only.
Export a cleaned table with a documented schema contract.

Introduction: Cleaning Is Not Optional

Exploratory notebooks often hide cleaning in one-off cells. Production pipelines encode cleaning as tested functions with expected inputs and outputs. The goal is not aesthetic CSVs—it is stable feature distributions and joinable keys across train, validation, and inference.

Definition — Data Normalization (ML Context)

Normalization maps heterogeneous representations to a single canonical form: lowercased emails, UTC timestamps, ISO country codes, consistent currency units. It differs from feature scaling (min-max, z-score), which is applied later for model input.

Example: "USA", "us", "United States" → "US" via a lookup table applied before one-hot encoding.

Profiling Before You Touch Anything

import pandas as pd

df = pd.read_parquet("data/raw/events_20260713.parquet")

print("shape:", df.shape)
print(df.dtypes)
print("duplicates:", df.duplicated().sum())
print("dup on business key:", df.duplicated(subset=["user_id", "event_ts"]).sum())
print(df.isna().mean().sort_values(ascending=False).head())
print(df["country"].value_counts(dropna=False).head(10))

Issue	Symptom	Typical Fix
Exact duplicate rows	`duplicated()` True	`drop_duplicates()`
Logical duplicates	Same user+timestamp, different hash	Dedupe on business key, keep latest
String numbers	object dtype, commas	`to_numeric(errors="coerce")`
Mixed time zones	NaT after parse	`to_datetime(utc=True)`
Category typos	High cardinality noise	Mapping dict + unknown bucket
Whitespace	Join failures	`str.strip()`, `str.lower()`

Handling Duplicates

Blind drop_duplicates() can delete legitimate repeated events (multiple clicks). Define the grain of your table: one row per order, per session, or per impression?

# Keep the most recent record per user-session
df = df.sort_values("event_ts")
df = df.drop_duplicates(subset=["user_id", "session_id"], keep="last")

# Flag duplicates instead of dropping — useful for fraud/anomaly lectures later
df["is_dup"] = df.duplicated(subset=["user_id", "event_ts"], keep=False)

Type Coercion and Validation

df["amount"] = (
    df["amount"]
    .astype(str)
    .str.replace(",", "", regex=False)
    .pipe(pd.to_numeric, errors="coerce")
)

df["event_ts"] = pd.to_datetime(df["event_ts"], utc=True, errors="coerce")
df["user_id"] = df["user_id"].astype("string")

# Fail fast if too many rows became invalid
bad_amount_rate = df["amount"].isna().mean()
assert bad_amount_rate < 0.01, f"amount parse failure rate {bad_amount_rate:.2%}"

Volume 03 Bridge Regex can preprocess strings before Pandas coercion—e.g. extract digits from "$1,299". Prefer explicit to_numeric and assertions over silent coercion that turns garbage into NaN without alerting anyone.

Normalization Patterns

Text Normalization

Unicode NFKC, strip, case-fold for matching
Canonical product SKUs and email domains
Regex cleanup of phone numbers

Structural Normalization

Long → wide pivots for feature tables
Unit conversion (lbs → kg) with constants
Enum mapping with explicit UNKNOWN

COUNTRY_MAP = {"usa": "US", "united states": "US", "uk": "GB"}

def normalize_country(series: pd.Series) -> pd.Series:
    base = series.astype("string").str.strip().str.lower()
    return base.map(COUNTRY_MAP).fillna("UNKNOWN")

df["country_iso"] = normalize_country(df["country"])
df["email_norm"] = df["email"].str.strip().str.lower()

Common Misconception: “Cleaning once in a notebook is enough for production.”

Reality: Live data drifts. Encode cleaning in versioned pipeline steps with monitoring on null rates, category cardinality, and parse failures.

Common Misconception: “Drop all rows with any missing value.”

Reality: Listwise deletion can bias datasets (missing not at random). Module 4.1’s Missing Values lecture covers principled imputation; cleaning focuses on fixable errors, not blanket drops.

Schema Contract

Publish expected columns and dtypes for consumers (labeling tools, ETL, trainers). A lightweight contract prevents breaking changes.

SCHEMA = {
    "user_id": "string",
    "event_ts": "datetime64[ns, UTC]",
    "amount": "float64",
    "country_iso": "string",
}

for col, expected in SCHEMA.items():
    assert col in df.columns, f"missing column {col}"
    if expected.startswith("datetime"):
        assert pd.api.types.is_datetime64_any_dtype(df[col])
    else:
        assert str(df[col].dtype) == expected

Knowledge Check

Short Answer: Difference between exact and logical duplicates? Answer: Exact = full row match; logical = same business key, possibly different other fields.
True/False: keep="last" in drop_duplicates retains the final row after sorting. Answer: True.
Multiple Choice: Parse "1,234" to float: (a) astype(float) alone, (b) remove commas then to_numeric, (c) regex only, (d) ignore. Answer: (b).
Short Answer: Why normalize country codes before encoding? Answer: Prevents split categories for the same entity.
True/False: Cleaning functions should be reapplied identically at inference time. Answer: True.
Multiple Choice: ML normalization in this lecture means: (a) z-score scaling, (b) canonical representation, (c) deleting outliers, (d) labeling. Answer: (b).
Short Answer: What does errors="coerce" do in to_numeric? Answer: Invalid values become NaN instead of raising.
True/False: Assertions on parse failure rates belong in production pipelines. Answer: True.
Multiple Choice: After cleaning, next module topic for supervised targets: (a) ETL only, (b) Data Labeling, (c) GPU tuning, (d) HTML. Answer: (b).
Short Answer: Name one column profile stat you should log nightly. Answer: Null rate, cardinality, or duplicate count (any valid).

Key Takeaways

Profile duplicates, dtypes, and categories before transforming.
Deduplicate on business keys, not arbitrary full rows.
Coerce types with validation; fail when parse error rates spike.
Normalize representations to stable categories and join keys.
Next: Data Labeling for supervised targets on clean tables.

Trainer’s Guide

Dirty data challenge: Provide a CSV with duplicate sessions, mixed date formats, and country typos. Students deliver a cleaned Parquet file plus a one-page schema contract.

Pair with Volume 03: Ask students to add a regex step for phone normalization before astype.

What’s Next Clean tables still need labels for supervised learning—continue to Data Labeling.