One category → Rows; two → Rows × Columns; "for only…" → Filter

Reading a question for its layout

The paper does not ask you to build a pivot. It gives a business question and four layouts, and asks which one answers it. Every such question has the same anatomy, and each part maps to one zone:

• "per province", "by channel", "for each month" → a categorical in Rows (the first) or Columns (the second).
• "total amount", "average order", "how many orders" → the measure in Values with the function the verb names: total = Sum, average = Average, how many = Count.
• "for only the online orders", "in Q1 alone" → the field in Filters.

Worked example — four questions

Average order amount per province, by channel, for Q1 only. Per province → prov in Rows. By channel → channel in Columns. Average amount → amount in Values, function Average. For Q1 only → order_date in Filters, set to January–March. Reading the result: the ON × Online cell is 465, the ON × Store cell 532.5.

Total units per channel. One categorical → channel in Rows; units in Values, Sum: Online 21, Store 20.

How many orders per province? prov in Rows and — the part people miss — a field to count. Any field will do, but the natural one is the identifier: order_id in Values with the function Count: AB 2, BC 3, ON 4, QC 3. Sum of order_id would add the identifiers up (a meaningless number, Lesson 2.3), and Sum of amount answers a different question.

Orders from AB only, by channel. "From AB only" → prov in Filters, set to AB; channel in Rows; amount in Values, Sum: Online 1320, Store 980.

The standing trap is the first one: swapping Rows and Values. A layout with amount in Rows and prov in Values gives twelve rows labelled 90, 180, 215 … with a count of provinces beside each — a table, but not an answer.

The R side: a second grouping is +

A second categorical joins the right-hand side of the formula with +:

> aggregate(amount ~ prov + channel, data = orders, FUN = mean)
  prov channel amount
1   AB  Online 1320.0
2   BC  Online  360.0
3   ON  Online  465.0
4   QC  Online   90.0
5   AB   Store  980.0
6   BC   Store  640.0
7   ON   Store  532.5
8   QC   Store  197.5

The same eight cells as the Rows × Columns pivot — but laid out long, one row per combination, not as a grid. Lesson 7.4's xtabs() gives the grid. The Q1 filter is a subset() before the aggregate: aggregate(amount ~ prov + channel, data = subset(orders, order_date < "2025-04-01"), FUN = mean). And "how many" is FUN = length: aggregate(order_id ~ prov, data = orders, FUN = length) prints 2, 3, 4, 3.

Type one, then choose four layouts

Type the two-factor aggregate. Each question gives a business question and four layouts.

CRISP-DM: choosing the layout is data understanding → explore data; the question it answers is business understanding written down first.