SyntaxPilot
Courses/Data Analytics with Python/Lesson 24

Data Analytics with Python · Lesson 24 of 90 · 10 min

GroupBy 1: split-apply-combine and aggregation

split-apply-combinegroupbyaggnamed aggregationmultiple aggregation functionssize vs countas_indexsort=Falsegrouping by multiple columnsreset_index after groupby

These are the notes for Data Analytics with Python, episode 24. The data is clean; now we ask it questions. The most common business question is "how much, per something?": sales per item, orders per city, visitors per day. Here you invent split-apply-combine, use groupby with sum, mean and count, run several functions with agg, name the results with named aggregation, learn size vs count, group by several columns, and turn results back into plain tables with as_index=False and reset_index, plus sort=False. Every term is defined the first time it appears and collected in the Glossary. Every program below was run for real (Python 3.13, pandas 3.0.5); outputs are unedited.

The data: two stalls, ten orders

Ramesh now has stalls in Pune and Nashik. Order 7 has no amount recorded (a missing value).

import sys; sys.tracebacklimit = 0
import pandas as pd, io
raw = """order_id,city,item,qty,amount
1,Pune,Chai,2,40
2,Pune,Samosa,3,45
3,Nashik,Chai,1,20
4,Pune,Coffee,1,30
5,Nashik,Samosa,2,30
6,Pune,Chai,4,80
7,Nashik,Coffee,2,
8,Pune,Vada Pav,2,50
9,Nashik,Chai,3,60
10,Pune,Samosa,1,15
"""
orders = pd.read_csv(io.StringIO(raw))

The obvious way: one filter per item

His accountant asks: how much did each item sell? With filtering (episode 15) you'd loop over the items you remember:

found = 0
for item in ["Chai", "Samosa", "Coffee"]:
    total = orders.loc[orders["item"] == item, "amount"].sum()
    print(item, total)
    found += total
print("items:", found, " whole table:", orders["amount"].sum())
Chai 200.0
Samosa 90.0
Coffee 30.0
items: 320.0  whole table: 370.0

320 ≠ 370: Vada Pav (started last week) is missing from the hard-coded list. A hard-coded list of groups goes stale the day the business changes, and 40 items × 5 cities would need 200 filters. We want the data itself to decide the piles.

Split, apply, combine

Real-life picture: a teacher with a stack of answer sheets from several sections.

Step Teacher pandas
Split sort sheets into piles by section, whatever sections appear orders.groupby("item")
Apply add up the marks in each pile ["amount"].sum()
Combine write one line per section on the board one row per group in the result

This pattern is called split-apply-combine. In pandas it's groupby. Each pile is a group; the column that decides the piles is the key (grouping key).

groupby with sum, mean, count

print(orders.groupby("item")["amount"].sum())
item
Chai        200.0
Coffee       30.0
Samosa       90.0
Vada Pav     50.0
Name: amount, dtype: float64

All four items, including Vada Pav, with nobody remembering it. The result is a Series whose index is the group keys, sorted alphabetically. Turning many values into one number per group is called aggregation; sum is an aggregation function. The sum skips missing values.

Several functions: agg

print(orders.groupby("item")["amount"].agg(["sum", "mean", "count"]))
            sum  mean  count
item                        
Chai      200.0  50.0      4
Coffee     30.0  30.0      1
Samosa     90.0  30.0      3
Vada Pav   50.0  50.0      1

One row per group, one column per function. Function names must be ones pandas knows:

print(orders.groupby("item")["amount"].agg("average"))
AttributeError: 'SeriesGroupBy' object has no attribute 'average'

Common aggregation functions:

Name Gives
"sum" total
"mean" average (arithmetic mean)
"median" middle value
"min", "max" smallest, largest
"count" number of non-missing values
"size" number of rows
"std" standard deviation
"nunique" number of distinct values
"first", "last" first / last value in each group

Named aggregation

