Data Engineering for AI

Handling missing data

A blank cell is information, and the reason a value is missing usually matters more than whatever number you use to fill the gap.

Read these first

On this page 8
  1. Why this trips people up
  2. Three reasons a cell is blank
  3. What happens if you ignore it
  4. The fix that costs one line
  5. Somewhere you have seen this
  6. What is honestly hard here
  7. Remember this
  8. What to learn next

One lesson, three depths. Pick the one that fits you today — you can switch any time.

Beginner — No maths. Plain English.

Missing data means a value that was never recorded. The reason it is missing usually matters more than whatever you fill in.

Think about a class where the teacher asks who failed the last test. Some hands go up. Many students look at the floor and say nothing.

The silence is not empty. The students who said nothing are not a random group.

A blank cell in a spreadsheet works the same way. Somebody chose not to answer, and that choice carries information.

Why this trips people up

The instinctive move is to fill the blanks and move on. Put the average in, get a full table, train the model.

That move throws away the most useful thing in the column. It replaces "this person refused to answer" with "this person is average", which is a specific and often false claim.

Three reasons a cell is blank

Random accidents. A sensor lost power for an hour. The blanks land anywhere, with no pattern.

Missing because of something you can see. Younger customers skip the "years at current job" field far more often. You can see their age, so you can account for it.

Missing because of the answer itself. People with high incomes decline to state their income. The reason for the blank is the hidden value.

The third one is the dangerous one, and it is also the most common in anything involving people.

What happens if you ignore it

   real incomes:      30  45  60  95  140  210
   who answered:      30  45  60   -    -    -
                                   \___________/
                                    the rich stayed quiet

   average of what you can see: 45
   average of the truth:        96

Fill the blanks with 45 and you have not repaired the column. You have taught the model that rich customers are average earners.

The fix that costs one line

Keep a second column that records whether the value was missing.

You end up with two things: a filled number, and an honest note saying the number was invented. The model can then learn what refusing to answer means, which in the picture above is worth a great deal.

Somewhere you have seen this

Loan forms where leaving a field blank makes the app ask more questions. Somebody worked out that the blank was predictive.

Health surveys, too. People who skip the drinking question are not a random sample of drinkers, and every epidemiologist knows it.

What is honestly hard here

You often cannot prove which of the three reasons applies, because the data needed to prove it is the data that is missing.

That is a genuine dead end, not a gap in your knowledge. The professional response is to state your assumption in writing, and to test whether your conclusions survive if the assumption is wrong.

Remember this

  • A blank cell is information, not an inconvenience.
  • Filling with the average silently claims the missing people were average.
  • Fill the value and keep a flag saying it was missing. It costs one column.

What to learn next

Developer — Code and libraries.

Here we compare the three standard strategies on the hardest and most common case: high earners decline to state their income, in training and in production.

That last part matters. Many tutorials evaluate on a complete test set, which quietly makes the missingness flag useless. Real prediction requests arrive with the same gaps as training rows.

Setup

bash
pip install numpy pandas scikit-learn

Three strategies, one honest test

missing_income.py
import numpy as np
import pandas as pd
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import roc_auc_score
from sklearn.pipeline import make_pipeline
from sklearn.preprocessing import StandardScaler

rng = np.random.default_rng(21)
n = 2600
income = rng.gamma(4.0, 15.0, n) + 10
years_job = rng.integers(0, 20, n)
repaid = rng.binomial(1, 1 / (1 + np.exp(-(0.05 * income + 0.12 * years_job - 4.0))))
df = pd.DataFrame({"income": income, "years_job": years_job, "repaid": repaid})

# High earners decline to state their income. This happens in training AND in production.
refuses = 1 / (1 + np.exp(-(0.06 * df["income"] - 4.5)))
df["income_seen"] = np.where(rng.random(n) < refuses, np.nan, df["income"])

train = df.sample(1800, random_state=1)
test = df.drop(train.index)
median = train["income_seen"].median()        # learned on train only, never on test


def prepare(frame):
    return frame.assign(income=frame["income_seen"].fillna(median),
                        missing=frame["income_seen"].isna().astype(int))


def auc(tr, te, cols):
    model = make_pipeline(StandardScaler(), LogisticRegression(max_iter=1000))
    model.fit(tr[cols], tr["repaid"])
    return round(roc_auc_score(te["repaid"], model.predict_proba(te[cols])[:, 1]), 3)


tr, te = prepare(train), prepare(test)
hidden = train.loc[train["income_seen"].isna(), "income"].mean()

