Pandas data cleaning turns messy real-world data into something you can actually analyze. Learn to detect and handle missing values (NaN, short for Not a Number), remove duplicates, fix data types, handle outliers with IQR (Interquartile Range), and build a systematic cleaning pipeline you can reuse on any dataset.

“Tidy datasets are all alike, but every messy dataset is messy in its own way.”

Hadley Wickham, R for Data Science

Last Updated: July 2026 | Tested on: Python 3.14.6, Pandas 3.0.3 | Difficulty: Advanced | Reading Time: 15 minutes

Real data is messy. Customer ages of -5 or 999. Missing phone numbers stored as empty strings, “N/A”, “null”, or an actual NaN. Duplicate records left behind by a system migration. Dates written three different ways inside the same column. Think of it like a kitchen before you cook: the vegetables are still muddy and the knives are in the wrong drawer. You wash and sort first, then you cook. Skip the cleaning and every analysis you build on top tastes like dirt.

This post gives you a simple, repeatable pandas data cleaning routine that turns messy data into something you can trust. When you want to run that routine against a full dataset end to end, the pandas project walkthrough gives you exactly that practice.

A data scientist named Niranjan once trained a machine learning model on raw customer data and got 99% accuracy. He was thrilled, until he looked closer. The dataset was full of duplicate rows, so the same records showed up in both training and testing. The model was not learning anything, it was just recognizing copies it had already seen. After he removed the duplicates and cleaned the rest, accuracy dropped to a realistic 78%. That 78% was the honest number he could actually ship. The 99% was a lie his data had told him.

Tip: click the diagram to open it full screen, then use the zoom and pan controls for a closer look.

The diagram shows a data cleaning pipeline in four stages: handle missing values (dropna(), fillna()), remove duplicates (drop_duplicates()), fix data types (astype(), pd.to_datetime()), and detect outliers (IQR or z-score methods). Almost every real dataset needs some mix of these steps before analysis can begin. Doing them in this order, missing values first, then duplicates, then types, then outliers, keeps one problem from hiding another. For example, if you fix types before you drop duplicate rows, you waste effort converting rows you are about to delete anyway.

Prerequisites

Complete Pandas loc and iloc tutorial. You should be comfortable with loc, iloc, and boolean indexing.

Recipe 1: Detecting Missing Values

📄 missing_detection.py: find what is missing

import pandas as pd
import numpy as np

# Messy dataset
df = pd.DataFrame({
    "name": ["Rahul", "Niranjan", "Viraj", None, "Sardar", "Prathamesh"],
    "age": [28, 31, np.nan, 29, 27, 33],
    "city": ["Mumbai", "Pune", "Mumbai", "Delhi", np.nan, "Mumbai"],
    "salary": [75000, np.nan, 71000, 65000, 78000, 91000],
    "email": ["rahul@mail.com", "", "viraj@mail.com", "N/A", "sardar@mail.com", None]
})

print(f"Raw data:\n{df}\n")

# Count missing values per column
print(f"Missing per column:\n{df.isnull().sum()}\n")

# Percentage missing
pct_missing = (df.isnull().sum() / len(df) * 100).round(1)
print(f"Percentage missing:\n{pct_missing}\n")

# Total missing cells
print(f"Total missing: {df.isnull().sum().sum()} out of {df.size} cells")

# Rows with any missing value
print(f"\nRows with missing data:\n{df[df.isnull().any(axis=1)]}")

▶ Output

Raw data:
         name   age    city   salary            email
0       Rahul  28.0  Mumbai  75000.0   rahul@mail.com
1    Niranjan  31.0    Pune      NaN
2       Viraj   NaN  Mumbai  71000.0   viraj@mail.com
3         NaN  29.0   Delhi  65000.0              N/A
4      Sardar  27.0     NaN  78000.0  sardar@mail.com
5  Prathamesh  33.0  Mumbai  91000.0              NaN

Missing per column:
name      1
age       1
city      1
salary    1
email     1
dtype: int64

Percentage missing:
name      16.7
age       16.7
city      16.7
salary    16.7
email     16.7
dtype: float64

Total missing: 5 out of 30 cells

