Data Cleaning with Python and Pandas: A Complete Practical Guide

Most data problems are not clever. They are a country spelled 43 different ways, a date written three ways in one column, a price stored as text with a currency symbol, and the same order appearing twice with a different capital letter. Left alone, these quietly corrupt every average, join, and model that follows.

This guide shows data cleaning with Python and pandas on a realistic messy file, step by step. Unlike most tutorials, the practice data is built from a known clean table, so after cleaning you can measure how much damage was repaired instead of just eyeballing head(). You will also learn what changed in pandas 3.0 that breaks a lot of older cleaning code.

You will end with a reusable pipeline, a way to validate it, and a decision guide for the hardest part: missing values.

What Data Cleaning Is (and Isn’t)

Data cleaning means making data consistent, valid, and usable without changing what it truthfully says. It is not the same as feature engineering, and it is not about making numbers look nicer.

Four habits make cleaning safer:

• Keep the raw file read-only. Always write cleaned data to a new file.
• Flag before you change. Mark suspicious values in a new column so the decision stays visible and reversible.
• Never guess silently. If a rule cannot resolve a value, leave it missing and count it.
• Count after every step. Rows, missing cells, and duplicates should change only where you expect.

Setup and Versions

pip install pandas numpy

pandas 3.0 was released on January 21, 2026 and supports Python 3.11 and later (release notes). The code in this article was run on both pandas 3.0.2 and pandas 2.3.3 (Python 3.12, NumPy 2.4.4 for the 3.0.2 run) and produced identical results, apart from dtype names and the one behaviour difference shown in the pitfalls section.

Three pandas 3.0 changes matter for cleaning:

• A dedicated str dtype is now inferred for text instead of object.
• Copy-on-Write is the default, so any indexing result or method result behaves like a copy.
• Chained assignment stops working, and SettingWithCopyWarning is gone.

The Practice Dataset

The script builds a clean table of 1,000 orders (the “truth”), then damages a copy of it on purpose and adds 70 duplicate rows. Because the truth is known, every fix can be checked.

Problem	Where	Count in the file
Messy column names	all columns	Order ID, E-mail, Qty, and so on
Whitespace and mixed case	names, countries, status	many
Country spellings	Country	43 distinct spellings for 5 real countries
Status spellings (including “canceled”)	Status	13 distinct for 4 real statuses
Three date formats in one column	Order Date	ISO, day/month/year, and “Mar 7, 2025”
Prices stored as text	Unit Price ($)	$45.20, 45.20, USD 45.20
Prices 100 times too large	Unit Price ($)	12
Negative quantities	Qty	8
Invalid email addresses	E-mail	35
Missing values in several spellings	several	N/A, n/a, NULL, -, unknown, empty
Exact duplicate rows	all	40
Near duplicates (differ only in case and spacing)	all	30

The Complete Code

Save this as clean_orders.py and run python clean_orders.py. It writes messy_orders.csv and clean_orders.csv to the current folder.

"""Data cleaning with pandas: build a messy orders file, clean it, and verify against ground truth."""
import difflib
import functools
import re
import warnings

import numpy as np
import pandas as pd

rng = np.random.default_rng(7)
N = 1000
MISSING_TOKENS = ["", "N/A", "n/a", "NULL", "-", "unknown"]

# ======================================================================
# 1. Make the data: a clean "truth" table, then damage a copy of it
# ======================================================================
FIRST = ["Ava", "Liam", "Noah", "Emma", "Mia", "Lucas", "Zoe", "Omar", "Yara", "Ethan", "Nora", "Ivan"]
LAST = ["Smith", "Jones", "Garcia", "Muller", "Dubois", "Haddad", "Rossi", "Khan", "Brown", "Novak"]
COUNTRIES = ["United States", "United Kingdom", "Germany", "France", "Morocco"]
VARIANTS = {
    "United States": ["USA", "U.S.", "US", "united states", "Untied States"],
    "United Kingdom": ["UK", "U.K.", "Great Britain", "united kingdom"],
    "Germany": ["germany", "GERMANY", "Deutschland", "Germny"],
    "France": ["france", "FRANCE", "Frnace"],
    "Morocco": ["morocco", "MOROCCO", "Maroc", "Moroco"],
}


