Why this chapter matters
Aggregation and modeling are only as good as input quality, so cleaning rules must be explicit and auditable.
What you will learn
- Profile datasets with shape, dtypes, and missing-value checks.
- Convert dates and numeric fields to correct types.
- Apply duplicate and null-handling rules based on business meaning.
Understand the core ideas
pandas cleaning should be deliberate and reproducible. Start by profiling shape, dtypes, null counts, and example rows so you know what changed after each operation. Convert columns to meaningful types early, for example pd.to_datetime for dates and pd.to_numeric for amounts with controlled coercion behavior. Normalization rules, such as channel label casing or whitespace stripping, should be explicit and centralized so they are applied consistently. Cleaning is not cosmetic. It determines whether later joins, groupby summaries, and tests reflect reality.
Missing values and duplicates require policy choices tied to business meaning. A null promotional code may be acceptable, but a null order_id is often a hard failure. Duplicate records may represent retries, updates, or true data duplication, so define keys and precedence rules before drop_duplicates. Error handling matters here too. Track rows affected by coercion or dropped by key rules, and keep counts for audit. Silent coercion without reporting can hide serious ingestion defects and produce confident but wrong metrics.
Key terms
- dtype
- The pandas data type of a column, which controls valid operations and memory representation.
- coercion
- Converting invalid values to a fallback such as NaN or NaT during type conversion.
- null policy
- A documented rule for whether missing values are allowed, imputed, or rejected.
- deduplication key
- The column or column set used to decide when records represent the same entity.
Clean an orders DataFrame with explicit quality rules
A DataFrame has columns order_id, order_date, amount, channel, and promo_code. Dates are mixed formats, amounts include currency symbols, channels have inconsistent labels, and duplicate order_id rows exist.
- Profile with df.shape, df.dtypes, and df.isna().sum() so baseline quality is measured. Save these checks as logs before modifications.
- Normalize channel labels with strip and lower mappings, convert order_date using pd.to_datetime(errors='coerce'), and convert amount with string cleanup plus pd.to_numeric(errors='coerce').
- Apply null policies: reject rows with null order_id, keep null promo_code, and route rows with null converted amount into an exceptions table for review.
- Deduplicate by order_id using a defined precedence rule, for example keeping the latest order_date. Record how many rows were removed and why.
A common misconception
Claim: dropna and drop_duplicates are always safe default cleaning steps.
Correction: Blind dropping can discard valid business events. Use column-specific null rules and key-based duplicate policies that match real process semantics.
Lessons in this chapter
- Inspect before editingMeasure quality issues before transforming data.
- Type conversionStandardize date and numeric columns for correct operations.
- Nulls and duplicatesResolve missing and repeated records with explicit policy.
- Guide: pandas data cleaningApply a repeatable cleanup sequence to real tables. Read the full guide →
Study task
Chapter checkpoint
Why should numeric columns be converted before aggregation?
If numeric fields stay as strings, sums and averages can be wrong or fail unexpectedly.