Session 20 · Phase 3: Python

Pandas — groupby, Merge & Pivot

Summarize by category (groupby), combine tables (merge/join/concat), and reshape into reports (pivot) — the operations that turn raw data into analysis.

⏱ ~2 hrs 📚 Core content 🎯 High priority

Learning Objectives

Use groupby to summarize data by category (split-apply-combine).
Apply multiple aggregations with agg().
Combine DataFrames with merge, join, and concat — and know the difference.
Reshape and summarize with pivot_table.

1. groupby — Split, Apply, Combine

groupby splits data into groups by a column's categories, applies a function (like sum or mean) to each group, and combines the results — Pandas's version of SQL's GROUP BY.

import pandas as pd

df = pd.DataFrame({
    "region":  ["North", "South", "North", "East", "South", "North"],
    "product": ["Pen", "Notebook", "Notebook", "Marker", "Pen", "Marker"],
    "sales":   [100, 150, 200, 50, 300, 75]
})

# Total sales per region
df.groupby("region")["sales"].sum()

# Multiple aggregations at once
df.groupby("region")["sales"].agg(["sum", "mean", "count"])

# Group by TWO columns
df.groupby(["region", "product"])["sales"].sum()

💡

Pro Tip: groupby("col")["metric"].sum() is the pattern you'll use constantly. It's the direct equivalent of an Excel Pivot Table or SQL GROUP BY.

2. merge — Combine on a Key Column

merge joins two DataFrames on a shared column (like a SQL JOIN).

orders = pd.DataFrame({"customer_id": [1, 2, 3], "amount": [100, 200, 300]})
customers = pd.DataFrame({"customer_id": [1, 2, 4], "name": ["Aarav", "Priya", "Sneha"]})

# Inner join (only matching keys)
pd.merge(orders, customers, on="customer_id", how="inner")

# Left join (keep all orders)
pd.merge(orders, customers, on="customer_id", how="left")

The how parameter controls the join type: inner, left, right, outer.

3. merge vs join vs concat

Three ways to combine DataFrames — and a favourite interview question.

Method	Combines on	Direction
`merge()`	key columns (like SQL JOIN)	side-by-side
`join()`	the index	side-by-side
`concat()`	no key — just stacks	top-to-bottom (or side-by-side)

# concat: stack rows vertically
pd.concat([df1, df2])

# join: combine on index
df1.join(df2)

# merge: combine on a key column
pd.merge(df1, df2, on="key")

📝

Memory hook: merge = key columns; join = index; concat = just stack. If you need SQL-like joins on a column, use merge.

4. Pivot Tables

pivot_table reshapes data into a summary grid — the Pandas equivalent of an Excel Pivot Table.

# Sales by region (rows) × product (columns)
df.pivot_table(index="region", columns="product",
               values="sales", aggfunc="sum")

This produces a matrix where each cell is the summed sales for a region-product combination.

💡

pivot vs pivot_table: pivot is a simple reshape (no aggregation); pivot_table handles duplicates and aggregation. In practice you almost always want pivot_table.

📋 Stable content — Reviewed: August 2026

5. Interview Questions (with Model Answers)

The groupby/merge/pivot questions interviewers ask. Self-test before revealing.

IQ1. What does groupby do? Explain split-apply-combine.

Model answer: "groupby splits data into groups by a column's categories, applies a function like sum or mean to each group, and combines the results. It's Pandas's equivalent of SQL GROUP BY."

IQ2. How do you get multiple aggregations from groupby?

Model answer: "With .agg() — for example, df.groupby('region')['sales'].agg(['sum', 'mean', 'count']) returns several stats at once."

IQ3. What's the difference between merge and concat?

Model answer: "merge joins two DataFrames on a key column (like a SQL JOIN); concat just stacks DataFrames together — top-to-bottom or side-by-side — without matching on any key."

IQ4. What's the difference between merge and join?

Model answer: "Both combine side-by-side, but merge matches on a key column, while join matches on the DataFrame index. For SQL-like joins on a column, I use merge."

IQ5. Explain merge, join, and concat in one line each.

Model answer: "merge = join on key columns; join = join on the index; concat = just stack DataFrames without a key."

IQ6. What does a pivot table do in Pandas?

Model answer: "pivot_table reshapes data into a summary grid — rows by one column, columns by another, and values aggregated. It's the Pandas equivalent of an Excel Pivot Table."

IQ7. How do you group by multiple columns?

Model answer: "Pass a list of columns to groupby — df.groupby(['region', 'product'])['sales'].sum() — to group by both."

IQ8. What's the difference between pivot and pivot_table?

Model answer: "pivot is a simple reshape that fails on duplicate index/column pairs; pivot_table handles duplicates and aggregates them. In practice I use pivot_table."

IQ9. How do you do a left join in Pandas?

Model answer: "With pd.merge(df1, df2, on='key', how='left'). The how parameter controls it — inner, left, right, or outer."

Hands-On Project: Summarize and Combine

Using the sample DataFrames, complete the following.

Steps

Find total sales per region using groupby.
Find both sum and mean sales per region using agg.
Merge an orders DataFrame with a customers DataFrame on customer_id (left join).
Concatenate two DataFrames vertically.
Create a pivot table of sales by region (rows) × product (columns).

View Solution / Walkthrough

# 1. Total sales per region
df.groupby("region")["sales"].sum()

# 2. sum and mean per region
df.groupby("region")["sales"].agg(["sum", "mean"])

# 3. Left merge
pd.merge(orders, customers, on="customer_id", how="left")

# 4. Concatenate vertically
pd.concat([df1, df2])

# 5. Pivot table
df.pivot_table(index="region", columns="product",
               values="sales", aggfunc="sum")

Key Takeaways

groupby = split-apply-combine; the Pandas GROUP BY.

agg() runs multiple aggregations at once.

merge = key columns; join = index; concat = stack.

pivot_table reshapes into a summary grid (like an Excel pivot).

The how parameter controls merge join type (inner/left/right/outer).

Learning Objectives

1. groupby — Split, Apply, Combine

2. merge — Combine on a Key Column

3. merge vs join vs concat

4. Pivot Tables

5. Interview Questions (with Model Answers)

Hands-On Project: Summarize and Combine

Steps

Key Takeaways

Objective Questions — Test Your Understanding