Cleaning real data + week 5 wrap

Almost every tutorial dataset is clean. Iris, MNIST, Boston Housing, Titanic — all curated, all consistent, all ready for sklearn out of the box. They're useful for learning algorithms; they're misleading about the actual job.

Real data is messy. Always. It has:

Mixed-type columns. The 'price' column is sometimes '12.99', sometimes '12,99' (European decimal), sometimes '$12.99', sometimes 'NA', sometimes empty string. Pandas reads this as object dtype, which kills NumPy's speed.

Inconsistent dates. '2024-01-01', '01/01/24', '01-Jan-2024', None, '2024-01-32' (yes, 32, real bug from a real dataset). Each format hides date arithmetic until parsed.

Duplicates that aren't byte-identical. Same person registered twice with slightly different email capitalisation, slightly different phone number formats. Drop_duplicates() catches none of them.

Free-text categories with typos and case. 'New York', 'new york', 'New york', 'NewYork', 'NY' — five strings, one city. Group-bys treat them as five categories.

Extreme outliers from copy-paste. Someone pasted a number with the wrong number of zeros. Now your 'age' column has a 99,000-year-old. The mean is meaningless until you handle it.

Missing values that aren't NaN. The original system used '-1' or '999' or empty string for missing. Pandas reads these as values. Mean and std are computed over them.

The rule that drops out — budget 60% of any data project for cleaning. Anyone who tells you they 'just trained a model' on real data is either lying or working on toy data. Real data starts dirty.

The good news — cleaning is mostly mechanical once you know the patterns. Tomorrow we cover the five I run on every dataset.

After cleaning hundreds of datasets, I've converged on five patterns that I apply almost reflexively at the start of any project. They handle 80% of the mess.

One — strip and lowercase string columns. df['col'].str.strip().str.lower(). Handles trailing whitespace and case-inconsistencies. 'New York ' becomes 'new york'; 'NEW YORK' becomes 'new york'. Now group-bys work correctly.

