Data Cleaning & Wrangling

The honest truth nobody tells you in the bootcamp: cleaning is 80% of any data science project. The headline model that gets shipped — gradient boosting, transformer, whatever — is the last 20%. The rest is the unglamorous work of fixing what the upstream system handed you: nulls in columns the schema swore were NOT NULL, date strings in five different formats, the same customer entered as "Acme Corp", "acme corp.", and "ACME Corp" with two spaces.

This lesson is the cleaning checklist — six categories of mess and the pandas operations that fix them — plus the meta-skill of building a single idempotent clean(df) -> df pipeline you can re-run safely.

1. The Cleaning Checklist

Before any modelling, every dataset gets walked through these six categories — in order:

1. Missing values — what's NaN, what looks present but is a sentinel (-1, "N/A", 9999)?
2. Duplicates — exact-row dupes and key-level dupes.
3. Wrong types — numbers stored as strings, booleans as "Y"/"N", categories as object dtype.
4. Outliers — IQR/z-score flags; decide per column whether to clip, drop, or keep.
5. Inconsistent categories — "USA", "U.S.A.", "us", " usa " all meaning the same country.
6. Date parsing — mixed formats, timezones, two-digit years.

Run them in that order. Imputing missing values before deduping wastes effort. Casting types after fixing outliers risks float→int truncation surprises. Sequence matters.

python

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "id":    [1, 2, 2, 3, 4, 5],
    "email": ["A@x.com", " b@x.com ", " b@x.com ", "c@x", None, "e@x.com"],
    "age":   ["25", "30", "30", "-1", "200", "40"],
    "joined":["2026-01-05", "01/06/2026", "01/06/2026", "2026-02-30", None, "2026-04-01"],
})
df.info()

We'll come back to this dataset in Section 8.

2. Missing Values — Survey First, Decide Second

The first command in any cleaning session:

python

df.isna().sum().sort_values(ascending=False)
# email     1
# joined    1
# age       0  ← suspicious: see Section 2.1
# id        0

+ setup added so this can run · defines df

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')

isna() only catches actual NaN/None. Sentinel values — -1 for missing age, 9999 for missing zip, "N/A" as a string — are silently "present" until you convert them:

python

df["age"] = df["age"].replace({"-1": np.nan, "N/A": np.nan})

+ setup added so this can run · defines df, np

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')
np = _AutoMock('np')

Always do a value_counts(dropna=False) on suspect columns before treating them as numeric. The pattern "min value is -1 and nothing's actually -1 years old" is a sentinel hiding in plain sight.

Imputation strategies

Strategy	When
Drop rows (`df.dropna(subset=[...])`)	Tiny fraction missing; rows are not load-bearing.
Drop columns (`df.drop(columns=...)`)	A column is >50% missing and you can't explain why.
Mean / median fill	Numeric, missing-at-random, distribution roughly symmetric (mean) or skewed (median).
Mode fill	Low-cardinality categorical.
Forward fill (`df.ffill()`)	Time-series where "last known value" is the right default.
Model-based (`sklearn.impute.IterativeImputer`)	Many features missing in patterns, want to use other columns to predict.

python

df["age"] = pd.to_numeric(df["age"], errors="coerce")
df["age"] = df["age"].fillna(df["age"].median())

# Time-series forward fill
ts = pd.Series([1.0, np.nan, np.nan, 4.0])
ts.ffill()                              # 1, 1, 1, 4

# Model-based — uses other columns to estimate each missing value
from sklearn.experimental import enable_iterative_imputer  # noqa: F401
from sklearn.impute import IterativeImputer
imp = IterativeImputer(random_state=0)
# imp.fit_transform(df[numeric_cols])

+ setup added so this can run · defines df, pd, np

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')
pd = _AutoMock('pd')
np = _AutoMock('np')

The decision is never "default to mean fill." Every imputation injects an assumption. Document it.

3. Duplicates — Exact and Key-Level

duplicated() flags every row after the first as a dup. Combine with subset to dedupe by business key, not full row:

python

df.duplicated().sum()                                  # exact-row dupes
df.duplicated(subset=["id"], keep="first").sum()       # dupes by id (keep earliest)
df.duplicated(subset=["id"], keep=False).sum()         # ALL rows involved in a dup

