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
- Preserve raw input or its immutable location and provenance.
- Normalize column names and obvious text whitespace.
- Parse types with explicit formats and controlled failure behavior.
- Resolve duplicates and missing values using domain rules.
- Validate keys, ranges, categories, units, and cross-column invariants.
- Emit both cleaned data and a quality report.
import pandas as pd
raw = pd.DataFrame({
"entity_id": [1, 1, 2],
"email": [" ADA@example.org ", "ada@example.org", "lin@example.org"],
"amount": ["10", "12", "bad"],
"occurred_at": ["2026-01-01", "2026-01-02", "invalid"],
"updated_at": pd.to_datetime(["2026-01-01", "2026-01-02", "2026-01-03"]),
"code": ["AB-01", "AB-02", "CD-03"],
})
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.
The following step separates rejected rows before deduplication and validation. For this sample, row 2 is rejected and the later record for entity 1 is retained, with amount 12.
invalid = clean["amount"].isna() | clean["occurred_at"].isna()
rejected = raw.loc[invalid].copy()
clean = clean.loc[~invalid].copy()
Text extraction
Use vectorized string methods before Python callbacks:
parts = clean["code"].str.extract(r"^(?P<prefix>[A-Z]+)-(?P<number>[0-9]+)\Z")
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", kind="stable")
.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"].notna().all()
assert clean["amount"].ge(0).all()
For production boundaries, prefer a reusable schema or validation layer over a collection of ad hoc assertions.
Keep validation meaningful
To distinguish parse failures from pre-existing missing values, compare the raw column’s notna() with the parsed column’s isna() on the same index before filtering. The extraction returns two string columns, not an integer: convert number explicitly if arithmetic is intended. [0-9] restricts digits to ASCII and \Z requires the actual end of the string.
Lowercasing the entire email address assumes the application treats addresses case-insensitively; RFC 5321 §2.4 requires preserving the case of mailbox local-parts, although exploiting their case sensitivity is discouraged. Here utc=True interprets the date-only inputs as UTC midnight, not local midnight. Deduplication assumes parsed, non-missing update times; a stable sort makes tied times keep the last input row, which is only valid if that is the chosen tie-break rule. Assertions can be disabled by Python -O; required checks should raise explicit exceptions.