Missing data: find it, understand it, decide

A value is missing when a cell holds no usable answer: nobody measured it, a device failed, or the question did not apply. pandas marks such cells as NaN, None, NaT or pd.NA, and isna() finds them. Files also disguise missing values as ordinary data, such as an empty string, a dash, the word “nil” or an impossible 0, and pandas counts those as real values until you say otherwise.

Handling missing data takes three steps, in this order. Find every gap, the disguised ones included. Understand it: count the gaps, see where they cluster and ask why they are there, because the reason decides what is safe. Then decide, column by column, whether to drop, fill, impute or flag, and write the rule and the number of rows it touched in your data log.

What pandas counts as missing

import numpy as np
import pandas as pd

# Eight ways a cell can say "no value", as they arrive from code and from files.
markers = [None, np.nan, pd.NaT, pd.NA, "", "-", "NA", 0]
found = pd.Series(markers, dtype=object).isna()

print(f"{'cell':<6} isna()")
for marker, missing in zip(markers, found):
    print(f"{marker!r:<6} {missing}")
print("Counted as missing:", int(found.sum()), "of", len(markers))

cell   isna()
None   True
nan    True
NaT    True
<NA>   True
''     False
'-'    False
'NA'   False
0      False
Counted as missing: 4 of 8

Only four markers count: None, NaN (NumPy’s “not a number” float), NaT (“not a time”, for dates) and pd.NA (the marker of pandas’ nullable types). An empty string, a dash, the text 'NA' and the number 0 are values like any other. pandas cannot know that a dash in a weight column means “not weighed”; you have to tell it.

read_csv helps with the common spellings. By default it reads an empty field and 18 other spellings, among them NA, N/A, NULL, null, NaN, nan, None and #N/A, as NaN. Anything outside that list, such as -, nil, ? or a lone space, arrives as text, and one such cell is enough to keep a whole column of numbers as text.

Finding the sentinels a file uses

A sentinel is an ordinary-looking value that stands for “no value”. This invented export of clinic visits uses five of them in its weight column: a blank, NA, -, nil and 0.

import pandas as pd

# 1. A default read: only the blank and "NA" become missing, so the column stays text.
raw = pd.read_csv("clinic_visits.csv")
weights = raw["weight_kg"]
print("Default read:", weights.dtype, "| missing:", int(weights.isna().sum()))

# The values that do not parse as numbers are the sentinels to look at.
not_numbers = weights[pd.to_numeric(weights, errors="coerce").isna()]
print(not_numbers.value_counts(dropna=False).to_string())

# 2. Name the sentinels at read time: the column becomes numeric.
visits = pd.read_csv("clinic_visits.csv", na_values={"weight_kg": ["-", "nil"]})
print("With na_values:", visits["weight_kg"].dtype, "| missing:", int(visits["weight_kg"].isna().sum()))

# 3. A weight of 0 kg is impossible: treat it as missing too.
zero = visits["weight_kg"] == 0
visits["weight_kg"] = visits["weight_kg"].mask(zero)
print("Zeros made missing:", int(zero.sum()), "| missing:", int(visits["weight_kg"].isna().sum()), "of", len(visits))

# 4. Where are the gaps? Count and share of missing weights per clinic.
gaps = visits["weight_kg"].isna().groupby(visits["clinic"]).agg(missing="sum", share="mean")
print(gaps.round(2).to_string())

Default read: str | missing: 2
weight_kg
-      3
NaN    2
nil    1
With na_values: float64 | missing: 6
Zeros made missing: 2 | missing: 8 of 16
        missing  share
clinic                
Indore        5   0.83
Kochi         1   0.20
Mysuru        2   0.40

visit_id,clinic,age,weight_kg
1,Kochi,34,61.5
2,Kochi,51,
3,Kochi,29,58.0
4,Indore,62,NA
5,Indore,45,74.2
6,Indore,38,-
7,Indore,57,-
8,Indore,41,nil
9,Mysuru,23,55.4
10,Mysuru,66,0
11,Mysuru,48,69.9
12,Mysuru,35,63.1
13,Kochi,44,70.3
14,Indore,52,-
15,Mysuru,31,0
16,Kochi,60,66.8