def make_truth(n):
    first, last = rng.choice(FIRST, n), rng.choice(LAST, n)
    return pd.DataFrame({
        "order_id": np.arange(10_001, 10_001 + n),
        "customer_name": [f"{f} {l}" for f, l in zip(first, last)],
        "email": [f"{f.lower()}.{l.lower()}{i}@example.com" for i, (f, l) in enumerate(zip(first, last))],
        "order_date": pd.Timestamp("2025-01-01") + pd.to_timedelta(rng.integers(0, 365, n), unit="D"),
        "country": rng.choice(COUNTRIES, n, p=[.35, .2, .2, .15, .1]),
        "unit_price": np.clip(np.round(rng.lognormal(3.6, 0.6, n), 2), 5, 400),
        "quantity": rng.integers(1, 11, n),
        "discount_pct": rng.choice([0, 5, 10, 15, 20], n, p=[.5, .2, .15, .1, .05]),
        "status": rng.choice(["completed", "shipped", "cancelled", "returned"], n, p=[.6, .2, .12, .08]),
    })


def make_messy(truth):
    n = len(truth)
    flag = lambda p: rng.random(n) < p                                  # noqa: E731
    pick = lambda fn, mask, s: [fn(v) if m else v for v, m in zip(s, mask)]   # noqa: E731
    miss = lambda s, p: [rng.choice(MISSING_TOKENS) if m else v for v, m in zip(s, flag(p))]  # noqa: E731

    name = pick(str.upper, flag(.2), truth["customer_name"])
    name = pick(lambda v: f"  {v} ", flag(.3), name)

    bad_email = flag(.03)
    email = pick(lambda v: v.replace("@", "."), bad_email, truth["email"])
    email = pick(str.upper, flag(.2), email)
    email = miss(email, .04)

    def fmt_date(d):
        return rng.choice([d.strftime("%Y-%m-%d"), d.strftime("%d/%m/%Y"), f"{d:%b} {d.day}, {d.year}"])
    date = miss([fmt_date(d) for d in truth["order_date"]], .02)

    country = pick(lambda v: rng.choice(VARIANTS[v]), flag(.4), truth["country"])
    country = pick(lambda v: f"{v} ", flag(.1), country)

    price_outlier = flag(.01)
    price = np.where(price_outlier, truth["unit_price"] * 100, truth["unit_price"])
    price = [rng.choice(["${:,.2f}", "{:,.2f}", "USD {:,.2f}"]).format(p) for p in price]
    price = miss(price, .03)

    neg_qty = flag(.01)
    qty = miss([str(-q if m else q) for q, m in zip(truth["quantity"], neg_qty)], .03)
    disc = miss([f"{d}%" if rng.random() < .5 else str(d) for d in truth["discount_pct"]], .05)

    status = pick(lambda v: rng.choice([v.upper(), v.capitalize() + " ", v]), flag(.35), truth["status"])
    status = pick(lambda v: v.replace("cancelled", "canceled"), flag(.3), status)

    messy = pd.DataFrame({
        "Order ID": truth["order_id"].astype(str), " Customer Name ": name, "E-mail": email,
        "Order Date": date, "Country ": country, "Unit Price ($)": price,
        "Qty": qty, "Discount %": disc, "Status": status,
    })
    # exact duplicates, plus "near" duplicates that differ only in case and spacing
    exact = messy.sample(40, random_state=1)
    near = messy.drop(exact.index).sample(30, random_state=2).copy()
    near[" Customer Name "] = near[" Customer Name "].str.strip().str.lower()
    near["E-mail"] = near["E-mail"].str.upper()
    messy = pd.concat([messy, exact, near]).sample(frac=1, random_state=3).reset_index(drop=True)
    injected = {"bad_email": set(truth.order_id[bad_email]), "price_outlier": set(truth.order_id[price_outlier]),
                "neg_qty": set(truth.order_id[neg_qty])}
    return messy, injected


