Imputing Missing Values: Mean, Median, Model or Nothing?

For reporting and analysis, the best way to handle most missing values is to leave them missing and report how many there are. Fill a value only when the next step can't work with gaps, such as a model that rejects nulls. When you do fill, use the median for skewed numbers, a group median when the value depends on a category, and a model-based imputer only when other columns genuinely predict the missing one. Always keep a column that records which values were filled. The mean is the weakest default: it shrinks the spread of the data, and on skewed columns it isn't a typical value at all.

Key Takeaways

• "Nothing" is a real option. SQL aggregates already skip nulls, so leaving gaps alone gives honest averages as long as you report the count of missing rows.
• Mean and median fills both shrink the spread of a column. In the example below, filling three of ten values cut the standard deviation from 167.36 to 136.65 (mean fill) or 138.54 (median fill).
• A group median (per category, region or carrier) is usually more accurate than one overall value and is easy to write in SQL or pandas.
• Model-based imputation (KNN, iterative) can help predictive models but makes values look more certain than they are. It is rarely worth it for reporting.
• Keep a _was_missing indicator and learn fill values from training rows only when the data feeds a model.

First, ask why the value is missing

Imputation means filling a missing value with an estimate, and the right method depends on why the value is absent, so start there. Statisticians use three categories, introduced by Donald Rubin in 1976 and explained in FIMD section 1.2. They translate into plain questions:

• Missing completely at random: the gap has nothing to do with the data. A form field failed to save for a random 2% of submissions.
• Missing at random (given other columns): the gap depends on something you have recorded. Older app versions didn't send delivery_days, and you know the app version.
• Missing not at random: the gap depends on the missing value itself. High earners skip the income question.

Stef van Buuren's Flexible Imputation of Missing Data notes that simply dropping incomplete rows "produces unbiased estimates of means, variances and regression weights" only when data is missing completely at random (van Buuren, FIMD section 1.3.1). In the other two cases, any shortcut can bias results, and no fill method can recover information that was never collected.

Two practical checks come before any of this. Make sure the gap really is missing and not a placeholder (N/A, -1, 999) or a blank string, which the guide on null, blank and zero covers. And record the reason when you know it, as the lesson on keeping the reason a value is missing shows.

The example data

Ten orders with an order_value, three of them missing. Garden orders are larger than book orders, and one garden order of 480 makes the column skewed.

CREATE TABLE orders AS SELECT * FROM (VALUES
 (1, 'books', 12.0), (2, 'books', 15.0), (3, 'books', 18.0), (4, 'books', NULL),
 (5, 'garden', 60.0), (6, 'garden', 75.0), (7, 'garden', 90.0), (8, 'garden', NULL),
 (9, 'garden', 480.0), (10, 'books', NULL)
) AS t(order_id, category, order_value);

SELECT count(*) AS rows_total, count(order_value) AS rows_with_value,
       round(avg(order_value), 2) AS mean_value, median(order_value) AS median_value,
       round(stddev_samp(order_value), 2) AS sd_value
FROM orders;

┌────────────┬─────────────────┬────────────┬──────────────┬──────────┐
│ rows_total │ rows_with_value │ mean_value │ median_value │ sd_value │
├────────────┼─────────────────┼────────────┼──────────────┼──────────┤
│         10 │               7 │     107.14 │         60.0 │   167.36 │
└────────────┴─────────────────┴────────────┴──────────────┴──────────┘

The mean (107.14) is almost twice the median (60.0). That gap is the first warning that a mean fill would insert a value that sits between the two categories and matches neither.

Option 1: Leave it missing

Leaving the value missing is the right choice for most reporting. avg, sum, median and stddev_samp in SQL skip nulls, and so do pandas' mean(), sum() and median() by default. The average of the seven known orders is an honest statement about orders whose value you know.

What makes "nothing" work is disclosure. count(*) against count(order_value) shows 10 rows and 7 values, so the report can say "average of 7 of 10 orders". The frequency table including missing exercise practises keeping missing values visible instead of letting them vanish.

Leave values missing when:

• The output is a report, summary or dashboard.
• The share of missing values is small and you can state it.
• A filled value would be mistaken for a measurement, for example a delivery time or a price.