The default read finds 2 gaps, the blank and NA, and leaves weight_kg as text (str), because - and nil are not numbers. Keeping the cells that pd.to_numeric(..., errors="coerce") cannot convert is a quick way to list the sentinels a column really uses: three dashes and one nil, beside the two values already missing. Naming them in na_values (a dict limits them to one column) turns the column into float64 with 6 gaps.

The two zeros are a different kind of problem. They are numbers, but nobody weighs 0 kg, so a rule turns them into gaps as well, and the column ends with 8 gaps in 16 rows. Keep such rules for values that cannot be true; a 0 in a sales column can be a real, quiet day.

The default list can also be wrong for your data. In a survey, “N/A” may mean “does not apply”, which is an answer worth counting. keep_default_na=False switches the list off, and na_values then names exactly what counts as missing.

Count the gaps and look for a pattern

df.isna().sum() counts the gaps in every column, and df.isna().mean() gives their share. The last table of the example above goes one step further and splits the gaps by clinic: 5 of the 6 Indore visits have no weight, against 1 of 5 in Kochi. Gaps that cluster like this are information. Here they point at one clinic’s scale or its staff, and any average weight “of all visits” now mostly describes the other two clinics.

Why the values are missing decides what you can safely do with the rest. Statisticians, and the European Medicines Agency in its guideline on missing data, sort the reasons into three mechanisms:

• Missing completely at random: the chance of a gap has nothing to do with the data, as when a page of a paper form is lost.
• Missing at random: the chance of a gap depends only on values you did record, such as the clinic or the region.
• Missing not at random: the chance of a gap depends on the missing value itself, as when high earners skip the income question.

The data alone cannot prove which mechanism you have, as the guideline itself warns. A simulation in which the true values are known shows what each one does to a simple mean:

import numpy as np
import pandas as pd

# 10,000 invented monthly incomes: the north earns more than the south.
rng = np.random.default_rng(7)
n = 10_000
region = rng.choice(["north", "south"], size=n)
income = np.where(region == "north", rng.lognormal(10.3, 0.5, n), rng.lognormal(10.0, 0.5, n)).round(-2)
people = pd.DataFrame({"region": region, "income": income})
true_mean = people["income"].mean()

# Three ways to lose about 30 % of the answers.
blank = {
    "completely at random": rng.random(n) < 0.3,
    "at random given region": rng.random(n) < np.where(people["region"] == "north", 0.5, 0.1),
    "not at random": rng.random(n) < 0.6 * people["income"].rank(pct=True),
}

print(f"True mean: {true_mean:,.0f}")
print(f"{'missing':<23} {'share':>6} {'mean of answers':>16} {'error':>7} {'region-weighted':>16}")
for name, gone in blank.items():
    answered = people[~gone]
    mean = answered["income"].mean()
    # Re-weight: average each region's answers, then weight by the region's true size.
    weighted = (answered.groupby("region")["income"].mean() * people["region"].value_counts(normalize=True)).sum()
    print(f"{name:<23} {gone.mean():>6.1%} {mean:>16,.0f} {mean / true_mean - 1:>+7.1%} {weighted:>16,.0f}")

True mean: 29,196
missing                  share  mean of answers   error  region-weighted
completely at random     29.1%           29,021   -0.6%           29,007
at random given region   30.2%           28,031   -4.0%           29,265
not at random            29.8%           25,709  -11.9%           25,958

With about 30 % of the answers lost completely at random, their mean is within 0.6 % of the truth: dropping the gaps costs precision, not accuracy. When the north, the richer region, leaves the question blank five times as often as the south, the mean of the answers falls 4.0 % short, because the answers over-represent the south. That bias can be repaired with what was recorded: average each region’s answers and weight the regions by their true sizes, and the estimate is back at 29,265 against a true 29,196.

When the highest earners are the most likely to skip the question, the answers understate the mean by 11.9 %, and the region weighting does not help (25,958), because the reason for each gap is the very value that is missing. No method recovers it from these columns. The honest report gives the number with that limitation, or the survey collects better data.

In practice, compare the share of gaps across every group you recorded, with df["col"].isna().groupby(df["group"]).mean(), and across time. If the share differs a lot between groups, the gaps are not completely at random, and any figure over “the rows we have” needs either a correction or a caveat.

Statistics with gaps

