Pandas · Lesson 3 of 4

Grouping, Merging and Pivots

Aggregate with groupby, join tables and reshape data.

Intermediate
17 min read
3 objectives

Before this lessonLesson 2: Cleaning Data

What you will learn

Use groupby with agg
Merge DataFrames
Build a pivot table

Analysis is mostly about summarizing ("average salary per country") and combining tables ("attach each order's customer name"). groupby, merge and pivot tables cover both, and they mirror the SQL you may already know.

import pandas as pd

employees = pd.DataFrame({
    "name": ["Ada", "Linus", "Grace", "Alan", "Tim"],
    "dept_id": [1, 2, 1, 3, 2],
    "country": ["UK", "FI", "US", "UK", "UK"],
    "salary": [120, 95, 150, 110, 105],
})
depts = pd.DataFrame({"dept_id": [1, 2, 4], "dept": ["Research", "Kernel", "Sales"]})

groupby: split, apply, combine

employees.groupby("country")["salary"].mean()

employees.groupby("country").agg(
    people=("name", "count"),
    avg_salary=("salary", "mean"),
    top_salary=("salary", "max"),
).reset_index()

Output

  country  people  avg_salary  top_salary
0      FI       1        95.0          95
1      UK       3       111.7         120
2      US       1       150.0         150

Named aggregation (new_name=(column, function)) yields clean column names. Group by several columns with a list: groupby(["country", "dept_id"]).

Transform and rank within groups

employees["pct_of_country_avg"] = (
    employees["salary"] / employees.groupby("country")["salary"].transform("mean")
)
employees["rank_in_country"] = employees.groupby("country")["salary"].rank(ascending=False)

transform returns a result the same length as the original, so it slots back in as a new column.

merge: joining tables

pd.merge(employees, depts, on="dept_id", how="inner")   # only matching
pd.merge(employees, depts, on="dept_id", how="left")    # keep all employees
pd.merge(employees, depts, on="dept_id", how="outer")   # keep everything

# different key names
pd.merge(employees, depts, left_on="dept_id", right_on="id")

The how values match SQL joins: inner, left, right and outer. Tim (dept 2) matches Kernel; Alan (dept 3) has no department, so a left merge gives NaN for his dept. Add validate="many_to_one" to catch accidental duplicate keys that would multiply rows.

Stacking tables

pd.concat([jan, feb, mar], ignore_index=True)     # same columns, stacked rows
pd.concat([a, b], axis=1)                       # side by side

Pivot tables

pd.pivot_table(
    employees, index="country", columns="dept_id",
    values="salary", aggfunc="mean", fill_value=0,
)

A pivot table reshapes long data into a grid, exactly like a spreadsheet pivot. melt does the reverse, turning wide columns back into rows.

# Write your solution here

Up next · Lesson 4Dates, Plots and ExportingWork with time series, make quick charts and save results.