pandas / Intermediate: grouping, joins and temporal quality / Lesson 7

GroupBy aggregation and transform

Choose the output grain, missing-key policy and denominator before summarizing groups or broadcasting group results.

About 5 min readContent reviewed 2026-10-28

On this page

Lesson goals

Distinguish aggregation, transform and group filtering.
Explain size, count, missing keys and categorical observation settings.

An aggregation changes the meaning of a row

Grouping inventory movements by depot and summing units produces one result per group, not one result per source movement. A transform instead returns values aligned to the input shape under its documented contract, making it suitable for attaching a group total to each original row. A group filter selects groups based on a condition rather than calculating a new measure by itself. Decide which output grain the next step expects. Rejoining a summary to detail can duplicate or misalign rows if its keys are not unique, so verify the group-key contract instead of assuming any object returned by groupby has the same dimensional meaning.

Count rows and observed values separately

GroupBy size counts rows, while count excludes missing values in the selected measure. A depot with two rows and one unknown quantity therefore has different row and observed-value denominators. Missing grouping keys can be excluded under dropna defaults, so choose dropna explicitly when their coverage matters. Categorical grouping also depends on observed: it controls treatment of unobserved categories, and defaults changed in pandas 3.0. Set the intended choice rather than inheriting a remembered release default. Sorting groups does not itself sort observations within each group for time-dependent calculations; define that order separately.

Expose both counts in a named summary

The illustrative frame has depot A twice, with one known units value and one missing value, and depot B once. Named aggregation produces rows and known columns, so A reports two source rows but one observed quantity. Explicit sort, dropna and observed settings make scope visible even though this small fixture contains no missing depot or categorical dtype. The expected assertions are derived, not executed. Named outputs also reduce ambiguity when several statistics are calculated from the same source column. Include units and denominator meaning in reporting labels rather than exposing an unlabeled number that readers may mistake for all observations.

python Illustrative row-count versus observed-count aggregation

import pandas as pd

frame = pd.DataFrame({'depot': ['A', 'A', 'B'], 'units': pd.Series([1, None, 3], dtype='Int64')})
summary = frame.groupby('depot', sort=True, dropna=False, observed=True).agg(rows=('units', 'size'), known=('units', 'count'))
assert summary.loc['A', 'rows'] == 2
assert summary.loc['A', 'known'] == 1
print(summary['rows'].to_dict())

Expected output

{'A': 2, 'B': 1}

Expected row counts are A:2 and B:1; A has only one known quantity. pandas was not executed.

Check the grouping invariant with plain records

The standard-library model builds row and observed counts separately for each supplied depot. It checks the same business expectation without modeling pandas indexes, categorical completion or extension dtypes. This independence helps locate whether a disagreement concerns grouping population or engine-specific representation. A grouped mean also needs weights when groups have different sizes; averaging depot means equally answers a different question from the overall movement mean. Retain additive components such as sum and observed count when later combining partitions. An all-missing group needs a documented result policy rather than silently becoming an observed zero total.

python Offline group-denominator contract

rows = [('A', 1), ('A', None), ('B', 3)]
summary = {}
for depot, units in rows:
    entry = summary.setdefault(depot, {'rows': 0, 'known': 0})
    entry['rows'] += 1
    entry['known'] += units is not None
assert summary['A'] == {'rows': 2, 'known': 1}
print(summary['A'])

Expected output

{'rows': 2, 'known': 1}

The supplied A group has two records but one observed value; no pandas grouping mechanics are simulated.

Use built-in operations with explicit shape expectations

Prefer a built-in aggregation or transform when it expresses the requirement, and verify output keys, shape and alignment. A custom apply can return several shapes and has version-sensitive grouping-column behavior, so do not treat it as a universal shortcut. For a transform returning a DataFrame, result-index alignment matters; an unlabeled conversion changes the contract and must be deliberate. This lesson runs only the plain-record grouping model. pandas aggregation, transform, categorical behavior and performance remain unexecuted, with no optional backend or engine installed. Keep those limits with the derived examples rather than claiming the model certifies a real groupby pipeline.

Common errors and fixes

count is reported as the number of all source rows.

Why it happens: Missing measure values were excluded without explaining the denominator.
How to fix it: Report size and observed count separately where coverage matters.
Prevent it next time: Include missing measures in group fixtures.

Rows with missing depot keys vanish from grouped totals.

Why it happens: The grouping-key missing policy was inherited implicitly.
How to fix it: Choose dropna deliberately and reconcile excluded key coverage.
Prevent it next time: Test a missing group key separately from a missing measure.

The average of depot averages differs from the overall mean.

Why it happens: Groups were weighted equally despite unequal observed counts.
How to fix it: Combine sums and counts or apply the intended explicit weights.
Prevent it next time: Document the population and weighting of every derived statistic.

Check your understanding

1. Depot A has values1 and missing. Should size and count both be two?

Show worked solution

No. size counts both rows, while count for units counts the single observed value. The distinction matters for completeness and any average denominator. Neither number alone proves the depot’s actual stock, because row grain and missing-data meaning still determine what the source represents.

2. When should transform be considered instead of a summary followed by a merge?