pandas skips missing values in its statistics by default (skipna=True). The mean of 1, a gap and 3 is 2.0, not 1.33, and count() returns 2 where len() returns 3. polars and DuckDB skip gaps too, and the three agree until a group has no values at all:

import duckdb
import pandas as pd
import polars as pl

print(f"pandas {pd.__version__} · polars {pl.__version__} · DuckDB {duckdb.__version__}")
values = [1.0, None, 3.0]   # one gap
nothing = [None, None]      # a group with no values at all

s, e = pd.Series(values, dtype="float64"), pd.Series(nothing, dtype="float64")
print(f"pandas  mean {s.mean()} | count {s.count()} of {len(s)} | sum of nothing {e.sum()} (min_count=1: {e.sum(min_count=1)})")

ps, pe = pl.Series(values), pl.Series(nothing, dtype=pl.Float64)
print(f"polars  mean {ps.mean()} | count {ps.count()} of {ps.len()} | sum of nothing {pe.sum()}")

con = duckdb.connect()
mean, count, rows = con.sql("SELECT avg(x), count(x), count(*) FROM (VALUES (1.0::DOUBLE), (NULL), (3.0)) t(x)").fetchone()
(total,) = con.sql("SELECT sum(x) FROM (VALUES (NULL::DOUBLE), (NULL)) t(x)").fetchone()
print(f"DuckDB  mean {mean} | count {count} of {rows} | sum of nothing {total}")

pandas 3.0.6 · polars 1.44.2 · DuckDB 1.5.6
pandas  mean 2.0 | count 2 of 3 | sum of nothing 0.0 (min_count=1: nan)
polars  mean 2.0 | count 2 of 3 | sum of nothing 0.0
DuckDB  mean 2.0 | count 2 of 3 | sum of nothing None

pandas and polars return 0 for the sum of nothing, as the documentation of each says. DuckDB returns NULL, Python’s None here: its aggregates skip NULLs, and all of them except count return NULL when nothing is left. A report built in pandas therefore shows 0 for a branch with no recorded sales, while the same report from DuckDB leaves the cell empty. Pass min_count=1 when an empty sum should stay missing in pandas.

Group-by has a quieter trap. A row whose group key is missing is left out of every group:

import pandas as pd

orders = pd.DataFrame({
    "city": ["Pune", "Pune", None, "Delhi", None],
    "amount": [250, 400, 900, 300, 150],
})
print("All orders:", orders["amount"].sum())

by_city = orders.groupby("city")["amount"].sum()
print(by_city.to_string())
print("Sum of the groups:", by_city.sum())

# dropna=False keeps the rows whose key is missing as a group of their own.
print(orders.groupby("city", dropna=False)["amount"].sum().to_string())

All orders: 2000
city
Delhi    300
Pune     650
Sum of the groups: 950
city
Delhi     300
Pune      650
NaN      1050

The orders total 2,000, but the groups add up to 950: the two orders with no city, 1,050 between them, are gone without a warning. dropna=False keeps them as a group of their own, labelled NaN, and the totals reconcile again. After a group-by on a key that can be missing, check that the groups add up to the whole.

One more rule: missing is not zero. A shop that sold nothing on a holiday took 0; a shop whose till failed took an unknown amount. fillna(0) is right for the first and wrong for the second, because it turns “unknown” into a fact that drags every average down.

Decide: drop, fill, impute or flag

Every option changes the data you analyse, so choose per column, for a reason you can state:

• Drop the row, dropna(subset=["col"]), when the row is useless without the value and the gaps are few and look random. It costs rows, and a bias whenever the gaps are not random.
• Fill a known value, fillna(0) or fillna("none"), when missing has a known meaning, such as no sales on a closed day. It gives a wrong number when missing really meant unknown.
• Carry forward, groupby("id")["col"].ffill(limit=2), for readings of one thing in time order, where the last value still holds. It brings stale values, and leaks between groups without the groupby.
• Impute, for example with the group median, when the next step needs a value in every row, as a model does. It narrows the spread and adds certainty that is not there.
• Flag, df["col_missing"] = df["col"].isna(), always, before any fill. It costs one extra column.

What imputation does to a distribution

Mean and median imputation are the usual first choices. On a right-skewed column, such as incomes, both leave marks:

import numpy as np
import pandas as pd

