6 Summarizing by group

summarize collapses rows. Each new column is an aggregation, and by names the columns that say which rows go together.

sales |> summarize(revenue = total(revenue), orders = row_count(), by = product)

product	revenue	orders
Gadget	450	2
Widget	800	3

sales >> summarize(revenue = total(col.revenue), orders = row_count(), by = col.product)

product	revenue	orders
Gadget	450.0	2
Widget	800.0	3

Grouping by several columns is a list.

sales |> summarize(revenue = total(revenue), by = c(region, product))

region	product	revenue
East	Widget	500
West	Gadget	450
West	Widget	300

sales >> summarize(revenue = total(col.revenue), by = [col.region, col.product])

region	product	revenue
East	Widget	500.0
West	Gadget	450.0
West	Widget	300.0

Without by, the whole table is one group.

sales |> summarize(biggest = largest(revenue), average = average(cost))

biggest	average
500	68

sales >> summarize(biggest = largest(col.revenue), average = average(col.cost))

biggest	average
500	68.0

Grouping is an argument rather than a step, so there is nothing to ungroup afterwards and no state to carry between verbs.

6.1 The nine ways to collapse a group

total and average are the two you will reach for most. There are nine altogether, and the list is closed.

total, average and median are the three that combine numbers into one. smallest and largest take the ends. first and last take a value by position, which is why they need the rows to be 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.

sales |> summarize(
  cheapest = smallest(cost),
  dearest  = largest(cost),
  middle   = median(revenue),
  kinds    = unique_count(product),
  by = region
)

region	cheapest	dearest	middle	kinds
East	100	100	500	1
West	40	100	175	2

sales >> summarize(
  cheapest = smallest(col.cost),
  dearest  = largest(col.cost),
  middle   = median(col.revenue),
  kinds    = unique_count(col.product),
  by = col.region
)

region	cheapest	dearest	middle	kinds
East	100	100	500.0	1
West	40	100	175.0	2

first and last answer by position, so a sort in front of them is what decides which row they mean. Without one they still answer, and the answer is whatever order the rows arrived in.

sales |> sort(descending(revenue)) |> summarize(
  best  = first(product),
  worst = last(product),
  by = region
)

region	best	worst
East	Widget	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	Widget
West	Gadget	Widget