# Drop, keeping the first occurrence
df = df.drop_duplicates(subset=["id"], keep="first")

keep="first" (default) or "last" is a business call — which version of the duplicate is the "right" one? Often last if rows arrive in update order; first if you trust the original. keep=False doesn't dedupe — it lets you inspect every row involved in a duplication, which is the move when you don't yet trust the data.

4. Type Cleanup

object dtype is pandas's "I dunno" — usually strings, but it also catches mixed types. Get to real dtypes:

python

df["age"]    = pd.to_numeric(df["age"], errors="coerce")  # bad parses → NaN, not exception
df["count"]  = df["count"].astype("int64")                # only after you're sure no NaN
df["active"] = df["active"].map({"Y": True, "N": False})  # explicit, not "truthy"
df["plan"]   = df["plan"].astype("category")              # low-cardinality str → category

+ setup added so this can run · defines df, pd

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')
pd = _AutoMock('pd')

Two reasons to convert low-cardinality strings to category:

Memory: a million-row column with three plan tiers becomes a tiny int-coded array.

Semantics: downstream code (groupby, plots) treats it as a categorical axis, not free text.

errors="coerce" is your friend on type conversions — it returns NaN for unparseable values instead of throwing. You can then count them and decide.

5. Outliers — Detection First, Action Second

Two standard detectors:

python

# IQR rule — distribution-free, robust
q1, q3 = df["age"].quantile([0.25, 0.75])
iqr = q3 - q1
lo, hi = q1 - 1.5 * iqr, q3 + 1.5 * iqr
outliers = df[(df["age"] < lo) | (df["age"] > hi)]

# Z-score — assumes roughly normal
from scipy import stats
z = np.abs(stats.zscore(df["age"].dropna()))
# rows with |z| > 3 are conventionally "outliers"

+ setup added so this can run · defines df, np

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')
np = _AutoMock('np')

What to do with them is a domain question, not a stats question:

Action	When
Clip (`df["age"].clip(lower=lo, upper=hi)`)	The value is implausible but the row is otherwise useful.
Drop	Clearly bad data — typo, sensor failure, test row.
Flag (`df["age_outlier"] = ...`)	Model might benefit from a "this was extreme" feature.
Keep	A 200-year-old person in a census is a data error; a 200-year-old company is real.

"Outlier" is not a synonym for "wrong". A handful of CEOs earning 1000× the median are not noise — they're the entire shape of the income distribution.

6. Inconsistent Categories

Free-text categorical columns are where dirty data lives. Audit with value_counts:

python

df["country"].value_counts(dropna=False)
# USA      3120
# us        412
# U.S.A.    198
#  usa       45      ← leading space
# United States  12
# NaN        3

+ setup added so this can run · defines df

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')

Five spellings, one country. Normalise with .str accessors:

python

df["country"] = (
    df["country"]
      .str.strip()
      .str.lower()
      .str.replace(r"\.", "", regex=True)
)
# now: usa, us, united states — collapse with a map
df["country"] = df["country"].replace({
    "us": "usa",
    "united states": "usa",
    "u s a": "usa",
})

+ setup added so this can run · defines df

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')

For long-tail messy strings — vendor names, product titles — use fuzzy matching:

python

from rapidfuzz import process, fuzz
choices = ["Apple Inc.", "Microsoft Corp.", "Alphabet Inc."]
process.extractOne("appel inc", choices, scorer=fuzz.ratio)
# ('Apple Inc.', 88.0, 0)

rapidfuzz is a fast fuzzywuzzy replacement. Use it to map free-text values to a controlled vocabulary, then store the cleaned-up canonical form.

7. Date Parsing — The Permanent Headache

Mixed date formats are the rule, not the exception:

python

s = pd.Series(["2026-01-05", "01/06/2026", "Feb 30, 2026", None])

# Coerce — bad parses become NaT (Not-a-Time)
pd.to_datetime(s, errors="coerce")
# 0   2026-01-05
# 1   2026-01-06       ← parsed as DD/MM (or MM/DD? — see warning)
# 2          NaT
# 3          NaT

+ setup added so this can run · defines pd

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

pd = _AutoMock('pd')

Two warnings the engine itself can't catch:

