pandas groupby aggregation vs transform: group rows or preserve them

Use groupby().agg() when you want a summary with one row per group. Use groupby().transform() when you want a group-derived value aligned with every original row. The difference is the output's unit of observation, not merely a preference for shorter syntax.

Our invented order table has several purchases per team, one missing team and one team whose total is zero. We want both a team summary and each order's share of its team's total. These tasks need different shapes.

State what one row means

An original row is an order. A summary row is a team. Dividing the order column directly by a team-indexed aggregation would trigger label alignment between unrelated indexes; it would not automatically match orders to teams.

transform('sum') repeats each team's total at its members' original row positions and preserves the input index. This makes it appropriate for a row-level share. It does not reduce the number of observations.

The dropna=False setting deliberately retains missing group keys as their own group. Default missing-key behavior can otherwise omit records from a summary. Whether an unknown team should be grouped, rejected or corrected is a data-quality decision, not something an aggregation can decide for you.

Calculate and verify both outputs

import pandas as pd
orders = pd.DataFrame({'team': ['A', 'A', 'B', 'B', None, 'C'],
                       'amount': [10., 30., 20., 20., 5., 0.]},
                      index=[11, 12, 20, 21, 30, 40])
grouped = orders.groupby('team', dropna=False)
summary = grouped.agg(total=('amount', 'sum'), count=('amount', 'size'))
totals = grouped['amount'].transform('sum')
orders['share'] = orders['amount'].div(totals.where(totals.ne(0)))
print('summary totals:', summary['total'].tolist())
print('summary counts:', summary['count'].tolist())
print('row totals:', totals.tolist())
print('shares:', ['undefined' if pd.isna(x) else f'{x:.2f}' for x in orders['share']])
print('index preserved:', totals.index.equals(orders.index))
assert summary['total'].sum() == orders['amount'].sum()
assert totals.index.equals(orders.index)
assert pd.isna(orders.loc[40, 'share'])

Checked output:

summary totals: [40.0, 40.0, 0.0, 5.0]
summary counts: [2, 2, 1, 1]
row totals: [40.0, 40.0, 40.0, 40.0, 5.0, 0.0]
shares: ['0.25', '0.75', '0.50', '0.50', '1.00', 'undefined']
index preserved: True

Interpret zero and missing values explicitly

Team C has a zero total, so its share is undefined. The example uses where to replace a zero denominator with a missing value before division. Reporting zero would suggest a defined fraction; reporting infinity would suggest a numerical result with little meaning for this task.

A missing team key is different from a missing amount. The example has the former. If amounts can be missing, decide whether sum should skip them and whether a group with no observed amount should have a missing total. sum(min_count=1) can express that distinction. size counts rows; count counts nonmissing values in the selected column. They are not interchangeable when data is incomplete.

Inspect the group ordering rather than assuming it follows input order. The default sort behavior places the displayed summary totals in group-key order. In contrast, the transformed values follow the original order index. Our output prints both so their different roles remain visible.

Choose a merge only when you need one

You can calculate a separate summary and join it back to the original table. That is useful when you need several independently maintained tables, but then you must validate the relationship and resulting row count. A duplicated summary key can multiply orders. See duplicate rows after a merge for the explicit failure case.

For one group statistic on the same table, transform often avoids that extra join and its key bookkeeping. It still requires a meaningful grouping key and a documented missing-data policy.

Extend the table

Add an order with a missing amount, and compare group size, count and sum(min_count=1). Then calculate a within-team centered amount using the group mean. Explain why the centered column should retain six original rows before you add the seventh fixture.

The Data Tools course connects grouping to complete pandas analyses. Review loc versus iloc before assigning results, and use the pandas projects to practise an entire cleaning and summary workflow.

References

pandas groupby guide — Aggregation, transformation, index behavior and missing group keys.