# ======================================================================
# 2. Cleaning steps: small, pure functions that return a new DataFrame
# ======================================================================
COUNTRY_MAP = {
    "united states": "United States", "usa": "United States", "us": "United States", "u.s.": "United States",
    "united kingdom": "United Kingdom", "uk": "United Kingdom", "u.k.": "United Kingdom", "great britain": "United Kingdom",
    "germany": "Germany", "deutschland": "Germany", "france": "France", "morocco": "Morocco", "maroc": "Morocco",
}
CANONICAL = {v.lower(): v for v in COUNTRY_MAP.values()}
DATE_FORMATS = ["%Y-%m-%d", "%d/%m/%Y", "%b %d, %Y"]      # day-first for slashes: confirm with the data owner!
EMAIL_RE = r"[^@\s]+@[^@\s]+\.[^@\s]+"
FLAGS = ["email_invalid", "quantity_invalid", "price_outlier"]


def standardise_columns(df):
    snake = df.rename(columns=lambda c: re.sub(r"[^0-9a-z]+", "_", c.strip().lower()).strip("_"))
    return snake.rename(columns={"e_mail": "email", "qty": "quantity", "discount": "discount_pct", "unit_price": "unit_price"})


def clean_text(df):
    out = df.copy()
    for col in ["customer_name", "email", "country", "status"]:
        out[col] = out[col].str.strip()
    out["customer_name"] = out["customer_name"].str.title()
    out["email"] = out["email"].str.lower()
    out["status"] = out["status"].str.lower().replace({"canceled": "cancelled"})
    key = out["country"].str.lower()
    mapped = key.map(COUNTRY_MAP)
    todo = mapped.isna() & key.notna()                          # spellings the dictionary does not know
    fuzzy = key[todo].map(lambda v: next(iter(difflib.get_close_matches(v, CANONICAL, n=1, cutoff=0.8)), None))
    mapped[todo] = fuzzy.map(CANONICAL)
    out["country"] = mapped
    return out


def parse_dates(s):
    parsed = [pd.to_datetime(s, format=f, errors="coerce") for f in DATE_FORMATS]
    return functools.reduce(lambda a, b: a.combine_first(b), parsed)


def convert_types(df):
    num = lambda s: pd.to_numeric(s.str.replace(r"[^0-9.\-]", "", regex=True), errors="coerce")   # noqa: E731
    return df.assign(
        order_id=pd.to_numeric(df["order_id"]), order_date=parse_dates(df["order_date"]),
        unit_price=num(df["unit_price"]), quantity=num(df["quantity"]), discount_pct=num(df["discount_pct"]),
    )


def drop_duplicate_orders(df):
    return df.drop_duplicates(subset="order_id", keep="first").reset_index(drop=True)


def add_quality_flags(df):
    out = df.copy()
    # na=False makes missing text behave the same in pandas 2 (object) and pandas 3 (str); then test presence explicitly
    looks_valid = out["email"].str.fullmatch(EMAIL_RE, na=False)
    out["email_invalid"] = out["email"].notna() & ~looks_valid
    out["quantity_invalid"] = out["quantity"] <= 0
    out["quantity"] = out["quantity"].mask(out["quantity_invalid"])       # impossible value -> missing
    logp = np.log(out["unit_price"])
    mad = (logp - logp.median()).abs().median()
    out["price_outlier"] = 0.6745 * (logp - logp.median()) / mad > 3.5    # modified z-score on log price
    out["unit_price"] = out["unit_price"].mask(out["price_outlier"])      # flag AND blank: we do not guess a fix
    return out


def clean_orders(raw):
    return (raw.pipe(standardise_columns).pipe(clean_text).pipe(convert_types)
               .pipe(drop_duplicate_orders).pipe(add_quality_flags))


