Missing values

In depth

Rubin’s definitions, a little more precisely

Picture a second table the same shape as your data, holding 1 where a value is missing and 0 where it is present. Call its entries $R$. Split the data values into those you have, $Y_{\text{obs}}$, and those you don’t, $Y_{\text{mis}}$. The three mechanisms say what the probability of a gap is allowed to depend on:

Rubin’s 1976 result is that, under MAR (and a technical condition that the parameters of the missingness process are separate from those of the data), likelihood-based and Bayesian analyses can ignore the missingness process. In practice the result helps only if the analysis actually uses the recorded variables that explain the gaps. At Lantern, the ratings are plausibly MAR given subscription status; an analysis that ignores subscription status is back to the biased 4.38.

What you can check is limited but useful. Compare the rows with a missing value against the rows without on variables you do have: the share of subscribers, the order total, the region, the month. Clear differences rule out MCAR and point to the variables an analysis must include. Similar profiles are consistent with MCAR but don’t prove it, because the gaps may depend on something unrecorded.

Deletion

Listwise deletion, also called complete-case analysis, drops every row with a missing value in any variable the analysis uses. For a single column it is what a spreadsheet’s AVERAGE and SQL’s AVG do automatically: they skip blanks and NULLs. Under MCAR the remaining rows are a random sample of all rows, so means, variances and regression coefficients stay unbiased; the price is precision, because fewer rows are left. Under MAR and MNAR it can be biased, as Lantern’s 4.38 shows. It also wastes good data: if each of five variables is missing in a different 10% of rows, up to half the rows disappear.

Pairwise deletion, or available-case analysis, uses every row that has the values a particular statistic needs. The correlation between rating and order total uses the rows that have both; the correlation between order total and quantity uses a different, larger set. More data survive, but each statistic rests on different rows, so the results can contradict one another: a correlation matrix built this way may be one that no single dataset could produce, which breaks methods such as regression that need a consistent matrix. Like listwise deletion, it is biased when the data are not MCAR.

Single imputation

Imputation means filling each missing value with a plausible substitute so that every row can be used. The common single-imputation methods are:

• Mean or median of the observed values in the column (mean imputation; use the median when the column is skewed).
• Mode, the most frequent value, for categories such as region or product.
• Group-wise mean, median or mode: the statistic computed within groups defined by a recorded variable, such as subscribers and one-off buyers. This is the simplest way to act on an MAR assumption.
• A constant or a new category: 0 where a blank really means zero, or an explicit “Unknown” level for a missing region. For categories, “Unknown” is often the most honest fill because it doesn’t pretend to know.
• A prediction from other columns, by regression or from the most similar complete rows (nearest neighbours).

All of them share one flaw: the filled values are then treated as if they had been observed. That has three costs.

Less spread. A value imputed at the mean sits exactly on the mean, so it adds nothing to the sum of squared deviations while adding one to the count. With $n$ observed values whose sample standard deviation is $s$, filling $m$ gaps with the mean leaves the mean unchanged and shrinks the standard deviation to:

Take ten Northfield University students, six of whom recorded their weekly study hours: 4, 8, 10, 12, 14 and 18. Their mean is 11 and their sample standard deviation is $\sqrt{118 \div 5} = 4.86$ hours. Fill the four gaps with 11 and the mean is still 11, but the standard deviation becomes $\sqrt{118 \div 9} = 3.62$, a quarter smaller, although nothing about the students has changed. Every later calculation that uses the spread (a confidence interval, a standard error, a z-score) becomes overconfident.

Weaker relationships. The same ten students all have a recorded GPA. In the six complete rows, hours and GPA move closely together; after mean imputation the relationship looks much weaker.

Correlation between hours and GPA: 0.92 in the six complete rows; 0.64 after mean imputation.