Rows with missing data:
         name   age    city   salary            email
1    Niranjan  31.0    Pune      NaN
2       Viraj   NaN  Mumbai  71000.0   viraj@mail.com
3         NaN  29.0   Delhi  65000.0              N/A
4      Sardar  27.0     NaN  78000.0  sardar@mail.com
5  Prathamesh  33.0  Mumbai  91000.0              NaN

What happened here: df.isnull() turns every cell into True (missing) or False (present), and .sum() counts the True values down each column. One missing value per column here. Two things surprise beginners. First, None and np.nan both count as missing, so the name in row 3 and the email in row 5 both show up as NaN when printed. Second, the empty string in row 1 (Niranjan’s email) does NOT count as missing.

Pandas sees an empty string as a real value, just a short one. We deal with that trap in the Common Mistakes section. Think of it like a stocktake in a shop: before you reorder anything, you walk the shelves and note exactly which items are missing and how many. Counting missing values is always step one: you cannot fix what you have not measured.

Recipe 2: Handling Missing Values

📄 handle_missing.py: drop or fill strategies

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "name": ["Rahul", "Niranjan", "Viraj", "Pravin", "Sardar"],
    "age": [28, 31, np.nan, 29, 27],
    "salary": [75000, np.nan, 71000, 65000, np.nan],
    "dept": ["Eng", "DS", "Eng", np.nan, "DS"]
})

# Strategy 1: Drop rows with ANY missing value
dropped = df.dropna()
print(f"After dropna():\n{dropped}\n")

# Strategy 2: Drop rows missing in specific column only
dropped_salary = df.dropna(subset=["salary"])
print(f"Drop only if salary missing:\n{dropped_salary}\n")

# Strategy 3: Fill with a constant
filled_const = df.fillna({"dept": "Unknown", "age": 0})
print(f"Fill with constants:\n{filled_const}\n")

# Strategy 4: Fill numeric with mean/median
df_filled = df.copy()
df_filled["age"] = df_filled["age"].fillna(df_filled["age"].median())
df_filled["salary"] = df_filled["salary"].fillna(df_filled["salary"].mean())
df_filled["dept"] = df_filled["dept"].fillna(df_filled["dept"].mode()[0])
print(f"Fill with median/mean/mode:\n{df_filled}")

▶ Output

After dropna():
    name   age   salary dept
0  Rahul  28.0  75000.0  Eng

Drop only if salary missing:
     name   age   salary dept
0   Rahul  28.0  75000.0  Eng
2   Viraj   NaN  71000.0  Eng
3  Pravin  29.0  65000.0  NaN

Fill with constants:
       name   age   salary     dept
0     Rahul  28.0  75000.0      Eng
1  Niranjan  31.0      NaN       DS
2     Viraj   0.0  71000.0      Eng
3    Pravin  29.0  65000.0  Unknown
4    Sardar  27.0      NaN       DS

Fill with median/mean/mode:
       name   age        salary dept
0     Rahul  28.0  75000.000000  Eng
1  Niranjan  31.0  70333.333333   DS
2     Viraj  28.5  71000.000000  Eng
3    Pravin  29.0  65000.000000   DS
4    Sardar  27.0  70333.333333   DS

What happened here: Four strategies, four different results. dropna() is the strictest: it deletes any row with even one gap, so only Rahul survives. Use it only when you have plenty of rows to spare. dropna(subset=["salary"]) is gentler, it drops a row only if the salary is missing, keeping Viraj even though his age is blank. Filling is usually smarter than dropping. For numbers, median resists outliers better than mean, so it is the safer default for skewed data like salaries.

For categories, mode (the most common value) is the natural fill. One detail worth knowing: here both “Eng” and “DS” appear twice, a tie, and mode() returns them sorted, so it picks “DS” for Pravin. The salaries print with long decimals because the mean is a float, that is just how pandas shows it, not an error. A quick analogy: dropping rows is throwing out the whole form because one box was blank; filling is writing in a sensible best guess so you keep the rest of the answers.

Recipe 3: Removing Duplicates

📄 duplicates.py: find and remove duplicate rows

import pandas as pd

