← All skills

Cleaning Tabular Data

Cleans messy CSV and spreadsheet exports — repairs headers, normalises dates and numbers, collapses duplicates and reports what changed — before any analysis is run on them. Use when a CSV will not parse, when columns are misaligned, or when the user mentions dirty data, deduplication or an export from another system.

Skill name

cleaning-tabular-data

Category

data

Price

Free

Install

~/.claude/skills/cleaning-tabular-data/SKILL.md

Tags

csv, spreadsheet, data cleaning, deduplication, etl

Cleaning Tabular Data

The expensive mistake is silent cleaning: rows disappear, a date shifts by a month, and the analysis that follows is confidently wrong. Every step here produces a count.

Order of operations

Structure before values. Fixing a date format in a file whose header row starts on line 4 wastes the work.

- [ ] 1. Sniff the file — delimiter, encoding, line endings, quote character
- [ ] 2. Find the real header row and drop banner lines above it
- [ ] 3. Normalise column names to lowercase with underscores
- [ ] 4. Fix types column by column — dates, numbers, booleans, identifiers
- [ ] 5. Handle missing values deliberately, one column at a time
- [ ] 6. Deduplicate on a stated key
- [ ] 7. Report every count before and after

Run scripts/profile.py first — it prints the shape, per-column types, null counts and candidate keys, which decides most of the choices below.

python scripts/profile.py export.csv

The traps that cause wrong answers

Dates. 03/04/2026 is ambiguous and always will be. Decide from evidence: look for any day value above 12 in the column, check the source system's locale, and if neither settles it, ask. Never let a parser guess silently — a day-first file read as month-first shifts most rows and breaks none.

Numbers that are text. Thousands separators, currency symbols, trailing spaces, parentheses for negatives, and a Unicode minus sign that is not a hyphen. Strip, then convert, then count how many failed to convert.

Identifiers that look numeric. Postcodes, phone numbers, account codes and ZIP codes lose leading zeros the moment they are read as integers. Read every identifier column as text.

Encoding. A file that produces é where é belongs was written as UTF-8 and read as Latin-1. Re-read it rather than replacing characters by hand.

Merged and repeated headers. Exports often repeat the header every 50 rows. Drop rows that equal the header.

Missing values

Decide per column and write down which was chosen:

• Leave as null when absence is meaningful
• Fill with a sentinel only if downstream code cannot take a null
• Drop the row only when the key is missing
• Never fill with the mean before someone has seen the distribution

Deduplication

State the key first. "Duplicate" means "same customer", not "same bytes". The example below uses pandas (pip install pandas); the same logic works with the standard library csv module and a dictionary keyed on the identifier.

before = len(df)
df = df.sort_values("updated_at").drop_duplicates(subset=["email"], keep="last")
print(f"deduplicated on email: {before} -> {len(df)} rows")

Keeping the most recent record needs the sort. Without it, drop_duplicates keeps whichever row the file happened to list first.

The report

Finish with a summary the person who owns the data can check:

rows           12,481 -> 12,205  (276 removed)
  duplicate email                 231
  missing customer_id              45
columns        18 -> 18
dates parsed   order_date 12,205/12,205 (day-first, confirmed by 31/01 rows)
numbers        amount 12,198 parsed, 7 blank, 0 failed
identifiers    postcode kept as text (1,042 values have a leading zero)

If a step cannot be summarised as a number, it was probably not deliberate enough.