Two — parse dates centrally. pd.to_datetime(col, errors='coerce'). Handles most format variations automatically. errors='coerce' converts unparseable strings to NaT (NumPy's date-NaN), which lets the rest of the pipeline continue while flagging bad rows.

Three — coerce numeric. pd.to_numeric(col, errors='coerce'). Forces object-dtype columns into numeric. Strings like '12.99' become 12.99; strings like 'unknown' become NaN. Now the column is float, NumPy operations work, summary stats are meaningful.

Four — dedup by composite key. df.drop_duplicates(subset=[...]). The subset matters — passing nothing dedups on all columns (rare to want). Usually you dedup by a meaningful subset like (email, signup_date) or (transaction_id) — only the columns that should be unique.

Five — bucket rare categories. Top-N + 'other'. If a categorical column has 10000 unique values and 9900 of them appear fewer than 5 times each, the long tail is noise. Keep the top 50; map the rest to 'other'. Reduces feature explosion in one-hot encoding; doesn't lose meaningful signal.

These five together tame most messy datasets. Apply them once at the start of every project, BEFORE you start exploratory analysis. The downstream EDA, modelling, and feature engineering all become smoother.

For production pipelines, encode each pattern as a function in your shared utilities. Reuse across projects. The first time you save 30 minutes by importing a function instead of rewriting it, you'll know it was worth the effort.

I keep this function in a shared utilities module. Apply once at the start of every project. Replaces 50 lines of cleaning into 12.

Look at the snippet. The tidy() function takes a DataFrame, returns a cleaned copy.

df.copy() — work on a copy so we don't mutate the caller's DataFrame. Good hygiene.

For object-dtype columns — strip whitespace, lowercase. Two transformations chained on the str accessor. Applies to all string-shaped columns automatically.

For columns with 'date' or 'at' in the name — parse as datetime with coerce. The naming convention 'date_*' or '*_at' for date columns is widespread; this leverages it.

drop_duplicates() at the end — removes exact-duplicate rows. For more sophisticated dedup, replace with drop_duplicates(subset=[...]) for a specific composite key.

The function is intentionally minimal. It handles the common cases. Edge cases — categorical buckets, numeric coercion on specific columns, custom date formats — are project-specific, added on top of the base.

My actual production version has more bells and whistles — logs row count before/after each step, validates that critical columns survived, optionally writes a 'cleanup report' showing what was removed. But the 12-line core is the same.

The broader philosophy — data cleaning is mechanical. Code it once; reuse it. The first time you write 'df.drop_duplicates()' in a new project after typing it 50 times in previous projects, you should hear yourself complaining and refactor it into a shared utility. Save your brain for the project-specific work; let the utility handle the universal.

Version-control your data utilities like you version-control your code. They compound across projects.

There's a class of pandas bug that's hard to catch and easy to ship — a step that silently drops rows. By the time you've chained 10 operations, your million-row DataFrame is now 600k and you don't remember which step did it.

The culprits:

Merges with how='inner' that drop unmatched rows.

Filters that exclude more than expected.

drop_duplicates that fires on a subset broader than intended.

dropna() that loses anything with a missing value in any column.

Any of these can drop 40% of your data with no error, no warning, no flag.

My fix is a tiny @log_shape decorator. Wraps any DataFrame-transforming function. Prints rows-before, rows-after, and the percent change. Five lines of code; saves entire categories of bugs.

The decorator looks like:

import functools

def log_shape(fn):
    @functools.wraps(fn)
    def wrap(df, *args, **kwargs):
        before = len(df)
        out = fn(df, *args, **kwargs)
        after = len(out)
        delta = (after - before) / max(before, 1) * 100
        print(f'{fn.__name__}: {before} → {after} ({delta:+.1f}%)')
        return out
    return wrap

Apply to your pipeline functions. Run the pipeline. Get a log line per step.

For quick exploratory work without decorators, just print(df.shape) liberally between operations. Five extra lines, instant visibility.

When the pipeline goes sideways and you've lost 40% of rows, the log tells you which step cost what. Without it, you're bisecting blind.

In ML, a 'why is my dataset so small' bug is almost always a hidden inner-join or a too-aggressive dropna. The shape decorator catches it the moment it happens.

A five-line decorator. Drop into every data utility module you write.

End of week five. 35 days. 175 posts. We're 39% through the sprint.

The data stack week is in the books. Tomorrow week 6 starts on classical machine learning — linear regression to gradient boosting. The stack we've built this week is the substrate underneath all of it.

What week 5 covered.

NumPy and broadcasting. C-speed numerical operations. Vectorise everything. Five built-in primitives that replace most loops. Broadcasting in one rule (pad and stretch). Cosine similarity in three lines.

Pandas. DataFrame as a dict of NumPy arrays. The five methods that cover 80% of work. Method chaining as the readability default. iterrows as the canonical perf bug.

groupby and merge. Split-apply-combine model. Always specify how= on merges. Named aggregations for clean group-by output. polars when pandas struggles.

Plotting. matplotlib as the engine. Pair plot as the orientation chart for new datasets. fig/axes pattern as the universal API. Sample big data before plotting.

EDA workflow. Seven questions to answer before opening a model. Pandas info/describe/isna trio plus correlation top-10. Leakage detection via correlation > 0.95. Notebook + markdown documentation.

Data cleaning. Five patterns that handle 80% of mess — strip+lower, to_datetime, to_numeric, drop_duplicates, bucket rare categories. Reusable cleaning function in 12 lines. @log_shape decorator for catching silent row drops.

A broader theme — most ML failures are data failures, not modelling failures. The week's investment in data hygiene pays back enormously over the next eight weeks.

Tomorrow, week 6 — classical ML. Linear regression, logistic regression, decision trees, random forests, gradient boosting, k-means, SVMs. The mental model is the same as the data work — pick the right tool for the question.

See you in week 6.