Pandas groupby: Split, Aggregate and Summarise

If there is one operation that defines data analysis, it is grouping: take a big table, split it into groups (per city, per month, per customer), calculate something for each group (a count, a sum, an average), and combine the results into a new, smaller table. In SQL this is GROUP BY. In Excel it is a pivot table. In pandas it is groupby, and it is probably the most powerful single method in the library.

The idea behind it is called split-apply-combine. First split the rows into groups by one or more keys. Then apply a function to each group, either an aggregation (reduce each group to one number), a transformation (return a value for every row, like "each order as a percent of its store's total") or a filter (keep or drop whole groups). Finally pandas combines the pieces. Once you see all three modes, most business questions become one-liners. We will work on real FreshBasket data: delivered orders joined to their order values, stores and customers.

Worked examples

Example 1: The basics

Question: How many orders and what average delivery time per store?

import pandas as pd
import numpy as np

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

print(orders.groupby("store_id").size().head(4))
print(orders.groupby("store_id")["delivery_minutes"].mean().round(2).head(4))
print(orders.groupby("order_status")["order_id"].count())

g = orders.groupby("payment_method")
print(g["discount_pct"].agg(["mean", "min", "max"]).round(2))

store_id
S01    682
S02    619
S03    457
S04    488
dtype: int64
store_id
S01    23.57
S02    23.98
S03    23.51
S04    23.64
Name: delivery_minutes, dtype: float64
order_status
Cancelled     370
Delivered    5391
Returned      239
Name: order_id, dtype: int64
                mean  min  max
payment_method                
COD             5.63    0   20
Card            6.00    0   20
UPI             5.85    0   20
Wallet          6.00    0   20

How it works: groupby("store_id") creates a lazy grouping object. Choosing a column and a function (size counts rows, count counts non-missing values, mean averages) triggers the calculation. The result is indexed by the group keys. With ["mean", "min", "max"], agg returns one column per function. Group results skip NaN values automatically.

Reading the result: Each store appears once, and the 11 stores together hold 6,000 orders. size includes rows with blank delivery times, while mean ignores them, so be clear about which you want.

Example 2: Named aggregation

Question: Build a clean per-store scorecard with readable column names.

scorecard = (
    orders.groupby("store_id")
    .agg(
        orders=("order_id", "count"),
        delivered=("order_status", lambda s: (s == "Delivered").sum()),
        avg_minutes=("delivery_minutes", "mean"),
        p90_minutes=("delivery_minutes", lambda s: s.quantile(0.9)),
        customers=("customer_id", "nunique"),
    )
    .round(2)
    .reset_index()
)
scorecard["delivered_pct"] = (scorecard["delivered"] / scorecard["orders"] * 100).round(1)
print(scorecard.head(5).to_string())
print(scorecard.sort_values("avg_minutes").iloc[[0, -1]][["store_id", "avg_minutes"]].to_string())

  store_id  orders  delivered  avg_minutes  p90_minutes  customers  delivered_pct
0      S01     682        601        23.57         32.0        114           88.1
1      S02     619        555        23.98         32.0        112           89.7
2      S03     457        423        23.51         32.0        106           92.6
3      S04     488        446        23.64         32.0        108           91.4
4      S05     654        587        23.69         32.0        112           89.8
  store_id  avg_minutes
7      S08        23.03
1      S02        23.98

How it works: Named aggregation has the form new_name=("column", function), where the function can be a string like "mean", a NumPy function, or your own lambda. It produces flat, well-named columns, unlike the older dictionary style that creates multi-level headers. reset_index() turns the group key back into an ordinary column so the table is easy to join or plot.

Reading the result: The scorecard has 11 stores. The fastest store is S08 (23.03 minutes on average) and the slowest is S02 (23.98).

Example 3: Group by several keys

Question: Orders and average delivery time for each store and delivery slot.

by_two = orders.groupby(["store_id", "delivery_slot"]).agg(
    orders=("order_id", "count"),
    avg_minutes=("delivery_minutes", "mean"),
).round(1)

print(by_two.head(6))
print(by_two.index.names, by_two.shape)

wide = by_two["orders"].unstack("delivery_slot")
print(wide.head(3))
print(wide.sum(axis=0).sort_values(ascending=False).to_dict())

                         orders  avg_minutes
store_id delivery_slot                      
S01      Evening (4-8)      254         23.6
         Midday (10-2)      133         24.7
         Morning (6-10)     143         22.9
         Night (8-11)       152         23.3
S02      Evening (4-8)      253         24.4
         Midday (10-2)       90         24.2
