groupby, merge and pivot: getting from rows to the table a question needs

Split, apply, combine

Every "per something" question is a groupby: revenue per month, average score per model, count of messages per language.

df.groupby("model")["cost_usd"].sum()

Read it as three steps. Split the rows into groups by model. Apply sum to the cost_usd column of each group. Combine the results into a Series indexed by model. Several statistics at once:

df.groupby("model").agg(
    calls=("cost_usd", "size"),
    total=("cost_usd", "sum"),
    mean_tokens=("output_tokens", "mean"),
    p95_latency=("latency_s", lambda s: s.quantile(0.95)),
)

Named aggregation — new_column=(source_column, function) — gives readable output columns. "size" counts rows including missing; "count" counts non-missing. A lambda works for anything without a built-in name, at the cost of speed.

Group by several keys and the result has a multi-level index:

by = df.groupby(["month", "model"])["cost_usd"].sum()
by.reset_index()          # back to ordinary columns
by.unstack("model")       # months as rows, models as columns

reset_index() is the move you will make constantly: a groupby result is indexed by its keys, and turning the index back into columns makes it a plain table again for plotting, merging or saving.

transform is groupby's other face: it returns a value per original row, aligned, so you can add a group statistic as a column:

df["share_of_month"] = df["cost_usd"] / df.groupby("month")["cost_usd"].transform("sum")

Merge: joining two tables

orders.merge(customers, on="customer_id", how="left")

merge is SQL's join. on= names the key; left_on=/right_on= when the names differ. how= decides what happens to rows without a match:

• inner (default): keep only rows whose key appears in both.
• left: keep every row of the left table; unmatched right columns become NaN.
• right: the mirror.
• outer: keep everything from both.

Choose left when the left table is "the thing you are describing" and the right is lookup data. An inner join silently drops orders whose customer is missing from the customer file, and you discover it as a total that is lower than it should be.

The multiplication trap

A join matches every row on the left with every row on the right that shares the key. If the key is unique on the right, each left row appears once. If it is not — a customer file with a row per address, a product file with a row per supplier — each left row appears once per match, and the total quietly balloons. A revenue sum over the result is then wrong by a factor nobody notices until the finance team does.

Defend against it with two habits. Print the row count before and after:

n = len(orders)
joined = orders.merge(customers, on="customer_id", how="left")
assert len(joined) == n, f"join changed row count {n} → {len(joined)}"

And tell pandas what you expect:

orders.merge(customers, on="customer_id", how="left", validate="many_to_one")

validate="many_to_one" raises if the right key is not unique. "one_to_one" checks both sides. It is one argument, and it turns a silent error into a loud one.

indicator=True adds a _merge column saying both, left_only or right_only for each row — the fastest way to find out which orders had no customer, and why.

Keys must match in type. int64 on one side and object on the other match nothing, which the pandas lesson warned about; df["customer_id"].astype(str) on both sides is the usual repair. Leading and trailing spaces in a string key have the same effect.

Concat: stacking tables

pd.concat([jan, feb, mar]) stacks tables with the same columns on top of each other, the operation for "twelve monthly files into one". ignore_index=True renumbers the rows. A column present in one file and absent in another becomes NaN where absent, silently, so compare df.columns across files first.

Reshape: wide and long

A table can be long — one row per (month, model, value) — or wide — months as rows, one column per model. Long is the shape groupby produces and the shape most analysis wants; wide is what a person reads.

wide = long.pivot_table(index="month", columns="model", values="cost_usd", aggfunc="sum", fill_value=0)
long = wide.reset_index().melt(id_vars="month", var_name="model", value_name="cost_usd")

pivot_table goes long to wide, aggregating where several rows land in one cell. melt goes back. When a spreadsheet arrives with a column per year, melt is the first thing to do to it, because a column per year cannot be grouped or filtered by year.

Sorting and ranking

df.sort_values(["month", "cost_usd"], ascending=[True, False])
df["rank"] = df.groupby("month")["cost_usd"].rank(ascending=False)
df.nlargest(10, "cost_usd")

nlargest is sort_values(...).head(n) done efficiently. rank within groups gives "this model's position within its month" without a loop.

Try this now

Make an orders table and a customers table where one customer appears twice. Merge them, watch the row count, then add validate="many_to_one" and read the error. Fix the duplicate, redo it with indicator=True, and count the left_only rows. Then groupby month and model, unstack, and melt it back.