Every data pipeline I've inherited starts the same way: someone exported a CSV from a tool that shall remain nameless, and the export is a mess. Not a hard mess — a boring one. That's what makes it annoying: you fix the same four things every single time.
The four mess types (and the 10-line fix)
import pandas as pd
df = pd.read_csv("messy_export.csv", dtype=str) # read as strings first — see gotcha #1
df.columns = (df.columns.str.strip()
.str.lower()
.str.replace(r"\s+", "_", regex=True)) # headers nobody agrees on
df = df.apply(lambda s: s.str.strip()) # trailing/leading spaces
df = df.drop_duplicates() # the classic
for c in ("date", "order_date", "created_at"): # whatever your date cols are
if c in df.columns:
df[c] = pd.to_datetime(df[c], errors="coerce", dayfirst=False)
df.to_csv("clean.csv", index=False)
That's 90% of it. The remaining 10% is where the gotchas live.
Three gotchas that silently corrupt your data
1. Letting pandas guess types on read. If a column has "1,234" in row 500, pandas decides the whole column is a string — or worse, silently NaNs it. Read everything as dtype=str, clean, then convert deliberately.
2. Mixed date formats in one column. 2026/03/01 vs 01-03-2026 vs Excel's 45560 serial numbers, sometimes in the same column. pd.to_datetime(..., errors="coerce") turns the unparseable ones into NaT — count them before you trust the column: df[c].isna().mean(). If more than a few percent fail, the column has a format war and you need to handle formats explicitly, not guess.
3. Duplicates that aren't duplicates. " Acme " and "Acme" and "ACME" are the same customer to a human and three rows to drop_duplicates(). Normalize the key columns (casefold + collapse spaces) into a helper column, dedupe on that, drop the helper.
If you don't code (or don't want to babysit formats)
The steps above are exactly what I packaged into the Excel Batch Cleaner — drop in CSV/XLSX/JSON exports, it runs the space-trim, date fixing, dedupe and header normalization in your browser (files never leave your machine), and writes clean files out. No install, no account.
Why it's $1: launch pricing while I collect the first honest reviews — the full cleaner, not a trial. If it misses a mess type you've hit, tell me which pattern and I'll add it. Price goes up once the first reviews are in: https://datafixtools.sell.app/product/excel-batch-cleaner
Sample columns and formats vary by export; always sanity-check row counts and a few converted dates before you trust any cleaning step, mine included.
Top comments (0)