In pandas, clean data in a fixed order: inspect, standardize types, handle missing values, normalize categorical labels, then remove duplicates by business key. Validate counts after each step so cleanup decisions remain auditable.
- Use DataFrame.info and value_counts to profile before editing.
- Convert dates and numeric fields early to avoid silent string behavior.
- Drop duplicates only after defining a real uniqueness key.
Profile and normalize the orders dataset
Start with orders.csv that contains order_id, order_date, channel, amount, and status. Check dtypes and missing values, then normalize channel labels like Web, web, and WEB into one standard value.
Use pd.to_datetime for date fields and pd.to_numeric with explicit error handling for numeric columns such as amount.
Handle missing values and duplicates deliberately
Missing values can mean unknown, not zero. Keep that meaning in mind when filling or dropping rows. For duplicate order IDs, verify whether they are true repeats or partial updates before removing records.
- Keep raw and cleaned DataFrames separate during development.
- Track how many rows each cleanup step changes.
- Persist a small QA sample for manual inspection.
Standardize and deduplicate order records
A DataFrame loaded from orders.csv includes inconsistent channel labels and repeated order IDs.
- Run df["channel"] = df["channel"].str.strip().str.lower() to normalize labels.
- Convert df["order_date"] with pd.to_datetime and df["amount"] with pd.to_numeric.
- Drop duplicates on order_id after reviewing collisions and keep the latest timestamp.
Common mistakes
- Filling all nulls with zero without checking business meaning.
- Dropping duplicates before normalizing text and types.
- Chaining many transforms without checking intermediate row counts.
- Editing the only raw copy and losing traceability.
Try one
Why should type conversion happen before most aggregations in pandas?
Because string-typed numeric or date fields can produce wrong groupings and calculations.
Sources
- Python documentationOfficial reference for Python syntax, standard library modules, and tutorials.
- pandas documentationOfficial reference for pandas DataFrame operations, indexing, and analysis APIs.