Pandas cleaning patterns that scale past the toy example

A cleaning notebook has forty cells. Cell 12 renames a column, cell 31 renames it back, and the frame is called df_final_v2 by the end. It ran correctly once, on a Tuesday, in an order nobody wrote down. Rerun it today and the revenue total is different, and the only way to find out why is to read all forty cells. A few patterns turn that into something you can rerun and believe.

One pipeline, top to bottom

Method chaining forces cleaning steps into an explicit order and eliminates the stale-intermediate problem — there is no df2 to accidentally use:

clean = (
    pd.read_csv("orders.csv")
    .rename(columns=str.lower)
    .assign(
        created_at=lambda d: pd.to_datetime(d.created_at, utc=True),
        amount=lambda d: pd.to_numeric(d.amount, errors="coerce"),
        country=lambda d: d.country.str.strip().str.upper(),
    )
    .query("amount > 0")
    .drop_duplicates(subset=["order_id"], keep="last")
    .pipe(validate_orders)          # more on this below
)

assign returns a new frame instead of mutating, lambda d: refers to the frame at that point in the chain, and pipe lets you slot in any custom function without breaking the flow. Rerunning the whole thing is one execution of one expression — notebook state can’t corrupt it.

The bug that chain hides

That pipeline has a hole in it, and it is the most expensive kind: it produces a plausible number that is wrong.

Picture orders.csv with 84,000 rows from a payments export. A slice of it — the rows written by the European billing entity — formats amounts as "1.234,56": dot for thousands, comma for decimals. pd.to_numeric cannot parse those, and errors="coerce" is doing exactly what it was told: it turns each one into NaN. The next line, .query("amount > 0"), then drops them, because NaN > 0 is False. No exception, no warning. The revenue total that comes out the far end is short by every euro that entity ever billed, and it looks entirely reasonable on a dashboard.

Two habits catch this. The first is a row-count ledger. A tap that prints and passes the frame through slots anywhere in a chain:

def tap(d: pd.DataFrame, label: str) -> pd.DataFrame:
    print(f"{label:<22} rows={len(d):>8,}  cols={d.shape[1]}")
    return d

Drop it after each destructive step — the ones that can remove rows — and the chain narrates itself:

loaded                 rows=  84,000  cols=12
after type parsing     rows=  84,000  cols=12
after amount > 0       rows=  80,858  cols=12
after dedupe           rows=  80,412  cols=12

3,142 rows disappeared at the amount > 0 step. Sometimes that is correct — refunds and zero-value orders are real. Here it is not, and the ledger is what makes the question askable at all.

The second habit is to stop coercing silently. Wrap the coercion so it audits itself:

def coerce_numeric(s: pd.Series, name: str, max_bad: float = 0.01) -> pd.Series:
    out = pd.to_numeric(s, errors="coerce")
    bad = out.isna() & s.notna()          # was a value, became NaN
    if bad.mean() > max_bad:
        raise ValueError(
            f"{name}: {bad.sum():,} of {len(s):,} values failed to parse "
            f"({bad.mean():.1%}). Sample: {s[bad].head(3).tolist()}"
        )
    return out

Note out.isna() & s.notna(): it counts only values that were something and became nothing. Cells that were already blank are missing data, not parse failures, and conflating the two makes the threshold meaningless. Run it on the export above and the failure is no longer silent — it is a stack trace naming the column, the count, and three offending values, which is enough to write the fix without opening the file:

.assign(
    amount=lambda d: coerce_numeric(
        d.amount.str.replace(".", "", regex=False)
                .str.replace(",", ".", regex=False),
        "amount",
    ),
)

One percent is a starting threshold, not a law. Set it per column: an amount column should be near zero, while a free-text field that occasionally holds numbers can tolerate much more.

Vectorize the string fixing

Row-wise apply on strings is slow and usually unnecessary. The .str accessor covers most real cleaning:

df["phone"] = (
    df.phone.str.replace(r"[^\d+]", "", regex=True)
            .str.replace(r"^00", "+", regex=True)
)
df["email_domain"] = df.email.str.lower().str.split("@").str[-1]

The other habitual crime is fixing categories one by one. Map them in one shot, and make the unmapped visible instead of silently passing through:

mapping = {"UK": "GB", "U.K.": "GB", "England": "GB", "Deutschland": "DE"}
df["country"] = df.country.replace(mapping)
unknown = set(df.country.dropna()) - set(VALID_ISO_CODES)
assert not unknown, f"unmapped countries: {unknown}"

The .dropna() matters. Without it, one null country puts nan in the failure message, which sends the reader hunting for a country code that does not exist. Nulls are a separate question with a separate answer — check them on their own line, so the two failures never get confused.

That assert is the difference between a cleaning script and a cleaning process: the next weird value fails loudly at the right line instead of becoming a wrong number in a report.

Make the frame prove it’s clean

Scatter cheap invariant checks at the boundaries — after loading and after cleaning. Five lines catches most upstream surprises:

def validate_orders(d: pd.DataFrame) -> pd.DataFrame:
    assert d.order_id.is_unique, "duplicate order_ids survived"
    assert d.created_at.notna().all(), "unparseable dates"
    assert (d.amount < 100_000).all(), "amount outlier — new currency bug?"
    assert d.created_at.max() <= pd.Timestamp.now(tz="UTC"), "future orders"
    return d

Because it takes and returns a frame, it drops straight into the chain via .pipe(validate_orders). When upstream changes — and it will — you find out at load time, not in a meeting.

Small habits that pay compound interest

• Parse types at the border. Dates, numerics, and categories should be correct in the first ten lines, not coerced ad hoc throughout the file.
• errors="coerce", then count. Coercion turns garbage into NaN; follow it with a check on how many NaNs appeared. Two bad rows is data; forty percent is an upstream incident.
• Keep raw immutable. The original file or query result is never edited. Every transformation lives in code, so every number is reproducible from source.
• Name the semantics, not the history. orders_deduped beats df3. If you can’t name what a frame is, the pipeline has too many stages.

Where chaining stops being the answer

Two limits are worth knowing before you hit them.

The first is memory. Every assign builds a new frame — pandas’ copy-on-write rules decide how much is actually duplicated — and a long chain over a wide table can hold several copies at once. On a few hundred thousand rows this is invisible. On tens of millions it is the reason the kernel dies, and the fix is usually not a cleverer chain — it is doing the filtering and the joins in the database instead, and handing pandas a result that already fits comfortably.

The second is control flow. A chain is one expression, so it cannot branch, loop, or retry. When cleaning genuinely depends on the data — different parsing per source system, a lookup that may fail — force it into a chain and you get a lambda nobody can read. Write a named function that takes a frame and returns a frame, put the branching inside it where a debugger can reach, and call it with .pipe. The chain stays flat; the complexity gets a name and a place to live.

None of this is clever. That’s the point — cleaning code is read far more often than it’s written, usually by someone (future you) trying to figure out why a number changed. Write it as one legible pipeline that checks its own work, and that investigation takes minutes instead of days.