Group-by in pandas: split, apply, combine

Sixteen authored canteen orders hold 1410 in recorded amounts, but a default outlet summary loses 125. A group-by answers “for each X, what is Y?”: revenue per outlet, orders per day, the average mark per class. pandas does it in three steps, often called split-apply-combine. It splits the rows into groups by the values of one or more key columnsgroup key: The fields whose values place source rows into groups under an explicit policy for equality, missing keys and unused categories., applies a function such as a sum or a mean to each group, and combines the results into a new table with one row per group. The clearest way to write it is a named aggregationnamed aggregation: A grouped output definition that explicitly names its result column and identifies the input column and aggregation function., df.groupby("outlet").agg(revenue=("amount", "sum")): the name on the left becomes an output column, and the pair on the right says which column to read and which function to apply. Three details decide whether the result is right: rows whose key is missing, which pandas leaves out by default; the difference between counting rows, values and distinct values; and the shape of the result.

One scalar aggregation reduces each observed group

The authored fixture uses 16 orders for a college canteen with three named outlets. One order was saved without its outlet and one without its amount, two distinct gaps whose treatment the report needs to state.

order_id,outlet,slot,customer,amount
1,Library,breakfast,C01,60
2,Library,lunch,C02,120
3,Hostel,lunch,C01,150
4,Main gate,snacks,C03,40
5,Library,lunch,C04,110
6,Hostel,breakfast,C05,70
7,Main gate,lunch,C02,130
8,Hostel,snacks,C01,45
9,Library,snacks,C06,35
10,Main gate,breakfast,C03,55
11,,lunch,C07,125
12,Hostel,lunch,C05,
13,Main gate,lunch,C08,140
14,Library,lunch,C02,115
15,Hostel,lunch,C09,165
16,Main gate,snacks,C03,50

import pandas as pd

orders = pd.read_csv("orders.csv")  # 16 canteen orders; one has no outlet, one has no amount

summary = orders.groupby("outlet", as_index=False).agg(
    orders=("order_id", "size"),     # rows in the group
    revenue=("amount", "sum"),       # missing amounts are skipped
    avg_basket=("amount", "mean"),
)
print(summary.round(1))
print()
print("revenue in the summary:", summary["revenue"].sum())
print("revenue in the table:  ", orders["amount"].sum())

      outlet  orders  revenue  avg_basket
0     Hostel       5    430.0       107.5
1    Library       5    440.0        88.0
2  Main gate       5    415.0        83.0

revenue in the summary: 1285.0
revenue in the table:   1410.0

Each output column comes from one pair. orders=("order_id", "size") counts the rows of each group, revenue=("amount", "sum") adds the amounts and avg_basket=("amount", "mean") averages them. as_index=False keeps the outlet as an ordinary column, so the result can be merged, sorted or saved like any table; without it, the keys become the index. Hostel’s average is 107.5, which is 430 divided by 4, not by 5: like most aggregations, sum and mean skip missing values.

The last two lines are the check that matters. The summary’s revenue is 1,285, but the table holds 1,410, and the missing 125 belongs to order 11, the order without an outlet.

An explicit missing-key policy restores the 125

By default, groupby drops every row whose key is missing (dropna=True), and nothing warns you. Two changes keep the row:

import pandas as pd

orders = pd.read_csv("orders.csv")
print("order 11 has no outlet:", orders.loc[orders["order_id"] == 11, "outlet"].isna().item())
print()

print(orders.groupby("outlet")["amount"].sum())                # default: the order is left out
print()
print(orders.groupby("outlet", dropna=False)["amount"].sum())  # a group for the missing key
print()
labelled = orders.fillna({"outlet": "Unknown"})                 # a name a report can show
print(labelled.groupby("outlet")["amount"].sum())

order 11 has no outlet: True

outlet
Hostel       430.0
Library      440.0
Main gate    415.0
Name: amount, dtype: float64

outlet
Hostel       430.0
Library      440.0
Main gate    415.0
NaN          125.0
Name: amount, dtype: float64

outlet
Hostel       430.0
Library      440.0
Main gate    415.0
Unknown      125.0
Name: amount, dtype: float64

dropna=False gives the missing key a group of its own, shown as NaN, and the totals reconcile again. Filling the key first with “Unknown” gives it a readable name in this fixture, which has no genuine outlet of that name. In a different source, a real “Unknown” label would collide with that sentinel; preserve a missing-key flag or keep a separate null group. polars and DuckDB’s GROUP BY keep a null group by default, so a pandas summary written without dropna=False can quietly disagree with the same query in SQL. Columns of the category type follow a related rule in pandas 3: only the categories that occur get a row by default; observed=False can include declared unused categories. Sort explicitly before comparing either engine with pandas.

Rows, recorded amounts and known IDs count different things

Counting is where most summaries go wrong, because three functions count three different things:

import pandas as pd

orders = pd.read_csv("orders.csv")
counts = orders.groupby("outlet").agg(
    rows=("order_id", "size"),        # every row of the group
    priced=("amount", "count"),       # rows whose amount is not missing
    customers=("customer", "nunique"),  # distinct recorded customer IDs
)
print(counts)

           rows  priced  customers
outlet                            
Hostel        5       4          3
Library       5       5          4
Main gate     5       5          3

For Hostel, size says 5 orders and count of the amount says 4, because order 12 has no amount. nunique of customer gives 3 distinct recorded IDs: C01 and C05 each occur twice. That is an ID count; the rows alone do not prove how many different people those IDs represent.

Pick the one that answers the question: orders per outlet is size, priced orders is count, and distinct known customer IDs is nunique.

Output keys and names make a summary usable

What you get back depends on how you ask. orders.groupby("outlet")["amount"].sum() selects one column and returns a Series, with the outlets as its index, as in the missing-key example. .agg(...) with named aggregations returns a DataFrame with one column per name, and with as_index=False the keys come back as columns too, numbered rows and all. A list of keys, groupby(["outlet", "slot"]), makes one group per combination of values that occurs, and its keys become a two-level index unless as_index=False turns them into two columns. For anything that goes on to a merge, a chart or a file, ask for the flat shape: a DataFrame with plain columns. A dictionary of lists, such as {"amount": ["sum", "mean"]}, also works, but it returns two levels of column names that need flattening first.

Practise the reading core

In missing_outlet.py, add a genuine outlet named Unknown; compare null-preserving groups with sentinel-filled groups.

The same summary in pandas, polars and DuckDB, compared row for row Pro: opens with a Pro, Premium or Ultimate pass

Reporting-ready summaries: two keys, flat names and totals that reconcile Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• groupby(...).agg(name=(column, function)) gives one row per group with columns you name; as_index=False keeps the keys as ordinary columns.
• groupby leaves out rows with a missing key unless you pass dropna=False or fill the key first.
• size counts rows, count counts values that are not missing, and nunique counts different values.
• Check every summary against its source: the group totals must add up to the table’s.