Pandas groupby: retain missing keys and define all-null totals

Operation contract

The receipt totals retain rows whose region is missing by setting dropna to false. Sum uses min_count equal to one, so a region containing only missing amounts keeps a missing total instead of becoming a misleading zero. Nullable integer storage preserves integer amounts alongside missing values. The fixture prints an explicit label for its missing region.

Failure and ownership boundary

Count counts nonmissing values, while size counts rows. They answer different questions. A missing group label must not silently become an actual business region; this fixture has no real unassigned region identifier. Joins after aggregation still require their own cardinality checks. Pandas nullable integers: missing amounts are not zero and Pandas joins: cardinality validation and missing lookup keys address those layers.

Tested environment

Dependency check: this program was executed on CPython 3.14.6 with pandas==3.0.6. Install these versions in a separate virtual environment. The download includes the recorded environment snapshot; no third-party package is part of the website runtime.

Working program

import pandas as pd

receipts = pd.DataFrame({"region": pd.Series(["DEL", "DEL", None, "BOM"], dtype="string"),
                         "amount": pd.Series([125, 250, 75, None], dtype="Int64")})
totals = receipts.groupby("region", dropna=False, sort=True)["amount"].sum(min_count=1)
for region, amount in totals.items():
    label = "missing-region" if pd.isna(region) else region
    value = None if pd.isna(amount) else int(amount)
    print(label, value)
print("rows retained:", int(receipts.groupby("region", dropna=False).size().sum()))

Output

BOM None
DEL 375
missing-region 75
rows retained: 4

Costs and limits

Aggregation scans rows and retains group state. Work depends on keys, sorting and the selected aggregation; do not assume arbitrary group apply has the same cost as sum. A large distinct-key set can retain nearly one accumulator per row.

Common Mistakes

• dropna for group keys is not min_count for aggregated values.
• Count and size differ when amounts are missing.

Connected lessons

Pandas nullable integers: missing amounts are not zero, Pandas joins: cardinality validation and missing lookup keys, Pandas chunked CSV aggregation: validate every chunk before returning totals.

Check the next state boundary

Pandas pivot: duplicates need an aggregation policy before reshaping.

Trace the next boundary

Pandas groupby(dropna=False): keep a missing category visible.