Clean Data

Data Profiling and Cleaning Skill

Profile, flag, and generate cleaning code for a clinical dataset in three stages, each ending at a user-approval gate. You generate code and reports; you do NOT auto-clean data. Every cleaning decision needs the researcher's explicit confirmation, because clinical cleaning calls need domain knowledge.

PHI first. If the dataset contains PHI or PII, run /deidentify before proceeding, and use *_deidentified.* files when they exist in the working directory. Otherwise work from the data dictionary / codebook alone, or in a local-only environment with no network access. The skill writes code that runs on the data; it does not need to see the raw rows to write it.

Three-Stage Workflow

Stage 1: Profiling

Input: CSV/Excel file path OR data dictionary/codebook

1. Adapt ${CLAUDE_SKILL_DIR}/references/profiling_template.py (pandas) to the dataset. It reports: variable and row counts, data types; missing count and percentage per variable; unique counts for categorical variables; min/max/mean/median/SD for numeric variables; histograms and bar charts.
2. If the user provides a codebook, cross-reference variable names, expected types, and expected ranges. Never guess a column name or coding: when a mapping is uncertain, write [VERIFY: variable_name] and ask the user to confirm it against the data dictionary.
3. Present the summary (see Output Format). Every count and statistic comes from the executed script's output.

Gate: The user reviews the profile. Ask whether to proceed to Stage 2 (Flagging) and whether any variables should be excluded or focused on.

Stage 2: Flagging

Read ${CLAUDE_SKILL_DIR}/references/cleaning_patterns.md for missing-data mechanisms, outlier decision rules, duplicate detection, date handling, and clinical pitfalls (inequality-prefixed lab values, mixed units, sentinel values). Flag issues in these categories:

1. Missing values: variables with >5% missing; pattern analysis (MCAR/MAR/MNAR heuristic).
2. Statistical outliers: IQR method (Q1 - 1.5IQR, Q3 + 1.5IQR) and Z-score (|z| > 3).
3. Duplicates: exact row duplicates AND near-duplicates (same patient ID, different dates).
4. Type mismatches: numeric stored as string, dates in inconsistent formats.
5. Implausible values: flag against the codebook's valid range or, when the codebook is silent, the domain-default hard physiologic bounds in references/implausible_value_rules.md §1. An implausible value is a likely data-entry/unit/sentinel error (correct or set missing); a statistical outlier (#2) is biologically possible (keep + sensitivity analysis). Check units before calling a bound violation an error. Never auto-fix. 5b. Cross-field inconsistencies: logical contradictions per references/implausible_value_rules.md §2 — temporal ordering (birth ≤ event ≤ death, admission ≤ discharge), derived-vs-source (recomputed BMI/age; subset ≤ superset; total = sum of parts), sex-/state-specific fields, and min ≤ max / diastolic < systolic pairs. Name the rule that fired; a hard contradiction is High severity.
6. Category inconsistencies: typos and variants in categorical values ("Male", "male", "M", "MALE").
7. Categorical-implied zeros: when a category defines a natural zero for a dose/duration variable (smoking_status == 'never' implies pack_years == 0; alcohol_use == 'never' implies grams_per_week == 0), flag records that store the implied zero as NULL. This is a contradiction, not a missing-data pattern: complete-case models silently drop those never-smokers and MICE imputes them a non-zero dose, corrupting the exposure contrast. Suggested action: "Set dose = 0 where category == reference level; impute only the residual missingness among the exposed." Detected by scripts/check_structural_zero.py given the category↔dose mapping; pairs with /analyze-stats "Covariate Pitfalls: Structural Zeros & Dose/Duration Variables".
8. Reverse-coded scale items: in a multi-item Likert scale that mixes positively and negatively worded items, every reverse item must be recoded (min+max) - x before the scale total or Cronbach's alpha is computed. An un-recoded reverse item correlates negatively with the rest and collapses alpha, often to a negative value. A negative alpha is a reverse-coding bug, not "multidimensional structure". Suggested action: "Recode reverse-worded items, then recompute reliability." Detected by scripts/check_reverse_coding.py (negative item-rest correlation and negative raw alpha, given the scale item columns); the recode itself is applied downstream by /analyze-stats likert_summary.py --reverse-items.