Pandas Data Cleaning Essentials
Clean a messy orders table with pandas: fix text, types, dates, duplicates, impossible values and missing data, in an order that avoids the usual mistakes. Every step is shown with its real output.
Data cleaning in pandas is a sequence: standardise text, convert types, remove duplicates, flag impossible values, then handle missing data. The order matters because duplicates hide behind inconsistent spelling. Interviewers expect awareness of silent errors, such as pandas reading the ambiguous date 06/01/2026 month first as 1 June unless told otherwise.
Real data arrives with stray spaces, mixed date formats, duplicate rows, impossible values and gaps. Cleaning is a sequence: standardise text, convert types, remove duplicates, flag impossible values, then handle missing data. The order matters, because duplicates hide behind inconsistent spelling and missing-value rules depend on what you decided counts as invalid.
- In pandas, df.isna().sum() counts missing values per column, but placeholders such as "-" or 0 are not detected as null and need a separate check.
- pd.to_numeric and pd.to_datetime with errors='coerce' turn unparseable values into NaN or NaT instead of raising an error.
- pandas parses the ambiguous date 06/01/2026 month first as 1 June 2026 by default, and dayfirst=True with format='mixed' also flips ISO dates.
- drop_duplicates should run after text is standardised, because " Asha " and "Asha" do not count as equal until whitespace is stripped.
- Filling missing values with groupby().transform('median') uses each group's median, which resists outliers better than the mean.
01Start with the messy table
The table below is small on purpose so every problem is visible. It has padded and mixed-case names, a blank amount, an "N/A" amount (read_csv already turns "N/A" into NaN by default), a negative amount, a duplicated row and two date formats.
import pandas as pd, numpy as np
from io import StringIO
raw = StringIO("""order_id,customer,city,amount,order_date
1001, Asha ,Hyderabad,250,2026-01-05
1002,ravi,hyderabad,,2026-01-06
1003,Meena,Chennai,1200,06/01/2026
1003,Meena,Chennai,1200,06/01/2026
1004,Asha,HYDERABAD,-50,2026-01-08
1005,Kiran,Pune,N/A,2026-01-09
1006,Ravi,Pune,300,not a date
""")
df = pd.read_csv(raw)
print(df.isna().sum().to_dict())The output is {'order_id': 0, 'customer': 0, 'city': 0, 'amount': 2, 'order_date': 0}. Only two missing values are detected, yet the table has more problems than that. Counting nulls is a start, never the whole audit.
02Step 1: standardise text
df['customer'] = df['customer'].str.strip().str.title()
df['city'] = df['city'].str.strip().str.title()After this, " Asha " becomes "Asha", and "ravi" and "Ravi" match. "hyderabad" and "HYDERABAD" both become "Hyderabad". Do this first. A group-by on city before this step would treat three spellings of Hyderabad as three cities and quietly give wrong totals.
03Step 2: convert types
df['amount'] = pd.to_numeric(df['amount'], errors='coerce')
df['order_date'] = pd.to_datetime(df['order_date'], errors='coerce', format='mixed')errors='coerce' replaces anything unparseable with NaN (or NaT for dates), so "not a date" in the date column becomes NaT, a missing value you can see and count. (The amount column is already numeric here, because read_csv turned the blank and "N/A" into NaN when loading; the same call would also catch text such as "12,50" or "$300" that read_csv leaves as strings.) The alternative, errors='raise', stops at the first bad value, which is useful when you want to know the data is clean.
There is a trap here. The value 06/01/2026 was parsed as 1 June 2026, month first, because that is pandas' default. If your source meant 6 January, as many Indian and European systems would, the result is silently wrong. Check what the source system uses. Do not just add dayfirst=True to this call: with format='mixed' it also flips the ISO dates (2026-01-05 becomes 1 May 2026) while fixing 06/01/2026. Parse each format separately instead, for example pd.to_datetime(s, format='%Y-%m-%d', errors='coerce') for the ISO rows and format='%d/%m/%Y' for the rest, then combine them with fillna. Silent misparsing of ambiguous dates is one of the most common real data bugs.
04Step 3: remove duplicates
print(len(df)) # 7
df = df.drop_duplicates()
print(len(df)) # 6The two rows for order 1003 are identical, so one is removed. Doing this after step 1 matters: "Asha" and " Asha " would not have counted as equal before stripping, and the duplicate would have survived.
For partial duplicates use drop_duplicates(subset=['order_id'], keep='last') when a key should be unique, and decide which copy to keep on purpose, for example the latest.
05Step 4: flag impossible values
df.loc[df.amount < 0, 'amount'] = np.nan
print(df.amount.isna().sum()) # 3A negative amount could be a refund, a data entry sign error, or a corrupt record. Do not decide silently. Here we treat it as invalid and mark it missing, which brings the missing count to three (rows 1002, 1004 and 1005). Writing the rule as a visible line of code, rather than fixing it by hand in a spreadsheet, makes the decision reviewable and repeatable.
The same idea covers ages of 250, future dates of birth, or percentages above 100.
06Step 5: handle missing values
city_median = df.groupby('city').amount.transform('median')
df['amount'] = df.amount.fillna(city_median).fillna(df.amount.median())
print(df)The result for amount is 250, 250, 1200, 250, 300 and 300 for the six orders. Each gap takes the median of its own city, and the overall median covers any city with no valid value at all. The median is used rather than the mean because one large order, like the 1200 here, drags a mean upward.
Filling is a modelling decision, not a cleaning formality. It shrinks the apparent variation and can invent patterns. Options are to drop the rows, fill with a statistic, or keep the NaN and add a column such as amount_was_missing so later analysis can see what was imputed. The missing order date for 1006 is left as NaT, because inventing a date would be worse than admitting we do not know it.
07Common interview questions
Q: How do you find missing values?
A: df.isna().sum() for counts, and df.isna().mean() for the share per column. Also look for placeholders such as "N/A", "-" or 0 that are not detected as null.
Q: drop or fill missing values?
A: Drop when few rows are affected and the loss is random. Fill when losing rows would bias the analysis. Always say what you did.
Q: What does errors='coerce' do?
A: It converts values that cannot be parsed into NaN or NaT instead of raising an error.
Q: Why use the median to fill?
A: It is not pulled by outliers, unlike the mean.
Q: apply or vectorised operations?
A: Use vectorised string and arithmetic methods where they exist. They are faster and shorter than apply with a Python function.
Q: What is a good first step on a new dataset?
A: Check shape, dtypes, null counts and a few sample rows before changing anything.