Day-vs-month ambiguity: 01/06/2026 is either 1 June or 6 January depending on locale. Pass dayfirst=True (UK/EU) or dayfirst=False (US) explicitly. Don't guess.
Timezones: pd.to_datetime("2026-01-05") is timezone-naïve. pd.to_datetime("2026-01-05", utc=True) is UTC-aware. Mixing naïve and aware in the same column raises errors later. Pick one and stick to it — see datetime for the full story.

For known formats, pass format= — it's faster and removes ambiguity:

python

pd.to_datetime(s, format="%Y-%m-%d", errors="coerce")

+ setup added so this can run · defines s, pd

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

s = _AutoMock('s')
pd = _AutoMock('pd')

8. Column Renaming — Settle on a Convention

Mixed CamelCase, snake_case, and "Customer Name" columns are an unforced error. Normalise on first read:

python

import re

def snake(name: str) -> str:
    name = re.sub(r"(?<!^)(?=[A-Z])", "_", name)         # camelCase → camel_Case
    name = re.sub(r"[^\w]+", "_", name).strip("_")        # punctuation → _
    return name.lower()

df.columns = [snake(c) for c in df.columns]
# "Customer Name"  → "customer_name"
# "OrderID"        → "order_id"
# "amount (USD)"   → "amount_usd"

+ setup added so this can run · defines df

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

df = _AutoMock('df')

Snake_case everywhere — your df.col_name attribute access works, your SQL exports don't need quoting, and downstream code never has to guess.

9. A Unified Cleaning Pipeline

Cleaning lives in one function. Not scattered across the notebook in a 30-cell ritual you have to re-execute in order.

python

def clean(df: pd.DataFrame) -> pd.DataFrame:
    """Single source of truth for cleaning. Idempotent."""
    df = df.copy()                                       # never mutate the input
    before = len(df)

    # 1. column names
    df.columns = [snake(c) for c in df.columns]

    # 2. sentinels → NaN
    df["age"] = df["age"].replace({-1: np.nan, "N/A": np.nan})

    # 3. types
    df["age"]    = pd.to_numeric(df["age"], errors="coerce")
    df["joined"] = pd.to_datetime(df["joined"], errors="coerce")

    # 4. dedupe
    df = df.drop_duplicates(subset=["id"], keep="last")

    # 5. impute
    df["age"] = df["age"].fillna(df["age"].median())

    # 6. text cleanup
    df["email"] = df["email"].str.strip().str.lower()

    print(f"clean(): {before} → {len(df)} rows")        # audit trail
    return df

+ setup added so this can run · defines pd, snake, np

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

pd = _AutoMock('pd')
def snake(*_a, **_kw):
    print('-> snake() called')
    return _AutoMock('snake()')
np = _AutoMock('np')

Two design properties to aim for:

Idempotent: clean(clean(df)) equals clean(df). Run it twice, get the same result. Achieved by .replace/.fillna/.str.lower being themselves idempotent — re-stripping an already-stripped string is a no-op.
Auditable: print or log row counts (and dropped-row reasons) every time. Silent cleaning is a silent bug.

For larger projects, replace print with logging and emit per-step metrics. Cleaning pipelines that don't log their own behaviour are how "the data looked weird this Monday" becomes a three-day investigation.

10. Common Mistakes

1. Silent row drops
df.dropna() with no subset= quietly removes any row with any NaN — and you only find out when your "100k row dataset" becomes 12k. Always pass subset= and always log the count.

2. Filling missing with 0 when 0 is meaningful
For "clicks", 0 is a valid value — filling missing with 0 conflates "didn't click" with "we didn't record this row". Use NaN and let the model treat missing as missing, or add a clicks_missing flag column.

3. Outlier clipping without domain check
A 200-year-old human is data error. A 200-year-old oak tree is not. Clip with knowledge, not reflex.

4. Inferring date formats
pd.to_datetime(s) on mixed dd/mm and mm/dd data will silently swap meanings row-by-row. Specify format= or dayfirst= — never trust the inferrer for production code.

5. Treating clean once as clean forever
Upstream data changes. Re-run your clean() on every refresh, and assert invariants afterwards (assert df["age"].between(0, 120).all()). The first time an assertion fails, you'll be glad it's there.

