Aggregations and groupby

Somewhere between “here are the rows” and “here is the story” sits aggregation: totals, averages, counts, grouped so a human can scan. In Sheets that is a pivot table. In SQL that is GROUP BY. In pandas that is groupby plus an aggregation. Same idea, same risks (wrong grain, silent duplicates), same payoff (one clear table of region totals).

This is Part 5 of Python for analytics. Parts 3 and 4 loaded and sliced tables. Now we summarize them. If you already write SQL aggregates, you will feel at home. If you live in pivots, you will recognize the move. Keep SQL and spreadsheet habits nearby, and the problem discipline from Analytics foundations. Charts can wait; a later stretch (think Part 11) can plot what you group here.

What you will learn

• Split-apply-combine in plain language
• How groupby maps to SQL GROUP BY
• Sum, mean, count, and multiple aggregations in one pass
• Why as_index=False and reset_index make results friendlier
• A worked sales-by-region example with before and after tables
• Mistakes that create confident wrong totals

Split, apply, combine

Hadley Wickham popularized a simple name for what group-by does in many tools: split the table into groups, apply a function to each group (sum, mean, count), then combine the results into a new table. pandas groupby is that engine.

You do not need the academic history to use it. You need the picture:

• Split on region: East rows together, West rows together, South rows together.
• Apply sum to revenue inside each pile.
• Combine into a small table with one row per region.

Grain changes on purpose. Before: one row per order. After: one row per region. If you forget that, you will join the summary back to details incorrectly. Say the new grain out loud every time you group.

SQL mind to pandas mind

count in pandas counts non-null values per column. size counts rows in the group, including rows where some columns are null. For “how many orders,” size (or counting a never-null id) is often closer to SQL COUNT(*).

First aggregations: sum, mean, count

Using the familiar orders file:

import pandas as pd

orders = pd.read_csv("hello_orders.csv")

# Total revenue by region (Series indexed by region)
totals = orders.groupby("region")["revenue"].sum()
print(totals)

# Same idea as a DataFrame with a normal region column
totals_df = (
    orders.groupby("region", as_index=False)["revenue"]
    .sum()
    .rename(columns={"revenue": "total_revenue"})
)
print(totals_df)

# Average order size by region
avg_df = (
    orders.groupby("region", as_index=False)["revenue"]
    .mean()
    .rename(columns={"revenue": "avg_revenue"})
)
print(avg_df)

# Number of orders by region
counts = orders.groupby("region").size().reset_index(name="order_count")
print(counts)

Example output:

as_index=False keeps the group keys as columns. That is usually what you want before you export to CSV or join back to something else. If you forget, reset_index() after the aggregation is the standard repair.

Multiple aggregations with agg

Real questions rarely want only a sum. They want sum and count and maybe average in one table.

summary = (
    orders.groupby("region", as_index=False)
    .agg(
        total_revenue=("revenue", "sum"),
        avg_revenue=("revenue", "mean"),
        order_count=("order_id", "count"),
    )
    .sort_values("total_revenue", ascending=False)
)

print(summary)

Example:

Named aggregation (new_name=("column", "function")) keeps column names readable. Older code uses nested dictionaries; you may still see that online. Prefer named aggregation when you can: it is clearer in reviews.

You can also aggregate different columns differently:

# If you had more columns, e.g. quantity and revenue:
# orders.groupby("region", as_index=False).agg(
#     total_revenue=("revenue", "sum"),
#     total_units=("quantity", "sum"),
#     orders=("order_id", "nunique"),
# )

Worked example: sales by region before and after

Visual: row-level data vs groupby total:

Before (order grain):

After (region grain):

import pandas as pd

orders = pd.DataFrame(
    {
        "order_id": [1, 2, 3, 4, 5],
        "region": ["East", "West", "East", "South", "West"],
        "revenue": [4200, 8100, 6900, 1500, 3200],
    }
)

by_region = (
    orders.groupby("region", as_index=False)
    .agg(
        total_revenue=("revenue", "sum"),
        avg_revenue=("revenue", "mean"),
        order_count=("order_id", "count"),
    )
    .sort_values("total_revenue", ascending=False)
    .reset_index(drop=True)
)

print(by_region)

# Optional: share of total revenue
by_region["pct_of_total"] = (
    by_region["total_revenue"] / by_region["total_revenue"].sum()
)
print(by_region)

That pct_of_total column is a common slide request. Notice it is calculated on the aggregated table, not by averaging percentages at the order level. Percent of total is a post-aggregation story. Mixing levels is how people invent “math that does not add to 100%” and then argue in meetings.

Equivalent SQL:

SELECT
  region,
  SUM(revenue) AS total_revenue,
  AVG(revenue) AS avg_revenue,
  COUNT(*) AS order_count
FROM orders
GROUP BY region
ORDER BY total_revenue DESC;

Grouping by more than one column

Multi-key groups are normal: region and month, team and status, product and channel. Pass a list to groupby.

# Toy extension: add a channel column
orders["channel"] = ["web", "web", "store", "web", "store"]

by_region_channel = (
    orders.groupby(["region", "channel"], as_index=False)
    .agg(
        total_revenue=("revenue", "sum"),
        order_count=("order_id", "count"),
    )
    .sort_values(["region", "total_revenue"], ascending=[True, False])
)

print(by_region_channel)

Example:

Each unique pair becomes a group. Row counts in the summary should still match the sum of group counts if you are only counting. Verify with a total row when the stakes are high.

