Split, apply, combine

The idea

Almost every report is "something per something": shipping per channel, revenue per order, products per category. groupby answers all of them with the same three-step pattern, called split-apply-combine.

Split the rows into groups by the value of one column (or several). Apply a calculation to each group on its own, one that reduces it to a single value. Combine those values into a new table with one row per group.

The picture to keep is piles. groupby('channel') does not calculate anything. It sorts the rows into piles, one per channel, and remembers which pile each row is in. Only the next call, .sum(), .mean() or .size(), does arithmetic, and it does it inside each pile separately.

Why it exists

In plain Python this is the counting loop from chapter 2 with a dictionary of lists, written by hand for every question: make an empty dictionary, run through every row, add it to its pile, then loop over the piles. In SQL it is GROUP BY. In a spreadsheet it is a pivot table or a SUMIF per group. The idea is always the same. In pandas it is one line, and the result is a DataFrame you can sort, filter, chart or merge with another table.

The other reason is volume. The store has 30,000 order lines. Nobody can scan that for the biggest baskets, and nobody should have to: the whole job is "give each order a total, then look at the totals".

How it works under the hood

What the groups are made from

groupby takes one column name, or a list of them. With two keys, groupby(['channel', 'status']), a pile exists for every combination that really occurs in the data: marketplace and cancelled, web and completed, and so on. A combination that never happens simply has no pile. The key values become the index of the result, so a two-key result has two index levels. reset_index() turns them back into ordinary columns, which is usually what you want before sorting or merging.

What an aggregation is

An aggregation reduces many values to one: sum, mean, min, max, size, count, nunique. The choice decides what the question means. size() counts the rows in each pile, including those with blanks. count() counts the values that are present in one column. sum() adds them. For "how many orders per channel" use size. For "how many units sold per product" use sum on quantity, not count, because count returns the number of lines.

New column first, then group

Group the right thing. The store keeps quantity, unit price and discount on each line, not the money, so the first level of the chapter is to compute line_revenue with one vectorised expression, exactly as in the Foundations. Only then does grouping make sense.

Notice the grain. The source table has one row per order line, and the result has one row per order. Group by a different key and you get a different grain: by product_id gives revenue per product, by channel would need the order's channel first, and that column is in orders, not order_items. When a key lives in another table you merge first, which is chapter 7.

Named aggregation

.agg(revenue=('line_revenue', 'sum'), n_lines=('order_item_id', 'size')) computes several aggregations at once and names each result column as it is made. The shape is new_name=(column, function). You get exactly the column names you want, no multi-level headers to untangle, and the code documents itself: anyone can read what revenue is.

Filtering after aggregating

Filtering the rows before grouping and filtering the groups after are different questions. "Orders from the web" is a row filter, applied first. "Departments with at least seven people" is a group filter: you cannot know a department's size until you have counted it, so you aggregate first and then filter the summary table with a mask, as in chapter 5.

Read a chain like this from the top: each step takes the table the previous one made. If a number is wrong, run the chain one step at a time and look at the table after each, to find the step where it went wrong.

A check before you trust the table

Two quick checks catch most groupby mistakes. First, the total of the result should match the total of the source: result['revenue'].sum() against order_items['line_revenue'].sum(). If the numbers differ, rows were lost, usually because the key has missing values, which groupby drops by default. Second, the number of rows in the result should equal nunique() of the key. Compare, for example, the 12,000 orders in the store with the rows you get from grouping lines by order_id. A gap tells you something is off before anyone else notices.

Common misconceptions

"groupby calculates the average for me"

groupby alone returns an object that has not computed anything, and printing it shows something like <DataFrameGroupBy object>. The calculation comes from the method you call next. This also means the same groups can answer many questions: mean, sum and size are three different reductions of identical piles.

"count() and sum() are interchangeable"

count() returns how many values there are, and sum() adds them up. On a column of quantities, count() gives the number of lines and sum() gives units sold. A bestseller list built with count ranks products by how many orders mention them, not how many were sold, and it looks completely believable. State the question in words first (orders, lines, units, or money) and then pick the function.

"The group column is just another column in the result"

By default the grouping key becomes the index of the result, not a column. That is why a groupby result often seems to be "missing" its first column, and why result['channel'] raises a KeyError. Call reset_index() or pass as_index=False when you want the key as a plain column.