/clean-data

name: clean-data
description: Use when a clinical CSV/Excel dataset needs profiling and cleaning before analysis (missing values, outliers, duplicates, type mismatches). Profiles, flags and generates cleaning code in three stages, each gated on the researcher's approval. Never auto-cleans.
metadata:
  triggers: "clean data, data cleaning, data preprocessing, data profiling, missing values, outliers, check my data, data quality"

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.5*IQR, Q3 + 1.5*IQR) 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`.

Present the flag report as a table:

| Variable | Issue Type | Count | Severity | Suggested Action | |----------|-----------|-------|----------|-----------------| | age | Outlier (IQR) | 3 | Medium | Review: values 150, 200, -5 | | pack_years | Categorical-implied zero | 12421 | High | Set 0 where smoking_status=='never' (structural zero, not missing) |

Severity levels:

• **High**: likely data errors that will affect analysis (type mismatches, impossible values)
• **Medium**: potential issues that need expert review (statistical outliers, moderate missingness)
• **Low**: minor inconsistencies that are easy to fix (category labels, trailing whitespace)

**Gate**: The user marks each row (A) Approve the suggested action, (R) Reject / keep as-is, or (M) Modify the action. Only appr