['store_id', 'delivery_slot'] (44, 2)
delivery_slot  Evening (4-8)  Midday (10-2)  Morning (6-10)  Night (8-11)
store_id                                                                 
S01                      254            133             143           152
S02                      253             90             136           140
S03                      137             84             119           117
{'Evening (4-8)': 2220, 'Night (8-11)': 1372, 'Morning (6-10)': 1308, 'Midday (10-2)': 1100}

How it works: Grouping by a list of keys makes a multi-level index. unstack moves the inner level (delivery_slot) into columns, producing a store × slot matrix, similar to a pivot table. Use reset_index() instead if you prefer a flat "long" table.

Reading the result: There are 44 store-slot combinations. The most popular slot overall is Evening (4-8) with 2,220 orders.

Example 4: Group by a derived key

Question: Group by the month of the order, and by delivery-time bands.

orders["order_date"] = pd.to_datetime(orders["order_date"])
orders["month"] = orders["order_date"].dt.to_period("M")

monthly = orders.groupby("month").agg(orders=("order_id", "count"), avg_discount=("discount_pct", "mean")).round(2)
print(monthly.head(4))
print(monthly["orders"].idxmax(), monthly["orders"].max())

bands = pd.cut(orders["delivery_minutes"], bins=[0, 15, 25, 35, 100], labels=["<15", "15-25", "25-35", "35+"])
print(orders.groupby(bands, observed=True).size())

print(orders.groupby(orders["order_date"].dt.day_name()).size().sort_values(ascending=False).head(3))

         orders  avg_discount
month                        
2025-01      66          5.61
2025-02      67          5.90
2025-03     109          6.15
2025-04     131          6.22
2026-06 630
delivery_minutes
<15       695
15-25    2727
25-35    1928
35+       280
dtype: int64
order_date
Sunday      1079
Saturday    1042
Monday       796
dtype: int64

How it works: You can group by any Series of the same length, not only by columns: a period extracted from a date, a binned numeric column from pd.cut, or a weekday name. observed=True skips empty categories when grouping by a categorical series.

Reading the result: The busiest month is 2026-06 with 630 orders. Banding delivery minutes turns a number into categories you can count.

Example 5: transform

Question: Show each order's delivery time relative to its own store's average.

delivered = orders[orders["order_status"] == "Delivered"].copy()

delivered["store_avg"] = delivered.groupby("store_id")["delivery_minutes"].transform("mean")
delivered["vs_store"] = delivered["delivery_minutes"] - delivered["store_avg"]

print(delivered[["order_id", "store_id", "delivery_minutes", "store_avg", "vs_store"]].head(4).round(1))
print(len(delivered), delivered["store_avg"].nunique())

z = delivered.groupby("store_id")["delivery_minutes"].transform(lambda s: (s - s.mean()) / s.std())
print(z.abs().gt(3).sum())

  order_id store_id  delivery_minutes  store_avg  vs_store
0   O02299      S03              34.0       23.5      10.5
1   O04533      S07              24.0       23.5       0.5
2   O00778      S09              37.0       23.8      13.2
3   O03607      S02              29.0       24.0       5.0
5391 11
8

How it works: agg collapses each group to one row, but transform returns a result the same length as the original, repeating the group value on every row. That makes it ideal for "percent of group total", "difference from group mean" and z-scores per group. transform("mean") is the fast built-in form.

Reading the result: The store_avg column has only 11 distinct values (one per store) but 5,391 rows. 8 deliveries were more than three standard deviations from their store's norm.

Example 6: Share of total within a group

Question: What percent of each city's revenue does each category bring?

items = pd.read_csv("order_items.csv")
products = pd.read_csv("products.csv")

lines = (
    items.merge(products[["product_id", "category"]], on="product_id")
    .merge(orders[["order_id", "store_id", "order_status"]], on="order_id")
    .merge(stores[["store_id", "city"]], on="store_id")
)
lines = lines[lines["order_status"] == "Delivered"]
lines["value"] = lines["quantity"] * lines["unit_price"]

cat_city = lines.groupby(["city", "category"], as_index=False)["value"].sum()
cat_city["pct_of_city"] = cat_city["value"] / cat_city.groupby("city")["value"].transform("sum") * 100

top = cat_city.sort_values(["city", "pct_of_city"], ascending=[True, False]).groupby("city").head(1)
print(top.round(1).head(6).to_string(index=False))
print(cat_city.groupby("city")["pct_of_city"].sum().round(6).unique())

     city  category    value  pct_of_city
Ahmedabad Household  48457.0         21.7
Bengaluru Household 163683.0         22.0
  Chennai Household  59176.0         22.6
    Delhi Household 170004.0         22.8
Hyderabad Household 110722.0         25.8
  Kolkata Household  55888.0         23.4