# ======================================================================
# 3. Run it, step by step, and check every claim
# ======================================================================
if __name__ == "__main__":
    truth = make_truth(N)
    messy, injected = make_messy(truth)
    messy.to_csv("messy_orders.csv", index=False)

    # read everything as text first; let read_csv turn missing-value tokens into NaN
    raw = pd.read_csv("messy_orders.csv", dtype=str, na_values=["-", "unknown"])
    print("--- Profile of the raw file ---")
    print(f"rows: {len(raw)}   exact duplicate rows: {raw.duplicated().sum()}")
    print("missing cells per column:", raw.isna().sum().to_dict())
    print("distinct country spellings:", raw["Country "].nunique(), "| distinct status spellings:", raw["Status"].nunique())

    step = raw.pipe(standardise_columns).pipe(clean_text)
    print("\n--- After standardising names and text ---")
    print("distinct countries:", step["country"].nunique(), sorted(step["country"].dropna().unique()))
    unknown = sorted(set(raw["Country "].str.strip().str.lower()) - set(COUNTRY_MAP))
    resolved = step.loc[raw["Country "].str.strip().str.lower().isin(unknown), "country"]
    print("spellings not in the dictionary:", dict(zip(raw["Country "].str.strip().str.lower()[resolved.index], resolved)))
    print("unresolved countries (set to missing, not guessed):", int(step["country"].isna().sum() - raw["Country "].isna().sum()))
    print("distinct statuses:", sorted(step["status"].dropna().unique()))
    print(f"duplicate rows now visible: {step.duplicated().sum()}  (was {raw.duplicated().sum()} before normalising)")

    typed = convert_types(step)
    print("\n--- After type conversion ---")
    print(typed.dtypes.astype(str).to_dict())
    still_bad = int(typed["order_date"].isna().sum() - step["order_date"].isna().sum())
    print("dates that failed to parse (beyond already-missing):", still_bad)

    deduped = drop_duplicate_orders(typed)
    value_cols = ["customer_name", "email", "order_date", "country", "unit_price", "quantity", "discount_pct", "status"]
    conflicts = (typed.groupby("order_id")[value_cols].nunique(dropna=False) > 1).any(axis=1).sum()
    print(f"\nrows {len(typed)} -> {len(deduped)} after dropping repeat order_ids; orders with conflicting values: {conflicts}")

    clean = clean_orders(raw)
    pd.testing.assert_frame_equal(clean.drop(columns=FLAGS),
                                  add_quality_flags(deduped).drop(columns=FLAGS))
    print("pipeline function matches the step-by-step result: True")

    def score(flagged_ids, true_ids):
        tp = len(flagged_ids & true_ids)
        return f"flagged {len(flagged_ids)}, injected {len(true_ids)}, caught {tp}, false alarms {len(flagged_ids - true_ids)}"
    ids = clean["order_id"]
    print("\n--- Quality flags vs the damage we injected ---")
    print("invalid emails :", score(set(ids[clean["email_invalid"]]), injected["bad_email"]))
    print("price outliers :", score(set(ids[clean["price_outlier"]]), injected["price_outlier"]))
    print("bad quantities :", score(set(ids[clean["quantity_invalid"]]), injected["neg_qty"]))

    print("\n--- Cleaned values vs ground truth (non-missing cells only) ---")
    t = truth.set_index("order_id").loc[clean["order_id"]].reset_index(drop=True)
    for col in ["customer_name", "order_date", "country", "unit_price", "quantity", "discount_pct", "status"]:
        ok = clean[col].notna()
        print(f"{col:<14} match rate: {(clean.loc[ok, col].to_numpy() == t.loc[ok, col].to_numpy()).mean():.4f}  ({ok.sum()} cells)")
    ok = clean["email"].notna() & ~clean["email_invalid"]
    print(f"{'email':<14} match rate: {(clean.loc[ok, 'email'].to_numpy() == t.loc[ok, 'email'].to_numpy()).mean():.4f}  ({ok.sum()} valid cells)")

    print("\n--- Missing values: what each strategy does ---")
    p, tp = clean["unit_price"], t["unit_price"]
    print(f"rows with at least one missing value: {clean.isna().any(axis=1).sum()} of {len(clean)}  -> df.dropna() would discard them all")
    print(f"unit_price missing: {p.isna().sum()}")
    print(f"{'strategy':<22}{'mean':>8}{'std':>8}")
    for name, s in [("truth (all rows)", tp), ("drop missing prices", p.dropna()), ("fill with median", p.fillna(p.median()))]:
        print(f"{name:<22}{s.mean():>8.2f}{s.std():>8.2f}")

    print("\nStress test: blank out 30% of prices at random")
    stress = tp.mask(rng.random(len(tp)) < 0.30)
    print(f"{'strategy':<22}{'mean':>8}{'std':>8}")
    for name, s in [("truth (all rows)", tp), ("drop missing prices", stress.dropna()), ("fill with median", stress.fillna(stress.median()))]:
        print(f"{name:<22}{s.mean():>8.2f}{s.std():>8.2f}")

    clean.to_csv("clean_orders.csv", index=False)
    print("\nsaved clean_orders.csv with", len(clean), "rows")

    print("\n--- Pitfall: chained inplace (a pattern common in older tutorials) ---")
    demo = pd.DataFrame({"x": [1.0, None, 3.0]})
    with warnings.catch_warnings(record=True) as caught:
        warnings.simplefilter("always")
        demo["x"].fillna(0, inplace=True)
    print("pandas", pd.__version__, "| missing after chained inplace fillna:", int(demo["x"].isna().sum()),
          "| warning:", caught[0].category.__name__ if caught else "none")
    demo["x"] = demo["x"].fillna(0)
    print("missing after assignment:", int(demo["x"].isna().sum()))

