Pandas Master Workbook: 50 GroupBy, Aggregate, and Merge Drills

Fifty drills on the only verbs that separate a casual Pandas user from an analyst who ships — groupby, aggregate, pivot, and merge — plus the exercises that make them reflexive.

What 50 questions drill and why that mix?

Heavy repetition on grouping/aggregation (Q1-30), then merging/concatenating (Q31-50), rereusing the same Team/Player and employee frames so your brain compares outputs, not datasets. Pre-read with Pandas fundamentals 40Q if any prompt feels fresh, and keep the Cheatsheet open.

Ingredients: Team data (Team, Player, Points, Assists) plus 8-row Teams/Employees extensions, each frame small enough to verify by hand.

How do you group and aggregate correctly?

Setup:

import pandas as pd
import numpy as np
data = {'Team': ['Red','Red','Blue','Blue','Green','Green','Red','Blue'],
        'Player': ['A','B','C','D','E','F','G','H'],
        'Points': [10,15,20,25,30,10,5,10],
        'Assists': [5,7,8,9,2,3,4,2]}
df = pd.DataFrame(data)
print(df.head())

# Q2-Q10 core patterns
team_groups = df.groupby('Team')
print(type(team_groups))  # DataFrameGroupBy
 
print(df.groupby('Team')['Points'].mean().round(1))
# Red 10.0, Blue 18.3, Green 20.0
 
print(df.groupby('Team').agg(
    avg_points=('Points','mean'),
    sum_assists=('Assists','sum'),
    headcount=('Player','count'),
    max_points=('Points','max')
).round(1))
 
# Transform keeps shape — like a window function
df['team_avg'] = df.groupby('Team')['Points'].transform('mean')
df['above_avg'] = df['Points'] > df['team_avg']
print(df[['Player','Team','Points','team_avg','above_avg']])

Rendered output: the agg table shows Green max 30 vs Red max 15; the transform block adds team_avg per row without collapsing — the window-function twin from SQL window functions.

How do you pivot and merge?

# Pivot: rows=Team, cols via aggregation
print(pd.pivot_table(df, values='Points', index='Team', columns=None, aggfunc='mean', fill_value=0))
 
# Richer pivot: department x city mean salary (uses employee frame from advanced masterclass)
import pandas as pd
emp = pd.DataFrame({'Dept':['HR','HR','Eng','Eng'], 'City':['NY','LA','NY','LA'], 'Salary':[70_000, 72_000, 80_000, 120_000]})
print(pd.pivot_table(emp, values='Salary', index='Dept', columns='City', aggfunc='mean', fill_value=0))
 
# Merge patterns — keep keys explicit
left = df[['Player','Team']]
right = pd.DataFrame({'Team':['Red','Blue','Green'], 'Coach':['Rex','Ben','Gus']})
print(pd.merge(left, right, on='Team', how='left').head(4))
print(pd.merge(left, right, on='Team', how='inner').shape)

Rendered output: the left merge preserves 8 rows with Coach repeated per team; an outer against a roster with an orphan team adds a row with NaN Coach — correct and expected.

How do you lock memory?

Randomise Q order and answer without looking. When every agg/transform/pivot/merge is a one-liner from memory, graduate to Pandas advanced masterclass for rank/shift, then to EDA playbook on your own data.

Download the Notebook and Practise

This article is a walkthrough of a runnable Jupyter notebook. Download the original .ipynb and run it locally or on Colab — every code block above appears in order.

Continue your track: Data Analyst Roadmap · Python and Pandas Guide · SQL NULL Handbook · SQL JOIN Fan-Out · Topfolio Practice · Data Analyst vs Engineer

Dataset generators where applicable are in courses/workbooks/generators/ — see citations atop for the exact *.py source for this notebook.

Frequently Asked Questions

How does groupby work under the hood?

Split (partition rows by key) -> Apply (aggregate per group) -> Combine (return indexed result). df.groupby('Team')['Points'].mean() is the canonical example.

What is the difference between agg and transform?

agg collapses groups to one row per group; transform broadcasts back to original shape (like a window function). Use transform for per-row flags.

How do you merge on multiple keys?

pd.merge(df1, df2, on=['year','month','category'], how='left') — include every key that defines uniqueness or you create fan-out duplicates.

Why does my groupby sum not match Excel?

Excel may hide filtered rows or NaNs. In Pandas, NaNs are skipped by sum/mean (like SQL). Check df.isna().sum() before comparing.