# 2,000 invented, right-skewed incomes; then 20 % of them blanked at random.
rng = np.random.default_rng(42)
income = pd.Series(rng.lognormal(mean=10, sigma=0.6, size=2_000).round(-2))
gaps = income.mask(rng.random(len(income)) < 0.2)
print("Missing:", int(gaps.isna().sum()), "of", len(gaps))

versions = {
    "true values": income,
    "mean fill": gaps.fillna(gaps.mean()),
    "median fill": gaps.fillna(gaps.median()),
}
stats = pd.DataFrame({name: {"mean": s.mean(), "median": s.median(), "std": s.std(),
                             "rows on one value": s.value_counts().iloc[0]} for name, s in versions.items()})
print(stats.round(0).astype(int).to_string())

# How many values fall in each income band: the filled values pile up in one band.
edges = [0, 15_000, 20_000, 25_000, 30_000, 40_000, np.inf]
labels = ["under 15k", "15k-20k", "20k-25k", "25k-30k", "30k-40k", "40k and up"]
counts = pd.DataFrame({name: pd.cut(s, edges, labels=labels).value_counts(sort=False) for name, s in versions.items()})
print(counts.to_string())

Missing: 415 of 2000
                   true values  mean fill  median fill
mean                     25485      25414        24622
median                   21700      25414        21600
std                      16532      14938        15018
rows on one value           13        415          418
            true values  mean fill  median fill
under 15k           549        441          441
15k-20k             353        280          280
20k-25k             286        230          645
25k-30k             265        616          201
30k-40k             264        209          209
40k and up          283        224          224

Filling with the mean keeps the mean of the answers (25,414), but moves the median from 21,700 to 25,414 and puts 415 rows on one value, so the 25k-30k band more than doubles, from 265 to 616. Filling with the median keeps the median (21,600 against a true 21,700) but drags the mean down to 24,622. Both shrink the standard deviation by 9 to 10 %, from 16,532 to about 15,000, which makes the data look more certain than it is: an interval or a test computed afterwards comes out too narrow.

For a mean or a total, leaving the gaps and reporting their count is often better than filling them. Fill when a later step needs a value in every row, and keep a flag so that the filled rows can always be told apart.

Filling by group, with a flag and a limit

A group’s median is usually closer to the truth than the median of the whole column, but only if the group has enough measured values. The clinic data shows why:

import pandas as pd

visits = pd.read_csv("clinic_visits.csv", na_values={"weight_kg": ["-", "nil"]})
visits["weight_kg"] = visits["weight_kg"].mask(visits["weight_kg"] == 0)

# How much is each clinic's median worth? It rests on the weights actually measured there.
per_clinic = visits.groupby("clinic")["weight_kg"].agg(measured="count", median="median")
print(per_clinic.to_string())

# Flag first, fill second, and only from a clinic with at least 3 measured weights.
visits["weight_missing"] = visits["weight_kg"].isna()
grouped = visits.groupby("clinic")["weight_kg"]
enough = grouped.transform("count") >= 3
visits["weight_filled"] = visits["weight_kg"].fillna(grouped.transform("median").where(enough))
print(visits[visits["weight_missing"]].to_string(index=False))

# Lines for the data log: what was done, by which rule, to how many rows.
filled = visits["weight_missing"] & visits["weight_filled"].notna()
log = pd.DataFrame([
    {"column": "weight_kg", "rule": "blank, NA, -, nil and 0 read as missing", "rows": int(visits["weight_missing"].sum())},
    {"column": "weight_filled", "rule": "clinic median, clinics with 3+ weights", "rows": int(filled.sum())},
    {"column": "weight_filled", "rule": "left missing (too few weights)", "rows": int(visits["weight_filled"].isna().sum())},
])
print(log.to_string(index=False))

        measured  median
clinic                  
Indore         1   74.20
Kochi          4   64.15
Mysuru         3   63.10
 visit_id clinic  age  weight_kg  weight_missing  weight_filled
        2  Kochi   51        NaN            True          64.15
        4 Indore   62        NaN            True            NaN
        6 Indore   38        NaN            True            NaN
        7 Indore   57        NaN            True            NaN
        8 Indore   41        NaN            True            NaN
       10 Mysuru   66        NaN            True          63.10
       14 Indore   52        NaN            True            NaN
       15 Mysuru   31        NaN            True          63.10
       column                                    rule  rows
    weight_kg blank, NA, -, nil and 0 read as missing     8