Output

This is the output from pandas 3.0.2 with a fixed random seed:

--- Profile of the raw file ---
rows: 1070   exact duplicate rows: 40
missing cells per column: {'Order ID': 0, ' Customer Name ': 0, 'E-mail': 30, 'Order Date': 14, 'Country ': 0, 'Unit Price ($)': 27, 'Qty': 31, 'Discount %': 57, 'Status': 0}
distinct country spellings: 43 | distinct status spellings: 13

--- After standardising names and text ---
distinct countries: 5 ['France', 'Germany', 'Morocco', 'United Kingdom', 'United States']
spellings not in the dictionary: {'frnace': 'France', 'untied states': 'United States', 'germny': 'Germany', 'moroco': 'Morocco'}
unresolved countries (set to missing, not guessed): 0
distinct statuses: ['cancelled', 'completed', 'returned', 'shipped']
duplicate rows now visible: 70  (was 40 before normalising)

--- After type conversion ---
{'order_id': 'int64', 'customer_name': 'str', 'email': 'str', 'order_date': 'datetime64[us]', 'country': 'str', 'unit_price': 'float64', 'quantity': 'float64', 'discount_pct': 'float64', 'status': 'str'}
dates that failed to parse (beyond already-missing): 0

rows 1070 -> 1000 after dropping repeat order_ids; orders with conflicting values: 0
pipeline function matches the step-by-step result: True

--- Quality flags vs the damage we injected ---
invalid emails : flagged 35, injected 35, caught 35, false alarms 0
price outliers : flagged 12, injected 12, caught 12, false alarms 0
bad quantities : flagged 8, injected 8, caught 8, false alarms 0

--- Cleaned values vs ground truth (non-missing cells only) ---
customer_name  match rate: 1.0000  (1000 cells)
order_date     match rate: 1.0000  (986 cells)
country        match rate: 1.0000  (1000 cells)
unit_price     match rate: 1.0000  (962 cells)
quantity       match rate: 1.0000  (963 cells)
discount_pct   match rate: 1.0000  (947 cells)
status         match rate: 1.0000  (1000 cells)
email          match rate: 1.0000  (936 valid cells)

