Data cleaning in Python is not a ritual for making every table look tidy. It is a documented set of decisions that makes data fit for a defined use while preserving raw evidence, exceptions, and lineage.

Begin with a data contract

Record the unit of observation, keys, types, allowed values, null meanings, time zone, precision, freshness, reconciliation totals, and downstream use before changing records. Keep immutable raw input and produce an exception table containing record keys, rule identifiers, original values, corrected values, and reason codes.

Profile before correcting

import pandas as pd

raw = pd.read_csv("customers.csv", dtype_backend="numpy_nullable")
profile = pd.DataFrame({
    "dtype": raw.dtypes.astype(str),
    "missing": raw.isna().sum(),
    "distinct": raw.nunique(dropna=False),
})
print(profile)

Pin and test the pandas version used in production. Counts reveal symptoms, not causes: a null may mean unknown, not applicable, withheld, delayed, or failed parsing.

Parse explicitly

clean = raw.copy()
clean["event_time"] = pd.to_datetime(
    clean["event_time"], format="ISO8601", utc=True, errors="coerce"
)
clean["amount"] = pd.to_numeric(clean["amount"], errors="coerce")

Do not silently strip currency punctuation unless locale, currency, sign, and decimal conventions are known. Store parse failures separately. Avoid chained or column-level in-place assignment; explicit assignment behaves predictably with pandas Copy-on-Write.

Handle missing values by mechanism and consequence

Deletion, imputation, a missing-category label, or leaving a value missing can each be defensible. Choose from the measurement process and intended analysis. Report how many rows and measures change, compare relevant distributions, and retain a missingness indicator when it has operational meaning. Forward fill is appropriate only when carrying the last observation forward matches the domain and ordering.

Define duplicates as a business rule

ordered = clean.sort_values(
    ["customer_id", "updated_at", "source_priority", "record_id"]
)
duplicate_candidates = ordered.duplicated("customer_id", keep=False)
review = ordered.loc[duplicate_candidates]
deduplicated = ordered.drop_duplicates("customer_id", keep="last")

Exact duplicate rows, repeated events, and multiple records for one entity are different problems. A deterministic survivor rule needs a stable tie-breaker and reconciliation tests.

Treat outliers as review candidates

IQR fences and z-scores describe values relative to a distribution; they do not prove error. Skew, small samples, mixtures, seasonality, and legitimate rare events can make automatic deletion destructive. Flag candidates, investigate source context, and document any cap, transformation, exclusion, or robust model.

Prevent machine-learning leakage

Choose a random, grouped, temporal, or geographic split that matches deployment. Fit imputers, encoders, scalers, selectors, anomaly thresholds, and models on training folds only, preferably in a scikit-learn pipeline. Keep the final test set untouched by cleaning-rule and hyperparameter selection. Structural schema checks fixed independently of outcomes may run before splitting when documented.

Operational checklist

  • Version rules, schemas, reference data, and software.
  • Quarantine failures instead of silently coercing them.
  • Make retries idempotent and reconcile row counts and totals.
  • Monitor freshness, distributions, rule failures, and source drift.
  • Require review for ambiguous or destructive corrections.

For related foundations, see handling missing data, feature engineering for machine learning, and data visualization best practices.