GroupBy, merge & pivot

Once the data is clean, three operations answer most business questions: group to summarise, merge to bring tables together, and pivot to lay a summary out as a readable grid.

GroupBy: split, apply, combine

summary = (df.groupby("city")
             .agg(total=("sales", "sum"), avg=("sales", "mean"), n=("name", "count"))
             .reset_index()
             .sort_values("total", ascending=False))

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

pivot = pd.pivot_table(df, index="city", columns="month", values="sales", aggfunc="sum", fill_value=0)
df.to_excel("report.xlsx", index=False)

Named aggregation — total=("sales", "sum") — is the clearest form: you name the output column and say which column and function produce it. Group by several keys for a finer breakdown:

(df.groupby(["city", "category"], as_index=False)
   .agg(revenue=("sales", "sum"), orders=("bill_id", "nunique")))

transform and filter

agg collapses groups; transform returns a value per original row, which is how you add group context:

df["city_total"] = df.groupby("city")["sales"].transform("sum")
df["share_of_city"] = (df["sales"] / df["city_total"] * 100).round(1)
df["rank_in_city"] = df.groupby("city")["sales"].rank(ascending=False)

big_cities = df.groupby("city").filter(lambda g: g["sales"].sum() > 500000)

Merging tables

sales_full = (orders
              .merge(customers, on="customer_id", how="left", validate="many_to_one")
              .merge(products, on="sku", how="left", suffixes=("", "_prod")))

how	Keeps
left	All rows of the left table (the usual choice for lookups)
inner	Only matching keys
right	All rows of the right table
outer	Everything from both, with NaN where unmatched

Two habits prevent most merge bugs. First, pass validate="many_to_one" so pandas raises an error if the lookup table has duplicate keys. Second, check the row count before and after:

before = len(orders)
after = len(sales_full)
print(before, after, sales_full["customer_name"].isna().sum(), "unmatched")

If after > before, your right table had duplicate keys and rows were multiplied. Use concat instead of merge when you are stacking the same columns from different months: pd.concat([jan, feb], ignore_index=True).

Pivot tables

report = pd.pivot_table(
    df, index="city", columns="category", values="sales",
    aggfunc="sum", fill_value=0, margins=True, margins_name="Total",
)

margins=True adds row and column totals, which is exactly what a finance team expects. pivot_table aggregates duplicates; plain pivot raises an error on them, so prefer pivot_table for real data.

A quick count cross-tab:

pd.crosstab(df["city"], df["payment"], normalize="index").round(2)

Key takeaways

• Named aggregation keeps output columns self-documenting.
• agg summarises; transform adds a group value back to each row.
• Always check row counts and validate= around merges.
• pivot_table with margins=True produces report-ready grids.