Data Cleaner

Turn messy tabular data into clean, analysis-ready data - and tell the user exactly what changed. Messy data is the tax everyone pays before doing anything useful. This skill handles the common cleaning problems systematically and transparently, so the user can trust the output.

When the actual deliverable is a spreadsheet, also read the xlsx skill. This skill covers the cleaning logic; the xlsx skill covers producing a polished Excel file.

Core principle: transparent cleaning

Never silently change data. Every transformation must be visible and reversible in principle. The user needs to trust that you didn't quietly drop rows or mangle values. Always deliver the cleaned file AND a summary of exactly what was done and why.

Workflow

Step 1: Profile the data first (don't clean blindly)

Before changing anything, understand what you have. Load the data and report:

• Number of rows and columns
• Column names and their apparent types
• How many missing values per column
• Obvious issues (duplicate rows, mixed formats, junk rows, inconsistent categories)
• A sample of the actual messy values

Show this to the user before major cleaning so they can confirm the plan. Some "errors" are intentional in their domain.

Step 2: Agree the cleaning plan

Based on the profile, propose what to fix. Common operations:

• Remove exact duplicate rows
• Standardize column names (consistent casing, no stray spaces)
• Fix data types (text numbers → numbers, text dates → dates)
• Standardize date formats to one consistent format
• Handle missing values (flag, fill, or drop - the user should decide the policy)
• Standardize categorical values (e.g. "USA"/"U.S."/"United States" → one value)
• Trim whitespace, fix casing in text fields
• Split or merge columns (e.g. "Name" → First/Last, or combine address parts)
• Remove junk rows (blank rows, header rows repeated mid-data, totals rows)

Flag anything destructive (dropping rows, filling values) and get confirmation on the policy.

Step 3: Clean systematically

Write a script (pandas) rather than eyeballing - it's reliable, repeatable, and shows exactly what happened. See scripts/clean.py for a starting template. Key practices:

• Keep the original file untouched; write to a new file
• Count what changes at each step (rows removed, values standardized)
• Handle each column according to its real type and meaning

Step 4: Deliver with a change summary

Output:

1. The cleaned file (CSV or Excel)
2. A summary of changes:

## Cleaning Summary
- Rows: 5,240 → 4,980 (removed 260)
  - 187 exact duplicates
  - 73 blank/junk rows
- Standardized 4 date columns to YYYY-MM-DD
- Fixed 'Country' column: merged 6 spellings into 3 standard values
- Filled 45 missing 'Region' values using [rule]; flagged 12 that couldn't be resolved
- Trimmed whitespace across all text columns
- Converted 'Amount' from text to numeric (removed currency symbols and commas)

## Needs your attention
- 12 rows have missing 'Region' that couldn't be inferred - highlighted in the file
- 3 rows have 'Amount' values that look like typos (e.g. 999999) - flagged, not changed

The "needs your attention" section is important - surface judgment calls rather than guessing.

Common cleaning patterns

Inconsistent categories: Group similar values ("Maharastra"/"Maharashtra"/"MH" → "Maharashtra"). Build a mapping, apply it, and report the mapping so the user can verify.

Mixed date formats: Detect the formats present (DD/MM/YYYY vs MM/DD/YYYY vs text), parse each, standardize to ISO (YYYY-MM-DD). Watch for ambiguous dates (01/02/2024) - flag if the format is genuinely ambiguous rather than guessing wrong.

Numbers stored as text: Strip currency symbols, commas, spaces; convert. Flag values that won't parse rather than dropping them.

Duplicates: Distinguish exact duplicates (safe to remove) from near-duplicates (same person, different formatting - needs fuzzy matching and more care). Don't aggressively merge near-duplicates without confirmation.

Missing values: Never silently fill. Options: leave blank and flag, fill with a rule the user approves (forward-fill, default value, computed), or drop rows (only with permission). State the policy.

Hard rules

• Never silently drop rows or alter values - every change is reported.
• Keep the original file intact; always write cleaned output to a new file.
• When a cleaning decision is a judgment call (ambiguous date, possible typo, near-duplicate), flag it for the user rather than guessing and moving on.
• Don't "fix" values that might be intentionally unusual in the user's domain without asking.
• For genuinely ambiguous data (like DD/MM vs MM/DD), surface the ambiguity - a wrong guess corrupts the dataset silently.

Reference files

• scripts/clean.py - Pandas-based cleaning template with profiling and change tracking