Data quality: missing values, duplicates, errors

Data work is said to be 80 % of an ML project. That is roughly true, and it is not tedious drudgery — it is where most of the actual improvements come from.

Three problems, in order:

Problem	How you find it
Missing values	df.isna().sum() — but also values coded as -1, 999, "unknown"
Duplicates	df.duplicated().sum(), and duplicates on the key even when the rows differ
Implausibilities	describe(): ages of 200, negative prices, dates in the future

The hardest question is why the value is missing, because that decides what you are allowed to do about it.

Type	Means	Example
MCAR	missing completely at random	a sensor dropped packets
MAR	missing depending on something you can see	younger people answer web surveys more often
MNAR	missing depending on the value itself	high earners do not state their income

MNAR is the most dangerous: filling in the mean where the highest values are missing shifts the whole distribution downwards, and makes the analysis systematically wrong.

Strategies for missing values:

Strategy	When	The risk
Drop the row	few missing, MCAR	skew unless MCAR; wasted data
Drop the column	> 50 % missing and low value	you lose the signal
Mean/median	numeric, MCAR	artificially reduces the variance
The most common value	categorical	exaggerates the majority
Model-based (kNN, iterative)	there is a pattern in the other columns	expensive; can leak
An indicator column + imputation	nearly always a good complement	none

The last row deserves emphasis: add a column x_was_missing with 0/1 in addition to the imputation. That a value is missing is often informative in itself — a patient with no test result differs from one with a normal test result — and the model can then use that information rather than being fooled by the filled-in value.

The most important rule about imputation: compute the mean on the training data and apply it to validation and test. Compute the mean over the whole dataset and you have leaked information from the test set into the training. The same goes for scaling and encoding.

Duplicates are not always errors. Two identical rows can be two real events. Always check on the key:

df.duplicated().sum()                    # completely identical rows
df.duplicated(subset=["pupil_id"]).sum() # the same key, possibly different data

The second line finds the genuinely problematic case: two records for the same person with different data. Somebody then has to decide which one holds.

Document every decision. One line per action, in the code or in a log file: what was changed, how many rows, and why. In six months somebody — probably you — will wonder why there are 4 812 rows instead of 5 000.

import pandas as pd, numpy as np

df = pd.DataFrame({
    "id":   [1, 2, 3, 3, 5, 6],
    "age":  [15, 16, 999, 999, -1, 17],
    "score": [80.0, np.nan, 70.0, 70.0, 65.0, np.nan],
    "city": ["Malmö", "malmö", "Lund", "Lund", "MALMÖ", "Lund "],
})

log = []

def action(text, before, after):
    log.append(f"{text}: {before} → {after} rows")

# 1. Sentinel values are missing values in disguise
n = len(df)
df["age"] = df["age"].replace({999: np.nan, -1: np.nan})
print(df["age"].isna().sum(), "ages are in fact missing")           # 3

# 2. Duplicates — both identical rows and duplicates on the key
before = len(df)
df = df.drop_duplicates()
action("identical duplicates", before, len(df))
print("duplicates on id:", df.duplicated(subset=["id"]).sum())       # 0

# 3. Normalise the categories before you count the unique values
df["city"] = df["city"].str.strip().str.lower()
print(sorted(df["city"].unique()))                                   # ['lund', 'malmö']

# 4. An indicator plus imputation, with the mean FROM THE TRAINING DATA
train, test = df.iloc[:3].copy(), df.iloc[3:].copy()
for d in (train, test):
    d["score_was_missing"] = d["score"].isna().astype(int)
mean = train["score"].mean()          # ← computed on the training data only
train["score"] = train["score"].fillna(mean)
test["score"] = test["score"].fillna(mean)

print("\n".join(log))
# identical duplicates: 6 → 5 rows

The indicator column score_was_missing costs nothing and often saves the model: without it a filled-in mean looks like a real mean.