print("training rows                :", len(train))
print("income missing in training   :", int(train["income_seen"].isna().sum()))
print("income missing in test       :", int(test["income_seen"].isna().sum()))
print("median used to fill the gaps :", round(median, 1))
print("true mean of the hidden values:", round(hidden, 1))
print()
print("repaid rate when income was given   :", round(train.loc[train["income_seen"].notna(), "repaid"].mean(), 3))
print("repaid rate when income was withheld:", round(train.loc[train["income_seen"].isna(), "repaid"].mean(), 3))
print()
print("A  drop the incomplete rows          :", auc(tr[tr["missing"] == 0], te, ["income", "years_job"]))
print("B  fill with the median              :", auc(tr, te, ["income", "years_job"]))
print("C  fill, and keep a was-missing flag :", auc(tr, te, ["income", "years_job", "missing"]))
print("   for reference, the true income    :", auc(train, test, ["income", "years_job"]))
Output
training rows                : 1800
income missing in training   : 769
income missing in test       : 326
median used to fill the gaps : 53.0
true mean of the hidden values: 88.8

repaid rate when income was given   : 0.496
repaid rate when income was withheld: 0.727

A  drop the incomplete rows          : 0.725
B  fill with the median              : 0.728
C  fill, and keep a was-missing flag : 0.813
   for reference, the true income    : 0.859

The one line that carries the whole lesson

repaid rate when income was given: 0.496 against repaid rate when income was withheld: 0.727.

The people who refused repay far more often. The refusal is a proxy for being wealthy, and being wealthy predicts repayment. The blank cell is one of the strongest features in the table.

Strategy A deletes it. Strategy B overwrites it with 53.0, a number that is wrong by 36 for those rows. Strategy C keeps it, and gains 0.085 of AUC for one extra column of zeros and ones.

For scale: that single flag recovers about two thirds of the gap between median filling (0.728) and having the true income you can never obtain (0.859).

Why dropping rows is worse than it looks

Strategy A scores 0.725, close to median filling, so it seems harmless. It is not, for two reasons the number hides.

It threw away 769 of 1800 training rows, 43% of the data, and got away with it here only because the remaining rows were plentiful.

More seriously, dropping is not available at prediction time. A live request with a blank income still needs an answer. You cannot refuse to score a customer because their form was incomplete. Any strategy that only works offline is not a strategy.

Common mistakes

Computing the median over the whole dataset before splitting. The median is a statistic learned from data, so it belongs to the training set. Computing it over everything leaks test information into training. Note median above is taken from train only.

Filling with zero for numeric columns. Zero is a real, meaningful value for income, age and temperature. Filling with it creates a fake cluster at zero that the model treats as a genuine group.

Using a fancy imputer and skipping the flag. Iterative and nearest-neighbour imputers estimate a plausible value. None of them recover the fact that it was absent. Add the flag regardless of the imputer.

Different handling in training and serving. Fill with the training median in both, using the same stored constant. Recomputing the median on each production batch guarantees drift.

One flag column per feature, for 300 features. That doubles your width. Add flags for columns whose missingness you believe is informative, and test the rest.

Try it yourself

Change 0.06 * df["income"] - 4.5 to a constant, say -1.0, so blanks appear at random regardless of income. Re-run.

Strategy C's advantage should collapse to nothing, because now the flag carries no information. That contrast is the whole difference between "missing at random" and "missing because of the answer", measured rather than asserted.

What to learn next

Researcher — Mathematics and papers.

Rubin's taxonomy

Rubin (1976), Inference and Missing Data, Biometrika, defines the missingness mechanism through the distribution of the indicator matrix $R$, where $R_{ij}=1$ if $X_{ij}$ is observed. Partition data into observed $X_{\text{obs}}$ and missing $X_{\text{mis}}$.

MCAR — missing completely at random:

$$ p(R \mid X_{\text{obs}}, X_{\text{mis}}, \phi) = p(R \mid \phi) $$

MAR — missing at random:

$$ p(R \mid X_{\text{obs}}, X_{\text{mis}}, \phi) = p(R \mid X_{\text{obs}}, \phi) $$

MNAR — missing not at random: neither holds; $R$ depends on $X_{\text{mis}}$ even after conditioning on $X_{\text{obs}}$. Here $\phi$ denotes the parameters of the missingness process.

Under MAR, and when $\phi$ is distinct from the parameters of interest $\theta$, the mechanism is ignorable: likelihood-based inference on the observed data is valid without modelling $R$. Complete-case analysis is unbiased only under MCAR, which is strictly stronger.

The developer example is MNAR by construction, since the refusal probability is a function of income itself.

Why the missingness indicator works here

