\( \newcommand{\vect}[1]{\boldsymbol{\mathbf{#1}}} \newcommand{\mat}[1]{\boldsymbol{\mathbf{#1}}} \newcommand{\R}{\mathbb{R}} \newcommand{\E}{\mathbb{E}} \newcommand{\Prob}{\mathbb{P}} \newcommand{\T}{^{\top}} \newcommand{\norm}[1]{\lVert #1 \rVert} \newcommand{\abs}[1]{\lvert #1 \rvert} \newcommand{\ihat}{\mkern1mu\hat{\boldsymbol{\imath}}\mkern2mu} \newcommand{\jhat}{\mkern1mu\hat{\boldsymbol{\jmath}}\mkern2mu} \newcommand{\pd}[2]{\frac{\partial #1}{\partial #2}} \newcommand{\argmax}{\operatorname*{arg\,max}} \newcommand{\argmin}{\operatorname*{arg\,min}} \newcommand{\cdata}[1]{\class{c-data}{#1}} \newcommand{\cmodel}[1]{\class{c-model}{#1}} \newcommand{\ctruth}[1]{\class{c-truth}{#1}} \newcommand{\cgrad}[1]{\class{c-grad}{#1}} \newcommand{\closs}[1]{\class{c-loss}{#1}} \newcommand{\cparam}[1]{\class{c-param}{#1}} \newcommand{\csecond}[1]{\class{c-second}{#1}} \renewcommand{\,}{\mkern3mu} \)

P02.6 · Group-by and aggregation

A free, illustrated module of 4 lessons and one interactive in P02 Scientific Python: NumPy, pandas and Plotting: Split, apply, combine; Several summaries, several keys; Transform: a group summary on every row; Pivot tables and cross-tabulation.

P02.6 L1 · Core 4 lessons · about 100 min Live · in review

Part of P02 Scientific Python: NumPy, pandas and Plotting in the Programming and Data track.

One operation answers “a number per group”: split the rows by a key, apply a summary to each group, combine the answers. These four lessons do it by hand on the weather readings and then with pandas: one summary and then several, one key and then two, a summary sent back to every row with transform, and the two-key results laid out as pivot tables and cross-tabulations, ending on the confusion matrix and the iris species.

What you need first. Arrays, reductions along an axis and broadcasting (P02.1, P02.2), Series, DataFrames, loc and label alignment (P02.4), and the long table readings built with melt (P02.5.3). Dictionaries of lists from P01.6 are used for every hand-made version.

Lessons

1. Split, apply, combine P02.6.1 25 min

   A group-by is three steps: split the rows into one pile per key, apply a summary to each pile, combine the answers into one table. Do all three by hand on the weather readings with a dict of lists, then in one line of pandas, and learn why the keys come back sorted.

2. Several summaries, several keys P02.6.2 25 min

   One number per group is rarely enough: a mean needs its count beside it. Ask a group-by for several summaries with agg and named aggregation, group by two columns at once, flatten the two-level result with reset_index, and meet value_counts, the class balance to check before training.

3. Transform: a group summary on every row P02.6.3 25 min

   An aggregate gives one number per group; transform sends that number back to every row of the group. Build it in two passes with a dict, compute each reading’s anomaly, z-score and share of its group, see that it matches broadcasting on the wide table, and avoid the leak of group features.

4. Pivot tables and cross-tabulation P02.6.4 25 min

   A group-by on two keys reads best as a grid: one key down the side, the other across the top. Build it with unstack, then with pivot_table and its margins, count pairs with crosstab, see that a confusion matrix is a crosstab, and close on the mean petal length of each iris species.

The interactive

P02.6-X1 · Split-apply-combine machine. The 12 readings with a choice of key (station, sky or day) and of summary (count, sum, mean, max). Step through split, apply and combine, and switch from aggregate to transform to send each group’s number back to every one of its rows. It lives in Split, apply, combine.