Chapter 6 · 6 levels

New columns, groupby and aggregation

Compute new columns like line revenue, then summarise by group with groupby, agg and named aggregation, and filter groups after aggregating.

Start

This is the chapter where pandas starts paying for itself. Almost every report is “something per something”: revenue per order, average shipping fee per channel, product count per category, salary per department. In pandas that’s groupby: split the rows into groups, apply a calculation to each group, and combine the results into a new table.

Before grouping you usually need the right column to aggregate. Harbor & Pine’s order lines don’t store revenue, so you’ll compute it from quantity, price and discount, which is the first level.

Then you’ll aggregate one column, several columns with named aggregation (the modern, readable way to get exactly the column names you want), group by two keys, and filter groups after aggregating. The boss level builds revenue per order and finds the store’s biggest baskets.

Levels

  1. Compute line revenue

    from Laura Bennett, Head of Finance

    new column, assign, vectorised arithmetic, round

    Code

  2. Average shipping fee per channel

    from Daniel Brooks, Head of Operations

    groupby, mean, size, count, split apply combine

    Code

  3. A category summary table

    from Thomas Whitaker, Head of Merchandising

    named aggregation, agg, as index, multiple aggregations

    Code

  4. Orders by channel and status

    from Angela Rivera, Head of Customer Support

    groupby multiple keys, size, reset index, unstack

    Code

  5. Departments with at least seven people

    from Laura Bennett, Head of Finance

    groupby, named aggregation, filter after aggregation, sort values

    Code

  6. Boss: find the biggest baskets

    from Megan Collins, Head of Sales

    new column, groupby, named aggregation, sort values, boolean mask

    Boss · 3 steps