Adding $R$ as a feature is often criticised in the statistics literature, and correctly so for inference: under MAR it can bias coefficient estimates, a point made by Jones (1996) and reiterated in Little and Rubin's textbook.

Prediction is a different objective. If the deployment distribution has the same missingness mechanism as training, then $R$ is a legitimately observable feature at prediction time, and conditioning on it is valid. The target becomes $p(Y \mid X_{\text{obs}}, R)$ rather than $p(Y \mid X)$, which is exactly the quantity you can act on.

Josse et al. (2019), On the consistency of supervised learning with missing values, formalise this: for prediction with the same mechanism at train and test time, imputation followed by a universally consistent learner is Bayes-consistent, and "impute then flag" is a natural way to make $R$ available. The two literatures disagree only because they optimise different objectives.

The assumption that fails loudly is a shift in the mechanism. Redesign the form so income becomes mandatory, and the flag column becomes constant, the learned coefficient becomes unreachable, and performance drops without any alert firing.

Multiple imputation

Single imputation understates variance, because it treats an estimated value as observed. Rubin's multiple imputation draws $m$ completed datasets from the posterior predictive distribution of the missing values, analyses each, and combines by Rubin's rules:

$$ \bar{Q} = \frac{1}{m}\sum_{k=1}^{m} \hat{Q}_k, \qquad T = \bar{U} + \left(1 + \frac{1}{m}\right) B $$

Where $\hat{Q}_k$ is the estimate from dataset $k$, $\bar{U}$ the average within-imputation variance, and $B = \frac{1}{m-1}\sum_k (\hat{Q}_k - \bar{Q})^2$ the between-imputation variance. The $(1+1/m)$ factor corrects for finite $m$; $m$ between 5 and 20 is conventional.

MICE (van Buuren and Groothuis-Oudshoorn, 2011, mice: Multivariate Imputation by Chained Equations, JSS) implements this by cycling over columns, regressing each incomplete variable on the others. sklearn.impute.IterativeImputer is a single-imputation variant of the same idea; it does not give you Rubin's variance correction.

Multiple imputation matters for confidence intervals. For a point prediction served to a user, it usually does not repay its cost.

MNAR: what is actually available

MNAR is not identifiable from observed data alone. Any estimate requires an untestable assumption. The two standard families:

Selection models factor $p(X, R) = p(X)\,p(R \mid X)$ and specify the second term parametrically — Heckman's bivariate normal formulation being the canonical case. Results are notoriously sensitive to the normality assumption.

Pattern-mixture models factor $p(X, R) = p(X \mid R)\,p(R)$, modelling the distribution separately per missingness pattern. The unidentified part is made explicit as a sensitivity parameter, which is a more honest presentation.

The practical recommendation across the literature is the same: perform a sensitivity analysis. Shift imputed values by $\delta$ across a plausible range and report how the conclusion changes. A conclusion that survives $\delta$ from $-1$ to $+1$ standard deviations is worth something; one that flips at $\delta = 0.2$ is not.

Native handling in gradient boosting

XGBoost, LightGBM and CatBoost learn a default direction per split: missing values are routed left or right by whichever choice maximises the split gain. This is a learned, non-linear analogue of the missingness indicator, applied at every node rather than once globally.

Consequence: on tabular data with informative missingness, boosted trees often need no explicit imputation, and imputing first can lose information by destroying the pattern. When benchmarking imputation strategies, include "pass the NaNs straight to the booster" as a baseline. It wins more often than people expect. See XGBoost.

Reading

  • Rubin, Inference and Missing Data, Biometrika 1976.
  • Little and Rubin, Statistical Analysis with Missing Data, 3rd edition, Wiley 2019.
  • van Buuren and Groothuis-Oudshoorn, mice: Multivariate Imputation by Chained Equations in R, Journal of Statistical Software 2011.
  • Josse et al., On the consistency of supervised learning with missing values, 2019 — arxiv.org/abs/1902.06931
  • van Buuren, Flexible Imputation of Missing Data, 2nd edition — freely readable online, and the most practical treatment available.

What to learn next

What to learn next

These follow on from what you just read.

  • Data Engineering for AI

    Imbalanced data

    When one outcome is rare, accuracy stops meaning anything, and rebalancing only moves the threshold while a new column adds real information.

  • Data Engineering for AI

    Data augmentation

    Augmentation makes new training examples by changing old ones in ways the real world also changes them, and a transform that does not match reality makes the model worse.

  • Data Engineering for AI

    Synthetic data

    Synthetic data is invented rows that imitate real ones, and it can copy the shape of your data without ever adding information you did not already have.