---

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.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.

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 approved actions generate cleaning code.

Stage 3: Code Generation

For ONLY user-approved actions, generate Python (or R if requested) code:

• Missing value handling: listwise deletion, mean/median imputation, or MICE setup (code only, the user runs it and picks the variables and method)
• Outlier handling: winsorization, removal, or keep-and-flag
• Duplicate removal: exact dedup with logging
• Type conversion: standardize dates, numeric parsing
• Category harmonization: mapping table for inconsistent labels

All generated code MUST include:

• Before/after row counts printed to console
• Logging of every modification to a cleaning log DataFrame
• Reproducibility: np.random.seed(42) and random.seed(42) where applicable
• Output: cleaned CSV (a new file; the input file is never overwritten) + cleaning_log.csv
• Clear comments explaining each cleaning step

End the generated script with this notice:

"This code implements ONLY the cleaning rules you approved. Review the cleaning_log.csv output to verify all changes before proceeding to analysis."

Out of scope: free-text extraction from clinical notes, and image data or DICOM metadata. After cleaning, hand off to /analyze-stats. Take any citation from /search-lit, never from memory.

Output Format

Structure all reports using this template:

## Data Profiling Report

### Dataset Overview
- Rows: [N]
- Columns: [N]
- File size: [size]
- Date range: [if applicable]

### Variable Summary
| Variable | Type | Missing N (%) | Unique | Min | Max | Mean | SD |
|----------|------|---------------|--------|-----|-----|------|-----|
| ...      | ...  | ...           | ...    | ... | ... | ...  | ... |

### Flags
| Variable | Issue | Count | Severity | Suggested Action |
|----------|-------|-------|----------|-----------------|
| ...      | ...   | ...   | ...      | ...             |

### Cleaning Code
[Python/R script -- only for approved actions]

### Cleaning Log
[What was changed, how many rows affected, before/after counts]