groupby and aggregation

Why it matters

"What is the average yield per production line?" "Total revenue by region and month?" "How many samples did each analyst run?" Questions like these are split-apply-combineThe pattern of splitting rows into groups, applying a calculation to each group and combining the results into a table.: split the rows into groups, apply a calculation to each group, and combine the answers into a table. It is the single most useful pattern in analysis, and in pandas it is groupby. If you know SQL, it is GROUP BY.

We will use this table of production batches:

batches = pd.DataFrame({
    "batch_id":  ["B1", "B2", "B3", "B4", "B5", "B6"],
    "line":      ["L1", "L2", "L1", "L3", "L2", "L1"],
    "shift":     ["day", "night", "night", "day", "day", "day"],
    "yield_pct": [94.0, 88.5, 91.0, 96.5, 90.0, 93.0],
    "defects":   [2, 7, 4, 1, 5, 3],
})

One group, one statistic

batches.groupby("line")["yield_pct"].mean()
# line
# L1    92.666667
# L2    89.250000
# L3    96.500000

Read it as: group by line, take the yield_pct column, compute the mean for each group. The result is a Series indexed by the group values. Common aggregations: sum, mean, median, min, max, count (non-missing values), size (rows, including missing), nunique, std.

Several statistics at once: named aggregation

.agg with new_name=(column, function) pairs gives a tidy summary table with clear column names:

summary = batches.groupby("line").agg(
    batches=("batch_id", "count"),
    mean_yield=("yield_pct", "mean"),
    total_defects=("defects", "sum"),
)
#       batches  mean_yield  total_defects
# line
# L1          3   92.666667              9
# L2          2   89.250000             12
# L3          1   96.500000              1

Now you can sort it, round it or plot it: summary.sort_values("total_defects", ascending=False).round(1).

Grouping by more than one column

Pass a list of columns:

batches.groupby(["line", "shift"])["defects"].sum()

The result has a two-level index (a MultiIndexA pandas index with more than one level, produced for example by grouping on two columns.). Two ways to make it easier to work with:

# back to ordinary columns
by_line_shift = batches.groupby(["line", "shift"], as_index=False)["defects"].sum()
#   line  shift  defects
# 0   L1    day        5
# 1   L1  night        4
# ...

# or spread one level into columns
batches.groupby(["line", "shift"])["defects"].sum().unstack(fill_value=0)
# shift  day  night
# line
# L1       5      4
# L2       5      7
# L3       1      0

pivot_table does the same in one call and will feel familiar from spreadsheets:

batches.pivot_table(index="line", columns="shift", values="defects", aggfunc="sum", fill_value=0)

Adding a group statistic to every row

Sometimes you want each row compared with its group, for example "how far is this batch from its line's average?" transform returns a result the same length as the original, aligned row by row:

batches["line_mean"] = batches.groupby("line")["yield_pct"].transform("mean")
batches["vs_line"] = batches["yield_pct"] - batches["line_mean"]

Finding the top group

totals = batches.groupby("line")["defects"].sum()
totals.idxmax()   # 'L2', the label of the largest value
totals.max()      # 12

Common mistakes

• count vs size. count skips missing values; size counts rows. Pick the one you mean.
• Averaging averages. The mean of per-line means is not the overall mean when groups have different sizes. Compute from the rows.
• Forgetting that the group column becomes the index. Use as_index=False or .reset_index() when you need it as a column.
• Grouping by a messy text column. "L1" and "l1 " are different groups; clean first.
• Reporting a group mean from one or two rows without saying so. Show the count next to the mean.

Quick recap

• df.groupby("col")["value"].agg_function() = split, apply, combine.
• Named aggregationPassing new_name=(column, function) pairs to .agg so a summary table gets clear column names. (.agg(new=("col", "func"))) builds clear summary tables.
• Group by a list for several levels; as_index=False, unstack or pivot_table to reshape.
• transform puts a group statistic on every row; idxmax finds the top group.

Try it

In the Do tab you will summarise production batches by line: mean yield, a named-aggregation summary, defects by line and shift, and the line with the most defects.