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.
Start a fresh conversation. Replace the highlighted placeholders.
Use data-cleaning-pass to profile and clean a copy of [dataset and origin] for [destination], applying [duplicate/conflict authority]; deliver the cleaned copy, any join-key match-rate result, and a transformation log while preserving the original.
Required inputs
[dataset and origin]
[destination]
[duplicate/conflict authority]
User-approved starter prompt based on a pinned source version; check current skill requirements before running. Task evidence ↗
SKILL.md
name: data-cleaning-passdescription: "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."
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.