Pandas GroupBy with Missing Data: Defend the Count, Total, and Denominator

Reviewed October 6, 2026. Examples were executed with Python 3.12.14 and pandas 2.2.3. Every amount is synthetic; no operational or survey dataset is represented.

A grouped report shows a total of zero for one team. Another team has disappeared altogether. The analyst concludes that the first team produced nothing and that every source row has been accounted for. Both conclusions may be wrong: an all-missing measurement can sum to zero under the default, and a missing group label can be excluded by grouping policy.

A reliable summary needs more than a sum. It should expose how many rows belong to each group, how many values were actually observed, whether its total is fully known, and what happened to rows whose group key was missing.

Practical rule: specify the group-key policy, carry size and observed-value count alongside totals, and treat missing measurements separately from genuine zeroes. Use min_count to express a minimum amount of evidence, but do not mistake min_count=1 for a completeness guarantee.

There are two different missing-data questions

A missing key affects where a row belongs. A missing amount affects what can be calculated for a group. These are independent conditions. Filling both with zero or dropping both under one generic cleaning rule changes the meaning of the report.

The pandas 2.2.3 groupby reference documents dropna=True as the default key policy. Passing dropna=False includes missing keys as a group. That is useful for reconciliation, although an “unknown team” group may still require follow-up rather than being treated as a real business category.

Consider four rows: A with 10, A with a missing amount, B with zero, and a missing team with 5. Group A has two rows but one observed amount. Group B has one real measurement of zero. The unlabeled row still contains a known amount.

A complete summary with an explicit unknown total

import pandas as pd

frame = pd.DataFrame({
    "team": ["A", "A", "B", None, "C", "C"],
    "amount": [10, None, 0, 5, None, None],
})
groups = frame.groupby("team", dropna=False, sort=False, observed=True)
summary = pd.DataFrame({
    "rows": groups.size(),
    "observed_amounts": groups["amount"].count(),
    "known_sum": groups["amount"].sum(min_count=1),
    "known_mean": groups["amount"].mean(),
})
summary["complete"] = summary["observed_amounts"] == summary["rows"]
assert int(summary["rows"].sum()) == len(frame) == 6
assert summary.loc["A", "rows"] == 2
assert summary.loc["A", "observed_amounts"] == 1
assert summary.loc["A", "known_sum"] == 10
assert summary.loc["A", "known_mean"] == 10
assert not summary.loc["A", "complete"]
assert summary.loc["B", "known_sum"] == 0
assert pd.isna(summary.loc["C", "known_sum"])
missing_key = summary[summary.index.isna()]
assert missing_key.iloc[0]["known_sum"] == 5
assert frame.groupby("team").size().sum() == 5
assert groups["amount"].sum().loc["C"] == 0
print(summary)

The default grouping excludes the missing-key row, so its group sizes sum to five rather than six. The explicit dropna=False version reconciles all six rows. That is a testable population difference, not a cosmetic change to the displayed table.

The amount column uses floating-point representation because it contains missing values under this constructed DataFrame. A nullable dtype can express another storage policy, but it does not remove the need to define key inclusion and measurement completeness.

Size and count answer different questions

Size counts rows in each group. Count counts nonmissing values in a selected column. Counting an entire DataFrame gives separate counts for its columns, which can differ because each column has its own missingness pattern.

Group A's observed mean is 10 divided by one observed amount. It is not 10 divided by two source rows. If you replace the missing value with zero, the mean becomes five, but that is a newly imposed missing-equals-zero assumption. It cannot be described as the same statistic computed more conveniently.

A useful report names these quantities clearly: rows, observed_amounts, known_sum, and known_mean communicate the population better than generic count and average. When the decision needs complete totals, add a completeness condition rather than relying on a known-value sum alone.

Min_count protects one boundary, not every boundary

The GroupBy sum reference defines min_count as the required number of valid values. With min_count=1, a group containing no observed amounts stays missing instead of receiving an apparently measured zero.

Group A still has a total of ten even though one of its two amounts is unknown. Min_count=1 therefore protects the all-missing case; it does not establish that every amount is present. Compare count with size to identify complete groups, then choose whether incomplete totals are withheld, marked partial, or reported as known subtotals.

import pandas as pd

frame = pd.DataFrame({"team": ["A", "A", "B"], "amount": [10, None, 0]})
groups = frame.groupby("team", dropna=False, observed=True)
rows = groups.size()
observed = groups["amount"].count()
known = groups["amount"].sum(min_count=1)
complete_total = known.where(observed == rows)
assert pd.isna(complete_total.loc["A"])
assert complete_total.loc["B"] == 0
assert known.loc["A"] == 10
print("Known subtotal:", known.to_dict())
print("Complete total:", complete_total.to_dict())

This second example keeps both measures. It does not discard the ten that is known, but it refuses to label it a complete total. An application may use a different policy; the requirement is that the report says which meaning its number has.

Categorical groups and version-sensitive defaults

A categorical column can declare categories that never occur in the input. Whether those unused categories appear in grouped output depends on observed and the other grouping choices. The pandas 2.2.3 documentation marks a future change to the observed default, so set it explicitly when the report depends on category presence.

An absent category is not necessarily a measured zero group. It can mean there was no source record for that category. If a dashboard needs every category displayed, join the aggregate onto an explicit category inventory and carry an observation count. Fill missing totals with zero only when the domain justifies that interpretation.

Sort controls grouped key order; it does not establish a business ranking. Use a separate explicit sort when the report needs descending known totals or a defined category order. With multiple keys, document how missing values in each component are handled and reconcile the result against the original row population.

Validate the input before grouping

GroupBy cannot repair a numeric column containing a mixture of measured amounts and arbitrary strings. Convert under a documented rule, preserve invalid-source evidence, and check the dtype before aggregating. If negative or infinite amounts violate the domain, reject or audit them before treating them as ordinary measurements.

Also define the grain. Multiple rows can represent multiple valid observations, accidental duplicates, or a missing key dimension. Summing them before resolving that distinction may produce a mathematically correct total for the wrong population. Grouping is downstream of identity and measurement validation.

Verification and reporting

Test a fully observed group, a partially observed group, an all-missing group, a genuine zero, a missing key, and an empty input. Reconcile grouped sizes with the retained source population. If rows are deliberately excluded, reconcile against accepted plus rejected records and publish the exclusion policy.

A defensible sentence might read: “A has two source rows, one observed amount, a known subtotal of ten, and no complete total.” That is more informative than “A totals ten.” The additional columns make the limitation visible without inventing the missing measurement.

Sources and reproducibility

Primary references: pandas 2.2.3 groupby, GroupBy size and count, and sum with min_count. Both examples are independently runnable with pandas. The six-row table and the figure's four-row subset are synthetic and explicitly distinguished.