Data Cleaning and Handling Missing Values

You load a customer dataset: 10,000 rows, 14 columns. A few cells are blank, so you type the obvious fix.

df = df.dropna()print(len(df))     # 1,247

You have just thrown away 87% of your data — and not a random 87%. The column with the most blanks was income, and people decline to state their income for reasons that correlate strongly with their income. What survived is a biased sample of unusually forthcoming customers, and any model trained on it will be confidently wrong about everybody else.

That is the shape of nearly every data cleaning mistake. The operation succeeds. The output looks tidier than the input. The damage is invisible until much later, when the numbers turn out not to describe the world. Cleaning is not about making data look neat; it is about making a series of judgement calls, deliberately, and knowing what each one costs.

What actually goes wrong in real data

The last row is worth pausing on. Legacy systems often encode "missing" as a sentinel number. If -999 means "no reading" and you compute a mean without noticing, your average temperature comes out below freezing and every downstream decision inherits the error.

df = pd.read_csv("data.csv", na_values=["N/A", "-", "unknown", -999, 9999])

Missing values

First, find out how much and where

1missing = pd.DataFrame({2    "n_missing": df.isna().sum(),3    "pct": (df.isna().mean() * 100).round(1),4}).query("n_missing > 0").sort_values("pct", ascending=False)5print(missing)67print(f"rows with any missing value: {df.isna().any(axis=1).sum()}")8print(f"rows that are entirely complete: {df.notna().all(axis=1).sum()}")

That second pair of numbers is what would have saved the opening disaster. Ten per cent missing spread across fourteen columns can still mean that almost no single row is complete.

Why it is missing changes what you should do

You usually cannot prove which case you are in, but you can look. Compare the rows with a blank against the rows without one:

flag = df.income.isna()print(df.groupby(flag)[["age", "spend", "tenure"]].mean())

If the two groups look alike, dropping is defensible. If the missing-income group spends twice as much, missingness carries information — and throwing those rows away throws the information away with them.

Missingness is data. Before you fill or drop a column, check whether the blanks themselves predict your target.

df["income_missing"] = df.income.isna().astype(int)   # keep the signaldf["income"] = df.income.fillna(df.income.median())   # then fill the hole

Dropping, when it is the right call

df.dropna(subset=["target"])              # never impute the thing you predictdf.dropna(thresh=int(0.7 * df.shape[1]))  # drop rows missing over 30% of fieldsdf.drop(columns=df.columns[df.isna().mean() > 0.6])   # drop mostly-empty columns

A bare dropna() is almost never right. dropna(subset=[...]) — dropping only where a specific critical column is blank — usually is. The one place dropping is unambiguously correct is the target column: a row with no label teaches a supervised model nothing, and inventing a label is manufacturing evidence.

Filling, and what each choice does to your data

Group-wise filling is usually the biggest single improvement over a global fill, and it is one line:

df["salary"] = df.groupby("job_title")["salary"].transform(    lambda s: s.fillna(s.median()))

Filling a junior developer's missing salary with the median across all job titles pulls them towards the company average, which is wrong in a predictable direction. Filling with the median of their own role is far closer to the truth.

Every fill method shares one cost: it reduces variance. Replace 200 blanks with the same median and you have added 200 identical points at the centre of the distribution, so the standard deviation drops and the data looks more certain than it is. With 2% missing that is negligible. With 40% missing you have largely invented the column, and adding the indicator flag is more honest than pretending.

The mistake that inflates every score you report

Compute the median over the whole dataset, fill with it, then split into training and test sets — and you have leaked information from the test set into training. The model has effectively seen a summary of data it is meant to be judged on, and your validation score comes out higher than reality.

1# WRONG2df["income"] = df.income.fillna(df.income.median())3train, test = train_test_split(df, test_size=0.2)45# RIGHT6train, test = train_test_split(df, test_size=0.2, random_state=42)7fill_value = train.income.median()          # learned from training data only8train["income"] = train.income.fillna(fill_value)9test["income"] = test.income.fillna(fill_value)   # applied to test

Split first, then learn every cleaning parameter from the training half alone. A statistic computed over the whole dataset is a channel through which the answers leak.

Duplicates

1print(df.duplicated().sum())              # exact copies across every column2df = df.drop_duplicates()34# Usually you care about a business key, not the whole row5print(df.duplicated(subset=["customer_id"]).sum())6df = df.sort_values("updated_at").drop_duplicates(subset=["customer_id"], keep="last")

Exact duplicates are the easy case and often mean a file was ingested twice. The harder case is a duplicated key with differing details — the same customer with two email addresses — where keep="last" after sorting by timestamp keeps the most recent version rather than an arbitrary one.

Then there are the duplicates that are not textually identical at all:

1df["company_key"] = (df.company2    .str.strip()3    .str.lower()4    .str.replace(r"\b(ltd|limited|inc|plc)\b\.?", "", regex=True)5    .str.replace(r"[^a-z0-9]", "", regex=True))6print(df.duplicated(subset=["company_key"]).sum())

