GROUP BY and Aggregation

GroupBy and Aggregation

The one idea: When you summarize data by group, two silent errors can flip the answer: whole categories with a missing label quietly disappear from the report, and averaging a rate (instead of adding up the parts first) can badly overstate it. Check that the group totals still add up to the whole, and be suspicious of any average of an average.

⚠️ The trap: df.groupby(...).count() tells me how many rows are in each group. That feels right and it's wrong. count counts only the non-missing values in the column, so a group of 50 rows with 12 recorded revenues reports 12, not 50 — reach for .size() when you actually want the row count.

Almost every number that reaches a decision-maker is an aggregate: revenue by region, conversion by channel, retention by cohort. In pandas that's groupby (the command that splits rows into groups so you can summarize each one), and it's where two of the most expensive silent bugs in business analytics live: whole categories vanishing from reports, and averages computed in the wrong order. Both produce clean-running code and plausible numbers.

The core pattern

df.groupby('region')['revenue'].sum()

Read it in three pieces: group the rows by region → take each group's revenue column → sum it. One output row per unique region:

# region
# East    350
# West    300
# Name: revenue, dtype: int64

Notice the region names are on the left with no column header — groupby moved them into the index (the row labels). You'll often see .reset_index() tacked on the end, which turns the index back into an ordinary column. It changes nothing about…