PivotTable
PivotTable[tab,f,rowcol,colcol]
constructs a table where the value at the position with keys {row,col} is given by applying the function f to the subtabulars corresponding to the rows of tab where rowcol has value row and colcol has value col.
PivotTable[tab,{key1f1,…},rowcols,colcols]
uses multiple aggregation functions fi denoted by keyi.
Details and Options
- PivotTable is also known as contingency table in statistics.
- PivotTable provides a way to summarize and reorganize data by applying an aggregation function to subtabulars specified by values of selected columns.
- PivotTable[tab,f,rowcols,colcols] produces a Tabular object in which rowcols are key columns.
- If the colcol specification is a list of several keys, the resulting table will have ExtendedKey column keys.
- For each group determined by Flatten[{rowcols,colcols}], there is a subtabular stab corresponding to a subset of rows of tab. The aggregation functions fi are applied to the association of columns given by FromTabular[stab,"Columns"].
- PivotTable[tab,{key1f1,…},rowcols,colcols] is effectively PivotToColumns[AggregateRows[tab, {key1f1,…}, Flatten[{rowcols,colcols}]],colcols{key1,…}].
- The following option can be given:
-
IncludeGroupAggregates False whether to show margins giving the aggregation for subsets of the groups »
Examples
open all close allBasic Examples (2)
Make a pivot table showing mean age of passengers:
titanic = ResourceData["Sample Tabular Data: Titanic"]PivotTable[titanic, Mean[#age]&, "class", "sex"]Show margins with the mean age for group subsets:
PivotTable[titanic, Mean[#age]&, "class", "sex", IncludeGroupAggregates -> True]Take tabular data about sales:
sales = ResourceData["Sample Tabular Data: Sales Data"]Construct a pivot table showing number of product types per region and sector:
PivotTable[sales, Length[#Product]&, {"Region", "Sector"}, "Product"]Scope (3)
Take a table of data about penguins in the Palmer Archipelago:
penguins = ResourceData["Sample Tabular Data: Palmer Penguins"]Construct a pivot table specifying the output row keys and column keys by multiple input columns:
PivotTable[penguins, Length[#species]&, {"year", "island"}, {"species", "sex"}]Show the number that lived and died for the Titanic:
titanic = ResourceData["Sample Tabular Data: Titanic"];PivotTable[titanic, {"lived" -> (Count[#survived, True]&), "died" -> (Count[#survived, False]&)}, "sex", "class"]Show group aggregate summaries:
PivotTable[titanic, {"lived" -> (Count[#survived, True]&), "died" -> (Count[#survived, False]&)}, "sex", "class", IncludeGroupAggregates -> True]Make a pivot table showing the percent survival for the Titanic:
titanic = ResourceData["Sample Tabular Data: Titanic"]Percent survived per class and per sex:
PivotTable[titanic, PercentForm[Mean[Boole[#survived]]]&, "class", "sex"]Show margins with the survival rate for group subsets:
PivotTable[titanic, PercentForm[Mean[Boole[#survived]]]&, "class", "sex", IncludeGroupAggregates -> True]Options (1)
IncludeGroupAggregates (1)
Take a table of data about penguins in the Palmer Archipelago:
penguins = ResourceData["Sample Tabular Data: Palmer Penguins"]Create pivot table to count the number of penguins per species and per island:
PivotTable[penguins, Length[#species]&, "species", "island"]Use IncludeGroupAggregates to get the values of the function for the whole category across the rows and across the columns:
PivotTable[penguins, Length[#species]&, "species", "island", IncludeGroupAggregates -> True]Applications (3)
Transplant Patients (1)
The data contains information on kidney transplant patients—time in days to death or on-study time since transplant at the given age:
transplants = Tabular[ResourceData["Sample Data: Kidney Transplant"]]Show the mean survival time in years split according to gender and race:
PivotTable[transplants, UnitConvert[Mean[#Time], "Years"]&, "Race", "Gender"]Show group aggregate summaries:
PivotTable[transplants, UnitConvert[Mean[#Time], "Years"]&, "Race", "Gender", IncludeGroupAggregates -> True]Show the mean survival time in years split according to alive status versus gender and race:
PivotTable[transplants, UnitConvert[Mean[#Time], "Years"]&, "Delta", {"Gender", "Race"}, IncludeGroupAggregates -> True]Fuel Efficiency (1)
Take data for a selection of car models:
data = ResourceData["Sample Tabular Data: Fuel Economy"]Find the mean city and highway milage per each model and each year:
pt = PivotTable[ResourceData["Sample Tabular Data: Fuel Economy"], { "city_mean" -> Function[Mean[#"city"]], "hwy_mean" -> Function[Mean[#"hwy"]]}, "model", "year"]ColumnKeys[pt]Find the change in mean milage from 1999 to 2008:
diff = ConstructColumns[pt, {"model", "city_change" -> Function[#[ExtendedKey["city_mean", 2008]] - #[ExtendedKey["city_mean", 1999]]], "hwy_change" -> Function[#[ExtendedKey["hwy_mean", 2008]] - #[ExtendedKey["hwy_mean", 1999]]]}]Select the models where both city and highway mileage decreased:
diff = Select[diff, Count[#, x_ /; x < 0] == 2&]ListPlot[MapThread[Callout[#1, #2]&, {FromTabular[diff[All, {"city_change", "hwy_change"}], "Matrix"], Normal[diff[All, "model"]]}], PlotRange -> {{-6, 2}, {-6.5, .5}}]New Mothers (1)
Take data containing information about new mothers:
data = Tabular[{...}]Create a pivot table counting how many new mothers smoked, depending on if they used or did not use alcohol, grouped by "motherage":
pt = PivotTable[data, ExtendedKey["smoked", "alcohol"] -> (Count[#smoke, True]&), "motherage", "alcohol"]BarChart[{pt -> ExtendedKey["smoked", "alcohol", True], pt -> ExtendedKey["smoked", "alcohol", False]}, ...]Properties & Relations (2)
Perform a PivotTable operation with a given list of aggregation functions:
titanic = ResourceData["Sample Tabular Data: Titanic"];aggrs = {"lived" -> (Count[#survived, True]&), "died" -> (Count[#survived, False]&)};PivotTable[titanic, aggrs, "sex", "class"]This is equivalent to using AggregateRows with the same aggregations, followed by PivotToColumns to restructure the result:
AggregateRows[titanic, aggrs, {"sex", "class"}]Tabular[PivotToColumns[%, "class" -> {"lived", "died"}], <|"KeyColumns" -> {"sex"}|>]The group aggregate summaries can be obtained as follows:
subs = Reverse@Most@Subsets[{"sex", "class"}]Map[AggregateRows[titanic, aggrs, #]&, subs]Compare to the group aggregation row and columns of this result:
PivotTable[titanic, aggrs, "sex", "class", IncludeGroupAggregates -> True]titanic = ResourceData["Sample Tabular Data: Titanic"];pt = PivotTable[titanic, PercentForm[Mean[Boole[#survived]]]&, "class", "sex", IncludeGroupAggregates -> True]Keys[pt]Use RowKey to access group aggregate values:
pt[RowKey["All"]]//NormalText
Wolfram Research (2025), PivotTable, Wolfram Language function, https://reference.wolfram.com/language/ref/PivotTable.html.
CMS
Wolfram Language. 2025. "PivotTable." Wolfram Language & System Documentation Center. Wolfram Research. https://reference.wolfram.com/language/ref/PivotTable.html.
APA
Wolfram Language. (2025). PivotTable. Wolfram Language & System Documentation Center. Retrieved from https://reference.wolfram.com/language/ref/PivotTable.html