The pivot, as a formula

The pivot table, written down

Module 7 put prov in Rows and amount in Values, summarised by Average. aggregate() is that pivot as one line, and the formula reads exactly the way the pivot is described — amount, grouped by prov:

> aggregate(amount ~ prov, data = orders, FUN = mean)
  prov    amount
1   AB 1150.0000
2   BC  453.3333
3   ON  498.7500
4   QC  161.6667

Three arguments. The formula amount ~ prov: the tilde separates the value column (left) from the grouping column (right). data = orders lets the names stand alone. FUN = mean is the statistic — the pivot's Summarize by — and it is the function itself, no parentheses. The result is a data frame with one row per group, which means it can go straight to write.csv() or into a bar plot — the same four averages Module 7 read off its pivot (AB 1150, BC 453.33, ON 498.75, QC 161.67).

Worked example — two groups, several statistics

Province and channel together is a pivot with Rows and Columns. In the formula, add a second grouping variable with +:

> aggregate(amount ~ prov + channel, data = orders, FUN = sum)
  prov channel amount
1   AB  Online   1320
2   BC  Online    720
3   ON  Online    930
4   QC  Online     90
5   AB   Store    980
6   BC   Store    640
7   ON   Store   1065
8   QC   Store    395

Eight rows, one per combination, in long layout — the pivot's 4 × 2 grid unstacked into a column of provinces, a column of channels and a column of totals. This is the tidy shape a chart or a merge wants; table() gave the wide grid.

The pivot showed Average and Count side by side. A FUN that returns a named vector does the same:

> aggregate(amount ~ prov, data = orders, FUN = function(x) c(mean = mean(x), n = length(x)))
  prov amount.mean  amount.n
1   AB   1150.0000    2.0000
2   BC    453.3333    3.0000
3   ON    498.7500    4.0000
4   QC    161.6667    3.0000

function(x) is a one-off statistic: for each group's amounts x, hand back the mean and the count. The columns come out as amount.mean and amount.n. QC: three orders averaging 161.67. (FUN = length alone is the pivot's Count.)

tapply() — the same numbers as a vector

> tapply(orders$amount, orders$prov, sum)
  AB   BC   ON   QC 
2300 1360 1995  485 
> tapply(orders$amount, orders$prov, sum)["ON"]
  ON 
1995 

Three arguments in a different order — the values, the groups, the function, no formula and no data = — and the result is a named vector, indexable by name. Use tapply() when the next line needs one number per group (tapply(...)["ON"]); use aggregate() when the next line needs a table to write, plot or merge.

What happens to a missing amount

Blank order 1005 (AB) again:

> aggregate(amount ~ prov, data = ona2, FUN = mean)
  prov   amount
1   AB 980.0000
2   BC 453.3333
3   ON 498.7500
4   QC 161.6667
> tapply(ona2$amount, ona2$prov, mean)
      AB       BC       ON       QC 
      NA 453.3333 498.7500 161.6667 

The formula method drops the incomplete row before grouping (its na.action default is na.omit), so AB's mean is quietly 980 — the one remaining order — with no NA to warn you. tapply() passes the NA through, and mean() returns NA for AB. Neither is wrong; only one of them tells you. Run colSums(is.na()) first (Module 11), and you know which you are looking at.

Type the three, then read two cells and reproduce mean and n in Python

The worksheet reads QC's mean and AB's count off the printed amount.mean / amount.n frame.

CRISP-DM: an aggregate is data preparation → construct data when it feeds a model or a merge, data understanding → explore data when it answers a question on its own.