pandas Cleaning Examples (Run Online)

1Turn price text into numbers

Currency symbols and thousands separators block numeric conversion. One regex removes both characters, and errors="coerce" turns the word free into NaN instead of crashing, so you can list the problem rows.

Turn price text into numbers

import pandas as pd

raw = pd.DataFrame({
    "item": ["Desk", "Chair", "Lamp", "Rug"],
    "price": ["$1,200.00", "$85", "free", "$140.50"],
})
raw["price_num"] = pd.to_numeric(
    raw["price"].str.replace(r"[$,]", "", regex=True), errors="coerce"
)
print(raw)
print("Unreadable:", raw.loc[raw["price_num"].isna(), "item"].tolist())
print("Total:", raw["price_num"].sum())

Expected output    item      price  price_num
0   Desk  $1,200.00     1200.0
1  Chair        $85       85.0
2   Lamp       free        NaN
3    Rug    $140.50      140.5
Unreadable: ['Lamp']
Total: 1425.5

2Keep the latest record per customer

Sort so the newest row comes last, then drop_duplicates(subset=..., keep="last") keeps only that row for each email. Sorting first is what makes keep="last" mean "most recent".

Keep the latest record per customer

import pandas as pd

customers = pd.DataFrame({
    "email": ["a@x.com", "b@x.com", "a@x.com", "c@x.com", "b@x.com"],
    "plan": ["free", "free", "pro", "free", "pro"],
    "updated": ["2024-01-05", "2024-02-01", "2024-03-10", "2024-01-20", "2024-04-02"],
})
latest = (
    customers.sort_values("updated")
    .drop_duplicates(subset="email", keep="last")
    .reset_index(drop=True)
)
print(latest)
print(len(customers) - len(latest), "old records removed")

Expected output     email  plan     updated
0  c@x.com  free  2024-01-20
1  a@x.com   pro  2024-03-10
2  b@x.com   pro  2024-04-02
2 old records removed

3Standardise categories and catch what the map missed

map() turns every value you list into its standard form and everything else into NaN. That makes the leftovers easy to find, and fillna() can then keep them in a tidy form.

Standardise categories and catch what the map missed

import pandas as pd

raw = pd.Series(["usa", "U.S.A.", "United States", "uk", "U.K.", "Britain", "india"])
mapping = {
    "usa": "US", "u.s.a.": "US", "united states": "US",
    "uk": "UK", "u.k.": "UK", "britain": "UK",
}
mapped = raw.str.lower().map(mapping)
print("Not in the mapping:", raw[mapped.isna()].tolist())
country = mapped.fillna(raw.str.title())
print(country.tolist())
print(country.value_counts().to_dict())

Expected outputNot in the mapping: ['india']
['US', 'US', 'US', 'UK', 'UK', 'UK', 'India']
{'US': 3, 'UK': 3, 'India': 1}

4Parse dates and read their parts

After to_datetime, the .dt accessor exposes month names, weekdays and hours. The row with an unreadable date becomes NaT, and isna() counts it.

Parse dates and read their parts

import pandas as pd

log = pd.DataFrame({
    "when": ["2024-03-01 09:15", "2024-03-02 14:40", "2024-03-09 18:05", "not recorded"],
    "event": ["login", "purchase", "purchase", "login"],
})
log["when"] = pd.to_datetime(log["when"], format="%Y-%m-%d %H:%M", errors="coerce")
log["month"] = log["when"].dt.month_name()
log["weekday"] = log["when"].dt.day_name()
log["hour"] = log["when"].dt.hour
print(log)
print("Bad dates:", log["when"].isna().sum())

Expected output                 when     event  month   weekday  hour
0 2024-03-01 09:15:00     login  March    Friday   9.0
1 2024-03-02 14:40:00  purchase  March  Saturday  14.0
2 2024-03-09 18:05:00  purchase  March  Saturday  18.0
3                 NaT     login    NaN       NaN   NaN
Bad dates: 1

5Flag outliers with the IQR rule, then clip

A common rule says a value above Q3 + 1.5 * IQR is suspicious. Build the mask first so you can inspect the flagged rows, and only then decide to clip them.

Flag outliers with the IQR rule, then clip

import pandas as pd

orders = pd.DataFrame({
    "order": range(1, 9),
    "amount": [24, 30, 27, 35, 29, 31, 480, 26],
})
q1, q3 = orders["amount"].quantile([0.25, 0.75])
upper = q3 + 1.5 * (q3 - q1)
orders["outlier"] = orders["amount"] > upper
print("Upper fence:", upper)
print(orders[orders["outlier"]])
orders["clipped"] = orders["amount"].clip(upper=upper)
print("Mean before:", orders["amount"].mean(), "after:", round(orders["clipped"].mean(), 2))

Expected outputUpper fence: 39.875
   order  amount  outlier
6      7     480     True
Mean before: 85.25 after: 30.23

6Tidy every column name in one pass

A regex replaces any run of characters that are not letters or digits with one underscore, so Unit Price ($) becomes unit_price. A rename then gives one column a shorter name.

Tidy every column name in one pass

import io
import pandas as pd

csv_text = """ Order ID,Customer Name ,Unit Price ($),QTY
1001,Asha,2.50,3
1002,Ben,1.80,5
"""
df = pd.read_csv(io.StringIO(csv_text))
print(list(df.columns))
df.columns = (
    df.columns.str.strip().str.lower()
    .str.replace(r"[^a-z0-9]+", "_", regex=True).str.strip("_")
)
print(list(df.columns))
df = df.rename(columns={"unit_price": "price"})
print(df)

Expected output[' Order ID', 'Customer Name ', 'Unit Price ($)', 'QTY']
['order_id', 'customer_name', 'unit_price', 'qty']
   order_id customer_name  price  qty
0      1001          Asha    2.5    3
1      1002           Ben    1.8    5

Practise next

pandas Data Cleaning exercises → Back to the lesson

Python CSV Files examples Pandas Basics examples pandas Selecting Data examples pandas groupby and Pivot Tables examples