"Acme Ltd", "acme limited" and "ACME Ltd." are one company. Until you normalise them they are three, and every per-company aggregate is wrong.

Outliers

An outlier is a value far from the rest. Whether it is an error or the most important row in your table is a judgement you must make, not one a formula can make for you.

Two ways to find them

The interquartile range method flags anything more than 1.5 box-widths beyond the quartiles. Worked through, on a column with $Q_1 = 30{,}000$ and $Q_3 = 70{,}000$:

So a salary of £250,000 is flagged and one of £120,000 is not. Note the lower bound came out negative, meaning nothing is flagged at the bottom — which is correct here, because salaries cannot be negative.

1def iqr_bounds(s, k=1.5):2    q1, q3 = s.quantile([0.25, 0.75])3    iqr = q3 - q14    return q1 - k * iqr, q3 + k * iqr56low, high = iqr_bounds(df.salary)7outliers = df[(df.salary < low) | (df.salary > high)]8print(f"{len(outliers)} outliers ({len(outliers)/len(df):.1%})")

The z-score method flags points more than about 3 standard deviations from the mean, using $z = \frac{x - \mu}{\sigma}$. It assumes a roughly bell-shaped distribution, and it has a self-defeating flaw: the outliers themselves inflate $\sigma$, so a few extreme values raise the threshold and hide themselves. On skewed data such as income or web traffic, the IQR method is the more reliable of the two.

Deciding what to do

The strongest habit here is to check what the flagged rows have in common before removing them. If every "outlier" comes from one store, one device or one date, you have found a data collection problem, and deleting the evidence is the worst available response.

Text and type problems

1df["city"] = df.city.str.strip().str.title()2df["email"] = df.email.str.strip().str.lower()3df["phone"] = df.phone.str.replace(r"[^0-9+]", "", regex=True)45df["country"] = df.country.replace({6    "uk": "United Kingdom", "U.K.": "United Kingdom", "GB": "United Kingdom",7})8print(df.country.value_counts(dropna=False))

value_counts(dropna=False) on every categorical column is five seconds of work that finds most category problems immediately: variants that should be one group, an unexpected blank count, a category with a single row that turns out to be a typo.

1df["price"] = pd.to_numeric(2    df.price.astype(str).str.replace(r"[£$,]", "", regex=True),3    errors="coerce",          # anything unparseable becomes NaN, not an exception4)5df["order_date"] = pd.to_datetime(df.order_date, errors="coerce")6df["category"] = df.category.astype("category")   # big memory saving

errors="coerce" is the key to bulk conversion: one bad cell in a million rows turns into a NaN you can count, rather than an exception that kills the run. Always count them afterwards — a coercion that quietly produces 40,000 new blanks needs investigating, not accepting.

Making cleaning repeatable

Cleaning done by hand in a notebook is cleaning you cannot reproduce next month. Put it in a function that reports what it did:

1import pandas as pd, numpy as np23def clean(df, fill_stats=None):4    """Clean a customer table. Pass fill_stats from training data to avoid leakage."""5    report = {"rows_in": len(df)}6    df = df.copy()78    df = df.drop_duplicates()9    report["dupes_removed"] = report["rows_in"] - len(df)1011    for col in df.select_dtypes(include=["object", "str"]):12        df[col] = df[col].str.strip()1314    df["price"] = pd.to_numeric(df.price, errors="coerce")15    df["order_date"] = pd.to_datetime(df.order_date, errors="coerce")1617    if fill_stats is None:                       # training pass: learn the values18        fill_stats = {c: df[c].median() for c in df.select_dtypes("number")}19    for col, value in fill_stats.items():20        df[f"{col}_was_missing"] = df[col].isna().astype(int)21        df[col] = df[col].fillna(value)2223    df = df[df.age.between(0, 120)]              # domain rule, not a statistic24    report["rows_out"] = len(df)25    report["kept_pct"] = round(100 * len(df) / report["rows_in"], 1)26    return df, fill_stats, report2728train_clean, stats, rep = clean(train)29test_clean, _, _ = clean(test, fill_stats=stats)   # reuse training statistics30print(rep)

Then check the result rather than trusting it:

assert train_clean.isna().sum().sum() == 0, "blanks survived cleaning"assert train_clean.duplicated().sum() == 0assert rep["kept_pct"] > 80, f"only kept {rep['kept_pct']}% of rows"

That last assertion is the one that would have caught the disaster at the top of this page. It is three seconds to write and it turns a silent 87% data loss into a loud failure at the moment it happens.

The judgement never goes away — no function can tell you whether a £250,000 salary is a typo or your best customer. What the function does is make your judgement explicit, repeatable, and auditable by someone else. That is the actual deliverable of cleaning: not a tidier table, but a written record of every decision you made about the data, and the numbers showing what each one cost.