--- Missing values: what each strategy does ---
rows with at least one missing value: 156 of 1000  -> df.dropna() would discard them all
unit_price missing: 38
strategy                  mean     std
truth (all rows)         44.31   29.20
drop missing prices      44.43   29.57
fill with median         44.15   29.04

Stress test: blank out 30% of prices at random
strategy                  mean     std
truth (all rows)         44.31   29.20
drop missing prices      43.69   26.92
fill with median         41.72   22.67

saved clean_orders.csv with 1000 rows

--- Pitfall: chained inplace (a pattern common in older tutorials) ---
pandas 3.0.2 | missing after chained inplace fillna: 1 | warning: ChainedAssignmentError
missing after assignment: 0

Step-by-Step Walkthrough

Step 1: Profile before you touch anything

The first rule is to read everything as text. dtype=str stops pandas from guessing, so a column like Unit Price ($) is not silently turned into something you did not choose, and identifiers such as ZIP codes keep their leading zeros.

read_csv already treats a built-in list of tokens as missing, including empty strings, N/A, n/a, and NULL. This file also uses - and unknown, so na_values adds them. Without the defaults, the same file leaves 12 empty strings, 9 N/A, 10 n/a, and 13 NULL values in just two columns as ordinary text. See the read_csv documentation for the full default list.

The profile shows 1,070 rows, 40 exact duplicates, and 43 distinct country spellings. Those numbers are the baseline every later step is compared against.

Step 2: Standardise names and text

standardise_columns lowercases each column name, replaces runs of non-alphanumeric characters with underscores, then applies an explicit rename map for the three names that need judgement (e_mail, qty, discount).

clean_text strips whitespace, applies title case to names, lowercases emails and statuses, and merges the “canceled” spelling into “cancelled”. Country names go through a dictionary first, then a fuzzy fallback using Python’s difflib.get_close_matches for typos. In this run the fallback resolved four spellings, and every one was right:

germny -> Germany    frnace -> France    moroco -> Morocco    untied states -> United States

Fuzzy matching is a convenience with a real risk: a cutoff that is too loose can merge two genuinely different values. Always print what it matched, as the script does, and leave unresolved values missing instead of forcing a match.

Step 3: Convert types

Text columns become numbers and dates here. convert_types removes everything except digits, dots, and minus signs before calling pd.to_numeric(errors="coerce"). That handles $, USD, thousands separators, and % in one expression, and anything still unparseable becomes missing instead of raising an error.

Dates need more care. parse_dates tries each known format explicitly and combines the results. Explicit formats are slower to write than letting pandas guess, but they make the assumption visible. The slash format is ambiguous: 07/03/2025 is 7 March in most of the world and July 3 in the US. The script assumes day first, which is correct for this synthetic file, but on real data you must confirm it with whoever produced the file. A useful diagnostic is to count failures: if a format fails on many rows, it is probably wrong (Exercise 1 shows this).

In pandas 3.0 the parsed dates come out as datetime64[us] instead of datetime64[ns], so avoid code that depends on nanosecond resolution.

Step 4: Deduplicate, but only after normalising

Look at the output: the raw file has 40 exact duplicate rows, but after standardising text there are 70. The extra 30 were hidden because they differed only in case and spacing. If you call drop_duplicates() first, you remove 40 and silently keep 30 duplicates.

The fix is to normalise first, then deduplicate on the business key (order_id), not on every column. Before dropping, the script checks that duplicate groups do not disagree on any value. Here there are 0 conflicts. In real data a conflict means two versions of the same order with different content, and keep="first" would pick one arbitrarily. Investigate those rows instead of dropping them.

Step 5: Flag, then fix

The final cleaning step adds three flag columns instead of deleting rows:

• email_invalid: the address is present but does not match a simple pattern.
• quantity_invalid: quantity is zero or negative, which is impossible for an order. The value is set to missing.
• price_outlier: the price is extreme compared with the rest. The value is set to missing but the flag stays.

For outliers the script uses the modified z-score, which relies on the median instead of the mean so extreme values do not distort the measure:

