Skip to main content

Data Cleaning with Pandas

Cleaning is a transformation from an observed schema to a declared schema. Make the transformation repeatable and make rejected data visible.

Pipeline​

  1. Preserve raw input or its immutable location and provenance.
  2. Normalize column names and obvious text whitespace.
  3. Parse types with explicit formats and controlled failure behavior.
  4. Resolve duplicates and missing values using domain rules.
  5. Validate keys, ranges, categories, units, and cross-column invariants.
  6. Emit both cleaned data and a quality report.
clean = (
    raw.rename(columns=str.strip)
       .assign(
           email=lambda x: x["email"].str.strip().str.lower(),
           amount=lambda x: pd.to_numeric(x["amount"], errors="coerce"),
           occurred_at=lambda x: pd.to_datetime(
               x["occurred_at"], format="%Y-%m-%d", errors="coerce", utc=True
           ),
       )
)

errors="coerce" turns parse failures into missing values; it does not make them acceptable. Count and inspect those failures before continuing.

Text extraction​

Use vectorized string methods before Python callbacks:

parts = clean["code"].str.extract(r"^(?P<prefix>[A-Z]+)-(?P<number>\d+)$")
clean = clean.join(parts)

Regular expressions should describe the accepted format, not merely find a substring that looks plausible. Keep the original column until validation passes.

Duplicates​

Define the entity key and ordering before calling drop_duplicates:

clean = (
    clean.sort_values("updated_at")
         .drop_duplicates(subset=["entity_id"], keep="last")
)

This is a business rule. Record why the retained row is authoritative.

Validate explicitly​

assert clean["entity_id"].notna().all()
assert clean["entity_id"].is_unique
assert clean["amount"].ge(0).all()

For production boundaries, prefer a reusable schema or validation layer over a collection of ad hoc assertions.

Source​