The four drop zones

◈ 6 cards

A pivot table is a group plus a summary function. prov in Rows and amount in Values (Sum) gives AB 2300 · BC 1360 · ON 1995 · QC 485, Grand Total 6140 — the same four rows aggregate(amount ~ prov, data = orders, FUN = sum) prints.

One idea, in a menu

Module 4 counted orders by province with COUNTIF; Module 6 summed them with SUMIF. Both are one instance of a single operation: split the rows into groups by a categorical variable, then apply a summary function to a numeric variable inside each group. That operation is aggregation, and a pivot table is aggregation as a drag-and-drop tool: no formula, no fixed list of provinces, and a new group appears the moment a row with a new province is added.

Worked example — total sales by province

Start from the orders sheet with the cursor inside A1:F13 and insert a pivot table (Sheets: Insert → Pivot table; Excel: Insert → PivotTable). An empty pivot and a field list appear, with four zones to drop fields into:

Rows — the categorical whose values become the row labels.
Columns — a second categorical whose values become column headings (empty for now).
Values — the numeric being summarised, with a summary function: Sum, Count, Average, Min, Max.
Filters — a field that restricts which source rows the pivot sees at all.

Drag prov to Rows and amount to Values. The default function for a numeric field is Sum, and the pivot reads:

prov   Sum of amount
AB              2300
BC              1360
ON              1995
QC               485
Grand Total     6140

Four rows because prov has four distinct values — in alphabetical order, which is the pivot's default, not the order the provinces appear in the sheet. Each cell is the sum of amount over the rows whose prov equals the label: ON is 420 + 760 + 510 + 305 = 1995, the SUMIF from Lesson 6.2. The Grand Total is the sum over all twelve rows, 6140 — the same total whichever field is in Rows.

Drop prov — a text field — into Values instead and the pivot cannot add it, so it falls back to Count: "Count of prov", 12 in total. That is a hint you have put a field in the wrong zone, not a result.

The R side: aggregate()

aggregate() takes a formula that reads this numeric, split by this categorical, the data frame, and the function:

> aggregate(amount ~ prov, data = orders, FUN = sum)
  prov amount
1   AB   2300
2   BC   1360
3   ON   1995
4   QC    485

The same four rows, alphabetical again, minus the grand total (that is sum(orders$amount)). ~ is read "by": amount ~ prov is the Values field on the left and the Rows field on the right. Change FUN = sum to mean and the column becomes 1150.00, 453.33, 498.75, 161.67 — Average in the pivot.

Type one, then place the fields

Type the aggregate call. The questions give a field and ask which zone it belongs in.

CRISP-DM: a pivot is data understanding → explore data — the first tool that shows how a measure varies across a category.

The four drop zones. A categorical goes in Rows or Columns; the numeric being summarised goes in Values with a function; a field that restricts the source rows goes in Filters.