weight_filled  clinic median, clinics with 3+ weights     3
weight_filled          left missing (too few weights)     5

visit_id,clinic,age,weight_kg
1,Kochi,34,61.5
2,Kochi,51,
3,Kochi,29,58.0
4,Indore,62,NA
5,Indore,45,74.2
6,Indore,38,-
7,Indore,57,-
8,Indore,41,nil
9,Mysuru,23,55.4
10,Mysuru,66,0
11,Mysuru,48,69.9
12,Mysuru,35,63.1
13,Kochi,44,70.3
14,Indore,52,-
15,Mysuru,31,0
16,Kochi,60,66.8

Indore’s median rests on a single weight, 74.2 kg. Filling its five gaps from it would write five copies of one patient into the data, so the rule fills only from clinics with at least three measured weights: Kochi’s gap gets 64.15 kg, Mysuru’s two get 63.1 kg, and Indore’s five stay missing, with their flag. transform("median") returns each row’s group median, lined up with the rows, which is what fillna needs; groupby(...).fillna() itself was removed in pandas 3.0.

Carrying values forward

For readings in time order, the last known value is often the best guess for the next gap, as long as it stays inside its own series:

import warnings

import pandas as pd

readings = pd.DataFrame({
    "sensor": ["A", "A", "A", "B", "B", "B"],
    "hour": [1, 2, 3, 1, 2, 3],
    "temp": [21.0, None, 23.0, None, 30.0, None],
})

# Carry the last reading forward: plain ffill() runs across sensors, a group-wise one does not.
readings["ffill"] = readings["temp"].ffill()
readings["ffill_by_sensor"] = readings.groupby("sensor")["temp"].ffill()
print(readings.to_string(index=False))

# Two pandas 3 traps.
try:
    readings["temp"].fillna(method="ffill")
except TypeError as e:
    print("TypeError:", e)

with warnings.catch_warnings(record=True) as caught:
    warnings.simplefilter("always")
    readings["temp"].fillna(0, inplace=True)  # chained: changes a copy, never readings
print("Warning:", caught[0].category.__name__, "| gaps left:", int(readings["temp"].isna().sum()))

readings["temp"] = readings["temp"].fillna(0)  # assign the result back instead
print("After assigning back, gaps left:", int(readings["temp"].isna().sum()))

sensor  hour  temp  ffill  ffill_by_sensor
     A     1  21.0   21.0             21.0
     A     2   NaN   21.0             21.0
     A     3  23.0   23.0             23.0
     B     1   NaN   23.0              NaN
     B     2  30.0   30.0             30.0
     B     3   NaN   30.0             30.0
TypeError: NDFrame.fillna() got an unexpected keyword argument 'method'
Warning: ChainedAssignmentError | gaps left: 3
After assigning back, gaps left: 0

Plain ffill() copies sensor A’s last reading, 23.0, into sensor B’s first hour, a value B never measured. readings.groupby("sensor")["temp"].ffill() starts again at each sensor and leaves that gap alone. Add limit= so that a value is never carried across a long outage.

The run also shows two pandas 3 changes. fillna(method="ffill") was removed and now raises TypeError: use ffill() and bfill(). And readings["temp"].fillna(0, inplace=True) changes a temporary copy of the column. Under Copy-on-Write, pandas warns with ChainedAssignmentError and the frame keeps its 3 gaps; assign the result back, as the last line does, or call readings.fillna({"temp": 0}, inplace=True) on the frame itself.

Report what you changed

A cleaning step that changes numbers is part of the result. For each column, keep the sentinels you converted, the rows you dropped, the rows you filled and by which rule, and the rows still missing, and put those counts next to the figures they affect. The data log lines that clinic_fill.py prints are the smallest useful version.

Real datasets make this matter. In the UCI Online Retail II data, 243,007 of the 1,067,371 rows (22.8 %) have no customer ID, which is also why that ID column loads as float64. Any per-customer figure, such as revenue per customer or the share of repeat buyers, therefore describes about three quarters of the rows, and the report has to say so. (Measured with pandas 3.0.6 on the full dataset.)

Exercise

