---
name: mohitagw15856/data-cleaning-pass
source: https://app.decimal.ai/s/mohitagw15856-data-cleaning-pass@1/SKILL.md
source_sha256: 441d79647f1a
---

# Data Cleaning Pass Skill

Dirty data doesn't announce itself — it double-counts in the pivot, drops rows in the join, and averages text as zero. Cleaning done ad hoc corrupts as it corrects (the dedupe that removed real records, the find-replace that hit the wrong column). The pass is methodical: *profile first* (what's actually wrong, counted), fix in an order where each step doesn't mask the next, keep the original untouched, and log every transformation — because "how did you get these numbers" deserves an answer.

## What This Skill Produces

- **The profile** — per column: type consistency, blank/error counts, distinct-value sanity, the weirdest values surfaced
- **The cleaning plan** — ordered fixes with their methods, run on a copy
- **The join-key repair** — the match-rate before/after when sheets must link
- **The cleaning log** — what changed, how many rows/cells, by what rule — the defensibility artifact

## Required Inputs

Ask for these if not provided:
- **The data** — the sheet/export, and where it came from (system exports have signature messes: leading zeros eaten, dates re-typed, thousands separators as text)
- **The destination** — a pivot, a join, a chart, an import; the destination defines "clean enough" (a join needs perfect keys; a chart needs consistent types)
- **The authority questions** — when duplicates conflict (two rows, same customer, different phone), which source wins? Cleaning makes merge decisions; someone must own the rule

## Framework: The Pass Rules

1. **Profile before touching:** per column — count blanks, count types (text-that-looks-numeric is the classic), list distinct values on category columns (finds `NY / N.Y. / New York`), min/max on numerics (finds the 1899 dates and the 9999 placeholders). The profile converts "it's messy" into a numbered work list.
2. **Fix in non-masking order:** trim whitespace & normalize case → fix types (text-numbers to numbers, dates to real dates) → standardize categories (the NY problem) → *then* dedupe → then handle blanks. Deduping before normalization misses duplicates; deduping after catches them. Order is the craft.
3. **Dedupe with a definition:** "duplicate" needs a key (same email? same name+date?) — stated before removing anything, with conflicting-field rules decided by the named authority ("keep most recent," "prefer CRM over export"). Removed rows go to a `_removed` tab, not to oblivion.
4. **Blanks are three different things:** truly-empty (fine), should-have-a-value (flag for source follow-up, don't invent), and blank-meaning-zero (convert only when the source confirms the semantic). Filling blanks by assumption is fabrication with a keyboard.
5. **The log is the deliverable's twin:** each step: rule, scope, count affected ("trimmed 412 cells; merged 37 duplicate customers by email, keep-most-recent; 9 unresolvable → flagged"). Original preserved untouched; the cleaned copy + log travel together.

## Output Format

# Cleaning Pass: [dataset] → for [destination]

## The Profile
| Column | Type issues | Blanks | Distinct/sanity findings |
|---|---|---|---|

## The Plan (ordered)
[Trim/case → types → categories (the mapping table) → dedupe (key + conflict rule + authority) → blanks (three-way sort)]

## Join Repair (if joining)
[Key match-rate before → after · the unmatched remainder, listed for follow-up]

## The Log
[Step · rule · rows/cells affected — running, final counts at bottom · original untouched at (location)]

## Quality Checks

- [ ] Profiling produced counts before any edit
- [ ] The order ran normalize-before-dedupe
- [ ] The duplicate key and conflict authority are stated
- [ ] No blank was filled without a confirmed semantic
- [ ] The log accounts for every transformation, and the original survives untouched

## Anti-Patterns

- [ ] Do not clean in place — the original is the rollback and the audit
- [ ] Do not dedupe first — un-normalized duplicates hide from the dedupe
- [ ] Do not invent values for should-have-value blanks — flag them; fabrication compounds downstream
- [ ] Do not global find-replace without column scoping — the classic self-inflicted corruption
- [ ] Do not deliver cleaned data without the log — numbers whose provenance can't be stated get re-cleaned by the next skeptic