df = pd.DataFrame({
    "name": ["Rahul", "Viraj", "Rahul", "Pravin", "Viraj"],
    "email": ["rahul@m.com", "viraj@m.com", "rahul@m.com", "pravin@m.com", "viraj2@m.com"],
    "order": [100, 200, 100, 300, 400]
})

print(f"Original ({len(df)} rows):\n{df}\n")

# Find duplicates (all columns must match)
print(f"Duplicate rows:\n{df[df.duplicated()]}\n")

# Check duplicates on specific columns
print(f"Duplicate names:\n{df[df.duplicated(subset='name', keep=False)]}\n")

# Remove duplicates
deduped = df.drop_duplicates()
print(f"After drop_duplicates ({len(deduped)} rows):\n{deduped}\n")

# Keep last occurrence instead of first
deduped_last = df.drop_duplicates(subset="name", keep="last")
print(f"Keep last per name ({len(deduped_last)} rows):\n{deduped_last}")

▶ Output

Original (5 rows):
     name         email  order
0   Rahul   rahul@m.com    100
1   Viraj   viraj@m.com    200
2   Rahul   rahul@m.com    100
3  Pravin  pravin@m.com    300
4   Viraj  viraj2@m.com    400

Duplicate rows:
    name        email  order
2  Rahul  rahul@m.com    100

Duplicate names:
    name         email  order
0  Rahul   rahul@m.com    100
1  Viraj   viraj@m.com    200
2  Rahul   rahul@m.com    100
4  Viraj  viraj2@m.com    400

After drop_duplicates (4 rows):
     name         email  order
0   Rahul   rahul@m.com    100
1   Viraj   viraj@m.com    200
3  Pravin  pravin@m.com    300
4   Viraj  viraj2@m.com    400

Keep last per name (3 rows):
     name         email  order
2   Rahul   rahul@m.com    100
3  Pravin  pravin@m.com    300
4   Viraj  viraj2@m.com    400

What happened here: By default duplicated() flags a row only when every column matches an earlier row, so the second Rahul (same name, same email, same order) is the lone full duplicate. The subset argument lets you loosen that: duplicated(subset='name', keep=False) marks all rows that share a name, even when other columns differ, which is how both Viraj rows get flagged. That distinction matters in real life. Two Viraj rows with different emails are probably two different people, not a copy you want to delete.

The keep argument decides which copy to keep: “first” (the default), “last”, or False to drop every copy. Think of it like deduplicating your phone contacts: sometimes you keep the newest entry, sometimes the oldest, and you only merge two “Viraj” cards if the number behind them is truly the same.

Recipe 4: Fixing Data Types

📄 fix_dtypes.py: convert columns to correct types

import pandas as pd

df = pd.DataFrame({
    "date": ["2026-01-15", "2026-02-20", "2026-03-10"],
    "revenue": ["1,250.50", "2,100.75", "950.25"],
    "active": ["yes", "no", "yes"],
    "count": ["10", "20", "15"]
})

print(f"Before:\n{df.dtypes}\n")

# Fix dates
df["date"] = pd.to_datetime(df["date"])

# Fix currency strings to float
df["revenue"] = df["revenue"].str.replace(",", "").astype(float)

# Fix yes/no to boolean
df["active"] = df["active"].map({"yes": True, "no": False})

# Fix string numbers to int
df["count"] = df["count"].astype(int)

print(f"After:\n{df.dtypes}\n")
print(df)

▶ Output

Before:
date       str
revenue    str
active     str
count      str
dtype: object

After:
date       datetime64[us]
revenue           float64
active               bool
count               int64
dtype: object

        date  revenue  active  count
0 2026-01-15  1250.50    True     10
1 2026-02-20  2100.75   False     20
2 2026-03-10   950.25    True     15

What happened here: It is like receiving a box of prices written on paper slips: they look like numbers, but until you actually treat them as numbers you cannot add them up. When pandas reads text, it stores everything as a string, so a date, a price, and a yes/no flag all start life as plain text. That is fine for printing but useless for math: you cannot subtract two dates that are really strings, and "1,250.50" will not add up.