The reason is visible in the correlation formula. Its numerator adds up (hours − mean hours) × (GPA − mean GPA) across rows, and an imputed row contributes nothing because its hours sit exactly at the mean: the sum is 9.2 with or without the four filled rows. Its denominator, though, includes the spread of GPA, and the four extra students add to that spread (the sum of squared GPA deviations rises from 0.84 to 1.76). Same numerator, bigger denominator: $9.2 \div \sqrt{118 \times 0.84} = 0.92$ becomes $9.2 \div \sqrt{118 \times 1.764} = 0.64$. Any model fitted to mean-imputed data will understate how strongly study hours relate to grades.

Bias. Mean imputation leaves the mean exactly where the observed values put it, so under MAR and MNAR it carries the same bias as deletion: Lantern’s 4.38 does not move when you fill the blanks with 4.38. Group-wise imputation removes the bias under MAR when the groups capture what drives the missingness (fill subscribers’ blanks with 4.6 and one-off buyers’ with 3.4 and the mean becomes 4.12), but no fill rule computed from the observed data removes bias under MNAR. Regression imputation has the opposite problem to mean imputation: every filled value lies exactly on the fitted line, so relationships look stronger than they are.

The missing-value indicator

A missing-value indicator is a new 0/1 column, such as rating_missing, that records where a value was missing before you filled it. Keep one whenever you impute. It preserves the audit trail, so the filled rows can be excluded or checked later, and it lets a predictive model use the fact of missingness itself: at Lantern, a customer who never rates may be one who is drifting away. The scikit-learn documentation puts it plainly: “When using imputation, preserving the information about which values had been missing can be informative.”

The indicator is a tool for prediction and record-keeping, not a cure for bias. When the goal is to estimate the effect of one variable on another, the old trick of filling gaps with a constant and adding the indicator to a regression can be badly biased even when the data are MCAR (van Buuren, section 1.3.7).

Model-based and multiple imputation

Better methods use the other columns to predict each missing value and then put the uncertainty back. Multiple imputation, developed by Donald Rubin in the 1970s and set out in his 1987 book, works in three stages. It creates $m$ completed copies of the dataset, each filling the gaps with different plausible values drawn from a model of the data; it runs the analysis separately on every copy; and it pools the results with what are now called Rubin’s rules. The pooled estimate is the average across copies, and its variance combines the average within-copy variance with the between-copy variance, inflated by $(1 + 1/m)$ to allow for using a finite number of copies.

The disagreement between copies is the honest measure of how much the missing values could matter, which is exactly what single imputation throws away. Multiple imputation gives valid results under MAR when the imputation model includes the variables that drive the missingness. Under MNAR it needs extra assumptions, usually explored with what-if analyses such as “suppose non-raters are half a star less satisfied than raters”. In software it is available in R’s mice package; scikit-learn’s IterativeImputer (still labelled experimental) produces one completed copy per run, and repeated runs with sample_posterior=True and different random seeds give multiple imputations.

Imputing before modelling: training data only

When the cleaned data feed a predictive model that will be tested on held-out rows, compute every fill value (the mean, the median, the group modes, the parameters of an imputation model) from the training rows only, then apply those same values to the test rows. If the test rows help decide the fill values, information from the test data leaks into training and the model’s test score becomes optimistic. scikit-learn’s guide to common pitfalls lists SimpleImputer among the transformations this applies to, and gives the general rule: never call fit on the test data. The scaling lesson in module 4 treats leakage in full.

In a spreadsheet, SQL or pandas

• Spreadsheet: AVERAGE skips empty cells but includes zeros, so a 0 placeholder silently drags an average down while a blank does not. COUNTBLANK counts the gaps in a range.
• SQL: aggregate functions such as AVG ignore NULL. COUNT(rating) counts non-missing values and COUNT(*) counts rows, so their difference is the number missing. COALESCE(children, 0) replaces NULL with 0, which is right only where a blank really means zero.
• pandas: pd.read_csv(path, na_values=['unknown', -999]) turns placeholders into missing values as the file loads (empty cells, NaN, N/A, NULL and other common markers are recognised by default); df.rating.isna().mean() gives the share missing; df.groupby('subscription').rating.transform('mean') produces group-wise fill values.