A report needs clear column names and different functions for different columns. Named aggregation: inside agg, write new_name=("column", "function").

report = orders.groupby("item").agg(
    total=("amount", "sum"),
    orders=("order_id", "count"),
    cups=("qty", "sum"))
print(report)
          total  orders  cups
item                         
Chai      200.0       4    10
Coffee     30.0       2     3
Samosa     90.0       3     6
Vada Pav   50.0       1     2

Coffee shows 2 orders here, but count of amount said 1. Why?

agg with a dictionary (older style)

You'll see this in other people's code: a dictionary mapping each column to a function or a list of functions.

r = orders.groupby("city").agg({"amount": ["sum", "mean"], "qty": "sum"})
print(r.round(1))
       amount       qty
          sum  mean sum
city                   
Nashik  110.0  36.7   8
Pune    260.0  43.3  13

It works, but the result has a two-level column header (amount above sum/mean), which is awkward to select and export. Prefer named aggregation: one flat row of names you chose.

size vs count

g = orders.groupby("item")
print(pd.DataFrame({"size": g.size(), "count": g["amount"].count()}))
          size  count
item                 
Chai         4      4
Coffee       2      1
Samosa       3      3
Vada Pav     1      1
  • size counts rows in each group (missing values included).
  • count counts non-missing values in a column.

Order 7 (Coffee) has no amount, so size 2, count 1. The gap is a free missing-value report.

Don't aggregate everything

print(orders.groupby("item").sum())
          order_id                  city  qty  amount
item                                                 
Chai            19  PuneNashikPuneNashik   10   200.0
Coffee          11            PuneNashik    3    30.0
Samosa          17        PuneNashikPune    6    90.0
Vada Pav         8                  Pune    2    50.0

No error, but order_id summed to meaningless numbers, and adding text glues it together ("PuneNashikPune"). Select the columns that make sense to aggregate first.

Grouping by several columns

Pass a list of keys; each group is a combination:

print(orders.groupby(["city", "item"])["amount"].sum())
city    item    
Nashik  Chai         80.0
        Coffee        0.0
        Samosa       30.0
Pune    Chai        120.0
        Coffee       30.0
        Samosa       60.0
        Vada Pav     50.0
Name: amount, dtype: float64

The index now has two levels (city, item): a MultiIndex, covered in episode 25. Look at Nashik Coffee: 0.0. Its only amount is NaN, and the sum of no values is 0, which is a lie: the amount is unknown, not zero. min_count=1 means "need at least one real value, otherwise NaN":

print(orders.groupby(["city", "item"])["amount"].sum(min_count=1))
city    item    
Nashik  Chai         80.0
        Coffee        NaN
        Samosa       30.0
Pune    Chai        120.0
        Coffee       30.0
        Samosa       60.0
        Vada Pav     50.0
Name: amount, dtype: float64

Grouping by a calculated key

The key doesn't have to be a column of the table: any Series with one value per row works. Do big orders (3 items or more) spend more?

size = orders["qty"].ge(3).map({True: "3+ items", False: "1-2 items"})
print(orders.groupby(size)["amount"].agg(["count", "mean"]).round(1))
print(orders.groupby("city")["item"].nunique())
           count  mean
qty                   
1-2 items      6  30.8
3+ items       3  61.7
city
Nashik    3
Pune      4
Name: item, dtype: int64

ge(3) means "greater than or equal to 3" (same as >= 3), and map turns True/False into labels. count is 6 for the seven small orders because one amount is missing. nunique counts distinct values: Pune sells 4 different items, Nashik 3. With episode 22's tools, groupby(df["ordered"].dt.day_name()) gives sales per weekday and groupby(df["ordered"].dt.to_period("M")) sales per month.

as_index=False, reset_index and sort=False

Group keys go into the index. For charts, merges or Excel exports you usually want them as ordinary columns: use as_index=False inside groupby, or reset_index() afterwards (episode 12).

