Intermediate 17 min

Grouping, Merging and Pivots

Aggregate with groupby, join tables and reshape 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"]})

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"]).

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.

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.

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

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.

Check your row counts

After every merge, compare len() before and after. Unexpected growth means duplicate keys; unexpected shrinkage means an inner join dropped rows.

Try it yourself

Using the tables above, find the department name with the highest average salary, ignoring employees without a department.

Show solution

merged = employees.merge(depts, on="dept_id", how="inner")
print(merged.groupby("dept")["salary"].mean().idxmax())   # Research