Summarising and combining data with groupby, merge and pivot_table

Analyst tasks usually end with a summary table: sales by region, average order value by month, or headcount by department. Three tools do most of this work: groupby, merge and pivot_table.

groupby: split, apply, combine

groupby splits rows into groups, applies a calculation to each group and combines the results.

df.groupby("region")["sales"].sum()          # Series
df.groupby(["region", "month"])["sales"].mean()
df.groupby("dept").agg(total=("salary", "sum"),
                       headcount=("emp_id", "count"))

Remember that count ignores missing values while size counts all rows in the group. If a question asks for the number of employees and some IDs are missing, the two give different answers. The group keys become the index; add .reset_index() if you need them back as columns.

merge: joining tables

merge works like a SQL join. The default is an inner join, which silently drops rows without a match.

• how="left" keeps every row of the left table and fills missing matches with NaN.
• how="outer" keeps rows from both sides.
• Use left_on and right_on when the key columns have different names.

The most common real mistake is a key that is not unique in the right table. If one customer_id appears twice in customers, every matching order is duplicated and totals go up. Check row counts before and after, or pass validate="many_to_one" so pandas raises an error when the right side has duplicate keys.

pivot_table: reshaping for reports

pivot_table turns a long table into a grid.

pd.pivot_table(df, index="region", columns="month",
               values="sales", aggfunc="sum", fill_value=0)

index gives the rows, columns gives the column headers, and aggfunc decides how repeated combinations are combined. Its default aggfunc is mean, so leaving it out when you wanted totals is a frequent error. fill_value replaces empty cells, for example a region with no sales in one month.

A checking routine

1. Note len(df) before every merge and compare it after.
2. Check df["key"].duplicated().sum() on the lookup table.
3. Recalculate one group by hand, such as one region's total, and compare it with the output.

These three checks catch most wrong answers in take-home tasks before a reviewer does.