AggregateRows
AggregateRows[tab,{key1f1,…}]
computes different aggregation functions fi[tab] and assigns them to different keys keyi.
AggregateRows[tab,fspec,gspec]
forms groups by the distinct values given by gspec and then aggregates them using fspec.
AggregateRows[fspec]
represents an operator form for the two-argument version of AggregateRows.
AggregateRows[fspec,gspec]
represents an operator form for the three-argument version of AggregateRows.
Details
- AggregateRows is also known as tabular reduce.
- AggregateRows is typically used to summarize the data in groups of rows.
- Possible forms of tab include:
-
Tabular[…] type-consistent tabular data Dataset[…] general hierarchical data {assoc1,assoc2,…} list of associations with common keys - The application of AggregateRows does not change the form of tab.
- For each group, 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"].
- Possible forms of grouping specification gspec include:
-
key groups by distinct values associated with key newkeyg groups by distinct values of g[rowi], adding them as newkey {spec1,…} groups by distinct values of all the speci together
Examples
open all close allBasic Examples (2)
Take a Tabular object:
tab = Tabular[{{"a", 1}, {"b", 2}, {"a", 3}, {"b", 4}}, {"col1", "col2"}]Aggregate all rows by computing the joined string of "col1" and the total of the values of column "col2":
AggregateRows[tab, {"j" -> (StringJoin[#col1]&), "t" -> (Total[#col2]&)}]Aggregate rows by first grouping those with common value of column "col1" and then totaling:
AggregateRows[tab, "total" -> Function[Total[#col2]], "col1"]Use operator form to aggregate a list of associations by computing a function of the values of key "a":
assocs = {<|"a" -> x, "b" -> 1|>, <|"a" -> y, "b" -> 2|>, <|"a" -> x, "b" -> 1|>};AggregateRows["c" -> Function[f[#a]]][assocs]Aggregate associations by first grouping those with common value of key "b":
AggregateRows["c" -> Function[f[#a]], "b"][assocs]Scope (8)
Input Data (3)
Aggregate all rows of a Tabular object:
AggregateRows[Tabular[Association["RawSchema" -> Association["ColumnProperties" ->
Association["b" -> Association["ElementType" -> "Integer64"],
"c" -> Association["ElementType" -> "Integer64"],
"date" -> Association["ElementType" -> TypeSpecifier["Date"]["Integer64", "Day", "Gregorian",
None]]], "KeyColumns" -> None, "Backend" -> "WolframKernel"], "Options" -> {},
"BackendData" -> Association["ColumnData" -> DataStructure["ColumnTable",
{{TabularColumn[Association["Data" -> {{4, 4, 4}, {}, None}, "ElementType" -> "Integer64"]],
TabularColumn[Association["Data" -> {{1, 3, 5}, {}, None}, "ElementType" -> "Integer64"]],
TabularColumn[Association["Data" -> {3, {{{1080777600000, 1112486400000, 1144195200000},
{}, None}}, None}, "ElementType" -> "Date"["Integer64", "Day", "Gregorian", None],
"CachedOriginalExpression" -> {DateObject[{2004, 4, 1}, "Day"], DateObject[{2005, 4, 3},
"Day"], DateObject[{2006, 4, 5}, "Day"]}]]}}]]]], "total" -> (Total[#b + #c]&)]Aggregate all rows of a Dataset object:
AggregateRows[Dataset[{Association["b" -> 4, "c" -> 1, "date" -> DateObject[{2004, 4, 1}, "Day"]],
Association["b" -> 4, "c" -> 3, "date" -> DateObject[{2005, 4, 3}, "Day"]],
Association["b" -> 4, "c" -> 5, "date" -> DateObject[{2006, 4, 5}, "Day"]]}], "total" -> (Total[#b + #c]&)]Aggregate a list of associations:
AggregateRows[{<|"b" -> 4, "c" -> 1|>, <|"b" -> 4, "c" -> 3|>, <|"b" -> 4, "c" -> 5|>}, "total" -> (Total[#b + #c]&)]Aggregation Functions (2)
Take a Tabular object:
tab = Tabular[{{"a", 1}, {"b", 2}, {"a", 3}, {"b", 4}}, {"col1", "col2"}]The aggregation function f receives an association with all columns, each one a list of values:
AggregateRows[tab, "newcol" -> f]Use a pure function with named Slot notation to extract individual columns in the aggregation:
AggregateRows[tab, "newcol" -> (f[#col1] + g[#col2]&)]Alternatively, use Part notation:
AggregateRows[tab, "newcol" -> (f[#[[1]]] + g[#[[2]]]&)]Aggregate a Tabular object with a list of aggregation functions:
tab = Tabular[{{4, 1, DateObject[{2004, 4, 1}, "Day"]}, {4, 3, DateObject[{2005, 4, 3}, "Day"]}, {4, 5, DateObject[{2006, 4, 5}, "Day"]}}, {"b", "c", "date"}]AggregateRows[tab, {"total b" -> (Total[#b]&), "max c" -> (Max[#c]&), "mean date" -> (Mean[#date]&)}]Mix the original columns in the computation:
AggregateRows[tab, {"total b+c" -> (Total[#b + #c]&), "mean date" -> (Mean[#date]&)}]Grouping Specifications (3)
Aggregate rows by first grouping those with common value of column "col1" and then averaging:
tab = Tabular[{{"a", 1}, {"b", 2}, {"a", 3}, {"b", 4}}, {"col1", "col2"}]AggregateRows[tab, "newcol" -> (Mean[#col2]&), "col1"]Aggregate rows by grouping those with common value determined by the function g and then averaging:
tab = Tabular[{{"a", 1}, {"b", 2}, {"a", 3}, {"b", 2}}, {"col1", "col2"}]AggregateRows[tab, "newcol" -> (Mean[#col2]&), "group" -> g]Use a specific grouping function:
AggregateRows[tab, "newcol" -> (Mean[#col2]&), "group" -> (g[#col1]&)]Take a Tabular object:
tab = Tabular[{{4, 1, DateObject[{2004, 4, 1}, "Day"]}, {2, 3, DateObject[{2005, 4, 3}, "Day"]}, {6, 5, DateObject[{2006, 1, 5}, "Day"]}, {2, 3, DateObject[{2004, 1, 20}, "Day"]}, {4, 1, DateObject[{2005, 12, 12}, "Day"]}}, {"b", "c", "date"}]Aggregate rows by first grouping those with common values of pairs of columns "b" and "c":
AggregateRows[tab, "newcol" -> (Mean[#date]&), {"b", "c"}]Group by common values of pairs {"b"+"c","c"}:
AggregateRows[tab, "newcol" -> (Mean[#date]&), {"b+c" -> (Total[#b + #c]&), "c"}]Applications (6)
Take a Tabular object with measurements of soil pH at three depth levels of 0, 30 and 80 meters:
Tabular[{...}]AggregateRows[%, "mean" -> (Mean[#pH]&), "depth"]Take measurements of various species of iris flower:
tab = ResourceData["Sample Tabular Data: Fisher Iris"]Compute the mean of the second column:
AggregateRows[tab, "SepalLengthMean" -> (Mean[#[[2]]]&)]Compute the median of the "PetalLength" column:
AggregateRows[tab, "PetalLengthMean" -> Function[Median[#PetalLength]]]Compute number of observations for each species:
AggregateRows[tab, "species" -> Function[Length[#Species]], "Species"]Compute the mean of "SepalLength" for each species variety:
AggregateRows[tab, "mean" -> Function[Mean[#SepalLength]], "Species"]Old Faithful geyser data containing eruption times and next eruption waiting times in minutes:
name = {"Statistics", "OldFaithful"};tab = Tabular[ExampleData[name], ExampleData[name, "ColumnHeadings"]]Compute correlation between the columns:
AggregateRows[tab, "corr" -> Function[Correlation[#Duration, #WaitingTime]]]Visualize the waiting time vs. duration:
ListPlot[tab//Normal, AxesLabel -> Automatic]Compute mean of the durations depending on the waiting time:
AggregateRows[tab, "MeanDuration" -> Function[Mean[#Duration]], "WT ≥ 50" -> Function[#WaitingTime >= 50]]Convert into the normal form of the Tabular object:
%//NormalTake a table of data about penguins in the Palmer Archipelago:
tab = ResourceData["Sample Tabular Data: Palmer Penguins"]ColumnKeys[tab]Find the mean body weight for each sex:
AggregateRows[tab, "body_mass_mean" -> Function[Mean[#"body_mass"]], "sex"]Find the median body weight for each island location:
AggregateRows[tab, "body_mass_median" -> Function[Median[#"body_mass"]], "island"]Find the body mass distribution for each species:
AggregateRows[tab, "distribution" -> (EstimatedDistribution[#"body_mass", NormalDistribution[a, b]]&), "species"]Plot the PDF for each estimated distribution:
Plot[Evaluate[PDF[#, Quantity[x, "Grams"]]& /@ %[All, "distribution"]], {x, 2000, 7000}, PlotLegends -> Normal[%[All, "species"]]]Take a Tabular object with data of monthly ozone readings for Los Angeles:
data = ResourceData["Sample Tabular Data: Los Angeles Ozone"]Compute the mean for each column, each month:
rules = Map[With[{name = #}, name -> Function[Mean[#[name]]]]&, Rest[ColumnKeys[data]]];means = AggregateRows[data, rules]Visualize the mean seasonality:
PieChart[Normal[means[1]], ChartLabels -> ColumnKeys[means], ColorFunction -> "RedBlueTones"]Take a Tabular object with data for a selection of car models:
data = ResourceData["Sample Tabular Data: Fuel Economy"]Find the mean city and highway milage per each drive type and year:
AggregateRows[data, { "city_mean" -> Function[Mean[#"city"]], "hwy_mean" -> Function[Mean[#"hwy"]]}, {"drive", "year"}]Find the mean city and highway milage per each manufacturer for year 2008:
means = AggregateRows[Select[data, #year == 2008&], { "city_mean" -> Function[Mean[#"city"]], "hwy_mean" -> Function[Mean[#"hwy"]]}, "manufacturer"]PairedBarChart[{means -> "city_mean"}, {means -> "hwy_mean"}, BarSpacing -> {22, 0, .2}, ColorFunction -> "RedGreenSplit", ChartLabels -> {Placed[{"city mpg", "highway mpg"}, Above], None, Normal[means[All, "manufacturer"]]}]Possible Issues (1)
Using AggregateRows without grouping input may reduce a Tabular object too much:
tab = ResourceData["Sample Tabular Data: Fisher Iris"]AggregateRows[tab, "counts" -> Function[Tally[#Species]]]AggregateRows[tab, "count" -> Function[Length[#Species]], "Species"]Text
Wolfram Research (2025), AggregateRows, Wolfram Language function, https://reference.wolfram.com/language/ref/AggregateRows.html.
CMS
Wolfram Language. 2025. "AggregateRows." Wolfram Language & System Documentation Center. Wolfram Research. https://reference.wolfram.com/language/ref/AggregateRows.html.
APA
Wolfram Language. (2025). AggregateRows. Wolfram Language & System Documentation Center. Retrieved from https://reference.wolfram.com/language/ref/AggregateRows.html