7  Summarizing by group

How much did each product bring in over the year? The question is not about any row; it is about the rows that belong together. summarize collapses rows into answers: each new column is an aggregation, and by names the columns that say which rows go together.

run('
  sales
    then summarize [revenue] as total([revenue]),
         [orders] as row_count() by [product]
')
product revenue orders
Doohickey 400 3
Gadget 720 4
Sprocket 240 2
Widget 700 6 
sales |>
  summarize(revenue = total(revenue), orders = row_count(), by = product)
product revenue orders
Doohickey 400 3
Gadget 720 4
Sprocket 240 2
Widget 700 6 
(sales
  >> summarize(revenue = total(col.revenue), orders = row_count(),
               by = col.product))
product revenue orders
Doohickey 400 3
Gadget 720 4
Sprocket 240 2
Widget 700 6

sales then summarize [revenue] as total([revenue]), [orders] as row_count() by [product]

Fifteen rows in, four out, one per product. This is the verb that turns a record of what happened into a statement about it, and it is the single most common reason anyone opens a table.

7.1 Several groups, or none

Grouping by several columns is a list, and every combination that appears in the data gets a row:

run('
  sales
    then summarize [revenue] as total([revenue]) by [region, product]
')
region product revenue
East Doohickey 200
East Gadget 180
East Sprocket 150
East Widget 200
North Doohickey 80
North Gadget 240
North Widget 225
West Doohickey 120
West Gadget 300
West Sprocket 90
West Widget 275 
sales |> summarize(revenue = total(revenue), by = c(region, product))
region product revenue
East Doohickey 200
East Gadget 180
East Sprocket 150
East Widget 200
North Doohickey 80
North Gadget 240
North Widget 225
West Doohickey 120
West Gadget 300
West Sprocket 90
West Widget 275 
(sales
  >> summarize(revenue = total(col.revenue),
               by = [col.region, col.product]))
region product revenue
East Doohickey 200
East Gadget 180
East Sprocket 150
East Widget 200
North Doohickey 80
North Gadget 240
North Widget 225
West Doohickey 120
West Gadget 300
West Sprocket 90
West Widget 275

Without by, the whole table is one group, and the answer is one row:

run('
  sales
    then summarize [biggest] as largest([revenue]),
         [average] as average([cost])
')
biggest average
300 80.13333 
sales |> summarize(biggest = largest(revenue), average = average(cost))
biggest average
300 80.13333 
(sales
  >> summarize(biggest = largest(col.revenue), average = average(col.cost)))
biggest average
300 80.13333

7.2 Grouping is an argument, not a state

In this grammar, grouping is a word inside the step it affects. It is not a mode you switch the table into. Nothing needs ungrouping afterwards, no verb behaves differently because of what an earlier verb declared, and a pipeline read a year from now says everything it does, where it does it. dplyr readers will recognize the failure this avoids: a group_by() three steps up silently changing what summarise() and mutate() mean, and the ritual ungroup() that follows every recipe. Here there is nothing to forget, because there is nothing being remembered.

7.3 One spelling where you have learned five

This one operation is where the world’s tools disagree the loudest. Group and total, five ways:

dplyr       df |> group_by(g) |> summarise(total = sum(x))
pandas      df.groupby('g')['x'].sum().reset_index()
polars      df.group_by('g').agg(pl.col('x').sum().alias('total'))
data.table  df[, .(total = sum(x)), by = g]
SQL         SELECT g, SUM(x) AS total FROM df GROUP BY g

Every one of these describes the same act: split the rows by a key, apply a collapse, combine the answers. The act is aggregation over a partition, older than the relational model itself (Codd, 1970), and the R world later named the pattern split-apply-combine (Wickham, 2011). The ideas have been stable for fifty years. The spelling is what churns, and the spelling is what you forget. god’s claim is that summarize ... by ..., read aloud, is the spelling you will still have when the others have faded, because it is the one that says what the operation does in the words you would use to describe it.

7.4 The eleven ways to collapse a group

total and average are the two you will use most. There are eleven aggregations altogether, and the list is closed on purpose: a small shelf you can hold whole beats a long one you have to search.

total, average and median combine numbers into one, and standard_deviation says how spread out they are. It is the sample deviation, which is what R, pandas, polars and both SQL engines all mean by their own bare word. The answer does not change with the engine. smallest and largest take the ends. first and last take a value by position, which is why they want the rows in a known order. row_count counts the rows and takes no column at all. unique_count counts how many different values a column holds. join_rows joins a group’s text values into one cell, with a separator you choose; tidying text teaches it.

run('
  sales
    then summarize [cheapest] as smallest([cost]),
         [dearest] as largest([cost]), [middle] as median([revenue]),
         [spread] as standard_deviation([revenue]),
         [kinds] as unique_count([product]) by [region]
')
region cheapest dearest middle spread kinds
East 40 125 150 58.99152 4
North 30 160 115 77.17675 3
West 20 200 110 87.08712 4 
sales |> summarize(
  cheapest = smallest(cost),
  dearest  = largest(cost),
  middle   = median(revenue),
  spread   = standard_deviation(revenue),
  kinds    = unique_count(product),
  by = region
)
region cheapest dearest middle spread kinds
East 40 125 150 58.99152 4
North 30 160 115 77.17675 3
West 20 200 110 87.08712 4 
sales >> summarize(
  cheapest = smallest(col.cost),
  dearest  = largest(col.cost),
  middle   = median(col.revenue),
  spread   = standard_deviation(col.revenue),
  kinds    = unique_count(col.product),
  by = col.region
)
region cheapest dearest middle spread kinds
East 40 125 150 58.99152 4
North 30 160 115 77.17675 3
West 20 200 110 87.08712 4

first and last answer by position, so a sort in front of them decides which row they mean:

run('
  sales
    then sort [revenue] descending
    then summarize [best] as first([product]), [worst] as last([product])
         by [region]
')
region best worst
East Widget Gadget
North Gadget Widget
West Gadget Widget 
sales |> sort(descending(revenue)) |> summarize(
  best  = first(product),
  worst = last(product),
  by = region
)
region best worst
East Widget Gadget
North Gadget Widget
West Gadget Widget 
sales >> sort(descending(col.revenue)) >> summarize(
  best  = first(col.product),
  worst = last(col.product),
  by = col.region
)
region best worst
East Widget Gadget
North Gadget Widget
West Gadget Widget

7.5 The same sentence at scale

Everything above ran on fifteen rows you can check by hand, which is how a verb should be met. Here is the same verb meeting a table nobody checks by hand: gapminder, the cast’s large table, holding a half century of countries and years.

run('
  gapminder
    then keep where ([year] is 2007)
    then summarize [life] as average([life]), [countries] as row_count()
         by [continent]
    then sort [life] descending
')
continent life countries
Oceania 80.71950 2
Europe 77.64860 30
Americas 73.60812 25
Asia 70.72848 33
Africa 54.80604 52 
gapminder |>
  keep(year == 2007) |>
  summarize(life = average(life), countries = row_count(),
            by = continent) |>
  sort(descending(life))
continent life countries
Oceania 80.71950 2
Europe 77.64860 30
Americas 73.60812 25
Asia 70.72848 33
Africa 54.80604 52 
(gapminder
  >> keep(col.year == 2007)
  >> summarize(life = average(col.life), countries = row_count(),
               by = col.continent)
  >> sort(descending(col.life)))
continent life countries
Oceania 80.7195 2
Europe 77.6486 30
Americas 73.60812 25
Asia 70.72848 33
Africa 54.80604 52

gapminder then keep where [year] is 2007 then summarize [life] as average([life]), [countries] as row_count() by [continent] then sort [life] descending

Gapminder’s 1,704 rows went in; five came out, one per continent, with the count beside each average, so you can see how many countries went into each number. The sentence is the same length it was on fifteen rows, and that is the point of the demonstration: nothing about the grammar knows or cares how big the table is. Somewhere else shows the same indifference reaching a warehouse.

7.6 Where the other columns went

summarize keeps the columns you grouped by and the ones you named, and takes away everything else. That is its whole behavior, and it is the thing readers are most often surprised by. You can look at it before you run anything.

sales |>
  summarize(total = total(revenue), by = region) |>
  show_steps()
sales date :text region :text product :text quantity :number revenue :number cost :number summarize [total] as total([revenue]) by [region] region +total :number -date -product -quantity -revenue -cost one row per group 
show_steps(sales >> summarize(total = total(col.revenue), by = col.region))
sales date :text region :text product :text quantity :number revenue :number cost :number summarize [total] as total([revenue]) by [region] region +total :number -date -product -quantity -revenue -cost one row per group

One line makes total, and the same line takes date, product, quantity, revenue and cost away. When a later step asks for a column that is not there, this is usually where it went. What it does to the table reads the drawing in full.

7.7 What travels with it

  • by is the same word, with the same meaning, that grouped take in the last chapter and will group windows in giving each row a place: these rows belong together.
  • as names each answer, name first, exactly as it does in add.
  • The eleven aggregations reappear inside add ... by, where they broadcast a group’s answer back onto its rows instead of collapsing them; the chapter on the small words shows the share-of-group idiom this buys.

7.8 What it refuses

Every value in a summarize must span its group. A bare column does not, and the grammar will not guess which of a group’s values you meant:

sales |> summarize(revenue = revenue, by = region) |> collect()
Error:
! 
illegal: `summarize` returns one row for each group, so `[revenue]` has to be a value that spans the group. Wrap it: `total(...)`, `average(...)`, `first(...)`, or count the rows with `row_count()`
  |
2 |   then summarize [revenue] as [revenue] by [region]
  |                                ^^^^^^^
try:
    collect(sales >> summarize(revenue = col.revenue, by = col.region))
except GodError as refusal:
    print(refusal)

illegal: `summarize` returns one row for each group, so `[revenue]` has to be a value that spans the group. Wrap it: `total(...)`, `average(...)`, `first(...)`, or count the rows with `row_count()`
  |
2 |   then summarize [revenue] as [revenue] by [region]
  |                                ^^^^^^^

SQL answers this same mistake with an error about grouping clauses that has confused beginners for decades; some tools answer it by silently taking the first value, which is worse. This message names the actual problem, one row per group against many values per group, and lists the words that resolve it. Refusing with a direction is the grammar’s answer to a whole class of half-remembered rules.