---
name: "csv_cleaning"
description: "Fixing broken/messy CSV files: delimiter sniffing, encoding/BOM handling, header normalization, ragged rows, quoted commas and embedded newlines, type coercion, dedupe, and producing a clean canonical CSV with csvkit + pandas. Triggers on: clean a CSV, messy CSV, fix this CSV, CSV wrong delimiter, CSV encoding problem, normalize CSV headers, dedupe CSV rows, CSV ragged rows."
metadata: { "includeInPrompt": true }
---

## Purpose

Turn a dirty CSV — wrong delimiter, BOM/encoding junk, messy headers, ragged rows, quoted commas, bad dates — into a clean canonical CSV: UTF-8, comma-delimited, one snake_case header row, coerced types, deduped rows. Diagnosing is part of the job: inspect before you mutate, and keep the original file untouched.

## Workflow

1. **Inspect first.** `file dirty.csv` for encoding hints; `head -5 dirty.csv` to eyeball the delimiter, quoting, and header shape. Never mutate the original — write cleaned output to a new file.

2. **Install tools if missing:** `pip install csvkit` (pandas ships with most agent runtimes). csvkit's CLIs (`csvclean`, `csvstat`, `csvformat`, `csvcut`) read stdin and write stdout.

3. **Settle the encoding.** Read with `utf-8-sig` first — it silently strips a real BOM. If you hit `UnicodeDecodeError`, fall back to `latin-1` (never corrupts bytes) and re-save as UTF-8.

4. **Sniff the delimiter.** `python3 -c "import csv; print(repr(csv.Sniffer().sniff(open('dirty.csv').read(4096), delimiters=[',',';','\t','|']).delimiter))"` — pass what you find to every csvkit command via `-d/--delimiter`. Quoted commas (`"1,200.50"`) and quoted embedded newlines are *valid* CSV; don't "fix" them by stripping quotes.

5. **Quarantine ragged rows with csvclean.** `csvclean -a --delimiter ';' --encoding utf-8-sig --omit-error-rows dirty.csv 1>clean_stage.csv 2>row_errors.csv` — clean rows go to stdout; stderr is a CSV error report (`line_number,msg,...`). Review `row_errors.csv` and decide per row: keep, fix, or drop. (csvkit 2.x requires `-a` or an explicit check flag, else it exits with "No checks or fixes were enabled".) For salvaging short rows, `--join-short-rows` / `--fill-short-rows` are available.

6. **Profile what survived.** `csvstat -c 1,2,3 --delimiter ';' clean_stage.csv` shows inferred types, null counts, most-common values — it exposes dirty cells (`" Smith "`, `"alice "`) that eyeballing missed. (csvstat chokes on ragged rows, so this step comes after step 5.)

7. **Normalize in pandas.** Header rule: strip, lowercase, replace non-alphanumerics with `_` → `first_name`. Then: `str.strip()` every cell, lowercase emails, strip thousand-separators before `pd.to_numeric(..., errors='coerce')`, parse dates with `pd.to_datetime(..., format='mixed', errors='coerce')` so `2026-02-30` becomes NaT instead of crashing. Flag suspect values in a `<col>_valid` column rather than silently deleting them.

8. **Dedupe deliberately.** `df.drop_duplicates()` *after* normalizing case/whitespace — dedupe before normalizing and near-identical rows survive. Default key is the full row; confirm a narrower key with the requester when identity columns exist.

9. **Write canonical output.** `df.to_csv('clean.csv', index=False, encoding='utf-8', lineterminator='\n')` — comma delimiter, no BOM, no index column. For a pure delimiter swap with no pandas step: `csvformat -D ',' --delimiter ';' in.csv`.

## Output Contract

- `clean.csv`: UTF-8, comma-delimited, single snake_case header row, one record per line, quoted only where needed (commas/newlines inside fields stay quoted — that is correct CSV).
- A short cleaning report: delimiter/encoding found, rows quarantined by csvclean and each one's disposition, coercion failures (values → NaN/NaT), duplicates removed and the key used.
- Any `<col>_valid` flag columns added and why; any rows or columns dropped and why.

## Operating Rules

- **Original stays read-only.** Every mutation writes a new file; you can always diff back.
- **Inspect before you coerce.** Profile first so type decisions are evidence-based, not guesses.
- **Quarantine, don't silently drop.** Ragged rows land in the error report for an explicit keep/fix/drop decision.
- **Flag, don't fabricate.** Unparseable dates → NaT, bad numbers → NaN, suspect emails get `email_valid=False` — never invent a replacement value.
- **Dedupe after normalizing.** Whitespace/case cleanup happens before duplicate detection, or near-dupes survive.
- **One canonical encoding out.** UTF-8 without BOM, `\n` line endings, comma delimiter — unless the downstream consumer needs otherwise, stated explicitly.