Options 2 and 3: Mean or median fill

Sometimes a downstream step can't accept gaps, for example a model that rejects nulls. The simplest fix is one value for the whole column. Here is what each choice does to the data:

WITH s AS (SELECT avg(order_value) AS m, median(order_value) AS med FROM orders)
SELECT 'leave missing' AS method, round(avg(order_value), 2) AS mean_after,
       round(stddev_samp(order_value), 2) AS sd_after FROM orders
UNION ALL
SELECT 'mean fill', round(avg(coalesce(order_value, m)), 2),
       round(stddev_samp(coalesce(order_value, m)), 2) FROM orders, s
UNION ALL
SELECT 'median fill', round(avg(coalesce(order_value, med)), 2),
       round(stddev_samp(coalesce(order_value, med)), 2) FROM orders, s;

┌───────────────┬────────────┬──────────┐
│    method     │ mean_after │ sd_after │
├───────────────┼────────────┼──────────┤
│ leave missing │     107.14 │   167.36 │
│ mean fill     │     107.14 │   136.65 │
│ median fill   │       93.0 │   138.54 │
└───────────────┴────────────┴──────────┘

The mean fill keeps the average unchanged by construction, which can look reassuring, but it inserts 107.14 three times: about seven times the typical book order, and above three of the four known garden orders. It also cuts the standard deviation by about 18%, because three rows now sit exactly at the centre. Van Buuren calls mean imputation "a fast and simple fix" and warns that it underestimates variance and weakens relationships between variables, so it should be limited to cases with very few missing values (FIMD section 1.3.3).

The median fill inserts 60.0, a real, typical value on a skewed column, and moves the average from 107.14 to 93.0. That shift is the honest cost of filling: any single value changes some statistic. The median is the better default for skewed measures like prices, durations and counts, but it shrinks the spread in the same way.

Option 4: Fill by group

A single fill value ignores what you already know about the row. A missing garden order is more likely to be near the other garden orders than near the book orders. A group median uses that, and in SQL it is one window function:

SELECT order_id, category, order_value,
       order_value IS NULL AS order_value_was_missing,
       coalesce(order_value,
                median(order_value) OVER (PARTITION BY category)) AS order_value_filled
FROM orders
ORDER BY order_id;

┌──────────┬──────────┬─────────────┬─────────────────────────┬────────────────────┐
│ order_id │ category │ order_value │ order_value_was_missing │ order_value_filled │
├──────────┼──────────┼─────────────┼─────────────────────────┼────────────────────┤
│        1 │ books    │        12.0 │ false                   │               12.0 │
│        2 │ books    │        15.0 │ false                   │               15.0 │
│        3 │ books    │        18.0 │ false                   │               18.0 │
│        4 │ books    │        NULL │ true                    │               15.0 │
│        5 │ garden   │        60.0 │ false                   │               60.0 │
│        6 │ garden   │        75.0 │ false                   │               75.0 │
│        7 │ garden   │        90.0 │ false                   │               90.0 │
│        8 │ garden   │        NULL │ true                    │               82.5 │
│        9 │ garden   │       480.0 │ false                   │              480.0 │
│       10 │ books    │        NULL │ true                    │               15.0 │
└──────────┴──────────┴─────────────┴─────────────────────────┴────────────────────┘

Book orders get 15.0 and the garden order gets 82.5, both plausible for their category. The overall median would have given every one of them 60.0. In pandas, groupby(...).transform("median") returns a value per row that lines up with the original frame:

import pandas as pd

df = pd.DataFrame({
    "order_id": range(1, 11),
    "category": ["books"] * 4 + ["garden"] * 5 + ["books"],
    "order_value": [12.0, 15.0, 18.0, None, 60.0, 75.0, 90.0, None, 480.0, None],
})

df["order_value_was_missing"] = df["order_value"].isna()
group_median = df.groupby("category")["order_value"].transform("median")
df["order_value_filled"] = df["order_value"].fillna(group_median)
print(df[df["order_value_was_missing"]])

   order_id category  order_value  order_value_was_missing  order_value_filled
3         4    books          NaN                     True                15.0
7         8   garden          NaN                     True                82.5
9        10    books          NaN                     True                15.0

