Handbooks / Pandas & Data Analysis / Chapter 3

GroupBy and Aggregation

7 pages · ~4 min✓ Reviewed

Builds on Cleaning Data. Next up: Merging and Reshaping.

Pandas & Data AnalysisPage 1

Split-Apply-Combine Mental Model

1. The Three Steps

Splitrows into groups by key
Applyfunction on each group
Combineresults into one object

2. GroupBy Is Lazy

df.groupby('city') only builds a DataFrameGroupBy object
No data is computed at that point
Work starts on an aggregation, transform, apply or iteration
One row per group, indexed by the key

python

df.groupby('city')['sales'].sum()

city	sales
Delhi	300
Pune	120

Series indexed by city

3. Three Result Shapes

Apply type	Returns
aggregate	One row per group
transform	Same length as the input
filter	Keeps or drops whole groups

Remember

groupby alone computes nothing; the apply step you pick decides the output shape.

Pandas & Data AnalysisPage 2

Core Workflow: groupby with agg

1. Keys, Column, Aggregation

Key(s)groupby(['city','year'])
Column['sales']
Aggregation.mean()

Two keys give a MultiIndex result
as_index=False keeps the keys as columns
.reset_index() turns the keys back into columns

2. Several Aggregations

A list on one column: columns are the function names
Named aggregation: name=(column, func) gives clean names

python

df.groupby('city')['sales'].agg(['sum','mean','count'])
df.groupby('city').agg(total=('sales','sum'),
    avg=('sales','mean'), n=('id','count'))

named form can mix columns

Different functions per column

Use named aggregation, one name=(column, func) keyword each.

3. Interview Point

Different functions per column? Named aggregation, one keyword per output
Named aggregation? name=(column, func); the keyword becomes the column name
as_index=False? Keys stay ordinary columns instead of the index

Pandas & Data AnalysisPage 3

transform vs agg vs apply

1. Shape of the result

	agg	transform
Output	one scalar per group	same length as df
Rows	one per group	same as df
Index	group keys	original index
Assign back	needs a merge	directly as a column

2. Transform examples

Share of group total: transform broadcasts the sum to every row

python

df['pct'] = df['sales'] / df.groupby('city')['sales'].transform('sum')

each row's share of its city total

Standardise per group: transform(lambda s: (s - s.mean()) / s.std())
Group-wise fill: transform(lambda s: s.fillna(s.mean()))

3. Interview Point

When transform, not agg? When you need a per-row value to assign back as a column
Index guarantee? Same length and original index as df, so it aligns
Why are built-in names faster? 'sum' runs optimized code; a lambda or apply runs Python per group

Remember

Need a column back on df? transform. Need a summary table? agg.

Pandas & Data AnalysisPage 4

pivot_table, value_counts and crosstab

1. pivot_table: groupby + unstack

groupbycity, year → sum
unstackyear to columns
Gridcity × year

aggfunc accepts a list or a dict
fill_value=0 replaces NaN
margins=True adds an 'All' row and column

python

df.pivot_table(index='city', columns='year',
    values='sales', aggfunc='sum',
    fill_value=0, margins=True)

2. Counting tools

Call	Counts	Options
`s.value_counts()`	unique values, sorted descending	`normalize=True` proportions; `dropna=False` keeps NaN
`pd.crosstab(df['city'], df['plan'])`	combinations of two categoricals	`normalize='index'` row %

value_counts vs groupby? Same as s.groupby(s).size(), sorted descending
pivot_table vs crosstab? pivot_table aggregates a value column; crosstab counts category pairs
pivot vs pivot_table? pivot only reshapes and fails on duplicates; pivot_table aggregates them

Remember

Need a value summary: pivot_table. Need counts of categories: crosstab or value_counts.

Pandas & Data AnalysisPage 5

Common Mistakes

1. Counts That Disagree

groupby drops NaN keys by default; keep them with dropna=False
count() skips NaN values; size() counts all rows

Call	Counts	NaN
`size()`	all rows	included
`count()`	non-NaN values	skipped

2. Wrong or Slow Aggregation

agg('sum') hits every non-key column: text raises or concatenates
Select the value columns explicitly
apply or a lambda is much slower than a built-in name on big data

python

df.groupby('k')['v'].agg('sum')  # fast, explicit
df.groupby('k')['v'].agg(lambda s: s.sum())  # slow

same result, use the built-in name

3. Fix Summary

Symptom	Fix
Group missing	`dropna=False`
Counts differ	`size()` vs `count()`
Slow groupby	built-in name
Text column error	`['v']` first

Remember

Select columns first, use built-in names, and set dropna=False when NaN keys matter.

Pandas & Data AnalysisPage 6

Cheat Sheet: Groupby and Summaries

1. Aggregate and Broadcast

agg: one row per group
transform: same length as df, assign back as a column
Named aggregation gives clean column names

python

df.groupby('k')['v'].agg(['sum','mean'])
df.groupby('k').agg(total=('v','sum'))
df['x'] = df.groupby('k')['v'].transform('mean')

agg reduces, transform keeps the index

2. Grids and Counts

pivot_table: value grid; fill_value=0 fills NaN, margins=True adds All
value_counts: one column's frequencies; normalize=True gives shares
crosstab: category counts; normalize='index' gives row %

python

df.pivot_table(index='a', columns='b', values='v',
    aggfunc='sum', fill_value=0, margins=True)
s.value_counts(normalize=True)
pd.crosstab(df.a, df.b, normalize='index')

3. Interview Point: Pick the Tool

Tool	Returns
`agg`	One row per group
`transform`	Same-length output
`pivot_table`	A value grid
`crosstab`	Category counts
`value_counts`	One column's frequencies

Select the column

groupby('k').agg('sum') aggregates every other column. Write groupby('k')['v'].agg('sum').

Pandas & Data AnalysisPage 7

Check yourself

Rows: city A has sales 10 and 20, city B has 60. What do groupby('city')['sales'].transform('sum') and .agg('sum') return?

transform: 30, 30, 60, with one value per original row
agg: A is 30 and B is 60, with one row per city

k is x, x, None and v is 1, NaN, 3. What do groupby('k')['v'].count() and .size() give for x?

count gives 1, because it skips the NaN
size gives 2, because it counts rows
The None key is dropped, so only 2 of 3 rows are grouped; dropna=False keeps it

Bug: df['pct'] = df['sales'] / df.groupby('city')['sales'].sum() gives NaN. Why, and what is the fix?

sum returns one row per city, indexed by city, so it does not align with the row index
Use transform('sum'), which keeps the original index