pandas Part 2: GroupBy, Merging and Pivot Tables

Part 3 of the Python for Data Science track. Last updated: September 2026.

Part 2 covered single-table basics. Real analysis means combining tables and summarizing groups — the pandas equivalent of SQL's GROUP BY and JOIN. That is this post.

import pandas as pd

sales = pd.DataFrame({
    "region": ["North", "North", "South", "South", "East"],
    "rep": ["Ana", "Ben", "Cara", "Dan", "Eli"],
    "amount": [120, 200, 150, 90, 300]
})

GroupBy: split-apply-combine

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

# Multiple stats at once with agg
report = sales.groupby("region").agg(
    total=("amount", "sum"),
    average=("amount", "mean"),
    deals=("amount", "count"))
print(report)

groupby splits rows into groups, applies a function, and combines the results — the single most-used pandas operation.

Merging: pandas JOINs

targets = pd.DataFrame({
    "region": ["North", "South", "East", "West"],
    "target": [400, 300, 250, 500]})

merged = sales.merge(targets, on="region", how="left")  # keep every sales row
# how="inner" → only matching regions
# how="outer" → everything from both tables
# how="right" → keep every target row
print(merged.head())

Concat: stacking tables

first_half = sales.iloc[:3]
second_half = sales.iloc[3:]
combined = pd.concat([first_half, second_half], ignore_index=True)  # stack rows
# axis=1 would stack COLUMNS side by side instead

Pivot tables

pivot = sales.pivot_table(index="region", values="amount",
                          aggfunc=["sum", "mean"])
print(pivot)   # one row per region, sum and mean side by side

Reshaping with melt

wide = pd.DataFrame({"rep": ["Ana", "Ben"],
                     "Q1": [120, 200], "Q2": [130, 210]})
long = wide.melt(id_vars="rep", var_name="quarter", value_name="amount")
print(long)   # quarters become rows — the tidy format for plotting

apply and map: custom transformations

sales["amount_k"] = sales["amount"].apply(lambda x: x / 1000)  # any function, per value
sales["code"] = sales["region"].map({"North": "N", "South": "S", "East": "E"})
# map = one-to-one lookup; apply = arbitrary function. Prefer vectorized ops when possible.

Key takeaways

• groupby + agg is the pandas GROUP BY — learn it cold.
• merge joins on keys; pick inner / left / right / outer deliberately.
• pivot_table summarizes; melt unpivots wide data into tidy rows.
• map for lookups, apply for custom functions.

Next in this series: Data Visualization with Matplotlib and Seaborn.