Data Concepts

Data Cleaning

The same mess arrives again next week. So write every fix down as a rule the data has to pass, and your cleaning turns into something you can trust twice.

A messy column passes through normalise, validate and resolve, losing rows at every stage, and the assertion gate is what makes that loss reproducible next week

Why Does This Exist?

Friday afternoon, and the numbers finally reconcile. Twenty-two notebook cells did it: a .str.replace here, a dropna there, two cells run twice and one run out of order. Monday the vendor sends the next file and none of it applies.

One move changes that. Stop writing fixes and start writing assertions. Every correction taught you a rule the data has to satisfy: age between 0 and 120, currency one of three values, ended_at never before started_at. Write the rule in code, beside the fix.

Now the script tells you when next week's file breaks a rule you already knew about. That's the gap between a notebook and a contract.

Think of It Like This

The goods-in bay at a restaurant

Two kitchens, same supplier.

In the first, the chef opens each crate, notices the lemons are limes, shrugs, and adapts the dish. Every week, the same shrug. Nothing gets written down, so the pastry chef meets it again on Thursday and nobody can say how many crates were wrong last month.

The second kitchen has a clipboard at the bay. Weight matches the invoice. Temperature under 5°C. Box labelled with the crop it actually contains. Fail a line and the crate goes back with a note.

The clipboard isn't more work than the shrug. It's the same knowledge, written where it runs every time.

How It Actually Works

Mess comes in a handful of shapes. Learn them and you stop meeting them one at a time.

Type errors — a price arriving as "$1,299.00", a string carrying a currency symbol and a thousands separator. Or a signup_date column holding 2024-03-07, 07/03/2024 and March 7, 2024 at once, the middle one ambiguous between March and July.
Unit inconsistency — kilograms beside pounds, °C beside °F, cents from the payments API and dollars from the CSV. No null, no error, and 70 means two things. Run descriptive statistics per source system; a bimodal histogram is the only tell you get.
Representational duplicates — "USA", "U.S.A.", "united states", "USA " with a trailing space, and a Cyrillic capital A that renders identically to the Latin one. Six values, one country, every group-by wrong.
Impossible values — ages of -3 and 999, timestamps at 1970-01-01 (a null integer read as a Unix epoch) and 2099-12-31 ("no expiry"), percentages of 140. Cheapest to catch: the rule is a range.
Contradictions across columns — ended_at before started_at, or a total that isn't the sum of its lines. Every cell plausible, the row impossible, and no single-column check finds it.
Referential breaks — an order pointing at a customer_id that no longer exists. The join drops the row silently and revenue is short until quarter end.

Order matters more than technique

Normalise before you dedupe. "USA" and "usa " aren't equal to any deduplication routine, so a dedupe run first finds nothing and reports success. Trim, case-fold, normalise Unicode, map synonyms, then look for duplicates — deduplication at scale assumes you already did.

Normalise before you call anything missing. "N/A" isn't a gap, it's a sentinel: convert it deliberately, count it, and hand only what's left to missing data.

Training and serving need identical rules

A cleaning rule that lives only in your notebook is a skew bug with a delay on it. Training saw prices stripped and converted to dollars; serving reads the raw field from an API. Same record, different number, no warning. That's online/offline skew. One implementation, imported by both paths.

Worked example

One amount column, eight rows, two upstream systems:

["$1,200.00", "1200", "1,200.00", "$1,200.00 ", "120000", "980", "n/a", "-45"]

Normalise. Trim, drop separators, move the currency symbol to its own column. Eight in, eight out.
Sentinels. "n/a" is neither a number nor a zero, so it becomes explicitly missing. Seven numeric.
Units. 120000 came from the payments feed in cents, so it's 1200.00. Count unchanged, and skipping it raises nothing.
Assert. amount > 0 rejects the -45. Six pass.
Resolve. Five rows now read 1200.0, so they're one order arriving twice and collapse to one. Two distinct.

8 → 7 → 6 → 2, and step 5 was impossible at step 1.

Show Me the Code

1import numpy as np2
3raw: list[str] = ["$1,200.00", "1200", "1,200.00", "$1,200.00 ", "120000", "980", "n/a", "-45"]4in_cents: set[int] = {4}  # the one row that arrived from the payments feed5
6def to_amount(cell: str, cents: bool) -> float:7    text = cell.strip().lstrip("$").replace(",", "")8    try:9        value = float(text)10    except ValueError:11        return float("nan")  # unparseable, and about to be counted rather than hidden12    return value / 100 if cents else value13
14values: np.ndarray = np.array([to_amount(c, i in in_cents) for i, c in enumerate(raw)])15print(len(values), int(np.isnan(values).sum()))    # -> 8 1  one sentinel, counted16clean: np.ndarray = values[~np.isnan(values)]17print(len(clean), float(clean.min()))              # -> 7 -45.0  one impossible row left18passed: np.ndarray = clean[clean > 0]              # the assertion, written as code19print(len(passed), sorted(set(passed.tolist())))   # -> 6 [980.0, 1200.0]

Every print is a count. 8 → 7 → 6 tells you when the file changed shape; a tidy array tells you nothing.

Watch Out For

Cleaning interactively and never turning it into code

The fixes work. They're also spread across twenty-two cells that have to run in an order the notebook doesn't record.

So the number in your report can't be reproduced, because the state that produced it is gone. And the same mess arrives in the next file, so everything you learned gets rediscovered.

The fix isn't discipline, it's a file: one function per rule, one test per function, a printed count at each stage.

Silent coercion turning a formatting problem into a missing-data problem

pd.to_numeric(column, errors="coerce") is the fastest way to make a messy column numeric, and a trap with a friendly interface.

Every value it can't parse becomes NaN. "1,200" becomes NaN. A typo becomes NaN. You hold a clean float column, a pile of missing values that were never missing, and no count of them — then someone imputes the median over the top.

Coercion is fine as a deliberate last step. What makes it a bug is not counting. Compare the null count before and after, then print the values you destroyed: if that list holds "1,200", your problem was a separator.

The Quick Version

Cleaning as a pile of notebook fixes is worth nothing next week. Write each fix as an assertion and the script becomes a contract.
Six shapes of mess: types, units, representation, impossible values, cross-column contradictions, referential breaks.
Unit inconsistency is the dangerous one. No null, no error, one number meaning two things.
Normalise representations before you dedupe, or the duplicates stay invisible and the dedupe reports success.
Convert sentinels before counting missing values. "N/A" is not a gap.
The same cleaning code runs at training and at serving. One implementation, two callers.
Print a count at every stage, and never let errors="coerce" destroy values silently.