t = orders.groupby(["city", "item"], as_index=False)["amount"].sum(min_count=1)
print(t)
print(orders.groupby("city")["amount"].sum().reset_index())
     city      item  amount
0  Nashik      Chai    80.0
1  Nashik    Coffee     NaN
2  Nashik    Samosa    30.0
3    Pune      Chai   120.0
4    Pune    Coffee    30.0
5    Pune    Samosa    60.0
6    Pune  Vada Pav    50.0
     city  amount
0  Nashik   110.0
1    Pune   260.0

groupby sorts groups by key; sort=False keeps the order in which they first appear (and is slightly faster on big data):

print(orders.groupby("item")["amount"].sum().index.tolist())
print(orders.groupby("item", sort=False)["amount"].sum().index.tolist())
['Chai', 'Coffee', 'Samosa', 'Vada Pav']
['Chai', 'Samosa', 'Coffee', 'Vada Pav']

Keys must match column names exactly, including capitals:

print(orders.groupby("Item")["amount"].sum())
KeyError: 'Item'

Mini project: item_report()

def item_report(df):
    r = df.groupby("item", as_index=False).agg(
        orders=("order_id", "size"),
        cups=("qty", "sum"),
        revenue=("amount", "sum"),
        avg_order=("amount", "mean"))
    r["share_%"] = (100 * r["revenue"] / r["revenue"].sum()).round(1)
    return r.sort_values("revenue", ascending=False)
print(item_report(orders))
       item  orders  cups  revenue  avg_order  share_%
0      Chai       4    10    200.0       50.0     54.1
2    Samosa       3     6     90.0       30.0     24.3
3  Vada Pav       1     2     50.0       50.0     13.5
1    Coffee       2     3     30.0       30.0      8.1

Chai brings 54.1% of revenue. Coffee's 2 orders but only 30 rupees shows the missing amount instead of hiding it. The share column divides each group's revenue by the grand total: a derived column after aggregation.

Common mistakes

Code / habit Real error / symptom Why Fix
A loop over a hard-coded list of items no error; 320 of 370 rupees the list forgot Vada Pav groupby("item")
.agg("average") AttributeError: 'SeriesGroupBy' object has no attribute 'average' the function is mean .agg("mean")
orders.groupby("item").sum() no error; city = "PuneNashikPune" text columns get glued select columns: ["amount"]
count to count orders Coffee shows 1 instead of 2 count skips NaN size for rows
.sum() on an all-NaN group shows 0.0 the sum of no values is 0 .sum(min_count=1)
groupby("Item") KeyError: 'Item' names are case-sensitive groupby("item")
Keys left in the index for export key column missing in Excel/merge keys are the index as_index=False / reset_index()

Practice

All practice questions use this marks table:

import pandas as pd, io
raw = """student,section,subject,marks
Asha,A,Maths,78
Ravi,A,Maths,64
Meena,B,Maths,91
Asha,A,Science,82
Ravi,A,Science,
Meena,B,Science,88
Kiran,B,Maths,55
"""
m = pd.read_csv(io.StringIO(raw))

1. Average marks per section, rounded to 1 decimal.

Solution
print(m.groupby("section")["marks"].mean().round(1))
section
A    74.7
B    78.0
Name: marks, dtype: float64

2. Min, max and median marks per subject.

Solution
print(m.groupby("subject")["marks"].agg(["min", "max", "median"]))
          min   max  median
subject                    
Maths    55.0  91.0    71.0
Science  82.0  88.0    85.0

3. Per section: how many rows, and how many have marks?

Solution
g = m.groupby("section"); print(pd.DataFrame({"rows": g.size(), "marked": g["marks"].count()}))
         rows  marked
section              
A           4       3
B           3       3

Section A has one unmarked paper (Ravi, Science).

4. A plain table per section and subject with the average mark and the number of distinct students.

