Data Cleaning skill for Codex

Clean data is data whose every change can be explained: the raw input is untouched, each rule is a named function, every row dropped or altered is counted in a cleaning log, and nothing is “fixed” by guessing without the user knowing. Flag before you delete.

Inspect before changing anything

1. Read AGENTS.md for the dataframe library (pandas 2.x or Polars), where raw data lives, and whether cleaning code belongs in a pipeline module or a notebook.
2. Keep raw data immutable. Read from data/raw/, write to data/interim/ or data/clean/. Never overwrite the source file.
3. Profile every column before writing rules:

import pandas as pd

df = pd.read_csv("data/raw/customers.csv", dtype=str, keep_default_na=False)
profile = pd.DataFrame({
    "non_empty": df.ne("").sum(),
    "unique": df.nunique(),
    "sample": df.apply(lambda s: s[s.ne("")].drop_duplicates().head(5).tolist()),
})
print(len(df), "rows"); print(profile.to_string())

Reading everything as strings first shows what is really in the file ("N/A", "-", "1,234.50", "12/03/24") before pandas guesses. Loading problems such as encodings and delimiters belong to ingest; hand off to $csv-data-ingest if the file will not parse.

4. Ask the user what each ambiguous field means before deciding: is 0 in age missing or a real value? Is 03/04/2025 March or April? Is a negative quantity a return?

Cleaning order

Order matters: types before ranges, normalisation before deduplication.

- [ ] 1. Standardise column names (snake_case, strip, de-duplicate)
- [ ] 2. Map sentinel and empty values to null
- [ ] 3. Normalise text: Unicode NFKC, whitespace, case where meaningless
- [ ] 4. Parse types: numbers, dates, booleans, categories
- [ ] 5. Validate ranges and cross-field rules; flag violations
- [ ] 6. Standardise categories with an explicit mapping table
- [ ] 7. Deduplicate: exact, then key-based, then fuzzy (reviewed)
- [ ] 8. Decide missing-value treatment per column
- [ ] 9. Flag outliers; remove only with a stated rule
- [ ] 10. Write cleaned data + cleaning log; rerun checks

Rules with code

import re
import unicodedata
import numpy as np
import pandas as pd

SENTINELS = {"", "na", "n/a", "null", "none", "-", "--", "?", "unknown", "#n/a"}

def to_null(s: pd.Series) -> pd.Series:
    return s.mask(s.str.strip().str.lower().isin(SENTINELS))

def norm_text(s: pd.Series) -> pd.Series:
    return (s.map(lambda v: unicodedata.normalize("NFKC", v) if isinstance(v, str) else v)
             .str.replace(r"\s+", " ", regex=True).str.strip())

def parse_number(s: pd.Series, decimal: str = ".") -> pd.Series:
    thousands = "," if decimal == "." else "."
    cleaned = (s.str.replace(r"[^\d,.\-()]", "", regex=True)       # drop currency, spaces
                .str.replace(r"^\((.*)\)$", r"-\1", regex=True)     # (12.50) -> -12.50
                .str.replace(thousands, "", regex=False)
                .str.replace(decimal, ".", regex=False))
    return pd.to_numeric(cleaned, errors="coerce")

def parse_date(s: pd.Series, fmt: str) -> pd.Series:
    return pd.to_datetime(s, format=fmt, errors="coerce")

• Always pass an explicit format= to to_datetime once you know it. Mixed formats: parse each known format separately and combine_first, then count what is still null.
• After every errors="coerce", count the new nulls and list examples. Coercion that silently nulls 8% of a column is data loss, not cleaning.

def coerced(raw: pd.Series, parsed: pd.Series) -> pd.Series:
    """Values that were present but failed to parse."""
    return raw[raw.notna() & parsed.isna()]

bad = coerced(df["amount"], df_clean["amount"])
print(len(bad), bad.value_counts().head(10))

Decision tables

Missing values

Situation	Treatment
Missing has meaning (no second phone, no cancellation date)	Keep null; document meaning
Few rows (<1-2%), missing at random, analysis needs complete rows	Drop rows, log count
Numeric feature for modelling	Impute in the modelling pipeline (fit on train only) and add an _was_missing flag
Key or identifier missing	Quarantine the row; never impute
Many missing (>40%)	Report it; ask whether the column is usable

Do not impute in a shared cleaned dataset; imputation is a modelling choice.

Duplicates

