Lesson 6 of 8 · 8 min · Cleaning data
Cleaning data: missing values and duplicates
What you will learn
- Count and locate missing values
- Choose between dropping, filling and flagging them
- Fill with a constant, a statistic, the previous value or a group average
- Find and remove duplicate rows without losing good data
- Log what you changed so your cleaning can be trusted
In simple words
Real data is rarely tidy: some cells are empty and some rows appear twice. If you ignore this, averages are wrong and totals are inflated. Cleaning means first finding the problems, then deciding for each one what to do and writing the decision down, so that anyone can see the numbers are fair.
Words to know
- Missing value: an empty cell, shown as
NaNorNAin pandas. - Imputation: filling a missing value with a reasonable estimate.
- Duplicate: a row that repeats another row, wholly or in its key columns.
- Audit trail: a record of what was changed and why.
The messy sample
We use one small messy table for all the cleaning lessons:
raw = pd.DataFrame({
"name": [" asha ", "RAVI", "Meena", "meena", None, "Kiran"],
"city": ["Hyderabad", "pune", "Chennai", "Chennai", "Hyderabad", None],
"age": [24, 31, None, 29, 27, 150],
"signup": ["2025-06-10", "10/07/2025", "2025-07-15", "2025-07-15", "2025-08-01", "not known"],
"salary": ["₹4,50,000", "6,00,000", "5,25,000", "5,25,000", "N/A", "4,00,000"],
})
Always work on a copy (df = raw.copy()), so the original stays for comparison.
Find the missing values
df.isna().sum() # missing per column
df.isna().mean().round(3) # share missing per column
df[df.isna().any(axis=1)] # rows with at least one missing value
df.notna().all(axis=1) # rows that are fully complete
For the sample, name has 1 missing, city 1 and age 1. Missing can also hide behind placeholder text such as "N/A", "-", "unknown", 0 or 999. Look at value_counts() of each column and convert placeholders to real missing values:
df["salary"] = df["salary"].replace("N/A", pd.NA)
df = pd.read_csv("file.csv", na_values=["N/A", "-", "unknown"]) # do it at load time
Decide: drop, fill or flag
Ask why the value is missing and what you will use the data for.
| Option | When it fits | How |
|---|---|---|
| Drop the row | few rows affected, missing at random, the column is essential | df.dropna(subset=["name"]) |
| Drop the column | most values missing and little use | df.drop(columns="col") |
| Fill with a constant | a missing category has meaning ("Unknown"), or zero is true | df["city"].fillna("Unknown") |
| Fill with a statistic | numeric column, modest gaps | df["age"].fillna(df["age"].median()) |
| Fill from neighbours | ordered data such as time series | ffill(), bfill(), interpolate() |
| Fill by group | values differ by category | df["x"].fillna(df.groupby("g")["x"].transform("median")) |
| Keep and flag | missingness itself is informative | df["age_missing"] = df["age"].isna() |
Remember that pandas methods return new objects, so assign back:
df["city"] = df["city"].fillna("Unknown")
df["age"] = df["age"].fillna(df["age"].median())
In pandas 3, fillna(..., inplace=True) on a selected column does not change the DataFrame (chained access), so avoid inplace and assign.
Be careful with filling numbers. Filling with the median keeps the typical value but shrinks the spread and can hide a pattern; never fill a target value you are about to predict or report as fact. Record how many values you filled. Also remember that sum and mean skip missing values by default, so a mean over a column with gaps is the mean of the known values.
Duplicates
df.duplicated() # True for the second and later copies
df.duplicated().sum() # how many extra copies
df[df.duplicated(keep=False)] # show every row in a duplicate set
df.duplicated(subset=["name", "signup"], keep=False) # duplicates by key columns
In the sample, rows 2 and 3 (Meena/meena) are the same person, but duplicated() says no, because "Meena" and "meena" differ in case. Normalise first, then check:
df["name"] = df["name"].str.strip().str.title()
df.duplicated(subset=["name", "city", "signup"], keep=False)
After cleaning the text in the next lesson, both Meena rows match. Remove duplicates:
df = df.drop_duplicates(subset=["name", "city", "signup"], keep="first")
keep="first" keeps the first copy, "last" the last, False drops all copies. When duplicates differ in some columns (the same customer with two addresses), decide which to keep: the latest by date, the most complete row, or merge the information. Sorting first helps:
df = df.sort_values("signup").drop_duplicates(subset="name", keep="last")
Check the effect: compare len(df) before and after, and look at the removed rows before you trust the result.
Keep a record
Cleaning without notes cannot be audited. Keep a small log:
log = []
before = len(df)
df = df.drop_duplicates(subset=["name", "city", "signup"])
log.append(f"Removed {before - len(df)} duplicate rows (same name, city, signup)")
filled = df["age"].isna().sum()
df["age"] = df["age"].fillna(df["age"].median())
log.append(f"Filled {filled} missing ages with the median")
print("\n".join(log))
Add this to the report or notebook so a reader knows what was assumed.
Common mistakes
- Dropping rows with
dropna()blindly and losing most of the data. - Leaving placeholder text such as "N/A" or 999 as real values.
- Checking duplicates before normalising text, missing near-duplicates.
- Filling with the mean on skewed data where the median is a better centre.
- Removing duplicates that are legitimately repeated events (two real orders of the same amount on the same day). Use a unique id when you have one.
Try it yourself
- Create the sample table and count missing values per column.
- Convert
"N/A"in salary to a real missing value. - Fill the missing city with
"Unknown"and the missing age with the median, logging both. - Make
nameconsistent, then find and remove the duplicate Meena. - Write down, for each column, why you chose to drop, fill or flag.
Check yourself
How do you count missing values in each column?
df.isna().sum().
When is it better to fill with the median than the mean?
When the data is skewed or has outliers, because the median is not pulled by extreme values.
What does df.duplicated(keep=False) return?
True for every row that belongs to a set of duplicates, including the first copy.
Why should you clean text before looking for duplicates?
Differences in case, spaces or spelling make equal records look different, so duplicates are missed.
Interview questions
How do you handle missing data?
First understand why it is missing and how much; then drop, fill with a constant or a statistic, fill from neighbours or groups, or flag it. Choose by the analysis, record the decision, and check that the cleaning did not change conclusions.
How would you remove duplicates but keep the most recent record per customer?
Sort by the date column and call drop_duplicates(subset="customer_id", keep="last").
What is the risk of mean imputation?
It reduces variance and can bias relationships, because many rows get the same artificial value. Consider the median, group-wise fills or leaving the values missing.
Summary
- Find gaps with
isna().sum()and convert placeholder text to real missing values. - Choose to drop, fill or flag according to why values are missing.
- Normalise text before detecting duplicates; use
duplicatedanddrop_duplicateswith a clear key. - Keep a log of every cleaning decision.
Going deeper
- Working with missing data:
isna,fillna,dropnaand nullable types. - DataFrame.duplicated and drop_duplicates: options for
subsetandkeep. - Copy-on-Write: why to assign results instead of using
inplace.
See how much of this you can explain
A free mock interview asks you new questions on Python for data analysis with pandas for Data Analyst and scores every answer, with what to fix.