Data Cleaning in Pandas: Missing Values, Duplicates, Outliers and Wrong Types

You download a sales CSV, run df["price"].mean(), and pandas throws an error. Or worse, it doesn't, and you report a number that is completely wrong.

Real datasets rarely arrive ready for analysis. Prices are stored as text, the same order appears twice, a customer's name has a trailing space, and one order is worth 200 times the others. This guide walks through a cleaning workflow you can repeat on any table.

The messy dataset

Let's build a small table with every common problem in it, so you can see each fix work.

import pandas as pd
import numpy as np
 
raw = pd.DataFrame({
    "order_id": [1001, 1002, 1002, 1003, 1004, 1005, 1006, 1007],
    "customer": ["Asha", "ben ", "ben ", None, "Chen", "Dev", "Ella", "Farah"],
    "city": ["Pune", "pune", "pune", "Delhi", "Delhi ", "Mumbai", "Pune", "Mumbai"],
    "price": ["499", "1,250", "1,250", "350", "abc", "420", "99999", "610"],
    "order_date": ["2026-07-01", "2026-07-02", "2026-07-02", "2026-07-03",
                   "2026-07-05", None, "2026-07-06", "2026-07-07"],
})

Eight rows, and already: a duplicated order, inconsistent capitals, stray spaces, a price with a comma, a price that says "abc", a missing name and a suspiciously large order.

Step 1: Look before you touch anything

Think of a doctor's check-up. Nobody prescribes medicine before taking your temperature. Start with two commands:

print(raw.dtypes)
print(raw.isna().sum())

order_id      int64
customer        str
city            str
price           str
order_date      str
 
order_id      0
customer      1
city          0
price         0
order_date    1

The important line is price str. Pandas read the prices as text, so mean() can't work on them. (On pandas 2.x you'll see object instead of str; pandas 3.0 gave text its own string type. The fix is the same.)

isna().sum() counts missing values per column. Right now it says price has none, but that's only because "abc" is technically a value. We'll come back to that.

Step 2: Remove duplicates

Order 1002 appears twice. Before deleting anything, ask: what makes a row unique? Here, it's order_id.

df = raw.copy()
 
print(df.duplicated().sum())                     # 1 fully identical row
print(df.duplicated(subset="order_id").sum())    # 1 repeated order ID
 
df = df.drop_duplicates(subset="order_id", keep="first")
print(len(df))  # 7

Why subset? Because duplicates aren't always perfect copies. If the second copy of order 1002 had a different timestamp, duplicated() with no arguments would say "not a duplicate", and you'd count that sale twice.

Step 3: Tidy the text

To a computer, "pune", "Pune" and "Pune " are three different cities. Count them and you get three tiny groups instead of one.

df["customer"] = df["customer"].str.strip().str.title()
df["city"] = df["city"].str.strip().str.title()
 
print(df["city"].value_counts())

city
Pune      3
Delhi     2
Mumbai    2

str.strip() removes spaces at both ends, and str.title() makes "pune" become "Pune". Run value_counts() on every text column you plan to group by. It's the fastest way to spot spelling variants.

Step 4: Fix wrong types

Now the prices. Two problems: "1,250" has a comma, and "abc" isn't a number at all.

df["price"] = pd.to_numeric(
    df["price"].str.replace(",", "", regex=False),
    errors="coerce",
)
print(df["price"].tolist())
# [499.0, 1250.0, 350.0, nan, 420.0, 99999.0, 610.0]

errors="coerce" is the key. Without it, pandas stops at "abc" with an error. With it, anything it can't read becomes NaN (missing). That's exactly what you want: the bad value is now visible to isna() instead of hiding as text.

Dates work the same way:

df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")
print(df["order_date"].dtype)  # datetime64[us]

Once dates are real dates, you can do df["order_date"].dt.month, sort chronologically, and resample by week. As text, "2026-10-01" and "2026-9-30" sort in the wrong order.

Step 5: Decide what to do with missing values

Run the check-up again:

print(df.isna().sum())

order_id      0
customer      1
city          0
price         1
order_date    1

There's no single right answer for missing data. Ask what the column is used for.

• customer: a missing name doesn't change revenue. Fill it with a label so the row isn't dropped by accident.
• price: this is the number we'll sum. Guessing would invent money, so keep it missing and flag it.
• order_date: keep the row, but it will be left out of any monthly chart.

df["customer"] = df["customer"].fillna("Unknown")

Step 6: Find outliers with the IQR rule

Order 1006 costs 99,999 when everything else is under 1,300. Is it a typo or a real bulk order? You can't know yet, but you can find candidates systematically.

The IQR rule is like a cricket umpire's crease: a line drawn from the data itself, beyond which a value is "out". IQR (interquartile range) is the spread of the middle half of your data.

q1, q3 = df["price"].quantile([0.25, 0.75])
iqr = q3 - q1
low, high = q1 - 1.5 * iqr, q3 + 1.5 * iqr
 
print(q1, q3, high)   # 439.75 1090.0 2065.375
print(df[df["price"] > high][["order_id", "price"]])

   order_id    price
6      1006  99999.0

Anything above about 2,065 is flagged. Notice we don't delete it. We label it:

df["price_flag"] = np.where(
    df["price"].isna(), "missing",
    np.where(df["price"] > high, "check", "ok"),
)

Now your report can say "7 orders, 1 missing price, 1 needs checking" instead of silently dropping data.

The final table

   order_id customer    city    price price_flag
0      1001     Asha    Pune    499.0         ok
1      1002      Ben    Pune   1250.0         ok
3      1003  Unknown   Delhi    350.0         ok
4      1004     Chen   Delhi      NaN    missing
5      1005      Dev  Mumbai    420.0         ok
6      1006     Ella    Pune  99999.0      check
7      1007    Farah  Mumbai    610.0         ok

Every number is a number, every city is spelled one way, the duplicate is gone, and nothing was thrown away without a trace.

When to use this, and when to go further

For small and medium projects, this six-step routine covers most real files: look, deduplicate, tidy text, fix types, handle missing values, flag outliers. Wrap it in a function and run it every time new data arrives.

For bigger pipelines, add automated checks (for example, "price must be positive", "order_id must be unique") so bad data fails loudly before it reaches a dashboard.

Next, take the clean table into an exploratory data analysis workflow, or learn why pandas Series and DataFrames behave differently with mixed types.

In one line: check first, then deduplicate, tidy text, convert types with errors="coerce", decide missing values per column, and flag outliers instead of deleting them.