Pandas groupby with missing categories and explicit denominators

Use groupby(dropna=False) when missing grouping values must remain visible, and specify what each denominator counts. size counts rows; count counts nonmissing values in a selected column. Those quantities differ whenever the measure is incomplete.

A grouped report should reconcile to the same population as its headline. Otherwise, an unknown region or missing amount can disappear behind a polished percentage.

Reproduce the disappearing category

The pandas quality lab has seven Paid orders with a known subtotal of 57,000 paise. Two orders have no region after enrichment: one matched customer lacks a region, and one customer did not match.

from build_and_verify import paid_enriched

paid = paid_enriched()
default_groups = paid.groupby('region')['amount_paise'].sum(min_count=1)
retained_groups = paid.groupby('region', dropna=False, observed=True)['amount_paise'].sum(min_count=1)
assert int(default_groups.sum()) == 50000
assert int(retained_groups.sum()) == 57000
assert int(retained_groups.loc[retained_groups.index.isna()].iloc[0]) == 7000
print(retained_groups)

The default result excludes the missing-region group and loses 7,000 paise from the displayed subtotals. The underlying rows still exist, so the omission can be easy to miss if only the grouped table is inspected.

The groupby reference documents dropna and observed. This article specifies both for clarity and was executed with pandas 3.0.2.

Put denominators beside the measure

from build_and_verify import paid_enriched

paid = paid_enriched()
group = paid.groupby('region', dropna=False, observed=True)
summary = group.agg(order_rows=('order_id', 'size'),
                    observed_amounts=('amount_paise', 'count'))
summary['known_subtotal_paise'] = group['amount_paise'].sum(min_count=1)
summary['missing_amounts'] = summary['order_rows'] - summary['observed_amounts']
assert summary.loc['North', 'order_rows'] == 3
assert summary.loc['North', 'observed_amounts'] == 2
assert summary.loc['North', 'known_subtotal_paise'] == 15000
assert summary.loc['North', 'missing_amounts'] == 1
assert int(summary['order_rows'].sum()) == 7
print(summary)

North's known subtotal is 15,000 paise across two observed amounts, with a third order whose amount is unknown. Calling 15,000 the complete North revenue would overstate what the data establishes.

Decide what an average means

Across all seven Paid orders, six amounts are observed. The observed-amount mean is 57,000 / 6 = 9,500 paise. Dividing by all seven orders gives about 8,142.86 paise and implicitly allocates zero to the missing amount.

Neither denominator should be hidden. If the business question requires average value across all Paid orders, the incomplete amount prevents a complete observed answer. Report the available-case mean with coverage, or apply an approved estimation method and label it separately.

Zero-value P04 is observed and belongs in the amount count. Missing-value P08 is not observed. Treating both as false-like values collapses the distinction.

Preserve all-missing groups

sum(min_count=1) prevents an all-missing measure group from automatically becoming zero. It does not make a partially observed subtotal complete. The count and missing-count columns remain necessary even when min_count is set.

If categorical grouping includes possible but unobserved categories, decide whether the report should display them. observed controls category expansion; it does not establish whether an absent category means a true zero or unavailable source coverage.

Reconcile before calculating shares

If reporting each region's share of known observed value, use the same retained population in numerator and denominator and name the measure accordingly. A North share of known value is not necessarily North's share of complete value while one North amount is missing.

Exercise: change every North amount to missing in a copied dataframe. Verify that its row count stays three, observed count becomes zero and its min_count subtotal stays missing. Explain why displaying zero would be misleading.

NeuraPath's Data Analytics with Generative AI course connects pandas aggregation with denominator design. A useful summary makes both the result and its coverage visible.

Continue learning

This article is part of the Pandas wrangling and data checks sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Pandas merge validation: catch many-to-many joins early.
• Continue with Convert mixed date formats without silent data loss.