Type	Detect	Resolve
Exact row duplicates	df.duplicated(keep=False)	Drop extras, log count
Same key, different values	df.duplicated(subset=["customer_id"], keep=False)	Survivorship rule: latest updated_at, or most complete row; keep the losers in a side table
Same entity, different keys	Normalised match keys (lowercased email, digits-only phone, postcode + name)	Merge only on strong keys automatically
Fuzzy matches (typos, abbreviations)	rapidfuzz on a blocking key (same postcode/city)	Produce a candidate list with scores for human review; never auto-merge on similarity alone

Outliers

Signal	Rule
Impossible (negative age, date in 2099, quantity 1e9)	Set to null or quarantine; these are errors
Implausible but possible	Flag with IQR (outside Q1-1.5IQR, Q3+1.5IQR) or MAD (modified z = 0.6745*(x-median)/MAD > 3.5)
Heavy-tailed by nature (revenue, latency)	Keep; analyse on log scale or with medians; do not trim

def mad_flag(x: pd.Series, threshold: float = 3.5) -> pd.Series:
    med = x.median()
    mad = (x - med).abs().median()
    if mad == 0:
        return pd.Series(False, index=x.index)
    return (0.6745 * (x - med) / mad).abs() > threshold

Text and categories

• Repair mojibake (é for é) with ftfy.fix_text if installed (third-party, pip install ftfy, needs network); otherwise re-read the file with the right encoding.
• Map categories through an explicit dictionary checked into the repo ({"U.S.": "US", "usa": "US", "United States": "US"}), and fail loudly on unmapped values rather than passing them through.
• Strip invisible characters: zero-width space ​, non-breaking space \xa0 (NFKC turns the latter into a space), BOM  at the start of the first header.
• Phone numbers, emails and postcodes: normalise to a canonical form for matching, keep the original for display.

Polars equivalents

import polars as pl

clean = (
    pl.read_csv("data/raw/customers.csv", infer_schema_length=0)   # all Utf8
    .with_columns(
        pl.col("email").str.strip_chars().str.to_lowercase(),
        pl.col("amount").str.replace_all(r"[^\d.\-]", "").cast(pl.Float64, strict=False),
        pl.col("signup").str.strptime(pl.Date, "%d/%m/%Y", strict=False),
    )
    .unique(subset=["customer_id"], keep="last", maintain_order=True)
)

strict=False nulls failures just like errors="coerce"; count them the same way.

The cleaning log

Every rule appends one record; write it next to the output.

log: list[dict] = []

def record(rule: str, column: str, affected: int, note: str = "") -> None:
    log.append({"rule": rule, "column": column, "rows_affected": affected, "note": note})

pd.DataFrame(log).to_csv("data/clean/customers.cleaning_log.csv", index=False)

Gotchas

• pd.read_csv turns "NA" (Namibia), "NULL" surnames and leading-zero IDs into nulls or integers by default. Read as strings first, convert deliberately.
• df.drop_duplicates() before normalising whitespace and case misses most duplicates.
• Float equality: compare money as integers (cents) or Decimal, not floats.
• Excel-touched files: 1-2 became a date, long IDs became 1.23E+15. The originals are gone; ask for a fresh export.
• Chained assignment (df[df.a > 0]["b"] = 1) does nothing under pandas copy-on-write; use df.loc[mask, "b"] = 1.

Verification

• Row count in = rows out + rows dropped + rows quarantined, per the log.
• Every target column has the intended dtype (df.dtypes), with null counts explained.
• Range and uniqueness assertions pass on the output:

assert df["customer_id"].is_unique
assert df["age"].between(0, 120).all() or df["age"].isna().any()
assert set(df["country"].dropna()) <= ALLOWED_COUNTRIES

• Spot-check 10 random changed rows against the raw file by key.

Report template

**Input:** data/raw/customers.csv - 48,213 rows, 14 columns
**Output:** data/clean/customers.parquet - 46,980 rows; quarantine 412 rows (missing customer_id)
**Changes:** amount parsed (EU decimals, 37 unparseable -> null); signup dd/mm/yyyy;
country mapped (23 variants -> 9 ISO codes); 821 exact duplicates removed
**Flagged, not removed:** 64 orders > 3.5 modified z-score on amount (column is_outlier)
**Needs a decision:** 1,140 fuzzy name matches in data/clean/dedupe_candidates.csv
**Log:** data/clean/customers.cleaning_log.csv