Pivot ⇔ SUMIFS ⇔ aggregate / tapply

The triangle the final examines

The cumulative final's signature question hands you one of three things — a pivot layout, a conditional-aggregate formula, or an R call — and asks for another that returns the same number. This lesson lines the three up for the one-way case and the two-way case; after it, a pivot cell should read as a SUMIFS and an aggregate() at sight.

Worked example — one way: total amount per province

Pivot: prov in Rows, Sum of amount in Values → AB 2300 · BC 1360 · ON 1995 · QC 485.

Formula: type the four province codes in H2:H5 and, in I2, a SUMIF whose criterion is the code beside it:

=SUMIF($B$2:$B$13, H2, $D$2:$D$13)

Filled down, the ranges stay put (absolute) and the criterion moves (relative): the pivot, rebuilt as four formulas. The formula version has one weakness the pivot does not: a fifth province in the data needs a fifth row typed in H.

R: two calls return the same four numbers in two shapes:

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

aggregate() returns a data frame — a column of labels and a column of values. tapply(values, groups, function) returns a named one-dimensional array that prints like a named vector: tapply(…)["ON"] picks the 1995 by name, which is handy inside a formula and awkward inside a report.

Worked example — two ways: province × channel

Pivot: prov in Rows, channel in Columns, Sum of amount → the grid of Lesson 7.4.

Formula: provinces down H2:H5, channel names across I1:J1, and in I2 a SUMIFS that can be filled both down and across:

=SUMIFS($D$2:$D$13, $B$2:$B$13, $H2,$C$2:$C$13,I$1)

The mixed references from Lesson 3.3 do the work. $H2 locks the column so that filling right still reads the province from H; I$1 locks the row so that filling down still reads the channel from row 1. Eight formulas, one typed. I2 is 930, J5 (QC × Store) is 395.

R: xtabs() returns the grid; aggregate(amount ~ prov + channel, …) returns the same eight numbers as a long table; tapply(orders$amount,list(orders$prov, orders$channel), sum) is a third route to the grid.

> xtabs(amount ~ prov + channel, data = orders)
    channel
prov Online Store
  AB   1320   980
  BC    720   640
  ON    930  1065
  QC     90   395

Swap the function, keep the shape

Every corner of the triangle takes the same substitutions: Count ↔ COUNTIFS ↔ table() or FUN = length; Average ↔ AVERAGEIFS ↔ FUN = mean; Max ↔ MAXIFS ↔ FUN = max. table(orders$prov,orders$channel) is the Count pivot; xtabs(amount ~ …) is the Sum pivot; the function is the only thing that changes.

Type three, then reproduce the group sum

Type the two-way SUMIFS, the tapply and the xtabs. The code block builds the one-way sums with a dictionary — the aggregate written out by hand, so the group + function idea is visible without any tool.

CRISP-DM: all three routes are data understanding → explore data. Module 12 gives the R route its own module.