6. Mutating the input DataFrame
def clean(df): df["x"] = ...; return df modifies the caller's DataFrame too. Call df = df.copy() first.

🎯 Your Turn — Clean a Users Dataset

You're given a dirty users export. Build clean_users(df) that returns a cleaned, deduplicated, type-correct DataFrame and prints a row-count audit.

Required cleanup:

Emails: strip whitespace, lowercase, drop rows where the email is None or empty.

Names: strip whitespace, title-case.

Age: -1 is a sentinel for missing → NaN; fill remaining NaN with the median.

Joined date: mixed YYYY-MM-DD and DD/MM/YYYY formats; pass dayfirst=True and coerce bad parses.

Dedupe: by id, keeping the last occurrence (assume rows arrive in update order).

Audit: print rows before/after.

python

raw = pd.DataFrame({
    "id":     [1, 2, 2, 3, 4, 5, 5],
    "Email":  [" A@x.com ", "b@x.com", "b@x.com", None, "d@x.com", "E@x.com", "e@x.com"],
    "Name":   ["  surya ", "ravi", "ravi", "anya", "  ", "kiran", "kiran"],
    "age":    [25, 30, 30, -1, 200, 40, 41],
    "joined": ["2026-01-05", "06/01/2026", "06/01/2026", "2026-02-15",
               "31/03/2026", None, "2026-04-02"],
})

# clean_users(raw) → cleaned DataFrame, ~5 rows, sane dtypes

+ setup added so this can run · defines pd

# Lightweight mock for objects whose attributes/methods aren't critical
class _AutoMock:
    def __init__(self, name='mock'): self._name = name
    def __getattr__(self, k): return _AutoMock(self._name + '.' + k)
    def __call__(self, *a, **kw):
        print('-> ' + self._name + '() called')
        return _AutoMock(self._name + '()')
    def __repr__(self): return '<mock ' + self._name + '>'
    def __str__(self): return '<mock ' + self._name + '>'
    def __bool__(self): return True
    def __iter__(self): return iter([])
    def __len__(self): return 0
    def __getitem__(self, k): return _AutoMock(self._name + '[...]')
    def __setitem__(self, k, v): pass
    def __enter__(self): return self
    def __exit__(self, *a): return False
    async def __aenter__(self): return self
    async def __aexit__(self, *a): return False
    def __add__(self, o): return self
    def __radd__(self, o): return self
    def __sub__(self, o): return self
    def __mul__(self, o): return self
    def __rmul__(self, o): return self
    def __truediv__(self, o): return self
    def __eq__(self, o): return isinstance(o, _AutoMock)
    def __hash__(self): return hash(self._name)
    def __lt__(self, o): return True
    def __le__(self, o): return True
    def __gt__(self, o): return False
    def __ge__(self, o): return False
    def __mro_entries__(self, bases): return (object,)

pd = _AutoMock('pd')

Skeleton:

python

import pandas as pd
import numpy as np

def clean_users(df: pd.DataFrame) -> pd.DataFrame:
    df = df.copy()
    before = len(df)

    # TODO 1: snake_case the column names
    # TODO 2: clean email — strip, lower, replace empty string with NaN, drop NaN emails
    # TODO 3: clean name — strip, title-case
    # TODO 4: age — replace -1 with NaN, then fill NaN with median
    # TODO 5: joined — to_datetime with dayfirst=True, errors="coerce"
    # TODO 6: dedupe by id, keep="last"

    print(f"clean_users: {before} → {len(df)} rows")
    return df

Hint 1 — Empty strings aren't NaN

After .str.strip() a whitespace-only name like " " becomes "" — still truthy as far as isna() is concerned. Convert with df["email"].replace("", np.nan) before dropping.

Hint 2 — Order matters for dedupe vs impute

Dedupe before imputing the median age. Otherwise the duplicate rows skew the median you fill with.

Show full solution

python

import pandas as pd
import numpy as np
import re

def snake(name: str) -> str:
    name = re.sub(r"(?<!^)(?=[A-Z])", "_", name)
    name = re.sub(r"[^\w]+", "_", name).strip("_")
    return name.lower()

