28  How much is there?

How much of something does the table hold? Every recipe on this page is that question in different words, and every answer is summarize with a word or two around it. Every verb here arrived in the six verbs; the chapter that owns the arguments is Summarizing by group.

28.1 How much, in total, per group?

run

run('
  sales
    then summarize [revenue] as total([revenue]) by [product]
')

product	revenue
Doohickey	400
Gadget	720
Sprocket	240
Widget	700

R

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

product	revenue
Doohickey	400
Gadget	720
Sprocket	240
Widget	700

Python

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

product	revenue
Doohickey	400
Gadget	720
Sprocket	240
Widget	700

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

One verb away. Group by two things: by = c(region, product) in R, by = [col.region, col.product] in Python. Average instead of total: swap total for average. Count instead of sum: row_count(), which takes no column at all.

28.2 How much of the whole is each group?

Collapse to totals, then divide each total by their sum.

run

run('
  sales
    then summarize [revenue] as total([revenue]) by [region]
    then add [share] as ([revenue] / total([revenue]))
    then sort [share] descending
')

region	revenue	share
West	785	0.3810680
East	730	0.3543689
North	545	0.2645631

R

sales |>
  summarize(revenue = total(revenue), by = region) |>
  add(share = revenue / total(revenue)) |>
  sort(descending(share))

region	revenue	share
West	785	0.3810680
East	730	0.3543689
North	545	0.2645631

Python

(sales
  >> summarize(revenue = total(col.revenue), by = col.region)
  >> add(share = col.revenue / total(col.revenue))
  >> sort(descending(col.share)))

region	revenue	share
West	785	0.381068
East	730	0.3543689
North	545	0.2645631

sales then summarize [revenue] as total([revenue]) by [region] then add [share] as [revenue] / total([revenue]) then sort [share] descending

The add uses an aggregate with no by, so the divisor spans the whole table: each region’s total over the grand total. The shares sum to one, which is the check to run by eye before you trust any share column.

One verb away. Share within a group instead of the whole: leave the summarize out, so every row stays a row, and put the by on the add, which is the idiom the small-words chapter reads aloud.

28.3 How many different values does a column hold?

run

run('
  sales
    then summarize [products] as unique_count([product]),
         [orders] as row_count()
')

products	orders
4	15

R

sales |> summarize(products = unique_count(product), orders = row_count())

products	orders
4	15

Python

(sales
  >> summarize(products = unique_count(col.product), orders = row_count()))

products	orders
4	15

sales then summarize [products] as unique_count([product]), [orders] as row_count()

One verb away. The values themselves rather than their count: pick then drop_duplicates, from the housekeeping chapter.

28.4 How much, after a cost comes out?

Make the derived number first, then collapse it like any other column.

run

run('
  sales
    then add [margin] as ([revenue] - [cost])
    then summarize [margin] as total([margin]) by [region]
')

region	margin
East	285
North	245
West	328

R

sales |>
  add(margin = revenue - cost) |>
  summarize(margin = total(margin), by = region)

region	margin
East	285
North	245
West	328

Python

(sales
  >> add(margin = col.revenue - col.cost)
  >> summarize(margin = total(col.margin), by = col.region))

region	margin
East	285
North	245
West	328

sales then add [margin] as [revenue] - [cost] then summarize [margin] as total([margin]) by [region]

One verb away. Only profitable orders in the totals: a keep(margin > 0) between the two steps, and nothing else moves.

28.5 What this section refuses

The recipe most often tried and least often meant: a total of a column that is not a number.

R

sales |> summarize(t = total(product), by = region) |> collect()

Error:
! 
illegal: `total` works on numbers, and this column is text. Count the rows instead with `row_count()`, or convert the column first
  |
2 |   then summarize [t] as total([product]) by [region]
  |                                ^^^^^^^

Python

try:
    collect(sales >> summarize(t = total(col.product), by = col.region))
except GodError as refusal:
    print(refusal)

illegal: `total` works on numbers, and this column is text. Count the rows instead with `row_count()`, or convert the column first
  |
2 |   then summarize [t] as total([product]) by [region]
  |                                ^^^^^^^

Some tools would concatenate the text, some would error from inside the engine, and one famously returns zero. Here the sentence is refused whole, with the column’s kind named, before anything runs. If the column should be a number, the conversion words in saying what something is are the repair.