Every pandas tutorial starts with a clean dataset. Real work never does.
Real work starts with a file called sales_final_v3_USE_THIS.xlsx where the header is on row four, prices are stored as text with currency symbols, half the dates are in the wrong format, and the region column contains “North”, “north”, “N” and an empty cell.
This article walks through cleaning a file like that from start to finish, in the order you would actually do it. Every step has code you can adapt.
The file we are working with
Imagine an orders export with these columns: Order ID, Customer Name, Order Date, Region, Product, Quantity, Unit Price, Discount.
Roughly 40,000 rows. Exported from a system somebody set up in 2019 and nobody has maintained since.
Step 1: Load it without losing information
The default read_csv is eager to be helpful, and that is the problem. It guesses data types, and its guesses on messy columns are frequently wrong. An order ID like 0012345 becomes the number 12345, and the leading zeros are gone forever.
Load everything as text first, then convert deliberately:
python
import pandas as pd
df = pd.read_csv(
"orders.csv",
dtype=str,
keep_default_na=False,
encoding="utf-8",
)
print(df.shape)
df.head(10)dtype=str stops the guessing. keep_default_na=False stops pandas turning strings like “NA” and “None” into NaN before you have decided whether that is correct.
If you get a UnicodeDecodeError, the file is probably not UTF-8. Try encoding="latin-1" or encoding="cp1252", which covers most Windows exports.
For Excel files with junk rows above the real header:
python
df = pd.read_excel("orders.xlsx", sheet_name="Orders", header=3, dtype=str)Step 2: Look before you touch
Three commands tell you most of what you need to know.
python
df.info()
df.describe(include="all").T
df.isna().sum().sort_values(ascending=False)Then profile the columns you suspect:
python
for col in ["Region", "Product"]:
print(f"\n--- {col} ---")
print(df[col].value_counts(dropna=False).head(20))This is the pandas version of a SQL GROUP BY count, and it does the same job: it shows you the casing variants, the typos and the blanks in one look.
Step 3: Fix the column names
Spaces and mixed casing in column names mean you can never use dot notation and you will constantly mistype things.
python
df.columns = (
df.columns
.str.strip()
.str.lower()
.str.replace(r"[^\w\s]", "", regex=True)
.str.replace(r"\s+", "_", regex=True)
)
print(df.columns.tolist())Order ID becomes order_id, Customer Name becomes customer_name. Small change, large quality of life improvement.
Step 4: Handle duplicates
Check before you drop, always.
python
print("exact duplicates:", df.duplicated().sum())
print("duplicate order ids:", df.duplicated(subset=["order_id"]).sum())Look at a few of them:
python
dupes = df[df.duplicated(subset=["order_id"], keep=False)]
dupes.sort_values("order_id").head(20)This matters because duplicated IDs are sometimes genuine duplicates and sometimes multi line orders that should stay. Only the data tells you which.
Once you know:
python
# exact duplicate rows can go
df = df.drop_duplicates()
# for business duplicates, keep the most recent version of each order
df = (
df.sort_values("order_date")
.drop_duplicates(subset=["order_id"], keep="last")
)keep="last" after sorting is the pandas equivalent of the SQL ROW_NUMBER pattern. Be explicit about the sort, because without it “last” just means whatever order the file happened to be in.
Step 5: Clean text columns
Strip, standardise casing, then map variants:
python
text_cols = ["customer_name", "region", "product"]
for col in text_cols:
df[col] = (
df[col]
.str.strip()
.str.replace(r"\s+", " ", regex=True)
)
df["region"] = df["region"].str.title()
region_map = {
"N": "North",
"S": "South",
"E": "East",
"W": "West",
"Nort": "North",
"": None,
}
df["region"] = df["region"].replace(region_map)
print(df["region"].value_counts(dropna=False))The \s+ replacement collapses double spaces inside strings, which is a common cause of names that look identical but will not group together.
Step 6: Convert data types
Now that the text is clean, convert. This is where you find the rows that were quietly broken.
Numbers stored as text with symbols:
python
df["unit_price"] = (
df["unit_price"]
.str.replace(r"[₹$,]", "", regex=True)
.str.strip()
)
df["unit_price"] = pd.to_numeric(df["unit_price"], errors="coerce")
df["quantity"] = pd.to_numeric(df["quantity"], errors="coerce")errors="coerce" turns anything unparseable into NaN instead of raising. That is what you want, but only if you then check how much it silently ate:
python
print("unparseable prices:", df["unit_price"].isna().sum())
df[df["unit_price"].isna()].head(10)If that count is 12 out of 40,000, look at them and move on. If it is 4,000, something systematic is wrong and you need to go back to step 5.
Percentages stored as text:
python
df["discount"] = (
df["discount"]
.str.replace("%", "", regex=False)
.pipe(pd.to_numeric, errors="coerce")
.div(100)
)Dates:
python
df["order_date"] = pd.to_datetime(
df["order_date"],
format="%d/%m/%Y",
errors="coerce",
)
bad_dates = df["order_date"].isna().sum()
print("unparsed dates:", bad_dates)Specify the format when you know it. Letting pandas infer is slower and, worse, it can interpret different rows differently when day and month are ambiguous. 03/04/2026 is either 3 April or 4 March, and guessing wrong shifts a third of your data by months.
If you have genuinely mixed formats, parse in passes:
python
parsed = pd.to_datetime(df["order_date"], format="%d/%m/%Y", errors="coerce")
fallback = pd.to_datetime(df["order_date"], format="%Y-%m-%d", errors="coerce")
df["order_date"] = parsed.fillna(fallback)Step 7: Deal with missing values
There is no default answer here. What you do depends on why the value is missing.
Understand the pattern first:
python
missing = df.isna().mean().sort_values(ascending=False)
print((missing * 100).round(1))Then choose per column.
Drop rows when the missing field makes the row meaningless:
python
df = df.dropna(subset=["order_id", "order_date"])Fill with a real default when zero or “Unknown” is genuinely correct:
python
df["discount"] = df["discount"].fillna(0)
df["region"] = df["region"].fillna("Unknown")Leave it as NaN when you do not know. This is underrated. Filling a missing price with the column mean makes your data look complete and your analysis wrong. NaN is honest, and pandas aggregations skip it by default anyway.
Drop a column when it is mostly empty and nobody uses it:
python
df = df.loc[:, df.isna().mean() < 0.9]The one thing to avoid: filling missing values silently and then forgetting you did it. Whatever you choose, print the counts before and after.
Step 8: Sanity check the values
Type conversion tells you the format is right. It does not tell you the values make sense.
python
checks = {
"negative_quantity": (df["quantity"] < 0).sum(),
"zero_price": (df["unit_price"] == 0).sum(),
"future_order_date": (df["order_date"] > pd.Timestamp.today()).sum(),
"discount_over_100": (df["discount"] > 1).sum(),
"very_old_orders": (df["order_date"] < "2015-01-01").sum(),
}
for name, count in checks.items():
print(f"{name}: {count}")Negative quantities are often returns, which is fine if you know that and terrible if you do not and they are quietly cancelling out your sales total.
Step 9: Build the derived columns
Only after everything above:
python
df["gross_amount"] = df["quantity"] * df["unit_price"]
df["net_amount"] = df["gross_amount"] * (1 - df["discount"])
df["order_month"] = df["order_date"].dt.to_period("M").dt.to_timestamp()Doing this before cleaning is a classic mistake: you compute revenue on prices that are still strings, or on dates that half failed to parse, and the error propagates everywhere.
Step 10: Make it repeatable
If this file arrives every month, do not do any of the above in a notebook cell you will lose. Put it in a function:
python
def clean_orders(path: str) -> pd.DataFrame:
df = pd.read_csv(path, dtype=str, keep_default_na=False)
df.columns = (
df.columns.str.strip().str.lower()
.str.replace(r"[^\w\s]", "", regex=True)
.str.replace(r"\s+", "_", regex=True)
)
df = df.drop_duplicates()
for col in ["customer_name", "region", "product"]:
df[col] = df[col].str.strip().str.replace(r"\s+", " ", regex=True)
df["region"] = df["region"].str.title().replace(REGION_MAP)
df["unit_price"] = pd.to_numeric(
df["unit_price"].str.replace(r"[₹$,]", "", regex=True),
errors="coerce",
)
df["quantity"] = pd.to_numeric(df["quantity"], errors="coerce")
df["order_date"] = pd.to_datetime(df["order_date"], format="%d/%m/%Y", errors="coerce")
df = df.dropna(subset=["order_id", "order_date"])
df["net_amount"] = df["quantity"] * df["unit_price"] * (1 - df["discount"].fillna(0))
return dfThen add a small assertion block so the function fails loudly when next month’s file is different:
python
def validate_orders(df: pd.DataFrame) -> None:
assert df["order_id"].is_unique, "duplicate order ids found"
assert df["order_date"].notna().all(), "unparsed dates present"
assert (df["quantity"] >= 0).all(), "negative quantities present"
assert len(df) > 0, "empty dataframe"Silent failure is the enemy. A script that crashes tells you something changed. A script that produces a smaller number than last month does not.
The short version
- Load as text so nothing is guessed away
- Profile before changing anything
- Fix column names
- Check duplicates, then drop deliberately
- Clean text, then standardise categories
- Convert types with
errors="coerce"and check what got coerced - Handle missing values per column, on purpose
- Sanity check the actual values
- Build derived columns last
- Wrap it in a function with assertions
The order matters more than any individual trick. Most cleaning disasters come from doing step 9 before step 6.