Module 7 — Joins, grouping and pivot tables

Real data arrives in pieces: orders in one table, clients in another, products in a third. And the questions the business cares about are aggregates: revenue per segment, average basket per month. This module covers the two mechanisms that connect the pieces to the answers — and their traps, which produce wrong numbers without raising a single error.

merge: joining two tables

enriched_orders = orders.merge(
    clients,
    on="client_id",     # the join key
    how="left",         # the join type
)

The four types, and when to use them:

how=	What is kept	Typical use
inner	Keys present on both sides	Crossing two reliable sources
left	All of the left table	Enriching without losing rows — the sensible default
right	All of the right table	Rare — rewrite as a flipped left
outer	Everything, from both sides	Source reconciliation audits

With how="left", orders without a matching client get NaN in the client columns — visible and traceable. With inner, they disappear: a careless inner is the quietest way to lose 8% of your revenue in an analysis.

The two non-negotiable checks

Count before and after. A left must return exactly the left table's row count; if it returns more, the right key contained duplicates and every left row got multiplied — downstream sums will be silently inflated. Count the unmatched: result["right_column"].isna().sum(). These four control lines prevent the two costliest join accidents.

assert len(result) == len(orders), "Row multiplication: non-unique right key"
print(f"Unmatched: {result['segment'].isna().mean():.1%}")

When keys have different names: left_on="client_id", right_on="id". When homonym columns coexist: suffixes=("", "_client").

groupby: split, apply, combine

The conceptual pattern: split the table into groups, apply an aggregation to each group, combine the results.

df.groupby("segment")["amount"].sum()          # one aggregation
df.groupby("segment")["amount"].agg(["count", "mean", "sum"])   # several

# Several keys, named aggregations — the most readable form
recap = df.groupby(["segment", "country"]).agg(
    order_count=("order_id", "count"),
    revenue=("amount", "sum"),
    avg_basket=("amount", "mean"),
).reset_index()

reset_index() at the end turns the indexed result into a flat table — almost always what you want next (join, export, chart).

The temporal groupby deserves its mention: combined with pd.Grouper or resample, it produces the monthly series that populate every report:

monthly_revenue = df.set_index("date")["amount"].resample("ME").sum()

Two reliability reflexes: check the group sizes (.size()) — a mean over three rows does not carry the weight of a mean over thirty thousand — and remember that NaN are excluded from aggregations, which can make count differ from one column to another.

pivot_table: the cross-tabulation

To present an aggregate in two dimensions — rows × columns — as in a spreadsheet:

tab = df.pivot_table(
    values="amount",
    index="segment",       # the rows
    columns="year",        # the columns
    aggfunc="sum",
    fill_value=0,
    margins=True,          # "All" row and column of totals
)

pivot_table is a reshaped groupby: same engine, different presentation. It aggregates duplicates (unlike pivot, which fails if there are any) — it is almost always the one you want. The inverse operation, melt, unfolds a wide table into long format, the one Seaborn and most tools prefer.

Composing: the complete analysis query

The three tools chain naturally, in the module 5 style:

top_segments = (
    orders
    .merge(clients[["client_id", "segment"]], on="client_id", how="left")
    .groupby("segment")
    .agg(revenue=("amount", "sum"), clients=("client_id", "nunique"))
    .assign(revenue_per_client=lambda d: d["revenue"] / d["clients"])
    .sort_values("revenue_per_client", ascending=False)
)

Join, group, derive, sort: it is the pandas equivalent of a full SQL query, and the shape most of your analyses will take. Every step of the chain can be run alone for inspection — the great advantage of this style over a variable overwritten twenty times.

Key takeaways

• merge with how="left" by default; always count rows before/after and the unmatched rate — multiplication through a duplicated key is the module's costliest bug.
• groupby = split-apply-combine; named aggregations for readability, reset_index() at the end, an eye on group sizes.
• resample for temporal aggregates; pivot_table for cross-tab presentation; melt to return to long format.
• Real analyses compose all three into a chain inspectable step by step.

Next module: visualization — turning these aggregates into charts that show the distribution, the trend and the anomaly.