Pandas · Lesson 7 of 12

Grouping, Merging and Pivots

Aggregate with groupby, join tables and reshape data.

  • Intermediate
  • 17 min read
  • 3 objectives

Before this lessonLesson 6: Text and Categorical Data

What you will learn

  • Use groupby with agg
  • Merge DataFrames
  • Build a pivot table

Your Progress

0 of 12 lessons 0%

  • Lessons0 / 12
  • Completed0
  • Est. time left~ 3 hours

Progress is saved in this browser. No account needed.

Tip: pressing Next marks this lesson complete automatically.

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

Finished reading? Mark this lesson complete to track your progress.

Up next · Lesson 8Reshaping: melt, stack and pivot_tableReshape pandas DataFrames between wide and long format with melt, pivot, pivot_table, stack, unstack and crosstab, and learn when to use each shape.