pandas Data Cleaning

Overview

Keywords: pandas, data cleaning, dtypes, missing values, NaN, imputation, duplicates, outliers, IQR, z-score, tidy data, melt, pivot, normalize, standardize, parse dates, categorical, data quality, ETL preprocessing.

This skill turns a messy DataFrame into a tidy, correctly-typed, analysis-ready dataset using a repeatable, auditable workflow. The core principle: profile first, decide explicitly, transform with logging, validate after. Never mutate data silently — every fill, drop, or cast should be a deliberate, documented choice you can defend.

Treat cleaning as a pipeline that produces (1) the cleaned DataFrame and (2) a record of decisions. Prefer chained, non-mutating transforms (df.assign(...), .pipe(...)) over scattered in-place edits so the pipeline is reproducible top-to-bottom.

Workflow

1. Profile the raw data. Before changing anything, understand it. Run scripts/profile_data.py <path> (or replicate inline) to get shape, dtypes, per-column null counts/percentages, unique counts, sample values, and candidate problems (mixed types, high-cardinality strings, numeric-looking objects, constant columns). See references/cleaning-checklist.md.

1. Fix structure. Standardize column names (snake_case, strip whitespace, dedupe). Set/verify the index. Drop fully-empty rows/columns and constant columns that carry no signal. Confirm one observation per row, one variable per column (tidy form). If not tidy, defer reshape to step 8.

1. Coerce dtypes. Convert numeric-looking strings ("1,234", "$5.00", "12%") to numbers, parse dates with explicit formats, cast low-cardinality strings to category, and use nullable dtypes (Int64, boolean, string) where missing values must coexist with non-float types. See references/dtype-conversion.md.

1. Standardize text & categories. Trim whitespace, normalize case, collapse synonyms ("USA"/"U.S.A."/"United States"), fix encoding artifacts, and map free-text categories to a controlled vocabulary.

1. Handle missing values. First distinguish disguised missing ("NA", "-", "unknown", -999, empty string) from real values and convert them to NaN/pd.NA. Then choose a strategy per column — drop, constant fill, statistical impute (mean/median/mode), forward/backfill for time series, or model-based — and document why. Use the decision framework below.

1. Remove duplicates. Detect exact duplicates and key-based duplicates (subset=[...]). Decide keep policy (first/last/aggregate). Watch for near-duplicates from inconsistent text (handle those in step 4 first).

1. Detect & treat outliers. Use IQR fences or z-scores to flag, then decide: keep (legitimate extreme), cap/winsorize, transform (log), or remove (data error). Never delete outliers reflexively. See references/outliers-and-reshaping.md.

1. Reshape to tidy. Use melt to go wide→long, pivot/pivot_table for long→wide, and str.split/explode to split packed columns. Confirm the result satisfies the three tidy rules.

1. Validate. Re-profile. Assert invariants: expected row count range, no unexpected nulls in required columns, dtypes correct, key uniqueness, value ranges/domains. Fail loudly if violated. See the validation section in references/cleaning-checklist.md.

1. Persist the recipe. Capture the ordered transforms in a single function/notebook cell so the same raw input always yields the same clean output. Use templates/cleaning_report.md to summarize what was done and why.

Missing-Value Decision Framework

| Situation | Recommended strategy | |---|---| | Column >50–60% missing, not critical | Drop the column | | A few rows missing in a required key/target | Drop those rows | | Numeric, missing-at-random, skewed | Impute median | | Numeric, roughly symmetric | Impute mean (or median for robustness) | | Categorical | Impute mode, or add explicit "Missing" category | | Time series / ordered | ffill/bfill, or interpolate (.interpolate()) | | Missingness is itself informative | Keep NaN + add boolean was_missing flag | | Need non-float ints with NaN | Cast to nullable Int64, don't fill |

Rule of thumb: imputing changes the distribution. For modeling, prefer adding a missingness indicator alongside the imputed value so the model can learn from "was missing."

Outlier Decision Framework

1. Flag, don't auto-delete. Compute IQR fences (Q1 - 1.5*IQR, Q3 + 1.5*IQR) or |z| > 3.
2. Investigate. Is it a data-entry error (age = 999), a unit mistake (cm vs m), or a real rare event?
3. Treat by cause: error → fix or remove; legitimate extreme but distorting → winsorize/cap at the fence or log-transform; legitimate and meaningful → keep.
4. Use robust methods (IQR, median) over mean/std on skewed data, since mean/std are themselves dragged by outliers.

Worked Example (condensed)

Given a column price of strings like "$1,299.00", "N/A", "":

python
df["price"] = (
    df["price"]
      .replace({"N/A": pd.NA, "": pd.NA})
      .str.replace(r"[$,]", "", regex=True)
      .pipe(pd.to_numeric, errors="coerce")   # bad parses -> NaN
)
df["price"] = df["price"].fillna(df["price"].median())  # documented: skewed

See examples/clean_messy_sales.md for a full raw→clean walkthrough with a 12-column messy sales file, and run scripts/clean_pipeline.py --help for a configurable end-to-end cleaner.

Best Practices

• Profile before and after. You cannot clean what you have not measured, and you cannot trust a clean you did not verify.
• Prefer non-mutating chains. Build the cleaned frame with .assign/.pipe so the whole recipe is one readable, rerunnable block. Avoid sprinkling inplace=True.
• Coerce with errors="coerce", then inspect the new NaNs — they reveal unparseable values you'd otherwise miss.
• Use explicit date formats (pd.to_datetime(s, format="%Y-%m-%d")) to avoid silent misparsing of ambiguous 01/02/03.
• Use nullable dtypes (Int64, boolean, string) instead of forcing floats just to hold NaN.
• Document every destructive choice (drop/fill/cap) in the cleaning report so reviewers can challenge it.
• Validate with assertions at the end; treat a cleaning script that produces silently-wrong data as a bug.
• Keep the raw file immutable. Always read raw, write cleaned to a new path.

Common Pitfalls

• Disguised missing values ("unknown", -999, "-", whitespace) left as real data, poisoning means and joins. Always normalize these to NaN first.
• inplace=True chaining bugs and accidental SettingWithCopyWarning from chained indexing — use .loc and reassignment.
• Auto-deleting outliers before checking whether they're legitimate, throwing away the most interesting rows.
• Mean-imputing skewed columns, dragging the central tendency and shrinking variance.
• float64 columns full of 1.0/2.0 because NaN forced float — cast to Int64 after cleaning.
• Ambiguous date parsing (dayfirst vs default) silently swapping day/month.
• Dropping duplicates before standardizing text, so "Acme " and "acme" survive as distinct.
• Pivot collisions — using pivot when index/column pairs aren't unique; use pivot_table with an explicit aggfunc.
• No post-clean validation, shipping a dataset whose row count or domain quietly broke.