Chapter 4 · Python and Data Handling
Data Cleaning with pandas
- Page 15 of 22
- 15 min read
Data that reaches you from spreadsheets, exports, forms and other teams is never clean. Cities are spelled four ways, amounts carry currency signs, dates come in two formats, a row is pasted twice, a typo adds a zero. Feed that to a model or a dashboard and it will learn or report the mess faithfully. Cleaning is where most of the real time in a data or ML project goes, and doing it well means doing it on purpose: every change visible, justified and repeatable.
Data Handling Fundamentals: Formats, Quality and Lineage gave you the vocabulary (validity, completeness, consistency, uniqueness, and the cleaning workflow). This page is the hands-on version in pandas. You will build a deliberately messy orders file, clean it step by step, and finish with a reusable clean() function that separates good rows from rejected ones.
What you will learn
- A first look that finds problems fast:
head,info,describe,value_counts. - Fixing names, text, numbers and dates:
strmethods, mappings,to_numeric(errors="coerce"),to_datetime(format=…). - Missing values, duplicates, outliers (the IQR rule) and range checks, and how to decide what to do with each.
- Enriching with a database table (
mergewithvalidateandindicator), reshaping withpivot_tableandmelt, and summarising withgroupby. - Quality checks with
assert, exporting, and a reusable cleaning pipeline with a quarantine.
The messy file
This is a typical export from a shop's order spreadsheet. The page writes it to orders_messy.csv so that you have exactly the same file:
messy = """ Order ID,Customer ,Order Date,City,Product,Qty,Amount (BDT),Status
1001,1,2026-01-12,Dhaka,Python Crash Course,1,"৳1,200",Delivered
1002,2,20/01/2026,chattogram,Mechanical Keyboard,1,4500,delivered
1003,1,2026-02-03, Dhaka ,SQL Masterclass,1,3000,DELIVERED
1004,3,2026-02-14,DHAKA,Laptop Stand,1,1500,delivered
1005,4,25/02/2026,Sylhet,27-inch Monitor,1,18500,cancelled
1006,2,2026-03-08,Ctg,AI Engineering Bootcamp,1,15000,delivered
1007,5,2026-03-15,,Wireless Mouse,2,1800,delivered
1008,3,2026-03-28,Dhaka,Python Crash Course,2,2400,Delivered
1008,3,2026-03-28,Dhaka,Python Crash Course,2,2400,Delivered
1009,6,2026-04-02,Khulna,Mechanical Keyboard,1,4050,delivered
1010,1,2026-04-19,Dhaka,27-inch Monitor,1,185000,delivered
1011,7,2026-05-06,Dhaka,SQL Masterclass,1,N/A,delivered
1012,2,2026-05-21,Chittagong,Laptop Stand,2,3000,shipped
1013,6,2026-06-02,khulna,AI Engineering Bootcamp,1,15000,Pending
1014,3,2026-06-09,Dhaka,USB-C Hub,-1,-2200,delivered
1015,,2026-06-11,Dhaka,Wireless Mouse,1,900,delivered
1016,9,2026-06-12,Rajshahi,Gift Card,1,1000,canceled
"""
with open("orders_messy.csv", "w", encoding="utf-8") as f:
f.write(messy)
print(len(messy.splitlines()) - 1, "data rows written")17 data rows writtenLook before you touch anything
import pandas as pd
raw = pd.read_csv("orders_messy.csv")
print(raw.shape)
raw.info()(17, 8)
<class 'pandas.DataFrame'>
RangeIndex: 17 entries, 0 to 16
Data columns (total 8 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Order ID 17 non-null int64
1 Customer 16 non-null float64
2 Order Date 17 non-null str
3 City 16 non-null str
4 Product 17 non-null str
5 Qty 17 non-null int64
6 Amount (BDT) 16 non-null str
7 Status 17 non-null str
dtypes: float64(1), int64(2), str(5)
memory usage: 1.9 KBinfo() already shows four problems. The first column is called " Order ID" with a leading space, so raw["Order ID"] would fail. Customer became float64 because one value is missing. Amount (BDT) is text (str, pandas 3's string dtype), because one value contains ৳ and a comma. And City and the amount each have a missing value. describe() shows the numeric columns' ranges:
print(raw.describe().round(2))
print(raw["Status"].value_counts()) Order ID Customer Qty
count 17.00 16.00 17.00
mean 1008.47 3.62 1.12
std 4.61 2.36 0.70
min 1001.00 1.00 -1.00
25% 1005.00 2.00 1.00
50% 1008.00 3.00 1.00
75% 1012.00 5.25 1.00
max 1016.00 9.00 2.00
Status
delivered 9
Delivered 3
DELIVERED 1
cancelled 1
shipped 1
Pending 1
canceled 1
Name: count, dtype: int64A quantity of −1 is impossible, and the status column has seven spellings of four values. value_counts() on every text column is the quickest way to find inconsistent categories.
Read as text, convert on purpose
pandas guessed the types, and guesses hide problems (it silently turned N/A into a missing value and the customer ids into floats). For cleaning, read everything as text and convert each column yourself, so every conversion is a decision you can see. Start with the column names:
df = pd.read_csv("orders_messy.csv", dtype=str, keep_default_na=False) # all text, nothing guessed
df.columns = (
df.columns.str.strip()
.str.lower()
.str.replace(r"[^a-z0-9]+", "_", regex=True) # anything else becomes _
.str.strip("_")
)
df = df.rename(columns={"customer": "customer_id", "amount_bdt": "amount_text"})
print(list(df.columns))['order_id', 'customer_id', 'order_date', 'city', 'product', 'qty', 'amount_text', 'status']Text: strip, case and mapping
for col in ["city", "product", "status"]:
df[col] = df[col].str.strip() # " Dhaka " -> "Dhaka"
print(sorted(df["city"].unique()))
df["city"] = df["city"].str.title().replace({
"Ctg": "Chattogram", "Chittagong": "Chattogram", # one name per city
"": None, # empty text means missing
})
df["status"] = df["status"].str.lower().replace({"canceled": "cancelled"})
print(df["city"].value_counts(dropna=False).sort_index())
print(sorted(df["status"].unique()))['', 'Chittagong', 'Ctg', 'DHAKA', 'Dhaka', 'Khulna', 'Rajshahi', 'Sylhet', 'chattogram', 'khulna']
city
Chattogram 3
Dhaka 9
Khulna 2
Rajshahi 1
Sylhet 1
None 1
Name: count, dtype: int64
['cancelled', 'delivered', 'pending', 'shipped']Case and spaces are fixed by rules; synonyms and spelling variants need a mapping that a person decided on. Keep such mappings in one place (a dictionary in code, or a lookup table in the database) so the next file is cleaned the same way.
Numbers: to_numeric with errors="coerce"
amount = df["amount_text"].str.replace("৳", "").str.replace(",", "")
df["amount"] = pd.to_numeric(amount, errors="coerce") # "N/A" -> NaN instead of an error
df["qty"] = pd.to_numeric(df["qty"], errors="coerce").astype("Int64")
df["customer_id"] = pd.to_numeric(df["customer_id"], errors="coerce").astype("Int64")
print(df.loc[df["amount"].isna(), ["order_id", "amount_text"]])
print(df[["customer_id", "qty", "amount"]].dtypes) order_id amount_text
11 1011 N/A
customer_id Int64
qty Int64
amount float64
dtype: objecterrors="coerce" turns anything unparseable into NaN instead of stopping with an error. That is powerful and dangerous: it also hides typos. Always print the rows that were coerced, as above, to make sure they really are missing values and not, say, "1.200,00" in a European format. Int64 (capital I) is pandas' nullable integer: unlike int64 it can hold a missing value, so customer 1 stays 1 and does not become 1.0.
Dates: one format at a time
iso = pd.to_datetime(df["order_date"], format="%Y-%m-%d", errors="coerce")
dmy = pd.to_datetime(df["order_date"], format="%d/%m/%Y", errors="coerce")
df["order_date"] = iso.fillna(dmy)
print(df["order_date"].isna().sum(), "dates could not be parsed")
print(df.loc[[1, 4], ["order_id", "order_date"]])0 dates could not be parsed
order_id order_date
1 1002 2026-01-20
4 1005 2026-02-25Give to_datetime an explicit format. Without one, pandas guesses, and 03/04/2026 is 3 April in Bangladesh and March 4 in the United States. Parsing each known format separately and combining them makes the ambiguity a decision you took, not one pandas took.
Duplicates
print(df.duplicated().sum(), "exact duplicate row(s)")
print(df["order_id"].duplicated().sum(), "repeated order_id(s)")
df = df.drop_duplicates().reset_index(drop=True)
assert df["order_id"].is_unique
print(len(df), "rows left")1 exact duplicate row(s)
1 repeated order_id(s)
16 rows leftHere the repeated order was an exact copy, so dropping it is safe. When the same key appears with different values (two versions of order 1008 with different amounts), dropping blindly keeps whichever came first. Look at them with df[df["order_id"].duplicated(keep=False)] and apply a rule you can defend, such as "the latest update wins".
Missing values: drop, fill or flag
print(df[["customer_id", "city", "amount"]].isna().sum())customer_id 1
city 1
amount 1
dtype: int64There is no single right answer; decide per column, from what the value means:
| Column | Decision | Why |
|---|---|---|
customer_id | reject the row | an order nobody placed cannot be attributed or joined; guessing would invent data |
city | fill from the customers table, else "Unknown" | a better source exists; "Unknown" keeps the row in totals without pretending |
amount | impute quantity × list price, and flag it | a domain rule beats a column average; the flag lets a model or analyst treat it differently |
Filling a numeric gap with the column mean or median is common in ML preprocessing (compute it on the training rows only, as Preparing ML-Ready Data with SQL and pandas explains), but always ask whether a better source exists first, and keep a flag column so imputed values are never mistaken for observed ones.
Enrich from the database: merge
The shop database knows each customer's city and each product's list price. merge is pandas' JOIN. Two arguments make it safe: validate="many_to_one" raises an error if the right-hand table has duplicate keys (which would silently multiply your rows: the fan-out trap), and indicator=True adds a _merge column saying where each row found a match:
import sqlite3
con = sqlite3.connect("shop.db")
customers = pd.read_sql("SELECT customer_id, city AS home_city FROM customers", con)
catalogue = pd.read_sql("SELECT name AS product, price AS list_price FROM products", con)
customers["customer_id"] = customers["customer_id"].astype("Int64") # same dtype on both sides
df = df.merge(customers, on="customer_id", how="left", validate="many_to_one", indicator=True)
df = df.merge(catalogue, on="product", how="left", validate="many_to_one")
print(df["_merge"].value_counts())
print(df.loc[df["_merge"] == "left_only", ["order_id", "customer_id"]])
df["city"] = df["city"].fillna(df["home_city"]).fillna("Unknown")
df["amount_imputed"] = df["amount"].isna()
df["amount"] = df["amount"].fillna(df["qty"] * df["list_price"])
print(df.loc[df["order_id"].isin(["1007", "1011"]), ["order_id", "city", "amount", "amount_imputed"]])_merge
both 14
left_only 2
right_only 0
Name: count, dtype: int64
order_id customer_id
14 1015 <NA>
15 1016 9
order_id city amount amount_imputed
6 1007 Unknown 1800.0 False
10 1011 Dhaka 3000.0 TrueTwo orders found no customer: 1015 has none, and 1016 points to customer 9, who does not exist (an orphan, the referential-integrity problem a foreign key would have prevented). Order 1007's city could not be filled either, because customer 5 has no city in the database too, so it honestly becomes "Unknown".
Outliers and range checks
The classic outlier rule is IQR: with Q1 and Q3 the 25th and 75th percentiles and IQR = Q3 − Q1, values below Q1 − 1.5 × IQR or above Q3 + 1.5 × IQR are suspicious:
q1, q3 = df["amount"].quantile([0.25, 0.75])
iqr = q3 - q1
low, high = q1 - 1.5 * iqr, q3 + 1.5 * iqr
print(f"Q1={q1:.0f} Q3={q3:.0f} IQR={iqr:.0f} -> normal range {low:.0f} to {high:.0f}")
print(df.loc[(df["amount"] < low) | (df["amount"] > high), ["order_id", "product", "amount"]])Q1=1425 Q3=7125 IQR=5700 -> normal range -7125 to 15675
order_id product amount
4 1005 27-inch Monitor 18500.0
9 1010 27-inch Monitor 185000.0The rule flagged the 185,000 typo, but also order 1005, a perfectly real monitor sale at list price, while the 15,000 Bootcamp sales only just escaped. Order amounts are naturally skewed: a few expensive products are not errors. An outlier rule finds candidates to inspect, never rows to delete automatically. A domain check is sharper: compare the unit price with the catalogue price, and add the hard range rules (quantity and amount must be positive):
df["price_ratio"] = (df["amount"] / df["qty"] / df["list_price"]).round(2)
checks = {
"no customer": df["_merge"] == "left_only",
"qty must be positive": ~(df["qty"] > 0).fillna(False),
"price far from list": (df["price_ratio"] < 0.5) | (df["price_ratio"] > 1.5),
}
df["problem"] = None
for reason, bad in checks.items():
df.loc[bad & df["problem"].isna(), "problem"] = reason # keep the first reason found
print(df.loc[df["problem"].notna(), ["order_id", "qty", "amount", "price_ratio", "problem"]]) order_id qty amount price_ratio problem
9 1010 1 185000.0 10.0 price far from list
13 1014 -1 -2200.0 1.0 qty must be positive
14 1015 1 900.0 1.0 no customer
15 1016 1 1000.0 1.0 no customerOrder 1009 sold at 0.9 of list price (a real discount) and passes; the 185,000 typo is ten times the list price and is caught. Note the ~(df["qty"] > 0).fillna(False). With the nullable Int64 dtype, comparing a missing quantity gives <NA>, not False, and .loc treats <NA> in a mask as "not selected". Turning <NA> into False before the ~ makes a missing quantity count as a problem; a plain df["qty"] <= 0 would let it through.
Every fix on this page is an assignment:
df[col] = …ordf.loc[mask, col] = …. In pandas 3, Copy-on-Write is always on, so a chained assignment such asdf["amount"][mask] = 0never changesdf(pandas only warns), and neither doesdf["city"].fillna("Unknown", inplace=True), becausedf["city"]behaves as a copy. Old tutorials and Stack Overflow answers are full of both; writedf.loc[mask, "amount"] = 0anddf["city"] = df["city"].fillna("Unknown")instead.
Split, check, summarise and export
Rejected rows are not deleted; they go to a quarantine file with their reason, so someone can fix the source. The good rows get a final set of assertions: if any fails, the script stops before bad data is saved.
keep = ["order_id", "customer_id", "order_date", "city", "product", "qty", "amount", "amount_imputed", "status"]
rejected = df[df["problem"].notna()]
clean = df.loc[df["problem"].isna(), keep].reset_index(drop=True)
assert clean["order_id"].is_unique
assert clean[["customer_id", "order_date", "amount"]].notna().all().all()
assert (clean["qty"] > 0).all() and (clean["amount"] > 0).all()
assert set(clean["status"]) <= {"pending", "shipped", "delivered", "cancelled"}
print(len(clean), "clean rows,", len(rejected), "rejected")
clean.to_csv("orders_clean.csv", index=False)
rejected[["order_id", "problem"]].to_csv("orders_rejected.csv", index=False)
clean.to_excel("orders_clean.xlsx", index=False, sheet_name="orders")
print(pd.read_excel("orders_clean.xlsx", sheet_name="orders").shape)12 clean rows, 4 rejected
(12, 9)Now the data can answer questions. Revenue per city counts only orders that were not cancelled. pivot_table turns long data (one row per order) into a wide month × city table, and melt turns it back into long form, which is what most plotting and ML tools want:
sold = clean[clean["status"] != "cancelled"]
print(sold.groupby("city")["amount"].agg(["count", "sum"]).sort_values("sum", ascending=False))
sold = sold.assign(month=sold["order_date"].dt.strftime("%Y-%m"))
wide = sold.pivot_table(index="month", columns="city", values="amount", aggfunc="sum", fill_value=0)
print(wide)
long = wide.reset_index().melt(id_vars="month", var_name="city", value_name="revenue")
print(long[long["revenue"] > 0].sort_values(["month", "city"]).head(4)) count sum
city
Chattogram 3 22500.0
Khulna 2 19050.0
Dhaka 5 11100.0
Unknown 1 1800.0
city Chattogram Dhaka Khulna Unknown
month
2026-01 4500.0 1200.0 0.0 0.0
2026-02 0.0 4500.0 0.0 0.0
2026-03 15000.0 2400.0 0.0 1800.0
2026-04 0.0 0.0 4050.0 0.0
2026-05 3000.0 3000.0 0.0 0.0
2026-06 0.0 0.0 15000.0 0.0
month city revenue
0 2026-01 Chattogram 4500.0
6 2026-01 Dhaka 1200.0
7 2026-02 Dhaka 4500.0
2 2026-03 Chattogram 15000.0Putting it together: a reusable clean()
Notebook steps are for discovering the problems. Once you know them, move the decisions into one function that takes raw data and returns clean and rejected rows. Next month's file goes through exactly the same rules, the function can be tested, and its counts become the log of every run:
CITY_MAP = {"Ctg": "Chattogram", "Chittagong": "Chattogram", "": None}
STATUS_MAP = {"canceled": "cancelled"}
def clean_orders(path, customers, catalogue):
"""Return (clean, rejected) DataFrames for a raw orders CSV."""
df = pd.read_csv(path, dtype=str, keep_default_na=False)
df.columns = (df.columns.str.strip().str.lower()
.str.replace(r"[^a-z0-9]+", "_", regex=True).str.strip("_"))
df = df.rename(columns={"customer": "customer_id", "amount_bdt": "amount_text"})
df = df.apply(lambda s: s.str.strip())
df["city"] = df["city"].str.title().replace(CITY_MAP)
df["status"] = df["status"].str.lower().replace(STATUS_MAP)
df["amount"] = pd.to_numeric(df["amount_text"].str.replace("৳", "").str.replace(",", ""), errors="coerce")
for col in ["qty", "customer_id"]:
df[col] = pd.to_numeric(df[col], errors="coerce").astype("Int64")
df["order_date"] = (pd.to_datetime(df["order_date"], format="%Y-%m-%d", errors="coerce")
.fillna(pd.to_datetime(df["order_date"], format="%d/%m/%Y", errors="coerce")))
df = df.drop_duplicates().reset_index(drop=True)
df = df.merge(customers, on="customer_id", how="left", validate="many_to_one", indicator=True)
df = df.merge(catalogue, on="product", how="left", validate="many_to_one")
df["city"] = df["city"].fillna(df["home_city"]).fillna("Unknown")
df["amount_imputed"] = df["amount"].isna()
df["amount"] = df["amount"].fillna(df["qty"] * df["list_price"])
ratio = df["amount"] / df["qty"] / df["list_price"]
df["problem"] = None
for reason, bad in {
"duplicate order_id": df["order_id"].duplicated(keep=False),
"no customer": df["_merge"] == "left_only",
"bad date": df["order_date"].isna(),
"unknown product": df["list_price"].isna(),
"qty must be positive": ~(df["qty"] > 0).fillna(False),
"price far from list": (ratio < 0.5) | (ratio > 1.5),
}.items():
df.loc[bad & df["problem"].isna(), "problem"] = reason
good = df["problem"].isna()
return df.loc[good, keep].reset_index(drop=True), df.loc[~good, ["order_id", "problem"]]
clean2, rejected2 = clean_orders("orders_messy.csv", customers, catalogue)
print(len(clean2), "clean,", len(rejected2), "rejected")
print(rejected2.to_string(index=False))
print("same result as the step-by-step version:", clean2.equals(clean))12 clean, 4 rejected
order_id problem
1010 price far from list
1014 qty must be positive
1015 no customer
1016 no customer
same result as the step-by-step version: TrueThe last line is a small regression test: the function reproduces exactly what the interactive steps produced. Keep a check like that whenever you refactor cleaning code.
Where this shows up
- Training data: label files from annotators have inconsistent class names ("Spam", "spam ", "SPAM"); a mapping step before training prevents the model from seeing three classes.
- RAG and LLM pipelines: documents are de-duplicated, stripped and normalised before chunking and embedding, or the retriever returns the same passage three times.
- Analytics and finance: imports from partners go through exactly this clean / reject / quarantine flow every night, with the rejected file sent back to the partner.
Common mistakes
- Cleaning by hand in Excel. It cannot be repeated, reviewed or tested, and next month's file needs the same work again. Put every decision in code.
- Using
errors="coerce"without looking at what was coerced. A whole column in the wrong format quietly becomesNaN. Count the new missing values right after converting:pd.to_numeric(s, errors="coerce").isna().sum()compared withs.eq("").sum(). - Letting pandas guess date formats. Day and month swap silently for days 1–12. Always pass
format=. - Deleting outliers automatically. The IQR rule flagged a real sale above. Flag, inspect, then decide.
- Merging without
validate. A duplicate key in the lookup table multiplies rows and inflates every total.validate="many_to_one"turns that silent bug into an immediate error:bad_catalogue = pd.concat([catalogue, catalogue.head(1)]) # "Python Crash Course" twice try: clean.merge(bad_catalogue, on="product", how="left", validate="many_to_one") except pd.errors.MergeError as exc: print("MergeError:", str(exc).splitlines()[0])MergeError: Merge keys are not unique in right dataset; not a many-to-one merge
Try it yourself
- Easy: from
clean, find each customer's total spend on non-cancelled orders, highest first. - Medium: check that no clean order is dated before its customer joined. Load
joined_onfromshop.db(as a date), merge it intoclean2withvalidate, and print the order ids that fail, or a message if there are none. - Hard: write
quality_report(df)that returns a DataFrame with one row per column: dtype, number of missing values, number of distinct values, and the most common value. Run it onrawand oncleanand compare.
Answers
# 1. Easy
spend = (clean[clean["status"] != "cancelled"]
.groupby("customer_id")["amount"].sum()
.sort_values(ascending=False))
print(spend)
# 2. Medium
joined = pd.read_sql("SELECT customer_id, joined_on FROM customers", con, parse_dates=["joined_on"])
joined["customer_id"] = joined["customer_id"].astype("Int64")
checked = clean2.merge(joined, on="customer_id", how="left", validate="many_to_one")
too_early = checked[checked["order_date"] < checked["joined_on"]]
print(too_early["order_id"].tolist() or "no order is dated before its customer joined")
# 3. Hard
def quality_report(df):
return pd.DataFrame({
"dtype": df.dtypes.astype(str),
"missing": df.isna().sum(),
"distinct": df.nunique(),
"most_common": df.mode().iloc[0],
})
print(quality_report(raw))
print(quality_report(clean[["city", "status", "amount"]]))Summary
- Look first (
info,describe,value_counts), then read as text and convert each column on purpose. - Text: strip, fix case, map variants to one value. Numbers:
to_numeric(errors="coerce")and inspect what becameNaN. Dates:to_datetimewith an explicitformat. - Decide per column whether a missing value means reject, fill from a better source, or impute with a flag. Drop exact duplicates; resolve conflicting ones with a rule.
- Outlier rules find candidates; domain checks and range checks decide. Merge with
validateandindicator. - End with assertions, a quarantine for rejected rows, and one reusable function that makes the whole cleaning repeatable.
Next: Importing and Exporting: CSV, JSON, Excel and Parquet covers the files at both ends of this process: reading and writing every common format correctly (encodings, delimiters, nested JSON, Parquet), loading them into a database in bulk, and quarantining rows that fail on the way in.