reset_index and the shape of the result

After groupby, you might hold:

• A Series (single metric, group keys as index)
• A DataFrame with a MultiIndex (multiple group keys as index levels)
• A flat DataFrame with keys as columns (as_index=False or reset_index())

For handoffs to Sheets, BI tools, or teammates who fear indexes, prefer the flat DataFrame. Part 9 will care about export shapes. Start clean now.

s = orders.groupby("region")["revenue"].sum()
flat = s.reset_index(name="total_revenue")
print(flat)

Filter then group, or group then filter?

Both happen. They answer different questions.

• Filter then group: “Among web orders only, revenue by region.” Apply a Part 4 mask first, then groupby.
• Group then filter groups: “Regions with total revenue over 10,000.” Aggregate first, then filter the summary (SQL HAVING energy).

# HAVING-style filter on aggregates
strong_regions = by_region[by_region["total_revenue"] > 10000]
print(strong_regions)

Do not mix them up in a meeting. “Orders over $10k by region” is not the same as “regions over $10k total.”

Sanity checks that catch bad groupbys

Aggregation errors are quiet. The code runs. The number looks round. Someone puts it in a slide. Build a short checklist into your muscle memory.

# 1) Detail total vs group totals for an additive metric
detail_total = orders["revenue"].sum()
group_total = by_region["total_revenue"].sum()
print(detail_total, group_total, detail_total == group_total)

# 2) Row counts
print(len(orders), by_region["order_count"].sum())

# 3) Unexpected group labels
print(by_region["region"].tolist())

If detail total and group total disagree, you filtered one side and not the other, or you aggregated a column that is not purely additive after a join explosion. If order counts disagree with len(orders), you may have dropped null keys in the group column. Null group keys are easy to miss: pandas can exclude them from groups depending on version and settings. Glance at orders["region"].isna().sum() when counts look short.

Also watch for “group by a continuous number by accident.” Grouping revenue itself creates a group per distinct amount, which is almost never the summary leadership wanted. Group dimensions (region, month, segment). Aggregate facts (revenue, quantity).

Charts are optional later

A grouped bar chart of total_revenue by region is the natural picture of this table. This series keeps plotting as a later stretch (Part 11 style), not a blocker. Get the numbers right first. A wrong chart with pretty colors is still wrong. When you do plot, feed it the aggregated table, not the raw orders, unless you mean to show distributions.

Common mistakes

• Averaging averages. Mean of regional averages is not the overall mean if regions differ in size.
• Grouping on a column with hidden duplicates or trailing spaces. "East" and "East " become two groups. Clean categories first.
• Using count when you meant row counts with nulls present. Know count vs size.
• Forgetting the grain change. Joining region totals back to order rows without care multiplies totals.
• Summing an id column by accident. Aggregate the fact columns, not the keys, unless you have a reason.
• Silent wrong results from pre-aggregated inputs. If the CSV is already a pivot, grouping again can double-count. Profile with head and row meaning first (Part 3).
• Skipping a totals check. Sum of group totals should match sum of the detail metric (for summable facts without filters).

Rule of thumb: After every groupby, write “one row now means…” and check that the sum of group sums equals the ungrouped sum for additive metrics.

Practice and next step

On your practice data:

1. Compute total revenue by region with as_index=False.
2. Add order counts and average revenue with agg.
3. Sort by total revenue descending.
4. Filter the summary to groups above a threshold (HAVING style).
5. Verify that group totals sum to the overall total revenue.

Next: Part 6, Joins and merges, brings a second table into the picture, with the same grain caution you just practiced. Missing values and dtypes get their own focus in Part 7.

Quick recap

• groupby is split-apply-combine; it changes grain on purpose.
• Map SUM/AVG/COUNT and GROUP BY to sum/mean/count/size and groupby.
• Use agg for multiple metrics; prefer named aggregations.
• as_index=False or reset_index keeps results flat and handoff-friendly.
• Filter-then-group differs from group-then-filter (HAVING).
• Charts are optional later; correct tables come first.

Sources

• pandas, “Group by: split-apply-combine”: https://pandas.pydata.org/docs/user_guide/groupby.html
• pandas DataFrame.groupby: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.groupby.html
• pandas DataFrame.agg: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.agg.html
• pandas, “10 minutes to pandas”: https://pandas.pydata.org/docs/user_guide/10min.html
• Analytics Made Simple, Learn: https://analyticsmadesimple.com/learn/
• Analytics Made Simple, SQL series: https://analyticsmadesimple.com/series/sql/
• Analytics Made Simple, From spreadsheets to real data: https://analyticsmadesimple.com/series/spreadsheets-to-data/
• Analytics Made Simple, Analytics foundations: https://analyticsmadesimple.com/series/analytics-foundations/

Merch on Etsy

Open store →

Boxplot Mouse Pad, Data Science Mouse Pad, Analytics Mouse Pad, Box Plot, Whisker Plot View on Etsy → Mute Mouse Pad, Data Science Mouse Pad, Analytics Mouse Pad, You're On Mute View on Etsy → Y Hat Mouse Pad, Data Science Mouse Pad, Analytics Mouse Pad, Y Pred Mouse Pad View on Etsy → Data Science White Ceramic Coffee Mug, Data Science Mug, Data Mug, Analytics Mug, Statistics Mug, Programming Mug, Gift Mug, Coffee Mug View on Etsy →