$$M_i = \frac{0.6745\,(x_i - \tilde{x})}{\text{MAD}}$$

Here $x_i$ is the log of the price, $\tilde{x}$ is the median of those logs, and $\text{MAD}$ is the median of $|x_i - \tilde{x}|$. A common rule of thumb flags values with $M_i > 3.5$. Prices are skewed, so taking logs first makes the scale symmetric.

The flags caught 35 of 35 invalid emails, 12 of 12 price outliers, and 8 of 8 negative quantities, with no false alarms. The script does not “fix” the 100x prices by dividing by 100, because that would be a guess. If the data owner confirms the pattern, you could correct it deliberately.

Step 6: Decide what to do about missing values

After cleaning, 156 of 1,000 orders have at least one missing value. Calling df.dropna() would throw away 15.6% of the data to remove a few percent of missing cells in each column, which is why it is rarely the right default.

Strategy	Use when	Main risk
Leave missing and add a flag	Default choice; you are not ready to decide	Downstream code must handle missing values
Drop rows	Few rows affected and missingness is unrelated to what you study	Loses data; biased if missingness is not random
Fill with a constant such as 0	You have confirmed that missing truly means zero	Silently wrong if it does not
Fill with median or mean	Quick baseline for a numeric feature	Shrinks the spread of the data
Model-based or group-wise imputation	The data has structure to exploit	Complexity and data leakage

With only 38 missing prices out of 1,000, the strategies give nearly identical means and standard deviations (see the output). So the script runs a stress test: it blanks 30% of prices at random in the ground truth, then compares strategies.

Dropping the gaps keeps the spread roughly right (26.92 vs a true 29.20), while filling with the median shrinks it much more (22.67), because every filled value sits exactly at the centre. Both also pull the mean down a little (43.69 and 41.72 vs 44.31). The lesson is that imputing is not free: it changes the distribution your model will learn from.

If you impute for machine learning, fit the fill values on the training split only, then apply them to validation and test data. Computing the median on the full dataset first leaks information from the test set into training.

Step 7: Validate against the truth

Two checks guard the pipeline:

1. clean_orders() (the one-call .pipe chain) is compared with the step-by-step result using pd.testing.assert_frame_equal. They match.
2. Because the truth is known, each cleaned column is compared with it. Every non-missing cell matched (match rate 1.0000 in all eight columns).

Read that second result with care. A perfect score is a property of this synthetic file: the damage types were chosen so that they can be repaired, and the rules were written knowing them. On real data you will not have the truth, so use the other checks instead: row counts, distinct-value counts, range assertions, and manual review of a sample of flagged rows.

pandas 3.0 Pitfalls That Break Older Tutorials

Chained inplace no longer works

A pattern common in older guides looks like this:

df["x"].fillna(0, inplace=True)

On pandas 2.3.3 this raised a FutureWarning and still filled the missing value. On pandas 3.0.2 it raised ChainedAssignmentError and left the value missing, because df["x"] now behaves as a copy. The portable fix is plain assignment:

df["x"] = df["x"].fillna(0)

String methods and missing values

With the new str dtype, .str.fullmatch() returns False for missing text, while the old object dtype returned NaN. That made an early draft of this script flag missing emails as invalid on pandas 3 and crash on pandas 2. Passing na=False and testing notna() explicitly makes the behaviour identical on both versions.

Check dtypes by meaning, not by name

Code that tests dtype == "object" to find text columns will miss str columns in pandas 3. Use pd.api.types.is_string_dtype() instead.

Common Mistakes and Troubleshooting

