Data Cleaning
Finding and fixing the errors, inconsistencies and gaps in raw data.
Why cleaning matters, and how to look at your data first
Real-world data are almost never clean. Forms are filled in carelessly, sensors fail, systems record the same thing in different formats, and spreadsheets are merged by hand. A model can only learn from what it is given, so errors in the data become errors in the model, a principle summed up as "garbage in, garbage out". Surveys of data scientists consistently find that preparing data takes more of their time than any other task, and it is often where the biggest improvements in accuracy come from.
Before changing anything, the first job is simply to look. A systematic first inspection answers a handful of questions. How many rows and columns are there, and does that match what you expected? What type does each column have? A column of ages that the software reads as text, because one entry says "unknown", is an immediate warning sign. What do the summary statistics look like: the minimum, maximum, mean and quartiles of every numeric column? A minimum age of −5 or a maximum of 250 shows at a glance that something is wrong. Which values occur in each categorical column, and how often? Listing the distinct values of a "city" column will quickly reveal "Brisbane", "brisbane", "BRISBANE" and "Bris" as four supposedly different cities. And how many values are missing in each column, and do the missing values cluster in particular rows?
In pandas these questions map onto a handful of commands: df.shape, df.dtypes, df.describe(), df["col"].value_counts() and df.isna().sum(). Plotting a histogram of each numeric column is equally valuable, because odd shapes, spikes at suspicious values such as 0 or 999, and isolated extreme points all stand out visually.
One rule governs everything that follows: clean with a reason and keep a record. Every change should be justified (by a rule, by domain knowledge, or by checking the source), and every step should be written as code rather than done by hand, so that it can be repeated on new data, reviewed by others, and undone if it turns out to be wrong.
The common problems and how to fix each one
Most data problems fall into a small number of families, and each has a standard fix. Knowing the families makes it much easier to spot problems during inspection.
Duplicate records. The same row can appear twice because a form was submitted twice or two files overlapped. Exact duplicates are easy to find and remove, but near-duplicates, such as the same customer with a slightly different spelling of their name, need more care: you must decide which fields identify a real-world entity (perhaps an email address or a customer ID) and deduplicate on those. Duplicates are especially dangerous if they end up on both sides of a train/test split, because the model is then tested on examples it has already seen.
Inconsistent categories. The same category written in different ways (differences in capitalisation, leading or trailing spaces, abbreviations, synonyms or typos) makes one category look like several. The fix is to standardise: trim whitespace, convert to a single case, and map known variants onto one canonical value ("Bris" and "BNE" both become "Brisbane"). For typos, a fuzzy-matching step that groups values with a small edit distance can suggest candidates, which a person should then confirm.
Wrong data types. Numbers stored as text ("$52,000", "1,250"), dates stored in several formats ("2024-03-05", "05/03/2024", "March 5 2024"), and yes/no values stored as "Y", "yes", "TRUE" and 1 all need converting to a single proper type. Dates deserve particular care, because "05/03/2024" means 5 March in Australia but 3 May in the United States: you must know the source's convention rather than guess.
Inconsistent units and scales. One system records height in metres and another in centimetres; one price is in dollars and another in cents; temperatures arrive in both Celsius and Fahrenheit. A value of 1.75 alongside values of 170 and 182 is almost certainly a height in metres. Fixing units requires a rule ("any height below 3 must be in metres, so multiply by 100") and, ideally, confirmation from whoever produced the data.
Impossible or invalid values. Some values break the rules of the real world: a negative age, a birth date in the future, a percentage of 140, a person 3 metres tall. These are best caught with explicit validation rules written down for every column (age between 0 and 120, percentages between 0 and 100, end date after start date). A value that breaks a rule is usually treated as missing, unless the correct value can be recovered.
Missing values and outliers are the two remaining families. They are important and subtle enough to get their own sections below.
| Problem | Example | Typical fix |
|---|---|---|
| Duplicates | the same order recorded twice | drop exact duplicates; deduplicate on an ID |
| Inconsistent categories | "Bris", " brisbane", "BRISBANE" | trim, lower-case, map synonyms |
| Wrong types | "$52,000"; three date formats | parse to numbers and real dates |
| Mixed units | height 1.75 and 175 | convert to one unit using a documented rule |
| Invalid values | age −5 or 250 | validation rules → correct or mark missing |
Try it: clean a messy table step by step
This small customer table contains every problem from the previous section. Tick the cleaning steps one at a time and watch exactly which cells change (green), which become missing (red) and which are filled in (orange).
Interactive: this part runs in your browser.
Missing values: understand them before you fill them
Missing values are the most common data problem, and handling them well starts with asking why they are missing, because the answer determines which fixes are safe. The three mechanisms introduced in the Data topic are worth recalling. If values are missing completely at random (MCAR), such as readings lost to random sensor glitches, the missing rows are a fair random sample, and nothing systematic is lost. If they are missing at random (MAR), the chance of being missing depends on other things you can see, such as older customers being less likely to fill in an online field. If they are missing not at random (MNAR), the chance depends on the missing value itself, such as people with very high incomes declining to report their income.
There are four broad families of fixes.
Deletion. The simplest option is to drop every row with a missing value (listwise deletion), or to drop a whole column if most of its values are missing. Deletion is reasonable when only a small fraction of rows is affected and the data are MCAR. But it throws information away, and under MAR or MNAR it introduces bias: if high earners are the ones who skip the income question, deleting those rows leaves a dataset that underestimates incomes.
Simple imputation fills each gap with a single summary value from that column: the mean for roughly symmetric numeric data, the median for skewed data or data with outliers (because the median isn't dragged around by extreme values), and the mode (the most common value) for categories. It is quick and keeps every row, but it has a hidden cost that the demo below makes visible: every filled-in value is identical, so the column's spread is artificially reduced and its relationships with other columns are weakened. A refinement is group-wise imputation, using the median of a relevant group (for example, the median income of people with the same occupation) rather than of the whole column.
Model-based imputation predicts each missing value from the other columns. k-NN imputation finds the most similar complete rows and averages their values. Iterative imputation (also called MICE) fits a regression model for each column with missing values, using the other columns as inputs, and cycles through the columns several times until the predictions settle down. These methods preserve relationships between variables much better, which makes them the right choice under MAR.
Indicator columns. Whatever you fill in, it is often worth adding a yes/no column recording that the value was originally missing. Under MNAR in particular, the fact that a value is missing can be one of the most informative features you have.
For time series, different tools apply: a missing reading can be filled with the last known value (forward fill) or by drawing a straight line between its neighbours (interpolation), because neighbouring moments in time are usually similar. And for every method, the same leakage rule applies as for all preprocessing: compute the fill values (means, medians, models) on the training data only, then apply them unchanged to the validation and test data.
Try it: compare imputation strategies
Here 30% of the y-values are missing. Switch strategies and compare the statistics with the truth. Then switch the missingness to MAR (values more likely to be missing when x is high) and see which methods still cope.
Interactive: this part runs in your browser.
Outliers: detect carefully, treat thoughtfully
An outlier is a value that is unusually far from the rest of the data. It might be an error (a misplaced decimal point turning 52.3 into 523), a genuine but rare case (a billionaire in an income survey), or the most important thing in the dataset (a fraudulent transaction, a failing machine). Because these possibilities call for completely different actions, outliers should be investigated, never deleted automatically.
Three simple rules are widely used to detect outliers in a single numeric column. The z-score rule flags values more than a chosen number of standard deviations from the mean, usually 3: . It works well for roughly bell-shaped data, but it has a weakness: extreme values inflate the mean and standard deviation that the rule depends on, so a cluster of outliers can partly hide itself.
The IQR rule, used in box plots, is more robust. It finds the first and third quartiles and (the values below which 25% and 75% of the data lie), computes the interquartile range , and flags anything outside the "fences" . Let's work an example with the eleven values 12, 14, 15, 15, 16, 17, 18, 19, 20, 22 and 58. The quartiles are and , so . The fences are and . Every value lies inside the fences except 58, which is flagged. Because quartiles depend only on the middle of the data, the single extreme value doesn't distort the fences.
The robust z-score combines the two ideas, replacing the mean with the median and the standard deviation with the median absolute deviation (MAD), the median distance of the points from the median. Beyond single columns, some outliers are only visible in combination, such as a height of 150 cm with a weight of 120 kg; methods such as the Mahalanobis distance, isolation forests and local outlier factor are designed for these multivariate cases.
Once an outlier is found, there are several possible treatments. First, verify it against the source if you can, and correct it if it is clearly an error. If it is an error that can't be corrected, treat it as missing. If it is genuine, consider keeping it but limiting its influence: capping (also called winsorising) replaces values beyond the fences with the fence values; a log transform compresses a long tail so extreme values sit closer to the rest; or a robust model (such as one using median-based or absolute-error loss, or a tree-based model) can be used so that extreme values matter less. Removing genuine outliers simply because they are inconvenient makes your model look better on paper while making it worse at exactly the rare cases it may matter most for.
Try it: detect and treat outliers
Add extreme values and compare the three detection rules. Then try each treatment and watch what happens to the mean, median and standard deviation.
Interactive: this part runs in your browser.
The statistics behind cleaning decisions
Some statistics are easily pushed around by a few bad values and some are not. The mean listens to every value equally, so one wild number moves it; the median only cares about the middle, so it barely notices. And filling gaps with the mean quietly makes the data look less variable than it really is.
🔤 Symbols in this section
| total values, observed values | |
| sample variance | |
| first and third quartiles | |
| interquartile range | |
| median absolute deviation | |
| standard deviation of a normal distribution |
This section explains, with a little mathematics, why some cleaning choices behave better than others.
Robustness and the breakdown point. A statistic's breakdown point is the fraction of the data that can be made arbitrarily bad before the statistic itself becomes arbitrarily bad. The mean has a breakdown point of 0: changing a single value to a huge number drags the mean as far as you like, because gives every value an equal say. The median has a breakdown point of 50%: up to half the values can be made extreme before the middle value moves outside the range of the good data. That is the precise reason the median, the IQR and the MAD are preferred when outliers may be present.
What mean imputation does to the variance. Suppose a column has values, of which are observed with sample variance , and the other are filled in with the observed mean . The filled values sit exactly at the mean, so they add nothing to the sum of squared deviations, but they do increase the count. The variance of the completed column is therefore
With 30% of values missing, , so the variance is understated by about 30% and the standard deviation by about 16%. Covariances with other columns shrink in the same proportion, so correlations are weakened too. Any model or statistical test that relies on this spread will be overconfident.
How the IQR rule relates to the z-score rule. For data that follow a normal distribution, the quartiles lie at , so . The upper fence is then . So for normal data the IQR rule behaves like a z-score rule with a threshold of about 2.7, flagging about 0.7% of perfectly good points. That is a useful reminder that a flagged point is a candidate for inspection, not proof of an error.
The robust z-score. The median absolute deviation is . For normal data, , so dividing by puts the robust score on the same scale as an ordinary z-score:
A common rule flags points with . Unlike the ordinary z-score, this rule cannot be fooled by the outliers it is looking for.
Advanced: multiple imputation, label noise and data validation
Filling in a missing value with one number pretends we know it. Multiple imputation admits the uncertainty by filling it in several plausible ways and seeing how much the answer changes. The same "measure your uncertainty" mindset applies to unusual points, wrong labels and incoming data.
🔤 Symbols in this section
| number of imputed datasets | |
| estimate from dataset m | |
| average within-dataset variance | |
| variance between datasets | |
| total variance (Rubin's rules) | |
| inverse covariance matrix |
Multiple imputation. Any single imputed value pretends to know something we don't, so analyses on singly imputed data are overconfident. Multiple imputation fixes this by creating several (say to 20) completed datasets, each with imputed values drawn randomly from their predicted distribution rather than set to a single best guess. The analysis is run on each completed dataset and the results are combined with Rubin's rules: the final estimate is the average , and its total variance is
where is the average of the within-dataset variances and is the variance of the estimates between datasets. The between-dataset part, , is exactly the extra uncertainty caused by not knowing the missing values. For prediction (rather than inference), a single good model-based imputation is often sufficient, but multiple imputation is the standard for statistical analysis.
MICE (multivariate imputation by chained equations), scikit-learn's IterativeImputer, works as follows. It first fills every missing value with a simple guess such as the mean. Then, for each column with missing values in turn, it fits a regression model predicting that column from all the others (using the rows where it is observed), and replaces that column's missing entries with the model's predictions. It cycles through the columns several times until the imputations stop changing. Because each column is predicted from the others, relationships between variables are preserved.
Multivariate outlier detection. The Mahalanobis distance measures how far a point is from the centre of the data in units that account for the spread and correlation of the features (it uses the inverse covariance matrix), so it catches combinations that are unusual even when each value is ordinary on its own. The isolation forest builds many random trees that split the data at random points; outliers, being few and different, get isolated in very few splits, so a short average path length signals an anomaly.
Label noise. The labels themselves can be wrong, for example when annotators disagree or make mistakes. Methods such as confident learning use a model's out-of-fold predicted probabilities to estimate how often each class is confused with each other class, and flag examples whose given label is very unlikely under the model. Reviewing and correcting the most suspicious labels often improves a model more than any change of algorithm.
Validation as code. In production systems, cleaning rules are written down as an explicit schema: the expected type, range, allowed categories and missing-value rate for every column. Tools such as pandera, Great Expectations or TensorFlow Data Validation check every new batch of data against the schema automatically and raise an alert when something drifts, so that problems are caught before they reach the model.
Code: a complete cleaning workflow in pandas and scikit-learn
The same problems as the demo table, fixed in code: inspect, standardise, parse, convert, validate, deduplicate, flag outliers, impute.
import numpy as np
import pandas as pd
raw = pd.DataFrame({
"name": ["Ava", "Ben", "Chen", "Dia", "Eli", "Ben", "Fay", "Gus"],
"city": [" brisbane", "BRISBANE", "Sydney ", "Bris", "melbourne", "BRISBANE", "Melb.", "sydney"],
"joined": ["2024-03-01", "05/03/2024", "March 9 2024", "2024-03-12", "14/03/2024", "05/03/2024", "2024-03-20", "March 22 2024"],
"age": ["34", "41", "-5", "abc", "29", "41", "250", "38"],
"income": ["$52,000", "61000", "n/a", "48000", "", "61000", "$75,500", "57000"],
"height": ["1.68", "182", "175", "1.59", "170", "182", "165", None],
})
# 1. Inspect
print(raw.dtypes, "\n") # everything is 'object' (text): a warning sign
print(raw["city"].value_counts(), "\n")
df = raw.copy()
# 2. Standardise text categories
df["city"] = (df["city"].str.strip().str.lower()
.replace({"bris": "brisbane", "melb.": "melbourne"}).str.title())
# 3. Parse dates (try each known format; Australian day-first convention)
def parse_date(s):
for fmt in ("%Y-%m-%d", "%d/%m/%Y", "%B %d %Y"):
try:
return pd.to_datetime(s, format=fmt)
except ValueError:
pass
return pd.NaT
df["joined"] = df["joined"].map(parse_date)
# 4. Convert types; anything unparseable becomes NaN (missing)
df["income"] = pd.to_numeric(df["income"].str.replace(r"[$,]", "", regex=True), errors="coerce")
df["age"] = pd.to_numeric(df["age"], errors="coerce")
df["height"] = pd.to_numeric(df["height"], errors="coerce")
# 5. Fix units with a documented rule: heights below 3 are in metres
df.loc[df["height"] < 3, "height"] *= 100
# 6. Validate ranges: impossible ages -> missing
df.loc[~df["age"].between(0, 120), "age"] = np.nan
# 7. Remove exact duplicates
df = df.drop_duplicates()
# 8. Flag outliers with the IQR rule (flag, don't delete)
q1, q3 = df["income"].quantile([0.25, 0.75])
iqr = q3 - q1
df["income_outlier"] = ~df["income"].between(q1 - 1.5 * iqr, q3 + 1.5 * iqr) & df["income"].notna()
# 9. Record missingness, then impute (in a real project: fit on TRAIN only)
for col in ["age", "income", "height"]:
df[col + "_was_missing"] = df[col].isna()
df[col] = df[col].fillna(df[col].median())
print(df)
print("\nmissing values left:", int(df[["age", "income", "height"]].isna().sum().sum()))from sklearn.experimental import enable_iterative_imputer # noqa: F401 (enables IterativeImputer)
from sklearn.impute import SimpleImputer, KNNImputer, IterativeImputer
rng = np.random.default_rng(0)
x = rng.normal(size=200)
y = 0.8 * x + 0.6 * rng.normal(size=200)
X = np.c_[x, y]
X_missing = X.copy()
X_missing[rng.random(200) < 0.3, 1] = np.nan # 30% of y missing
for name, imp in [("mean", SimpleImputer(strategy="mean")),
("kNN", KNNImputer(n_neighbors=5)),
("iterative (MICE)", IterativeImputer(random_state=0))]:
filled = imp.fit_transform(X_missing)
print(f"{name:17s} std(y) = {filled[:, 1].std():.3f} corr = {np.corrcoef(filled.T)[0, 1]:.3f}")
print(f"{'true values':17s} std(y) = {y.std():.3f} corr = {np.corrcoef(x, y)[0, 1]:.3f}")Quiz
1. A column of ages is read as text by pandas. The most likely reason is…
2. Which statistic is most robust to outliers?
3. Values: Q1 = 15, Q3 = 20. With the 1.5·IQR rule, the upper fence is…
4. What does mean imputation do to a column's variance?
5. High earners tend to skip the income question. This missingness is…
6. When should fill values (like the median) be computed?
📚 Further reading
Trusted, mostly free sources: official documentation, well-known textbooks and courses. When you want to confirm or go deeper on anything here, start with these.
- 10 minutes to pandas ↗ Official pandas quick start
- scikit-learn: Preprocessing data ↗ Official guide