The filled rows match the SQL result, with NaN in place of NULL. Two cautions: a group with no known values gets no median at all, so decide on a fallback (the overall median, or leave it missing), and very small groups produce unstable medians. The median delivery days by carrier exercise practises the grouped median itself.

Option 5: Model-based imputation

Model-based imputers predict each missing value from the other columns. scikit-learn ships two: KNNImputer, which averages the values of the most similar rows, and IterativeImputer, which "models each feature with missing values as a function of other features". The scikit-learn documentation still labels IterativeImputer experimental: its "default parameters or details of behaviour might change without any deprecation cycle" (scikit-learn, Imputation of missing values).

These methods can help a predictive model when other columns really do predict the gap. They come with costs:

• False precision. A predicted value looks like a measurement. Van Buuren puts it bluntly: "Regression imputation is a recipe for false positive and spurious relations", because predicted values sit exactly on the fitted line and overstate correlations (FIMD section 1.3.4).
• Hard to explain. "Filled with the median of garden orders" fits in a data dictionary. "Filled by a 5-nearest-neighbour model on six columns" needs a document and a version.
• Leakage risk. The imputer is a model. It must be fitted on training rows only, or it carries information from the test set into training.

For statistical inference, where you need honest standard errors, the established approach is multiple imputation, which creates several filled datasets and combines the results. That is beyond a cleaning step and belongs with the analysis.

Always keep an indicator column

Whatever you fill with, add a boolean column that says which values were filled. Every example above does this with order_value IS NULL in SQL or .isna() in pandas. scikit-learn's documentation makes the same point: "preserving the information about which values had been missing can be informative", and its imputers accept add_indicator=True for this reason.

The indicator protects reports (you can exclude filled values from a quality metric) and helps models (missingness can itself predict the outcome). One caution from van Buuren: adding an indicator doesn't fix the bias of a regression estimate, and he concludes that the indicator method "fails as a generic method to handle missing data" for inference (FIMD section 1.3.7). Treat it as a record of what you did, not as a cure. The impute with an indicator column exercise drills the pattern.

Labels and measurements are different

Categorical labels and numeric measurements deserve different treatment. Filling a missing category with an explicit Unknown label is usually safe: it is honest, it groups cleanly and nobody mistakes it for a real category. Filling a missing measurement with a number creates a value that looks real. The lesson fill labels, not measurements and the exercise label unknown categories, keep missing numbers missing cover this split.

Which method should you use?

If the data feeds a model, compute every fill value from training rows and reuse it on test and production rows, as explained in data cleaning for machine learning. The fill gaps without leakage exercise shows the pattern, and the imputation glossary entry defines the terms.

Frequently Asked Questions

Is it better to impute with the mean or the median?

The median, in most cleaning work. Real-world measures like prices, durations and counts are usually skewed, and the median is a typical value while the mean can be pulled far from any real row. Both shrink the spread of the column, so keep an indicator either way.

When should you not impute missing values?

When the output is a report or summary, when a filled value could be mistaken for a measurement, and when the value is missing because of what it would have been (for example, people with high incomes skipping an income question). Leave the value missing and report how many rows are affected.

Does SQL ignore null values in averages?

Yes. avg, sum, min, max and median skip nulls, and count(column) counts only non-null values while count(*) counts every row. Comparing the two counts shows how many values the average is based on.

What percentage of missing data is acceptable?

There is no universal threshold. What matters is why the data is missing and how the result will be used. A small share missing completely at random barely matters; a small share missing for a systematic reason can bias every result. Report the share and the likely reason rather than relying on a cut-off.

Related reading

• pandas fillna Without Regret: When Filling Missing Values Is Wrong
• How to Detect Outliers: IQR, Z-Scores and Domain Rules Compared

Sources

• Stef van Buuren, Flexible Imputation of Missing Data, 2nd ed., section 1.2: Concepts of MCAR, MAR and MNAR, retrieved 2026-11-05.
• Stef van Buuren, Flexible Imputation of Missing Data, 2nd ed., section 1.3: Ad-hoc solutions, retrieved 2026-11-05.
• scikit-learn developers, Imputation of missing values, retrieved 2026-11-05.