Data Cleaning Playbook

Data cleaning is irreversible surgery performed on evidence, which is why it needs an operating order and a written record. This playbook cleans tabular data in a fixed sequence — snapshot, profile, structure, types, missing values, duplicates, outliers, validation — with every transformation logged, every judgment call justified, and the raw file never touched.

When to use this skill

• A CSV, export, or scraped table needs to become analysis-ready
• Merging datasets whose keys, units, or category labels almost-but-don't match
• A pipeline keeps breaking on the same feed, and the fixes live in someone's head
• Auditing cleaning someone else already did, given the raw file and the final file

Instructions

Work the stages in order; each stage assumes the previous ones are done.

1. Snapshot and checksum the raw data. All work happens on a copy. The log records the source, retrieval date, and row and column counts. If you cannot get back to raw, you are not cleaning — you are gambling.
2. Profile before touching anything: per-column types-as-stored, missing rates, distinct counts, min and max, top values. Save this profile; it is the before picture and half of the final report.
3. Fix structure first: stray header and footer rows, merged or multi-row headers, columns holding two facts ("city, state"), wide layouts that should be long. Structural problems disguise themselves as thousands of value problems — fix the shape and watch the error count collapse.
4. Normalize types with explicit parsing: dates via stated formats — never ambiguous day/month guessing; if both readings are plausible, escalate rather than pick — numbers stripped of currency symbols and thousands separators with the locale recorded, IDs kept as text so leading zeros survive, booleans mapped from their zoo of encodings ("Y", "TRUE", "1", "yes ").
5. Handle missing values per column, per cause. Classify first: truly absent, not applicable, or a placeholder in disguise (0, -1, "n/a", 1899-12-31, the empty string). Then choose per column — leave as null, fill from a rule, or drop rows — and record the counts affected. Blanket-filling zeros is the single most damaging move in cleaning; imputing with statistics is a modeling decision that needs the analysis owner's sign-off, not a cleaning default.
6. Deduplicate at the declared grain: exact duplicates first (log how many), then near-duplicates via normalized keys — case, whitespace, punctuation. For conflicting near-duplicates, write a survivorship rule ("latest timestamp wins") rather than deciding row by row.
7. Treat outliers as suspects, not criminals. Investigate the extremes: many are unit errors — cents versus dollars, grams versus kilograms — or entry slips obvious on inspection. Correct what you can prove, flag what you cannot, and delete only with a written reason. Trimming and capping are analysis choices, not cleaning.
8. Validate the result against rules written in advance: key uniqueness, referential integrity, ranges, allowed categories, cross-field logic (ship date on or after order date), and a reconciliation total against a trusted external number. Failures loop back to the relevant stage.
9. Emit the cleaning log with the clean file. Data without its log is data with an unknown past.

Output format

## Cleaning log: <dataset> — <date>
Raw: <file, checksum, rows x cols>  →  Clean: <file, rows x cols>

| Stage | Action | Columns | Rows affected | Rule / reason |

Rows removed: <n> (<breakdown>)   Values corrected: <n>   Flagged, not fixed: <n>
Validation: <checks run, pass/fail>
Reconciliation: <metric> = <value> vs trusted <value> (delta <x>)
Open issues: <what the analyst must know before using this data>

Guardrails

• Raw files are read-only forever; every transformation is a script or a logged step that can be re-run
• Never fix by hand-editing cells in bulk — hand edits are unrepeatable and invisible
• Do not invent data: anything beyond trivial rule-fills is the analysis owner's explicit choice
• Preserve rejected rows in a quarantine file with reasons; deleted evidence has a way of being asked about later
• If more than about 5% of rows would be dropped, stop and report before proceeding — that is a source problem, not a cleaning problem

Quick reference: the placeholder rogues gallery

Zeros in fields where zero is impossible; -1 and 999 in ages and counts; 1900-01-01 and 1970-01-01 in dates; "N/A", "NULL", "-", and "." as text; five-nines postal codes; whitespace-only strings. Profile for these explicitly — they poison means and joins in complete silence.