Data Cleaner

---
name: data-cleaner
description: >
  Clean, standardize, and structure messy tabular data (CSV, Excel) into analysis-ready form. Use
  when the user has messy data that needs fixing: inconsistent formats, duplicate rows, missing
  values, mixed date formats, inconsistent categories/spellings, merged or split columns, junk rows,
  or data scattered across a badly-structured sheet. Triggers on "clean this CSV", "my data is
  messy", "standardize these columns", "remove duplicates from", "fix the dates in this file", "this
  spreadsheet is a mess", "dedupe my contact list", or when someone uploads tabular data that
  obviously needs cleaning before use. Produces a clean file plus a summary of what was changed.
---

# 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