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
- 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.
- 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.
- 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.
- 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.
- 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
Anti-Patterns
1---2name: data-cleaning-pass-23description: "Clean a messy dataset methodically — the profiling pass that finds what's actually wrong (dupes, format drift, phantom spaces, mixed types), the fix order that doesn't corrupt while correcting, and the log that makes the cleaning defensible. Use when asked clean this export, why is my pivot double-counting, these names don't match between sheets, or prep this data for analysis. Produces the profile of what's wrong, the ordered cleaning plan, the join-key repairs, and the cleaning log."4homepage: https://mohitagw15856.github.io/pm-claude-skills/skill/data-cleaning-pass.html5metadata:6 source: https://skillmd.com/skills/mohitagw15856/data-cleaning-pass-27 openclaw:8 emoji: 🧠9---1011# Data Cleaning Pass Skill1213Dirty 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.1415## What This Skill Produces1617- **The profile** — per column: type consistency, blank/error counts, distinct-value sanity, the weirdest values surfaced18- **The cleaning plan** — ordered fixes with their methods, run on a copy19- **The join-key repair** — the match-rate before/after when sheets must link20- **The cleaning log** — what changed, how many rows/cells, by what rule — the defensibility artifact2122## Required Inputs2324Ask for these if not provided:25- **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)26- **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)27- **The authority questions** — when duplicates conflict (two rows, same customer, different phone), which source wins? Cleaning makes merge decisions; someone must own the rule2829## Framework: The Pass Rules30311. **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.322. **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.333. **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.344. **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.355. **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.3637## Output Format3839# Cleaning Pass: [dataset] → for [destination]4041## The Profile42| Column | Type issues | Blanks | Distinct/sanity findings |43|---|---|---|---|4445## The Plan (ordered)46[Trim/case → types → categories (the mapping table) → dedupe (key + conflict rule + authority) → blanks (three-way sort)]4748## Join Repair (if joining)49[Key match-rate before → after · the unmatched remainder, listed for follow-up]5051## The Log52[Step · rule · rows/cells affected — running, final counts at bottom · original untouched at (location)]5354## Quality Checks5556- [ ] Profiling produced counts before any edit57- [ ] The order ran normalize-before-dedupe58- [ ] The duplicate key and conflict authority are stated59- [ ] No blank was filled without a confirmed semantic60- [ ] The log accounts for every transformation, and the original survives untouched6162## Anti-Patterns6364- [ ] Do not clean in place — the original is the rollback and the audit65- [ ] Do not dedupe first — un-normalized duplicates hide from the dedupe66- [ ] Do not invent values for should-have-value blanks — flag them; fabrication compounds downstream67- [ ] Do not global find-replace without column scoping — the classic self-inflicted corruption68- [ ] Do not deliver cleaned data without the log — numbers whose provenance can't be stated get re-cleaned by the next skeptic