Data Cleaning

Data cleaning

Cleaned data with no record of what changed is worse than the dirty data it came from: nobody can separate a real 40% collapse in orders from a dedup on the wrong key. This is the only stage that mutates data, and it runs only if something does.

Applies to one flat export, twelve related tables, a 4 GB log you never load, a warehouse table queried in place, 500 emails in a folder. Clean in the medium the data lives in — a parquet file, filtered parts, a saved query, or cleaned items plus their index frame, and deriving that frame or a column no source holds is a mutation like any other. Enter here when a cell, a row count or the grain changes, stop here when the cleaned data is the deliverable.

Never edit the source, and never mutate without a log row

The source is the one thing you cannot recreate. Re-read it as text and coerce as data-ingestion did, write beside run/raw/ and never into it, and clean data queried in place with a saved SELECT, never an UPDATE.

Every operation that changes a cell, a row count or the grain appends one row. The rationalization is always "this one is obvious", and a whitespace strip that collapsed two distinct SKUs leaves no trace at all. Scale the trail to the data, never the checks: one small source states each mutation inline in its reply.

import json, os, pandas as pd
df = pd.read_csv("run/raw/orders.csv", dtype="str", keep_default_na=False, na_values=[""])
df["qty"] = pd.to_numeric(df["qty"], errors="coerce").astype("Int64")

def log_change(op, column, rows_affected, rule, before, after):
    """The ONLY writer for run/changelog.jsonl. Copy it verbatim; these six keys
    are the contract, not a suggested shape. Renaming one to `detail`, `rows_in`
    or `rows_out` breaks eda-storytelling's citation check, which keys on `op`."""
    os.makedirs("run", exist_ok=True)
    row = {"op": op, "column": column, "rule": rule, "rows_affected": int(rows_affected),
           "before": int(before), "after": int(after)}
    assert set(row) == {"op", "column", "rule", "rows_affected", "before", "after"}
    with open("run/changelog.jsonl", "a") as fh:
        fh.write(json.dumps(row) + "\n")

Write every edit through .loc

Under pandas 3 copy-on-write, chained assignment only warns, leaves the frame unchanged, and makes the log claim an edit that never happened. Same for inplace=True on a slice.

# ❌ SILENT NO-OP — warns, and qty is still -1 afterwards
df[df["qty"] < 0]["qty"] = 0
# ✅ one step, and the mask gives you the count to log
mask = df["qty"] < 0
df.loc[mask, "qty"] = 0
log_change("clip_to_zero", "qty", mask.sum(), "negative qty is impossible", len(df), len(df))

Every treatment has a default, and a case where the default is wrong

Normalise and coerce before you deduplicate

Both change what counts as equal. Normalise through an explicit dict, asserting set(key.dropna()) - set(MAPPING) is empty first — a .fillna(original) fallback is how "Canada " and "CA" reach the report as two categories.

parsed = pd.to_datetime(df["ordered_at"], errors="coerce", format="ISO8601")
lost = df["ordered_at"].notna() & parsed.isna()       # errors="coerce" nulls silently
print(df.loc[lost, "ordered_at"].head().tolist())     # look before you accept it
df["ordered_at"] = parsed
log_change("to_datetime", "ordered_at", lost.sum(), "ISO8601, bad -> NaT", len(df), len(df))

Then deduplicate — but only where the grain admits it, and the grain says so far more often than the data does.

No key, no dedup. This is a gate, not a preference. You may deduplicate only on a key the user stated or run/manifest.json recorded as the grain. Where the grain is an index — a sensor stream, an event log, a survey — identical rows are data: one device reporting twice in the same second is two readings, and df.drop_duplicates( deletes the second one along with the sampling rate. That the rows are byte-identical is not evidence they are copies; it is what a repeated measurement looks like.

Deciding they were "re-ingested duplicates rather than genuine repeat readings" is not a call available from the data — it needs a key, or the user. Without one: do not drop them, report the count as a finding, and lead with the number computed over every row. Offering both a raw and a deduplicated figure is not a substitute for asking; the headline is the raw one until someone says otherwise.

Where a key does exist, take a tiebreaker, then df.drop_duplicates(, and log the key you used in the rule.

Leave a gap rather than invent a value

Flag outliers; a dropped outlier is a deleted finding

An extreme value is only wrong if it is impossible: a 90,000 order is a fact until someone proves it a typo, and the biggest customer is what a blind 3-sigma filter deletes first. Flag it — (df[c] < q1 - 3*(q3-q1)) | (df[c] > q3 + 3*(q3-q1)), from df[c].quantile([0.25, 0.75]) or df.groupby(circuit)[c] when one frame pools several populations, into a <col>_outlier boolean, before and after equal. Impossible values are the other case: coerce or drop those.

What you write, and who reads it

Only the data changes shape; the log never does. Downstream resolves the data positionally — run/clean.parquet, else run/clean/, else run/clean.sql.

run/changelog.jsonl holds one object per transformation in applied order — {"op", "column", "rows_affected", "rule", "before", "after"} — ending in a terminal {"op": "write_clean", "path": …, "format": …} row that tells the next stage where the data is. rule says why, and the new grain when one changes: "keep earliest created_at per order_id", not "removed duplicates". Anything that changes the grain lands here alone — a df.merge(, a df.pivot_table( whose aggfunc and fill_value you chose, an aggregation of events to sessions — proved not to have fanned out by validate="1:m", or in SQL by an unchanged count(*) and count(distinct …) either side; then re-validate on the new grain. A metric no column holds — churn, retention, a per-item label — is a definition you take from the user and log as the rule.

Re-run data-validation on what you wrote; one treatment routinely breaks another check. Then hand it to exploratory-data-analysis or to modeling, the log going with it for eda-storytelling. You may be the last stage: then end the reply with the literal Stages: … · skipped: … line that skill defines — and on a pass with nothing to treat, say so and pass the source on with the manifest's dtypes.

Common failure modes