Cleaning Data with Pandas: Answer Types, Missing Values and Duplicates Confidently
Most beginners think data cleaning means “delete the bad rows and move on.” In real business data, the dangerous errors are quieter - a revenue column stored as text, the same customer counted twice, or missing values that are actually a signal.
- Data cleaning in pandas means making a DataFrame fit for analysis by fixing types, missing values, duplicates, invalid values and inconsistent labels.
- Always start with the business grain: one row represents one order, one customer, one SKU, one transaction or one day.
- Use `df.info()`, `df.describe()`, `df.isna().sum()`, `df.duplicated().sum()` and `df.nunique()` before changing anything.
- Type errors come first: numbers, dates, booleans, categories and IDs must be stored in analysis-ready dtypes.
- Missing values are not one problem: you either drop, impute, flag, leave as missing, or investigate source-system gaps.
- Duplicates are defined by the business key, not by visual similarity. `drop_duplicates()` without a key can destroy valid rows.
- A strong answer ends with validation: row counts, null rates, duplicate rates, schema checks and before-after reconciliation.
Big Picture: Cleaning Is a Loop, Not a One-Time Command
Think of pandas data cleaning as a disciplined loop: first understand the raw DataFrame, then diagnose quality issues, apply controlled fixes, validate the output, and document the assumptions. If the validation fails, you loop back instead of forcing the analysis.
Core Explanation: The Five Decisions That Make Data Analysis-Ready
The practical objective is simple: convert a messy DataFrame into a reliable analytical table. The deeper skill is knowing which cleaning action is valid for the business question. For example, a missing coupon code in an order table may mean “no coupon used,” while a missing customer age may mean “unknown.” Treating both the same is poor analysis.
1. Data Types: The First Cleaning Battle
Pandas can store a column in the wrong dtype and still let your code run. That is dangerous because calculations, sorting, joins and charts may silently become wrong.
Placement-ready phrase: “Before cleaning missing values, I first check whether missingness is real or created by parsing errors, especially in date and numeric columns.”
2. Missing Values: Choose the Treatment, Not the Shortcut
In pandas, missing values appear as `NaN`, `None`, `NaT` for missing dates, or `pd.NA` in newer nullable dtypes. The right treatment depends on the field, the business question and whether the missingness itself carries meaning.
3. Duplicates: Decide the Business Key Before `drop_duplicates()`
A duplicate is not always an identical row. In an orders table, two rows with the same customer and amount may be valid separate purchases. But two rows with the same `order_id` may be a duplicate if `order_id` is the unique business key.
Typical duplicate handling uses `df.duplicated(subset=["order_id"])`, `df.sort_values()` to decide which record to keep, and `df.drop_duplicates(subset=["order_id"], keep="last")` only after you know the latest record is the correct one.
Definitions You Should Be Able to Say in One Breath
- Data cleaning: The process of detecting, correcting or removing data issues so analysis reflects the business reality.
- Data type: The storage and interpretation format of a column, such as numeric, string, datetime, boolean or category.
- Missing value: A cell where the expected value is absent, unknown, not applicable or not captured.
- Duplicate: A record that repeats the same business entity at the chosen grain and key.
- Business grain: The exact meaning of one row in the dataset.
Quality Metrics to Track After Cleaning
If you say “I cleaned the data,” an interviewer may ask: “How do you know?” Answer with measurable checks, not confidence. Use these metrics before and after cleaning.
Worked Example: Cleaning Health-Commerce Orders
Suppose you receive 1,000 order rows. `delivery_date` is missing in 60 rows, 25 rows repeat the same `order_id`, and 15 rows have invalid amount values such as text or negative amounts.
The important insight: you do not “fix” all three issues the same way. Delivery date requires business interpretation, duplicate order IDs require key-based deduplication, and invalid amount requires parsing plus rule validation.
Case Study: Tata 1mg and the Discipline of Cleaning Health-Commerce Data
Tata 1mg shows why data cleaning matters when product catalogues, prescriptions, diagnostics and order data must be reliable before analytics or personalisation.
Situation: A health-commerce platform like Tata 1mg operates across medicine catalogues, diagnostics bookings, prescriptions, user profiles, payments and delivery records. The same medicine may appear with spelling variants, pack-size differences or manufacturer naming inconsistencies. Orders can also have missing delivery fields because some orders are cancelled, pending, returned or fulfilled through different operational paths.
The move: A strong analyst would not start with a model. They would first define the grain - for example, one row per order line or one row per SKU. Then they would standardize data types, map category labels, separate “not applicable” from “unknown,” deduplicate using stable identifiers, and validate against business rules such as non-negative quantity, valid order status and realistic order dates.
Outcome or lesson: The primary driver of trustworthy analysis is a stable business grain and identifier logic, supported by schema checks, domain rules, and documented assumptions. The lesson is powerful for interviews: in sensitive categories like health, cleaning data is not just technical hygiene - it protects business decisions and customer trust.
How AI Changes Cleaning Data with Pandas
AI does not remove the need to understand data. It changes how quickly you can profile, explain and validate messy datasets.
- Faster profiling and code generation: LLMs can suggest pandas checks for nulls, dtypes, outliers and duplicates once you describe the DataFrame. You still decide the business rules.
- Smarter anomaly detection: ML can flag unusual values such as impossible delivery times, abnormal discounts or sudden category spikes that simple rules may miss.
- Schema and documentation assistance: AI can help generate data dictionaries, validation rules and cleaning logs, which is useful when multiple analysts work on the same dataset.
Load a sample CSV summary, column dictionary and your cleaning assumptions into ChatGPT or Claude. Ask: “Generate a pandas cleaning checklist, possible business meanings of missing values, and validation tests for this dataset.” Then manually verify every rule before using it.
The 2026 skill is not “AI wrote my pandas code.” The skill is: “I used AI to accelerate profiling, but I owned the business logic, validation and interpretation.”
Interview Relevance
“You are given a messy sales CSV with wrong data types, missing customer ages and duplicate order IDs. How would you clean it in pandas before analysis?”
Use this line in answers: “I would avoid cleaning choices that change the business meaning of the data, especially dropping missing values or duplicates without checking bias and grain.”
Common Mistake
The single biggest mistake is using `dropna()` and `drop_duplicates()` blindly. It costs candidates because it shows command knowledge without analytical judgment. The fix: first define the business grain, key and meaning of missingness - then apply the pandas command.
What to Revise Next
Once your DataFrame is clean, the next skill is extracting business insight from it. Revise these in sequence: