Data Cleaning
Overview
Cleaning turns raw data into a consistent, model-ready table without leaking information from the future or the test set. The golden rule: every statistic used to clean (means, medians, modes, bounds, category maps) must be learned from the training split only, then applied to validation/test.
When to use
- Raw data has nulls, duplicates, mixed types, or junk categories.
- Before feature-engineering and modeling.
- After EDA flagged specific quality issues.
Workflow
- Deduplicate — exact and key-based duplicates. Decide which to keep (latest timestamp, highest completeness).
- Fix types — parse dates, cast numerics stored as strings, normalize booleans.
- Standardize categoricals — trim whitespace, unify case, map synonyms ("US"/"USA"/"United States").
- Handle missing values — choose per-column strategy (see below).
- Treat outliers — cap/winsorize or flag; never blindly delete.
- Validate — assert schema, ranges, and row counts after each step.
Missing-value strategy
| Situation |
Strategy |
| Numeric, MCAR, small % |
Median impute (robust to skew) |
| Numeric, informative missingness |
Impute + add was_missing indicator |
| Categorical |
Impute with "Missing" as its own category |
| Time series |
Forward/backward fill within group |
| >50% missing |
Consider dropping the column |
Reference snippet (leakage-safe)
from sklearn.model_selection import train_test_split
from sklearn.impute import SimpleImputer
X_train, X_test, y_train, y_test = train_test_split(
X, y, test_size=0.2, random_state=42, stratify=y
)
# Fit imputers on TRAIN ONLY
num_imputer = SimpleImputer(strategy="median").fit(X_train[num_cols])
X_train[num_cols] = num_imputer.transform(X_train[num_cols])
X_test[num_cols] = num_imputer.transform(X_test[num_cols]) # reuse train stats
Prefer doing this inside a Pipeline/ColumnTransformer (see the sklearn-pipelines skill) so leakage is impossible by construction.
Outlier handling
# Winsorize numeric columns to train-derived 1st/99th percentiles
lo = X_train[col].quantile(0.01)
hi = X_train[col].quantile(0.99)
X_train[col] = X_train[col].clip(lo, hi)
X_test[col] = X_test[col].clip(lo, hi)
Pitfalls
df.fillna(df.mean()) on the whole dataset — classic leakage. Compute stats on train only.dropna() on rows silently shrinks and biases the dataset; prefer imputation + indicators.- Deleting outliers that are real signal (fraud, rare events) — investigate before removing.
- Cleaning before splitting — split first, then clean using train statistics.
Hand-off
Produce a clean, typed dataframe plus a documented list of decisions (what was imputed/capped/dropped and why) so feature-engineering and reproducible-ml can rely on it.
1---2name: data-cleaning3description: Use when preparing raw data for modeling — handling missing values, duplicates, inconsistent types, outliers, and bad categorical values. Emphasizes fitting all imputation on train-only to avoid leakage.4---56# Data Cleaning78## Overview910Cleaning turns raw data into a consistent, model-ready table without leaking information from the future or the test set. The golden rule: **every statistic used to clean (means, medians, modes, bounds, category maps) must be learned from the training split only**, then applied to validation/test.1112## When to use1314- Raw data has nulls, duplicates, mixed types, or junk categories.15- Before feature-engineering and modeling.16- After EDA flagged specific quality issues.1718## Workflow19201. **Deduplicate** — exact and key-based duplicates. Decide which to keep (latest timestamp, highest completeness).212. **Fix types** — parse dates, cast numerics stored as strings, normalize booleans.223. **Standardize categoricals** — trim whitespace, unify case, map synonyms ("US"/"USA"/"United States").234. **Handle missing values** — choose per-column strategy (see below).245. **Treat outliers** — cap/winsorize or flag; never blindly delete.256. **Validate** — assert schema, ranges, and row counts after each step.2627## Missing-value strategy2829| Situation | Strategy |30|-----------|----------|31| Numeric, MCAR, small % | Median impute (robust to skew) |32| Numeric, informative missingness | Impute + add `was_missing` indicator |33| Categorical | Impute with `"Missing"` as its own category |34| Time series | Forward/backward fill within group |35| >50% missing | Consider dropping the column |3637## Reference snippet (leakage-safe)3839```python40from sklearn.model_selection import train_test_split41from sklearn.impute import SimpleImputer4243X_train, X_test, y_train, y_test = train_test_split(44 X, y, test_size=0.2, random_state=42, stratify=y45)4647# Fit imputers on TRAIN ONLY48num_imputer = SimpleImputer(strategy="median").fit(X_train[num_cols])49X_train[num_cols] = num_imputer.transform(X_train[num_cols])50X_test[num_cols] = num_imputer.transform(X_test[num_cols]) # reuse train stats51```5253Prefer doing this inside a `Pipeline`/`ColumnTransformer` (see the `sklearn-pipelines` skill) so leakage is impossible by construction.5455## Outlier handling5657```python58# Winsorize numeric columns to train-derived 1st/99th percentiles59lo = X_train[col].quantile(0.01)60hi = X_train[col].quantile(0.99)61X_train[col] = X_train[col].clip(lo, hi)62X_test[col] = X_test[col].clip(lo, hi)63```6465## Pitfalls6667- **`df.fillna(df.mean())` on the whole dataset** — classic leakage. Compute stats on train only.68- **`dropna()` on rows** silently shrinks and biases the dataset; prefer imputation + indicators.69- **Deleting outliers** that are real signal (fraud, rare events) — investigate before removing.70- **Cleaning before splitting** — split first, then clean using train statistics.7172## Hand-off7374Produce a clean, typed dataframe plus a documented list of decisions (what was imputed/capped/dropped and why) so feature-engineering and reproducible-ml can rely on it.
param087