[100.]

How it works: We join items to product, order and store information (the next lesson covers merges in depth), then group twice: first to total per city and category, then with transform("sum") to find each city's total on every row so we can divide. A groupby(...).head(1) after sorting picks the top row per group. as_index=False keeps keys as columns.

Reading the result: Within every city the shares add up to 100 percent, as they should. The leading category in Ahmedabad is Household at 21.7%.

Example 7: filter groups (HAVING)

Question: Keep only customers who placed at least 20 orders.

big = orders.groupby("customer_id").filter(lambda g: len(g) >= 20)
print(big["customer_id"].nunique(), len(big))

counts = orders.groupby("customer_id").size()
heavy = counts[counts >= 20]
print(len(heavy), heavy.sum())

by_city_count = orders.merge(stores[["store_id", "city"]], on="store_id").groupby("city").size()
print(by_city_count[by_city_count > 700].index.tolist())

60 2586
60 2586
['Bengaluru', 'Delhi', 'Mumbai']

How it works: groupby(...).filter(func) keeps entire groups for which the function returns True, returning the original rows. The equivalent in two steps is to compute the group sizes, filter the resulting Series, and use it to select. This is the pandas version of SQL's HAVING COUNT(*) >= 20.

Reading the result: 60 customers placed 20 or more orders, accounting for 2,586 of the 6,000 orders (43%). A small group of loyal customers drives a big share of volume.

Example 8: rank, cumsum and shift within groups

Question: Rank each store's days by orders, and compute a running total and day-over-day change.

daily = (
    orders.groupby(["store_id", "order_date"]).size().rename("orders").reset_index()
    .sort_values(["store_id", "order_date"])
)
daily["rank_in_store"] = daily.groupby("store_id")["orders"].rank(ascending=False, method="min")
daily["running_total"] = daily.groupby("store_id")["orders"].cumsum()
daily["prev"] = daily.groupby("store_id")["orders"].shift(1)
daily["change"] = daily["orders"] - daily["prev"]

print(daily.head(4).to_string(index=False))
best = daily[daily["rank_in_store"] == 1].sort_values("orders", ascending=False).head(3)
print(best[["store_id", "order_date", "orders"]].to_string(index=False))
print(daily.groupby("store_id")["running_total"].max().sum())

store_id order_date  orders  rank_in_store  running_total  prev  change
     S01 2025-01-03       1          184.0              1   NaN     NaN
     S01 2025-01-05       3           33.0              4   1.0     2.0
     S01 2025-01-06       1          184.0              5   3.0    -2.0
     S01 2025-01-10       1          184.0              6   1.0     0.0
store_id order_date  orders
     S07 2026-03-15       8
     S01 2026-01-26       7
     S06 2026-04-18       7
6000

How it works: Window-style functions inside groups act row by row but restart for each group: rank orders values within each store, cumsum accumulates, shift(1) looks at the previous row. This replaces SQL window functions like RANK() OVER (PARTITION BY store ...) and LAG. Remember to sort by the group and time before using shift or cumsum.

Reading the result: The first row of each store has no previous day, so prev and change are blank there. The busiest store-day was S07 on 2026-03-15 with 8 orders. The last running totals add to 6,000, the grand total of orders.

Example 9: Top N per group

Question: Find the three best-selling products in each category.

units = (
    items.merge(products[["product_id", "product_name", "category"]], on="product_id")
    .groupby(["category", "product_name"], as_index=False)["quantity"].sum()
)
top3 = units.sort_values(["category", "quantity"], ascending=[True, False]).groupby("category").head(3)
print(top3.head(6).to_string(index=False))

alt = units.groupby("category", group_keys=False).apply(lambda d: d.nlargest(3, "quantity"))
print(len(top3), len(alt))
print(units.groupby("category")["quantity"].agg(["sum", "max"]).head(3))

 category        product_name  quantity
Beverages Coconut Water 200ml       973
Beverages          Cola 750ml       513
Beverages    Masala Chai 500g       480
    Dairy         Paneer 200g      2540
    Dairy   Greek Yogurt 100g      1421
    Dairy           Curd 400g       797
21 21
               sum   max
category                
Beverages     3269   973
Dairy         6728  2540
Fruits & Veg  5088  2353

How it works: Sort by group then by value descending, and take groupby(...).head(n). The apply with nlargest is a longer alternative that gives the same rows. Top-N-per-group is one of the most common interview questions, and it is often written with window functions in SQL.

Reading the result: With 7 categories we get 21 rows (three per category). The best seller in the first category is Coconut Water 200ml with 973 units.

Example 10: Several statistics at once, and apply