Solution
print(m.groupby(["section", "subject"], as_index=False).agg(avg=("marks", "mean"), students=("student", "nunique")))
  section  subject   avg  students
0       A    Maths  71.0         2
1       A  Science  82.0         2
2       B    Maths  73.0         2
3       B  Science  88.0         1

5. Total marks per student, highest first, without turning a missing mark into 0.

Solution
print(m.groupby("student")["marks"].sum(min_count=1).sort_values(ascending=False))
student
Meena    179.0
Asha     160.0
Ravi      64.0
Kiran     55.0
Name: marks, dtype: float64

Ravi still has 64, because one real mark is enough for min_count=1. Comparing totals is unfair when students have different numbers of papers: the mean is often better.

Quick quiz

df = pd.DataFrame({"k": list("aab"),
                   "v": [1, None, 3]})
g = df.groupby("k")
s, c = g.size(), g["v"].count()
print(s["a"], c["a"])

A. 2 2 B. 2 1 C. 1 1 D. 1 2

Answer

B. 2 1. Group a has 2 rows (size), but one v is missing, so count is 1.

Interview questions

  1. What is split-apply-combine? Split the data into groups by a key, apply a function to each group, combine the results into one table. groupby implements it.
  2. What does df.groupby("city")["sales"].sum() return? A Series indexed by city with the total sales of each city.
  3. size vs count? size counts rows per group including missing values; count counts non-missing values per column.
  4. What is named aggregation? agg(new_name=("column", "func"), ...): choose the output column names and a function per column.
  5. How do you get group keys as columns instead of the index? as_index=False in groupby, or .reset_index() afterwards.
  6. What happens when you group by two columns? Each unique combination becomes a group; the result has a two-level MultiIndex (unless as_index=False).
  7. Why can groupby().sum() be misleading? It may aggregate columns that shouldn't be summed (ids, text glued together), and an all-missing group sums to 0 unless min_count=1.
  8. What does sort=False do? Keeps groups in order of first appearance instead of sorting by key; slightly faster.
  9. How do you compute each group's share of the total? Aggregate, then divide each group's value by the column total (r["rev"] / r["rev"].sum()).
  10. SQL equivalent of groupby? SELECT city, SUM(amount) FROM orders GROUP BY city.

Glossary

Term Meaning Example
Split-apply-combine Split into groups, apply a function to each, combine the results sales per item
groupby pandas' split-apply-combine orders.groupby("item")
Group The rows sharing one key value all Chai rows
Key (grouping key) The column whose values decide the groups "item"
Aggregation Turning many values into one number per group total per item
Aggregation function A function that does the aggregating sum, mean, count
agg Run one or several aggregation functions agg(["sum", "mean"])
Named aggregation agg(name=("column", "func")) with your column names total=("amount", "sum")
size Rows per group, missing values included Coffee → 2
count Non-missing values per group Coffee amount → 1
nunique Number of distinct values Pune sells 4 items
Calculated key A Series you compute (one value per row) used as the grouping key big vs small orders
agg with a dict {"column": func or [funcs]}; gives a two-level header {"amount": ["sum", "mean"]}
min_count=1 A sum needs at least one real value, else NaN Nashik Coffee → NaN
MultiIndex An index with more than one level (city, item)
as_index=False Keep group keys as ordinary columns plain table
reset_index() Move the index back into columns after groupby
sort=False Keep groups in first-seen order Chai, Samosa, Coffee…
Share A group's value divided by the total, often in % Chai 54.1%

Summary

  • One filter per group misses new groups; groupby lets the data decide the groups.
  • Split-apply-combine: df.groupby(key)[column].function().
  • agg runs several functions; named aggregation gives clear names and per-column functions.
  • size counts rows, count counts non-missing values.
  • Select columns before aggregating; use min_count=1 for honest sums.
  • Group by a list for combinations; as_index=False / reset_index for a plain table; sort=False for first-seen order.
  • Next episode: GroupBy 2, transform, filter and MultiIndex.