So we convert each column to the type it should be. pd.to_datetime() parses the dates, a quick .str.replace(",", "") strips the thousands comma before astype(float), .map() turns “yes” and “no” into real booleans, and astype(int) fixes the counts. One modern detail to flag: on Pandas 3.x (which produced the output above) text columns report their dtype as str rather than the older object, and datetimes land as datetime64[us] (microsecond resolution) instead of the old datetime64[ns].

If you are on Pandas 2.x you will still see object and datetime64[ns]. The cleaning code itself is identical either way.

Recipe 5: Detecting Outliers with IQR

📄 outliers_iqr.py: find and handle outliers

import pandas as pd
import numpy as np

rng = np.random.default_rng(42)
salaries = rng.normal(75000, 10000, 100).astype(int)
# Inject outliers
salaries[0] = 250000
salaries[1] = 5000

df = pd.DataFrame({"salary": salaries})

# IQR method
Q1 = df["salary"].quantile(0.25)
Q3 = df["salary"].quantile(0.75)
IQR = Q3 - Q1
lower = Q1 - 1.5 * IQR
upper = Q3 + 1.5 * IQR

outliers = df[(df["salary"] < lower) | (df["salary"] > upper)]
print(f"Q1: {Q1:,.0f}, Q3: {Q3:,.0f}, IQR: {IQR:,.0f}")
print(f"Bounds: [{lower:,.0f}, {upper:,.0f}]")
print(f"Outliers found: {len(outliers)}")
print(f"Outlier values: {sorted(outliers['salary'].tolist())}\n")

# Option 1: Remove outliers
clean = df[(df["salary"] >= lower) & (df["salary"] <= upper)]
print(f"After removal: {len(clean)} rows (was {len(df)})")

# Option 2: Cap outliers (winsorize)
df_capped = df.copy()
df_capped["salary"] = df_capped["salary"].clip(lower=lower, upper=upper)
print(f"After capping: min={df_capped['salary'].min():,.0f}, max={df_capped['salary'].max():,.0f}")

▶ Output

Q1: 69,560, Q3: 80,539, IQR: 10,978
Bounds: [53,093, 97,006]
Outliers found: 2
Outlier values: [5000, 250000]

After removal: 98 rows (was 100)
After capping: min=53,093, max=97,006

What happened here: The IQR method finds values that sit far outside the typical spread. Q1 is the 25th percentile and Q3 is the 75th, so the IQR is the range where the middle half of your data lives. Anything more than 1.5 times the IQR below Q1 or above Q3 is treated as an outlier. Here that flags the two values we planted: a 5,000 salary and a 250,000 salary.

A simple analogy: line up everyone in a class by height, look at the middle group, and anyone standing way taller or way shorter than that group stands out. After that you choose what to do. Removing outliers deletes those rows (98 left from 100). Capping (also called winsorizing) with .clip() keeps the rows but pulls extreme values back to the boundary, so no row is lost and no single giant value can warp your average.

We seeded the random generator with default_rng(42), so you will get these exact numbers; change the seed and your numbers will differ.

One note on scale: when you move from a few hundred thousand rows into the millions, this kind of column-wide scan starts to feel slow in Pandas. Polars 1.41.2 runs the same quantile and filter work in parallel and is often several times faster on large frames. The cleaning ideas in this post carry over directly; only the tool changes.

Common Mistakes

📄 Mistake 1: Empty strings are NOT NaN

import pandas as pd

df = pd.DataFrame({"email": ["rahul@m.com", "", "N/A", None]})
print(df.isnull())  # Only None shows as NaN!

# Fix: replace common missing indicators
df["email"] = df["email"].replace({"": pd.NA, "N/A": pd.NA, "null": pd.NA})

▶ Output

   email
0  False
1  False
2  False
3   True

Why this bites you: Only row 3, the real None, shows up as missing (True). The empty string in row 1 and the text "N/A" in row 2 both look like missing data to a human, but pandas treats them as ordinary values, so isnull() reports False. Your missing-value count comes out too low and the gaps slip through. The fix is one line: use .replace() to turn the usual fake-missing markers ("", "N/A", "null", "None", "-") into a real pd.NA first, then count and clean. Always eyeball a few rows before you trust an automatic count.