MISSING = {"-", "na", "n/a", "nil", "null"}  # sentinel spellings, compared after strip() and casefold()


def to_weight(raw):
    """Return the weight in raw as a float, or None when raw means missing; raise ValueError for anything else."""
    # Replace these lines with your code.
    if raw is None:
        return None
    return float(raw)


def summary(raws):
    """Return {"rows", "missing", "share_missing", "mean"} for a list of raw cells."""
    # Replace these lines with your code.
    return {"rows": len(raws), "missing": 0, "share_missing": 0.0, "mean": None}

from weights import summary, to_weight


def test_blank_and_none():
    """treats None, an empty string and spaces as missing"""
    assert to_weight(None) is None
    assert to_weight("") is None
    assert to_weight("   ") is None


def test_sentinels():
    """treats -, NA, N/A, nil and null as missing, in any case and with spaces around them"""
    for raw in ["-", "NA", "na", "N/A", "n/a", "nil", "NIL", " nil ", "null", "Null"]:
        assert to_weight(raw) is None, raw


def test_impossible_weights():
    """treats 0 and negative weights as missing"""
    assert to_weight("0") is None
    assert to_weight("0.0") is None
    assert to_weight("-3") is None


def test_numbers():
    """returns real weights as floats, spaces and all"""
    assert to_weight("58") == 58.0
    assert isinstance(to_weight("58"), float)
    assert to_weight(" 61.5 ") == 61.5
    assert to_weight("2.9") == 2.9


def test_unreviewed_spelling():
    """raises ValueError for a spelling nobody has reviewed"""
    for raw in ["sixty", "?", "62 kg"]:
        try:
            to_weight(raw)
        except ValueError:
            continue
        raise AssertionError(f"expected ValueError for {raw!r}")


def test_summary():
    """counts the missing cells and averages the rest"""
    assert summary(["61.5", "", "-", "70.5"]) == {"rows": 4, "missing": 2, "share_missing": 0.5, "mean": 66.0}
    assert summary(["55.4", "0", "nil", "69.9", "63.1", "NA"]) == {"rows": 6, "missing": 3, "share_missing": 0.5, "mean": 62.8}
    assert summary(["70", "NA", "72"]) == {"rows": 3, "missing": 1, "share_missing": 0.333, "mean": 71.0}


def test_nothing_to_average():
    """returns a mean of None when no weight is present, and handles an empty list"""
    assert summary(["", "-", "0"]) == {"rows": 3, "missing": 3, "share_missing": 1.0, "mean": None}
    assert summary([]) == {"rows": 0, "missing": 0, "share_missing": 0.0, "mean": None}

Interview questions

Why can a pandas report and a SQL report disagree on totals when some values are missing?

Both skip missing values, so a mean over a column with a few gaps agrees: the mean of 1, a gap and 3 is 2.0 in pandas, polars and DuckDB. They part ways when a group has no values at all. pandas and polars return 0 for the sum of nothing, while DuckDB returns NULL. A pandas report then shows 0 sales for a branch with no records, where the SQL report shows an empty cell, and a later fillna(0) or COALESCE decides which one a reader sees. A second source of differences is group-by: pandas drops rows whose key is missing unless you pass dropna=False, while DuckDB’s GROUP BY keeps NULL keys as a group of their own. Pass min_count=1 to keep an empty sum missing in pandas, and compare row counts per group before comparing totals.

Key takeaways

• isna() sees only None, NaN, NaT and pd.NA. Dashes, nil, cells of spaces and impossible zeros are values until you convert them, with na_values at read time or a rule such as mask(col == 0).
• List a column’s sentinels by keeping what pd.to_numeric(..., errors="coerce") cannot convert.
• Count the gaps per group and over time. Gaps that cluster are not completely at random, and figures over the rows you have need a correction or a caveat.
• Statistics skip gaps; the sum of nothing is 0 in pandas and polars but NULL in DuckDB. Use min_count=1 when that matters, and groupby(..., dropna=False) when keys can be missing.
• Drop, fill, carry forward or impute per column, for a stated reason; flag before you fill; carry forward only within a group and with a limit.
• Mean and median fills pile values on one point and shrink the spread. Report how many values were filled.
• pandas 3: fillna(method=...) and groupby(...).fillna() are gone, and an in-place method on a selected column changes nothing. Assign results back.