csv_cleaning v1.0.0

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.

1. 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.

1. 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.

1. 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.

1. 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.

1. 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.)

1. 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.

1. 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.

1. 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.