Question: Summarise delivery times with a custom function per group.

def summary(s):
    s = s.dropna()
    return pd.Series({
        "n": len(s),
        "mean": s.mean(),
        "median": s.median(),
        "p95": s.quantile(0.95),
        "late_pct": (s > 30).mean() * 100,
    })

by_slot = orders.groupby("delivery_slot")["delivery_minutes"].apply(summary).unstack().round(2)
print(by_slot)
print(by_slot["late_pct"].idxmax())

fast = orders.groupby("delivery_slot")["delivery_minutes"].agg(["mean", "median", "std"]).round(2)
print(fast.sort_values("mean").head(2))

                     n   mean  median   p95  late_pct
delivery_slot                                        
Evening (4-8)   2090.0  23.73    24.0  35.0     15.98
Midday (10-2)   1026.0  23.80    24.0  36.0     16.37
Morning (6-10)  1229.0  23.67    24.0  35.0     15.30
Night (8-11)    1285.0  23.47    24.0  35.0     14.86
Midday (10-2)
                 mean  median   std
delivery_slot                      
Night (8-11)    23.47    24.0  6.91
Morning (6-10)  23.67    24.0  6.93

How it works: apply runs any function on each group and combines the results. When your function returns a Series, unstack() turns the output into a table with one row per group. Use apply for logic that built-in aggregations cannot express, but prefer agg and transform when possible because they are faster.

Reading the result: The slot with the highest share of late deliveries is Midday (10-2) at 16.4%, against 14.9% for the best slot.

Common mistakes

1. Mixing count and size. count ignores NaN, size counts rows.

2. Forgetting to reset_index and then being unable to merge or plot with the key.

3. Applying shift or cumsum without sorting first.

4. Grouping by a column that contains NaN, which drops those rows silently by default (dropna=False keeps them).

5. Using apply for everything when agg or transform would be much faster.

orders.groupby("store_id")["nonexistent"].mean()

Traceback (most recent call last):
  File "example.py", line 1, in <module>
    orders.groupby("store_id")["nonexistent"].mean()
    ~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^
  File "example.py", line 2938, in __getitem__
    return super().__getitem__(key)
           ~~~~~~~~~~~~~~~~~~~^^^^^
  File "example.py", line 228, in __getitem__
    raise KeyError(f"Column not found: {key}")
KeyError: 'Column not found: nonexistent'

Practice questions

Practice 1 (Easy): Orders by payment

Count orders per payment method and show each method's share as a percentage, largest first.

counts = orders.groupby("payment_method").size().sort_values(ascending=False)
print((counts / counts.sum() * 100).round(1))

payment_method
UPI       55.5
Card      20.4
COD       16.3
Wallet     7.8
dtype: float64

Practice 2 (Medium): Prime versus non-Prime

Join customers.csv to the orders and compare Prime and non-Prime customers on order count, average delivery time and average discount.

customers = pd.read_csv("customers.csv")
j = orders.merge(customers[["customer_id", "is_prime"]], on="customer_id")
cmp = j.groupby("is_prime").agg(
    orders=("order_id", "count"),
    customers=("customer_id", "nunique"),
    avg_minutes=("delivery_minutes", "mean"),
    avg_discount=("discount_pct", "mean"),
).round(2)
cmp["orders_per_customer"] = (cmp["orders"] / cmp["customers"]).round(1)
print(cmp)

          orders  customers  avg_minutes  avg_discount  orders_per_customer
is_prime                                                                   
False       4022        428        23.73          5.97                  9.4
True        1978        160        23.55          5.61                 12.4

Practice 3 (Hard): Month-over-month growth by store

Compute orders per store per month, then each store's month-over-month growth in percent, and report the store with the highest average growth.

m = orders.groupby(["store_id", "month"]).size().rename("orders").reset_index()
m = m.sort_values(["store_id", "month"])
m["growth_pct"] = m.groupby("store_id")["orders"].pct_change() * 100
avg_growth = m.groupby("store_id")["growth_pct"].mean().sort_values(ascending=False)
print(avg_growth.round(2).head(3))

store_id
S08    56.19
S04    39.20
S09    32.31
Name: growth_pct, dtype: float64

Key takeaways

• groupby splits rows into groups, applies a function and combines the results.
• Use named aggregation agg(new=("col", "func")) for tidy output.
• transform returns a result for every row, perfect for shares and z-scores.
• filter keeps whole groups (SQL HAVING); head, rank, cumsum, shift and pct_change work inside groups.
• Group by multiple keys, derived keys (dt.to_period, pd.cut) and unstack to build matrices.
• Always check size versus count, sort before sequence operations, and reset the index when you need columns.