Function Repository Resource:

# PivotTable

A spreadsheet-like operation to collate data by values common to specified columns, summarizing corresponding values in a specified column

Contributed by: Kelvin Mischo
 ResourceFunction["PivotTable"][{{label1,label2,…},{i1,i2,…},{i3,i4,…}},binlabel,valuecolumn,func] collates values of valuecolumn common to binlabel and applies func to them. ResourceFunction["PivotTable"][{{label1,label2,…},{i1,i2,…},{i3,i4,…}}, binlabel1,binlabel2,valuelabel,func] collates values common to the two bin labels.

## Details and Options

The output contains as many rows as unique elements in binlabel or binlabel1.
The output contains two columns if one binlabel is specified, and three columns if a binlabel1 and binlabel2 are specified.
Common values for func are Total or Length, but any other function can be used.
The first argument can be a nested list or a variable storing an imported dataset as a nested list.

## Examples

### Basic Examples (2)

Get the Total of the "Second" column with a common name in the "First" column:

 In[1]:=
 Out[1]=

Example dataset with labels in the first rows:

 In[2]:=
 In[3]:=
 Out[3]=

List all unique values in the column "City" and sum the corresponding numeric values in "Item 1" using Total:

 In[4]:=
 Out[4]=

Perform the same calculation, but format in a grid with TextGrid:

 In[5]:=
 Out[5]=

List all unique values in the column "City" and sum the corresponding numeric values in "Item 2" using Total; use TextGrid in postfix form:

 In[6]:=
 Out[6]=

List all unique values common to both the "City" and "State" columns and sum the corresponding numeric values in "Item 1" using Total:

 In[7]:=
 Out[7]=

List all unique values common to both the "City" and "State" columns and sum the corresponding numeric values in "Item 2" using Total:

 In[8]:=
 Out[8]=

### Scope (6)

Example dataset with labels in the first rows:

 In[9]:=

List all unique values common to both the "City" and "State" columns and count the corresponding numeric values in "Item 2" using Length:

 In[10]:=
 Out[10]=

List all unique values common to both the "City" and "State" columns and count the corresponding numeric values in "Item 1" using Length:

 In[11]:=
 Out[11]=

Perform the same calculation, with additional options for TextGrid to add a gray frame:

 In[12]:=
 Out[12]=

Perform the same calculation, with different columns and Total to sum values in "Item 1":

 In[13]:=
 Out[13]=

Although strings are commonly binned with pivot tables, numeric values can be used as well:

 In[14]:=
 Out[14]=

### Applications (2)

Example dataset with labels in the first rows:

 In[15]:=

Make a Table of pivot tables to compare values:

 In[16]:=
 Out[16]=

Example dataset with labels in the first rows:

 In[17]:=

Perform a simple pivot table and export the resulting list:

 In[18]:=
 Out[18]=
 In[19]:=
 Out[19]=

### Neat Examples (2)

Example dataset with labels in the first rows:

 In[20]:=

Make a Manipulate with varying column values for a pivot table:

 In[21]:=
 Out[21]=

Create a pivot table for a large dataset; first create a large test dataset:

 In[22]:=
 In[23]:=
 Out[23]=

Import the test dataset with 1.2 million rows and 4 columns:

 In[24]:=
 In[25]:=
 Out[25]=

Create a pivot table based on the large dataset:

 In[26]:=
 Out[26]=

Kelvin Mischo

## Version History

• 1.0.0 – 12 August 2019