Skip to content

ericrisco/rsc-harness / skills/data-cleaning

data-cleaning

Unverified

Use when a raw table is too dirty to trust — nulls, sentinels, duplicate rows, category sprawl, mixed types, bad dates — and you need a re-runnable clean() plus a schema gate that fails loud. NOT emitting .xlsx (that is spreadsheet-ops), NOT acquiring rows (that is data-scraper), NOT parsing PDF/HTML into rows (that is structured-extraction).

Data cleaning — make dirty data trustworthy, and make the cleaning auditable

A clean table is typed + deduped + normalized + validated + reproducible. The deliverable here is never "I opened a notebook and fixed some rows by hand." It is a re-runnable function clean(raw) -> df plus a schema gate that fails loud when next month's file violates the contract. Reproducible means the same input always yields the same output: versions pinned, sorts deterministic, nothing random without a seed. If you can't re-run it tomorrow and get the identical result, you haven't cleaned the data — you've edited a snapshot.

Cleaning starts once you hold tabular rows and ends at a validated table/DataFrame/Parquet. Before that boundary the job is acquisition (data-scraper, structured-extraction); after it, consumption (spreadsheet-ops, analytics, business-intelligence, forecasting). Multi-GB analytical SQL is an engine choice, not a cleaning one — duckdb.

Current stack (verified 2026-06-02): pandas 3.0.x (3.0.0 shipped 2026-01-21) and pandera 0.31.1 (supports pandas ≥ 3) for in-pipeline schema validation; Polars and DuckDB when pandas runs out of RAM. Pin them: pandas==3.0.3, pandera==0.31.1.

The pipeline shape

One canonical order. Each step is positioned for a reason, not by habit.

import pandas as pd

def clean(raw_path: str) -> pd.DataFrame:
    df = read_typed(raw_path)     # 1. read with explicit dtypes — never let pandas guess
    df = normalize(df)            # 2. strings/categories/numbers/dates — collapse invisible variance
    df = dedupe(df)               # 3. AFTER normalize+type, so "1"/1 and "US "/"US" actually collapse
    df = handle_missing(df)       # 4. decide per column: drop / impute+flag / leave NA / quarantine
    df = Schema.validate(df, lazy=True)  # 5. the GATE — fail loud, surface every violation at once
    return df
  • Type before dedupe — otherwise "1" (string) and 1 (int) survive as two distinct keys.
  • Normalize before dedupe — "US " and "US" are the same customer; dedupe can't see that until whitespace/case are collapsed.
  • Validate last — it is the gate, not a cleaning step. It asserts the contract holds after all fixes.
  • Write to a NEW artifact — the raw file is read-only; you never overwrite your only source.

Read it right

The single most common reproducibility footgun: pandas' legacy numpy path silently casts an integer column containing one NaN to float64, so your id becomes 1001.0. Control the dtype on read.

# BAD — pandas guesses: ids become floats, "N/A" stays a string, "" is sometimes NaN sometimes ""
df = pd.read_csv("raw.csv")

# GOOD — explicit, deterministic, real nullable types
df = pd.read_csv(
    "raw.csv",
    dtype_backend="pyarrow",          # real nullable ints/strings; no silent float-cast
    na_values=["", "N/A", "NA", "null", "-1", "999"],  # YOUR sentinels become real NA
    keep_default_na=True,             # keep pandas' default NA tokens too
    encoding="utf-8",                 # state it; don't let locale decide
)

Two pandas 3.0 facts that read depends on. dtype_backend="pyarrow" only works if pyarrow is actually installed — PDEP-14 deliberately kept a NumPy-object fallback so PyArrow stays recommended, not required — so pip install pyarrow for the faster backed path, or pass dtype_backend="numpy_nullable" when it is absent. And the default str dtype (PyArrow-backed when pyarrow is present, NumPy-object-backed otherwise) uses NaN missing-value semantics like every other default dtype: test for null with pd.isna(), never by comparing against whichever null token happened to appear.

Profile before you fix

Let the numbers drive the plan, not a glance at df.head(). Run this first, every time.

def profile(df: pd.DataFrame) -> pd.DataFrame:
    return pd.DataFrame({
        "dtype":     df.dtypes.astype(str),
        "null_pct":  (df.isna().mean() * 100).round(1),
        "n_unique":  df.nunique(dropna=True),       # cardinality — catches category sprawl
        "sample":    df.apply(lambda s: s.dropna().unique()[:3].tolist()),
    })

print(profile(df))
print("rows:", len(df), "exact dupes:", df.duplicated().sum())

A column at 90% null is a drop candidate; one with 400 distinct "countries" needs a mapping table; an "age" with min -1/max 999 has sentinels to map. The profile is your TODO list.

Normalize

Each fix below: Bad → Good, with a one-line why.

Strings — invisible variance (trailing space, mixed case, lookalike unicode) silently breaks joins and dedupe.

# BAD: "US ", "us", "us" all look different to a join
# GOOD:
s = df["country"].str.strip().str.casefold().str.normalize("NFKC")

Categories — use a mapping table, never a tower of regex. A dict is auditable and an unmapped value gets quarantined instead of silently passing through.