Practice Exercises

1. Exercise 1: Take the messy DataFrame from Recipe 1 and fill the missing values three different ways: the age with the median, the salary with the mean, and the city with the mode. Compare the three results.
2. Exercise 2: Build a small contacts table with duplicate names and inconsistent casing (like "rahul" and "Rahul"). Standardize the names with .str.strip().str.title(), then remove the duplicates.
3. Exercise 3: Chain the recipes into one cleaning function that takes a raw DataFrame and returns a clean one: replace fake-missing markers, fill or drop nulls, drop duplicates, fix dtypes, then cap outliers with IQR. Run it twice to confirm it gives the same result.

Conclusion

You now have a repeatable pandas data cleaning workflow: detect and handle missing values with isnull(), dropna(), and fillna(), remove duplicates with drop_duplicates(), fix data types with astype() and pd.to_datetime(), and catch outliers with the IQR method. Run these steps in order, missing values first and outliers last, so one problem never hides another. The honest lesson from Niranjan's 99% model still holds: clean data beats a flattering number every time.

Next up is Pandas data transformation with apply, groupby, and pivot_table, where you turn your freshly cleaned data into real answers. For the full learning path from Python basics to machine learning, visit the Python + AI/ML tutorial series home.

Frequently Asked Questions

Interview Questions on Pandas Data Cleaning

How interviewers actually probe this topic: real scenarios, with answers you can say out loud.

Q: How do you get a quick count of missing values in every column of a DataFrame?

Chain df.isnull().sum(). The isnull() call turns each cell into True or False, and .sum() counts the True values down each column. To see it as a share of rows, do (df.isnull().sum() / len(df) * 100).round(1), and use df.isnull().sum().sum() for the grand total of missing cells.

Q: Your import shows zero missing values, but you can clearly see blanks and "N/A" text in the data. What is going on?

Pandas only treats None, np.nan, and pd.NA as missing. An empty string "" or the text "N/A" is an ordinary value to pandas, so isnull() reports False and your count comes out too low. Fix it by normalizing the fake markers first: df = df.replace({"": pd.NA, "N/A": pd.NA, "null": pd.NA}), then recount. Always eyeball a few rows before trusting an automatic count.

Q: You run drop_duplicates() but duplicate-looking rows survive. What do you check first?

Pandas compares values exactly, so rows that look identical to a human can differ by whitespace or casing, like "Rahul" versus "rahul ". Normalize the key columns before deduping: df["name"] = df["name"].str.strip().str.title(). Also confirm you are deduping on the right columns with the subset argument, since a trailing space in an email or an extra digit in an order id makes the row count as unique.

Q: A revenue column will not sum and reports its dtype as object or str. How do you debug and fix it?

The column is text, not numbers, usually because of thousands commas or a currency symbol like "1,250.50". Strip the offending characters, then cast: df["revenue"] = df["revenue"].str.replace(",", "").astype(float). If a few values are non-numeric, use pd.to_numeric(df["revenue"], errors="coerce") so bad entries become NaN instead of raising, then handle those NaN values.

Q: What does the keep argument do in duplicated() and drop_duplicates()?

keep="first" (the default) marks or drops every copy except the first occurrence, keep="last" keeps the last, and keep=False flags or drops all copies including the originals. Use keep=False with duplicated() when you want to inspect every row involved in a duplicate group, not just the extra copies.

Q: Why cap outliers with clip() instead of deleting the rows?

Capping, also called winsorizing, pulls extreme values back to the IQR boundary with df["col"].clip(lower=lower, upper=upper), so you keep every row and lose no other information those rows carry. Deleting outliers shrinks your sample and can bias the result if the extremes are legitimate. Cap when the row is otherwise valid and you only want to tame one wild value; remove only when the whole record is clearly corrupt.

Want more? pandas official documentation documents everything this post could not fit.

Related Posts

Previous: Pandas: Data Selection with loc, iloc, Boolean Indexing

Next: Pandas: Data Transformation with apply, groupby, pivot_table

Series Home: Python + AI/ML Tutorial Series