Machine Learning ML-02.04
Data cleaning
Handle missing values, outliers, duplicates, messy categories and unit errors, and apply every fix identically at training and serving time.
The big idea
EDA found the problems; cleaning fixes them. Real data is messy because it is made by people and systems: forms with optional fields, two exports of the same order, a country typed as "France", "france " and "FR", a weight entered in grams in a field meant for kilograms. A model has no idea any of this is wrong. It will happily learn that "FR" customers behave differently from "France" customers.
Cleaning is like preparing vegetables before cooking: wash, trim, throw out what is rotten, cut everything to the same size. And the same prep has to happen every time the dish is made. If the restaurant peels carrots for the tasting menu but not for regular service, diners get a different meal. In ML terms, every cleaning step must run identically at training time and at prediction time.
How it works
Step 1: Missing values
First ask why a value is missing, because the answer decides what is safe:
- Missing at random: a sensor dropped a reading, a form field failed to save. Gaps are unrelated to the value itself. Most fixes work.
- Missing for a reason: high earners skip the income question, sick patients miss follow-up visits. The gaps are systematically different from what you can see. No filling method recovers the truth, and the fact that a value is missing is itself information.
Then pick a strategy:
- Drop the rows (or the column, if it is mostly empty). Simple, but you lose data, and if values are missing for a reason, the rows you keep are no longer representative.
- ImputeFilling in missing values with an estimate, such as the column's mean or median, or a prediction from other columns. with a statistic: the mean, or the median for skewed columns. Keeps every row, but every filled value is identical, so the column's spread shrinks and its relationships with other columns weaken.
- Impute with a model: predict the missing value from other columns, for example income from age. Closer to the truth per row, but it can make relationships look stronger than they really are, because filled values sit exactly on the fitted line.
- Add a missing indicatorAn extra 0/1 column that records whether the original value was missing, so the model can learn from the missingness itself.: a 0/1 column saying "this was missing", alongside any filling. Cheap, and it lets the model learn that missingness matters. Tree-based models such as gradient boosting can also handle missing values natively.
Step 2: Outliers
An outlier is either an error or a rare truth, and the fix depends on which:
- Errors: a house of 893 square metres among flats of 70, an age of 212, a negative price. Fix them if you can tell what was meant, otherwise set them to missing and treat them as in step 1.
- Real but extreme: a genuinely huge order, a celebrity's follower count. Keep them. You may cap them at a high percentile (winsorizingCapping extreme values at a chosen percentile, such as the 1st and 99th, so a few huge values do not dominate.), log-transform the column, or use a model that is not thrown off by them. Never delete them just because a box plot drew them in red: in fraud detection the outliers are the whole point.
Step 3: Duplicates
The same record exported twice, a user who signed up twice, a retry that created two orders. Exact duplicates are easy to drop. Near duplicates (same person, slightly different spelling) need a rule for what counts as "the same". Duplicates do more than inflate counts: if copies of a record land in both the training and test set, the model is tested on data it has already seen, which is a form of leakage ( Data leakage ).
Step 4: Inconsistent categories and unit errors
- Categories: trim spaces, unify case, and map known aliases ("FR" to "france"). Then look at the rare values: a long tail of one-off spellings usually hides a few real categories.
- Units: grams and kilograms, cents and dollars, Celsius and Fahrenheit, local time and UTC. A value about 1,000 times or 100 times the rest is a strong hint. Fix the unit, and fix the upstream form or pipeline so it stops happening.
import numpy as np
import pandas as pd
from sklearn.impute import SimpleImputer
orders = pd.DataFrame({
"order_id": [101, 102, 102, 103, 104, 105, 106],
"country": ["France", "france ", "france ", "FR", "Germany", "germany", None],
"weight": [1.2, 0.8, 0.8, 950.0, 2.5, np.nan, 1.1], # kg, but one row in grams
"total": [30.0, 12.5, 12.5, 18.0, np.nan, 44.0, 25.0],
})
# 1. Exact duplicates (the same order exported twice)
orders = orders.drop_duplicates()
# 2. Inconsistent categories: trim, lowercase, then map known aliases
orders["country"] = (orders["country"].str.strip().str.lower()
.replace({"fr": "france"}).fillna("unknown"))
# 3. Unit errors: a parcel over 100 "kg" here is grams typed into a kg field
grams = orders["weight"] > 100
orders.loc[grams, "weight"] /= 1000
# 4. Missing values: keep a flag, then fill. Fit the filler on training rows only.
orders["total_missing"] = orders["total"].isna().astype(int)
cols = ["weight", "total"]
train = orders.iloc[:4] # pretend the first 4 rows are training data
imp = SimpleImputer(strategy="median").fit(train[cols])
print("medians learned on train:", imp.statistics_)
orders[cols] = imp.transform(orders[cols]) # same filler for train and new rows
print(orders.to_string(index=False))
print("rows fixed for units:", int(grams.sum()), "| countries:", sorted(orders["country"].unique()))Output
medians learned on train: [ 1.075 18. ]
order_id country weight total total_missing
101 france 1.200 30.0 0
102 france 0.800 12.5 0
103 france 0.950 18.0 0
104 germany 2.500 18.0 1
105 germany 1.075 44.0 0
106 unknown 1.100 25.0 0
rows fixed for units: 1 | countries: ['france', 'germany', 'unknown'] Each fix is one or two lines. The important detail is at step 4 of the code: the medians are learned from the training rows only, then the same fitted imputer fills the gaps in every row. That is how it must work in production too.
Step 5: Clean the same way at training and serving time
If training filled missing incomes with a median of 52k but the serving code fills them with 0, the model receives inputs it never saw during training and its predictions quietly degrade. This mismatch is called training-serving skew. The cure is to make cleaning part of one saved pipeline, such as a scikit-learn Pipeline, rather than separate scripts that drift apart.
Try it
The lab has 400 people with an age (always known) and a yearly income (sometimes missing). Because the data is synthetic, the true incomes are known, so you can see exactly what each strategy gets wrong.
Try this:
- Keep "at random" and switch between mean and drop. The estimated mean barely moves, but watch the spread and the correlation with age: what does a tall pink spike at one value do to them?
- Switch to predict from age. The correlation with age now comes out higher than the true value. Why would filled values sitting exactly on a line exaggerate the relationship?
- Now choose "high earners skip it". Try every strategy. Can any of them recover the true mean of 60.5? What would a missing indicator let a model learn here that filling cannot?
Trade-offs
| Option | Wins when | Loses when |
|---|---|---|
| Drop rows | Few rows are missing and gaps are random | Many rows are missing or the gaps follow a pattern; you also cannot drop at serving time |
| Mean or median fill | Quick baseline; median for skewed columns | Spread shrinks and relationships weaken, especially with many gaps |
| Model-based fill | Other columns predict the missing one well | It overstates relationships and adds a model to maintain |
| Missing indicator | Missingness itself carries signal | Rarely; it adds a column per feature with gaps |
| Cap or log-transform outliers | Extreme but real values dominate a model | The extremes are exactly what you need to detect |
Common mistakes
- Filling missing values before splitting the data, so test-set statistics leak into training (see Data leakage ).
- Writing cleaning code twice, once in a notebook and once in the serving service, and letting them drift.
- Deleting all outliers by rule, including the rare real cases the model most needs.
- Dropping rows with missing values in training, then getting rows with missing values in production anyway.
- Fixing a unit error in the dataset but not in the upstream form, so it comes back next month.
In the wild
Production ML systems typically package cleaning and feature steps with the model, as a scikit-learn pipeline or a transformation graph, so the exact same logic runs offline and online. Data validation libraries check incoming batches against expected ranges, types and missing-value rates and stop a pipeline when an upstream change, such as a field switching from kilograms to grams, breaks those expectations. Feature stores, covered in Feature stores and training-serving skew , exist largely to keep training and serving data consistent.
Check yourself
-
You fill missing incomes with the column mean. What happens to the column's standard deviation?
Show answer
C. It shrinks, because every filled value sits exactly at the mean Filled values add no spread around the mean, so the overall spread is understated. With many gaps the shrinkage is large.
-
Patients who feel worse skip follow-up surveys. Which statement is true?
Show answer
C. The gaps are related to the missing value, so no fill recovers the truth; a missing indicator lets the model use the missingness When data is missing for a reason, the observed rows are biased. Filling uses that biased data. Recording missingness is often the most informative move.
-
Where should the median used for imputation be computed?
Show answer
B. On the training data only, then reused for validation, test and serving The filler is part of the model. Fit it on training data and apply the same values everywhere, or you leak test information and create training-serving skew.
-
A weight column has values around 1 to 5 and a few around 1,000 to 5,000. What is the most likely fix?
Show answer
B. Divide them by 1,000: they are grams in a kilogram field A clean factor of about 1,000 is the fingerprint of a unit error. Convert them, and fix the source so it stops happening.
Recap
- Ask why values are missing before choosing a fix; missingness that follows a pattern cannot be filled away.
- Dropping loses data, mean and median fills shrink spread, model fills exaggerate relationships, and indicators keep the signal.
- Outliers are either errors to fix or real events to keep; decide case by case.
- Remove duplicates, normalise category spellings and fix unit mix-ups, at the source as well as in the data.
- Fit cleaning steps on training data and run the same saved steps at serving time.
Next up
- From notebook to production learning path