COUNTRY = {"usa": "US", "u.s.": "US", "united states": "US", "u.s.a.": "US", "es": "ES", "españa": "ES"}
key = df["country"].str.strip().str.casefold()
df["country"] = key.map(COUNTRY)            # unmapped -> NA, which the gate below will catch (no silent pass)

Numbers — turn sentinels into NA, then choose a range policy explicitly: clip (cap to bound) when out-of-range is plausibly a recording cap, reject (→ NA / quarantine) when it is impossible.

df["age"] = df["age"].mask(df["age"].isin([-1, 999]))   # sentinels -> NA
df["age"] = df["age"].clip(lower=0, upper=120)          # clip policy; or .mask(~df["age"].between(0,120)) to reject

Dates — state the format, coerce, then count the casualties. Never trust dayfirst inference; 03/04/2026 is ambiguous and pandas will pick silently.

parsed = pd.to_datetime(df["signup"], format="%Y-%m-%d", utc=True, errors="coerce")
bad = parsed.isna() & df["signup"].notna()
assert bad.sum() == 0, f"{bad.sum()} dates failed the expected format — inspect before proceeding"
df["signup"] = parsed

Copy-paste versions of all of these — category mapping with unmapped→quarantine, a robust date parser, unicode/encoding repair, a sentinel→NA table, numeric clip-vs-reject, plus Polars equivalents — are in references/normalization-recipes.md.

Dedupe

drop_duplicates(keep="first") is meaningless without a defined key and a stable sort — "first" of what order? Define both.

key = ["customer_id"]                                   # the BUSINESS key, stated explicitly
df = (df.sort_values(["customer_id", "updated_at"], ascending=[True, False], kind="stable")
        .drop_duplicates(subset=key, keep="first"))     # keep most-recent per customer, deterministically

Near-duplicates ("Acme Inc" vs "Acme, Inc.") are a normalization problem — collapse them in the normalize step first; only then does exact dedupe catch them. Fuzzy matching is a separate, riskier decision — make it visible, never automatic.

Missing values — decide per column

No silent fillna(0): a zero is a value, and treating "unknown" as zero poisons every mean, sum, and model downstream. Pick deliberately.

SituationActionWhy
Column is mostly null (e.g. >70%) and not load-bearingDrop the columnImputing it invents signal that isn't there
A few rows missing a required key (id, date)Drop the row (and log/quarantine)Can't dedupe or join without the key
Numeric gap you must fill for a modelImpute and add a _was_missing flagThe model can learn "was missing"; you keep the audit trail
Genuinely optional fieldLeave NANA is information; don't fabricate a value
Value is present but invalid (unmapped category, bad date)Quarantine the rowDon't drop silently and don't let it pass the gate
df["income_was_missing"] = df["income"].isna()
df["income"] = df["income"].fillna(df["income"].median())   # impute + flag, never bare fillna(0)

Validate — the gate

This is where cleaning becomes trustworthy. Declare the contract as a pandera DataFrameModel, validate output (and input expectations where they exist), and split valid rows from failures instead of crashing — the failures become your quarantine.

import pandera.pandas as pa
from pandera.typing import Series

class CustomerSchema(pa.DataFrameModel):
    customer_id: Series[int]   = pa.Field(unique=True, ge=1)
    country:     Series[str]   = pa.Field(isin=["US", "ES", "FR"])     # only mapped categories survive
    age:         Series[float] = pa.Field(ge=0, le=120, nullable=True)
    signup:      Series[pa.DateTime] = pa.Field(nullable=False)

    class Config:
        strict = True       # reject unexpected columns
        coerce = True       # coerce to declared dtype, fail loud if impossible

# lazy=True collects EVERY violation at once instead of dying on the first
try:
    valid = CustomerSchema.validate(df, lazy=True)
except pa.errors.SchemaErrors as e:
    failures = e.failure_cases          # dataframe of exactly which rows/checks failed
    failures.to_parquet("quarantine.parquet")   # keep, don't drop — someone investigates these
    valid = df.drop(index=e.failure_cases["index"].dropna().unique())  # proceed with the clean subset

coerce=True fixes types the contract expects; nullable states which columns may hold NA; field Checks (ge, le, isin, unique) are the allowed-value rules. strict catches columns that shouldn't be there. Together they are the data contract in code. Log the row-count diff on every run — in, out, coerced, quarantined — so what the pipeline changed is an auditable record, not an assumption.