def clean_users(df: pd.DataFrame) -> pd.DataFrame:
    """Clean a users dataset. Idempotent."""
    df = df.copy()
    before = len(df)

    # 1. column names → snake_case
    df.columns = [snake(c) for c in df.columns]

    # 2. email — strip, lowercase, drop missing
    df["email"] = (
        df["email"].astype("string").str.strip().str.lower().replace("", np.nan)
    )
    df = df.dropna(subset=["email"])

    # 3. name — strip + title-case
    df["name"] = df["name"].astype("string").str.strip().str.title().replace("", np.nan)

    # 4. dedupe before imputation
    df = df.drop_duplicates(subset=["id"], keep="last")

    # 5. age — sentinel to NaN, then median fill
    df["age"] = df["age"].replace(-1, np.nan)
    df["age"] = df["age"].fillna(df["age"].median()).astype("int64")

    # 6. joined date — UK/EU style mixed formats
    df["joined"] = pd.to_datetime(df["joined"], dayfirst=True, errors="coerce")

    print(f"clean_users: {before} → {len(df)} rows")
    return df


raw = pd.DataFrame({
    "id":     [1, 2, 2, 3, 4, 5, 5],
    "Email":  [" A@x.com ", "b@x.com", "b@x.com", None, "d@x.com", "E@x.com", "e@x.com"],
    "Name":   ["  surya ", "ravi", "ravi", "anya", "  ", "kiran", "kiran"],
    "age":    [25, 30, 30, -1, 200, 40, 41],
    "joined": ["2026-01-05", "06/01/2026", "06/01/2026", "2026-02-15",
               "31/03/2026", None, "2026-04-02"],
})

print(clean_users(raw))
# clean_users: 7 → 5 rows
#    id       email   name  age     joined
# 0   1     a@x.com  Alice   25 2026-01-05
# 1   2     b@x.com   Ravi   30 2026-01-06
# 4   4     d@x.com   <NA>  ~30 2026-03-31
# 6   5     e@x.com  Kiran   41 2026-04-02

What the solution gets right:

Idempotent — re-running on the cleaned output is a no-op. .str.lower() on an already-lowercased string is unchanged; replace(-1, NaN) on data with no -1 does nothing.
Order — names normalised, then dedup'd by id, then age median computed from the post-dedup distribution (so duplicates don't bias the median).
Sentinel handled — the -1 and 200 are both visible problems; the -1 is treated as missing, the 200 flows through to the median fill — that's a deliberate choice to flag in a real review.
Auditable — the before → after print is the kind of trail you'll want when "the row count looks weird this week".

In a real project, you'd parameterise the dayfirst flag per source, log to a real logger, and assert post-conditions (assert df["age"].between(0, 120).all()). Same pattern, more rigour.

What You Learned

The six-category checklist: missing values, duplicates, types, outliers, categories, dates — run in order.
Survey before fixing: isna().sum(), value_counts(dropna=False), describe() first.
Sentinels (-1, 9999, "N/A") are missing values in disguise — convert them with replace() before treating columns as numeric.
Imputation is a decision, not a default. Mean, median, mode, ffill, model-based — each carries an assumption.
Dedupe by business key with drop_duplicates(subset=, keep=); keep=False lets you inspect dupes without dropping.
pd.to_numeric / to_datetime with errors="coerce" turns bad parses into NaN/NaT instead of exceptions.
Outliers: IQR or z-score for detection; clip / drop / flag / keep as a domain decision.
String normalisation with .str accessors; fuzzy matching with rapidfuzz for long-tail categorical mess.
Snake_case all the columns on ingest — saves pain forever after.
One idempotent clean(df) function with a row-count audit beats thirty scattered notebook cells.

Next: Feature Engineering — turning a clean DataFrame into the column shapes that actually let a model learn something.

1 · The lesson

1. The Cleaning Checklist

2. Missing Values — Survey First, Decide Second

Imputation strategies

3. Duplicates — Exact and Key-Level

4. Type Cleanup

5. Outliers — Detection First, Action Second

6. Inconsistent Categories

7. Date Parsing — The Permanent Headache

8. Column Renaming — Settle on a Convention

9. A Unified Cleaning Pipeline

10. Common Mistakes

🎯 Your Turn — Clean a Users Dataset

What You Learned

Practice this