• Deduplicating before normalising. You will miss near duplicates, as the 40 versus 70 result shows.
• Overwriting the raw file. Always write to a new path so you can rerun the pipeline.
• inplace=True and chained assignment. Assign results back to a column or a new DataFrame.
• Ambiguous dates. Confirm day-first or month-first with the data owner. Count parse failures after every format.
• astype(int) on a column with missing values. It raises an error. Keep the column as float, or use pandas’ nullable Int64 dtype.
• Treating 0 or an empty string as missing without checking. They may be real values.
• Fuzzy matching without review. Print every fuzzy match and keep the cutoff strict.
• Imputing before splitting data for machine learning. This leaks test information into training.
• UnicodeDecodeError on read_csv. Try encoding="utf-8-sig" or encoding="latin-1" after confirming the file’s real encoding.
• Different results on a colleague’s machine. Check the pandas version first. Pin it in requirements.txt.

Limitations

• The practice data is synthetic, and every injected problem is one the pipeline was designed to handle. Real files contain unknown unknowns.
• Domain knowledge decides many cleaning questions, such as whether a negative quantity means a return or a typo. Code cannot settle that for you.
• This pipeline handles one table. Joins across tables, time zones, and very large files that do not fit in memory need additional techniques.
• Dedicated validation libraries exist for enforcing schemas on production data, such as pandera and Great Expectations. They are worth exploring once your checks outgrow a few assertions.

Data Cleaning Checklist

• Keep the raw file untouched and read it as text.
• Profile: rows, missing cells per column, duplicates, distinct values.
• Standardise column names, whitespace, case, and category spellings.
• Convert types with explicit formats and count parse failures.
• Deduplicate after normalising, on the business key, and check for conflicts.
• Flag invalid and extreme values before changing them.
• Choose a missing-value strategy per column and document it.
• Add assertions, then save the result to a new file.
• Re-run the pipeline end to end from the raw file.

Practice Exercises

1. In the script, change the second entry of DATE_FORMATS to "%m/%d/%Y" (month first). How many dates fail to parse, and what does that tell you?

2. After cleaning, how many orders have a missing quantity, and how many of those are missing because of the cleaning rules rather than the raw file?

3. Every cleaned column matched the truth exactly. Why is that not evidence that the same pipeline would be correct on a real company’s data?

Solutions

1. 230 dates fail to parse beyond those already missing. Slash dates with a day above 12, such as 25/03/2025, cannot be valid month-first dates. A large failure count is the sign that a format is wrong.
2. 37 orders have a missing quantity. 8 of them were made missing by the cleaning rule for negative quantities. The remaining 29 were already missing in the raw file.
3. The damage was injected by the same author who wrote the cleaning rules, so every problem was known and fixable, and there was a perfect answer key. Real data has problems nobody anticipated and no answer key, so you need counts, assertions, and manual sampling instead.

Frequently Asked Questions

What order should I clean data in?

Profile first, then standardise text, convert types, deduplicate, flag invalid values, and handle missing values last. Deduplicating before normalising and imputing before flagging are the two most common ordering mistakes.

Should I use dropna() or fillna()?

Neither by default. Check how many rows each would affect, whether the missingness is related to what you are studying, and what the fill does to the distribution. Flagging and deferring the decision is often the safest first step.

How do I handle outliers in pandas?

Detect them with a robust rule such as the modified z-score or an IQR rule, flag them, and investigate before changing anything. An outlier can be an error, or it can be the most important row in the data.

How do I clean a date column that has several formats?

Parse with an explicit list of formats and errors="coerce", combine the results, and count what still fails. Confirm any ambiguous format, such as day-first versus month-first, with the data’s owner.

Should I still use inplace=True?

Avoid it. On pandas 3 the chained form does not modify the original DataFrame, and in general assigning results back is clearer and works on every version.

Does this code work on older pandas versions?

It was run on pandas 2.3.3 and 3.0.2 with identical cleaning results. Versions earlier than 2.x were not tested.

Next Steps

Run the script, then break it on purpose: change a rule and watch which counts move. Replace the synthetic file with a small real dataset of your own and write down every assumption you make. After that, learn how to turn your checks into reusable tests, and how cleaned data feeds feature engineering and model training.

References

• pandas, What’s new in 3.0.0 (January 21, 2026).
• pandas, pandas 3.0 released.
• pandas, read_csv reference.
• pandas, Working with missing data.
• Python, difflib.