Lesson 6 of 8 · 8 min · Cleaning data

Cleaning data: missing values and duplicates

Covers: pandas 3.x. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

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 NaN or NA in 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

  1. Create the sample table and count missing values per column.
  2. Convert "N/A" in salary to a real missing value.
  3. Fill the missing city with "Unknown" and the missing age with the median, logging both.
  4. Make name consistent, then find and remove the duplicate Meena.
  5. 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 duplicated and drop_duplicates with a clear key.
  • Keep a log of every cleaning decision.

Going deeper

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.