Data Science ยท Chapter 11 of 43
GroupBy & Aggregation
GROUPBY splits a DataFrame by one or more columns, applies a function per group, and combines the results (SPLIT-APPLY-COMBINE).
Essential for reporting: per-city sales, per-user activity, etc.
Example 1 (python)
import pandas as pd
df = pd.read_csv('sales.csv')
print(df.groupby('city')['amount'].sum())
Total sales per city.
Example 2 (python)
print(df.groupby('city').agg(total=('amount','sum'), n=('amount','count')))
Multiple named aggregations.
Key points
- Split โ apply โ combine.
- Aggregate with sum, mean, count, etc.
- agg() supports multiple metrics.
- Foundation of dashboards.
๐ก Note: `agg` with named outputs (`total=('amount','sum')`) is the modern, readable way to write multi-metric groupbys.
Understanding GroupBy & Aggregation
Grouping changes the unit of analysis. A sales table might contain one row per transaction; a city summary contains one row per city. Before calculating anything, state what an input row represents and what an output row should represent. Otherwise, a mathematically correct sum can answer the wrong business question, such as treating line items as separate orders.
The pandas user guide organizes GroupBy around split, apply, and combine. Splitting identifies rows with matching grouping keys; applying calculates a result within each group; combining assembles those results. Rows need not be adjacent to belong together. Calling `groupby()` creates a grouping object, not the finished summary. Selecting a column and calling `sum()`, for example, completes an aggregation.
An aggregation reduces each group to summary values. Use `sum` for additive quantities, `mean` for an arithmetic average, `min` and `max` for extremes, and `nunique` for distinct values. These operations answer different questions: total spending measures volume, while mean transaction spending describes the average recorded transaction. Neither alone describes the entire distribution; a large purchase can strongly influence both total and mean.
Named aggregation makes a report's meaning visible. In `agg(total=('amount', 'sum'))`, `total` is the output column, `amount` is the source column, and `sum` is the operation. Multiple named outputs can summarize the same source differently. Grouping by two columns creates groups for key combinations, such as city and product. With `as_index=False`, grouping keys remain ordinary output columns rather than becoming index levels.
Missingness requires two separate decisions. Missing grouping keys are excluded by default; `dropna=False` retains them as a group. Missing measurements are generally skipped by reductions such as mean. `size` counts rows, whereas `count` counts nonmissing values in the selected column. Reporting both exposes incomplete measurements instead of silently treating them as zeros.
Aggregation compresses information: it removes transaction detail and can hide variation within cities. Compare groups using compatible currencies, periods, and inclusion rules. A difference between city averages is descriptive, not evidence that location caused spending differences. If each original row needs its group's mean alongside it, use `transform('mean')` instead; unlike aggregation, transformation preserves the original row structure.
Practical walkthrough
Start with a precise question: for each city, how much recorded spending exists, how many transactions appear, and how complete are their amounts? Assume each row below is one distinct transaction, all amounts use the same currency, and all transactions belong to the same reporting period. pandas must already be installed; the example creates its data directly without reading files.
Inspect the six records before grouping. North has three transactions, but one amount is missing. South has two complete transactions. The remaining transaction has an amount but no city. This separates missing measurements from missing keys, making both reporting decisions visible.
Group by `city`, retaining missing keys with `dropna=False`. Set `as_index=False` for a flat reporting table and `sort=True` for predictable key ordering. Define four named aggregations: observed spending, transaction rows, recorded amounts, and mean recorded amount. Built-in reductions keep this straightforward; a custom `apply` function would add unnecessary complexity here.
Calculate completeness after aggregation by dividing recorded amounts by transaction rows and multiplying by 100. This denominator includes transactions with missing amounts. Do not replace their amounts with zero: doing so would claim knowledge that the data does not provide and change the mean.
Finally, validate the summary against the source. Transaction counts should add to six because missing cities were retained. Recorded amounts should add to five, and observed totals should add to 200. These checks detect dropped rows or inconsistent filters, although they cannot prove the source data is correct. Print explicitly formatted numbers so display settings do not affect the shown output.
Worked example
import pandas as pd
df = pd.DataFrame({
'city': ['North', 'North', 'North', 'South', 'South', None],
'amount': [20.0, 40.0, None, 90.0, 30.0, 20.0],
})
summary = df.groupby(
'city', as_index=False, dropna=False, sort=True
).agg(
observed_total=('amount', 'sum'),
transactions=('amount', 'size'),
recorded=('amount', 'count'),
mean_recorded=('amount', 'mean'),
)
summary['complete_pct'] = (
100 * summary['recorded'] / summary['transactions']
)
assert summary['transactions'].sum() == len(df)
assert summary['recorded'].sum() == df['amount'].count()
assert summary['observed_total'].sum() == df['amount'].sum()
print('city | observed_total | transactions | recorded | mean_recorded | complete_pct')
for row in summary.itertuples(index=False):
city = '(missing)' if pd.isna(row.city) else row.city
print(
f'{city} | {row.observed_total:.2f} | {row.transactions} | '
f'{row.recorded} | {row.mean_recorded:.2f} | {row.complete_pct:.2f}'
)
Expected output
city | observed_total | transactions | recorded | mean_recorded | complete_pct
North | 60.00 | 3 | 2 | 30.00 | 66.67
South | 120.00 | 2 | 2 | 60.00 | 100.00
(missing) | 20.00 | 1 | 1 | 20.00 | 100.00
North's observed total is 20 + 40 = 60, and its recorded mean is 60 / 2 = 30, not 60 / 3. Its completeness is two recorded amounts out of three transactions, displayed as 66.67%. South's mean is 60, but these few records do not establish a general city difference. The missing-city row preserves 20 of spending that default grouping would omit. Across all groups, observed spending is 200 across five known amounts, giving an overall recorded mean of 40. The label `observed_total` matters: North's actual total remains unknown because its third transaction has no recorded amount.
Common mistakes and how to avoid them
Using count as the transaction count.
Use size for rows and count for available measurements. If rows are line items rather than transactions, count distinct transaction identifiers with nunique instead.
Interpreting an all-missing group's sum as confirmed zero spending.
Default sum can return zero when no values are available. Use grouped sum(min_count=1) to preserve missingness, or report counts and mark totals with zero recorded measurements as unknown.
Averaging group means to recover the overall transaction mean.
Weight each mean by its nonmissing measurement count, or divide combined observed totals by combined recorded counts. Equal weighting answers a different question: the average of group averages.
Expecting grouping to repair duplicate transactions or inconsistent city labels.
Validate transaction identifiers and normalize labels before aggregation. Investigate duplicates rather than automatically deleting them; repeated amounts can represent legitimate separate purchases.
Review questions
What changes when grouping by city and product instead of city alone?
Each output row represents a city-product combination. A city's transactions can therefore contribute to several output rows, each summarizing a different product.
Why does retaining missing cities not solve missing amounts?
dropna=False controls group membership for missing keys. It does not fill measurements; means still use available amounts, so report measurement counts separately.
How would you attach each city's mean to every transaction?
Use a grouped transform('mean') on amount, with an explicit missing-key policy. It returns row-aligned values rather than one summary row per city.
What should you check before comparing city totals?
Check reporting periods, currencies, row definitions, duplicate handling, and missingness. Larger totals may reflect more transactions rather than higher spending per transaction; examine counts and means together.