When to escalate beyond pandera: reach for GX Core 1.0 (Great Expectations' rebranded OSS — Data Context → Data Source → Expectation Suite → Validation Definition → Checkpoint) when you need a shared data-quality platform across many datasets and teams with a results store and docs. Use dbt model contracts (enforced at build) plus dbt tests (post-materialization) when the cleaning lives in a SQL warehouse, not Python. The full DataFrameModel (custom @pa.check, lazy SchemaErrors report, valid/quarantine split helper), the GX checkpoint sketch, the dbt model-contract + data_tests YAML, and the "which validator" chooser are in references/validation-patterns.md.

Scale — when pandas hurts

Heuristic: pandas is fine while the data fits comfortably in RAM (roughly ≤ 1–2 GB working set). Beyond that, or when a groupby/join dominates the runtime, switch the mechanics (not the principles):

  • Polars for clean-at-scale: pl.scan_csv(...) (lazy, parallel, Rust), then .unique(), .drop_nulls(), .fill_null(...), .str.* — the same profile→normalize→dedupe→validate shape, faster. pandera validates Polars frames too, and the recipes reference has the Polars equivalent of every fix above.
  • DuckDB when the bottleneck is analytical SQL over multi-GB files — point heavy joins/aggregations there: duckdb. It is an engine choice; correctness/normalization is still this skill's job.

Anti-patterns

Anti-patternWhy it breaks
"fillna(0) to get rid of the nulls"Zero is a value; it distorts every mean/sum/model. Impute deliberately and add a _was_missing flag.
"drop_duplicates() — done"No subset, no sort → which row survives is nondeterministic. Define the key, sort_values(kind="stable"), set keep.
"pd.read_csv(path) and start cleaning"pandas guesses: ids become floats, dates become strings. Pass dtype_backend + na_values.
"I fixed the rows in a notebook cell"Not reproducible — next month's file gets nothing. Wrap it in clean(raw) -> df.
"Drop the rows that look wrong"Silent data loss with no audit trail. Quarantine to a file; someone investigates.
"A few regexes will normalize the countries"Unmaintainable and silent on new values. Use a mapping dict; unmapped → NA → caught by the gate.
"pd.to_datetime figures out the format"Ambiguous dates parse silently wrong. State format=, errors="coerce", then assert the NaT count.
"Validation passed, so we're good"A gate that never fails is a no-op. Feed it a known-bad row and confirm it rejects.
"It's slow, rewrite everything in Polars"Switch the engine, not the discipline — profile→normalize→dedupe→validate still applies.

Verify

scripts/verify.sh runs from anywhere, no network. It does static structure checks on this skill (frontmatter keys, references present) always, and — when pandas + pandera are installed — extracts the documented pattern, feeds it one clearly-good row and one clearly-bad row, and asserts the good row PASSES validation while the bad row is FLAGGED/quarantined, proving the gate is not a no-op. Without pandas/pandera it prints SKIP for the runtime check and still passes the static checks.

Project grounding (02-DOCS + CLAUDE.md)

In a project with a 02-DOCS/ layer (the harness wiki), record this dataset's cleaning decisions — the schema/contract, the category mapping tables, the dedupe key, the quarantine location, version pins — in 02-DOCS/wiki/data/<dataset>.md, link it from the root CLAUDE.md ## Knowledge map, and read it first on every re-run so the contract stays consistent. No 02-DOCS/? Skip silently. Conventions are recorded, never gated.

canvas-designanthropics/skillsCreate beautiful visual art in .png and .pdf documents using design philosophy. You should use this skill when the user asks to create a poster, piece of art, design, or other static piece. Create original visual designs, never copying existing artists' work to avoid copyright violations.Stars: 196kUpdated Claude CodeCodexVerifiedInstall
docxanthropics/skillsUse this skill whenever the user wants to create, read, edit, or manipulate Word documents (.docx files) or Word templates (.dotx files). Triggers include: any mention of 'Word doc', 'word document', '.docx', '.dotx', or requests to produce professional documents with formatting like tables of contents, headings, page numbers, or letterheads. Also use when extracting or reorganizing content from .docx or .dotx files, inserting or replacing images in documents, performing find-and-replace in Word files, working with tracked changes or comments, or converting content into a polished Word document. If the user asks for a 'report', 'memo', 'letter', 'template', or similar deliverable as a Word or .docx file, use this skill. Do NOT use for PDFs, spreadsheets, Google Docs, or general coding tasks unrelated to document generation.Stars: 196kUpdated Claude CodeCodexVerifiedInstall
pdfanthropics/skillsUse this skill whenever the user wants to do anything with PDF files. This includes reading or extracting text/tables from PDFs, combining or merging multiple PDFs into one, splitting PDFs apart, rotating pages, adding watermarks, creating new PDFs, filling PDF forms, encrypting/decrypting PDFs, extracting images, and OCR on scanned PDFs to make them searchable. If the user mentions a .pdf file or asks to produce one, use this skill.Stars: 196kUpdated Claude CodeCodexVerifiedInstall
pptxanthropics/skillsUse this skill any time a .pptx or .potx file is involved in any way — as input, output, or both. This includes: creating slide decks, pitch decks, or presentations; reading, parsing, or extracting text from any .pptx or .potx file (even if the extracted content will be used elsewhere, like in an email or summary); editing, modifying, or updating existing presentations; combining or splitting slide files; working with templates (.potx), layouts, speaker notes, or comments. Trigger whenever the user mentions "deck," "slides," "presentation," or references a .pptx or .potx filename, regardless of what they plan to do with the content afterward. If a .pptx or .potx file needs to be opened, created, or touched, use this skill.Stars: 196kUpdated Claude CodeCodexVerifiedInstall