How would you do a PivotTable function in Mathematica?

Question

PivotTables in Excel (or, cross tabulations) are quite useful. Has anyone already thought about how to implement a similar function in Mathematica?

Answer 1

@Mr.Wizard's answer is indeed robust and long-lasting as it grounds on ReapSow method suitable for some map reduce jobs in Mathematica. Due to the fact that MMA itself develops, consider a new option as well.

GroupBy (introduced in Mathematica v.10.0) provides a generalization of the map reduce operation.

So, the above data job may be implemented as follows (partly an overkill for readability):

headings = Union @ data[[All, #]] & /@ {1, 4}

{{"East", "North", "South", "West"}, {"01/2011", "02/2011", "03/2011", "04/2011", "05/2011", "06/2011", "07/2011", "08/2011", "09/2011", "10/2011", "11/2011", "12/2011"}}

We may use Outer to set up a rectangular template for TableForm:

template = Outer[List, Apply[Sequence][headings]];

Main job with GroupBy and Total as third argument:

pattern = Append[Normal @
 GroupBy[data, (#[[{1, 4}]] &) -> (#[[-1]] &), Total], 
 _ -> Null];

Finally, inject pattern into template (and apply TableForm headings for beauty):

TableForm[Replace[template, pattern, {2}], TableHeadings -> headings]

This outputs some:

Note: we have made a total of last column in data. (Many other aggregations are, of course, possible.)