Skip to main content

How to perform multiple aggregations for groups

This guide will show you how to collect summary information for groups of data using combined aggregations.

Often when working with data, you will want to break the data into subgroups and then perform calculations on the grouped data. For example, a large multi-national corporation may want to know their average employee salary by country, or a teacher might want to analyze test scores for various classes.

The process of breaking a table into subgroups and then performing one or more calculations on the subgroups is known as "combined aggregation." The term comes from most operations creating a summary of data within a group (aggregation), and from more than one operation being computed at once (combined).

Why use combined aggregations?#

Deephaven provides many dedicated aggregations, such as maxBy and minBy. These are good options if only one type of aggregation is needed. If more than one aggregation is needed or if you have a custom aggregation, combined aggregations are a more efficient and more flexible solution.

Syntax#

Combined aggregators need to be wrapped inside the AggCombo method to format the results as an argument for the by method:

The general syntax follows:

source.by(                                       # group the source table using .by    AggCombo(                                    # create a collection of aggregators        AggMin(sourceColumns),                   # first aggregation        AggMax("inputColumn = outputColumn")),   # second aggregation        groupingColumns...)
note

Multiple aggregations can be used inside AggCombo. See our reference documentation.

What aggregations are available?#

A number of built-in aggregations are available:

  • AggArray - Array of values for each group.
  • AggAvg - Average value for each group.
  • AggWAvg - Weighted average for each group.
  • AggCount - Number of rows for each group.
  • AggCountDistinct - Number of unique values for each group.
  • AggDistinct - Array of unique values for each group.
  • AggFirst - First value for each group.
  • AggFormula - A formula for each group.
  • AggLast - Last value for each group.
  • AggMax - Maximum value for each group.
  • AggMed - Median value for each group.
  • AggMin - Minimum value for each group.
  • AggPct - Percentile of values for each group.
  • AggStd - Standard deviation for each group.
  • AggSum - Sum of values for each group.
  • AggUnique - Returns one single value for a column, or a default.
  • AggVar - Variance for each group.
  • AggWSum - Weighted sum for each group.

Example 1#

In this example, we have math and science test results for classes during periods 1 and 2. We want to summarize this information to see if students perform better in one period or the other.

Although designed for multiple, simultaneous aggregations, AggCombo can also be used for a single aggregation. In this first example, we group and average the test scores by Period.

from deephaven.TableTools import newTable, stringCol, intCol, doubleColfrom deephaven import ComboAggregateFactory as caf
source = newTable(    stringCol("Period", "1", "2", "2", "2", "1", "2", "1", "2", "1"),    stringCol("Subject", "Math", "Math", "Math", "Science", "Science", "Science", "Math", "Science", "Math"),    intCol("Test", 55, 76, 20, 90, 83, 95, 73, 97, 84),)
result = source.by(caf.AggCombo(caf.AggAvg("AVG = Test")), "Period")

The data can also be grouped and averaged by Subject.

from deephaven.TableTools import newTable, stringCol, intCol, doubleColfrom deephaven import ComboAggregateFactory as caf
source = newTable(    stringCol("Period", "1", "2", "2", "2", "1", "2", "1", "2", "1"),    stringCol("Subject", "Math", "Math", "Math", "Science", "Science", "Science", "Math", "Science", "Math"),    intCol("Test", 55, 76, 20, 90, 83, 95, 73, 97, 84),)
result = source.by(caf.AggCombo(caf.AggAvg("AVG = Test")), "Subject")

We can also group the data by Subject and Period to see the total average in a period and subject.

from deephaven.TableTools import newTable, stringCol, intCol, doubleColfrom deephaven import ComboAggregateFactory as caf
source = newTable(    stringCol("Period", "1", "2", "2", "2", "1", "2", "1", "2", "1"),    stringCol("Subject", "Math", "Math", "Math", "Science", "Science", "Science", "Math", "Science", "Math"),    intCol("Test", 55, 76, 20, 90, 83, 95, 73, 97, 84),)
result = source.by(caf.AggCombo(caf.AggAvg("AVG = Test")), "Subject", "Period")

Example 2#

In this example, we want to know the first and last test results for each subject and period. To achieve this, we can use AggFirst to return the first test value and AggLast to return the last test value. The results are grouped by Subject and Period, so there are four results in this example.

from deephaven.TableTools import newTable, stringCol, intCol, doubleColfrom deephaven import ComboAggregateFactory as caf
source = newTable(    stringCol("Period", "1", "2", "2", "2", "1", "2", "1", "2", "1"),    stringCol("Subject", "Math", "Math", "Math", "Science", "Science", "Science", "Math", "Science", "Math"),    intCol("Test", 55, 76, 20, 90, 83, 95, 73, 97, 84),)
result = source.by(caf.AggCombo(caf.AggFirst("FirstTest = Test"), caf.AggLast("LastTest = Test")), "Subject", "Period")

Example 3#

In this example, tests are weighted differently in computing the final grade.

  • The weights are in the Weight column.
  • AggWAvg is used to compute the weighted average test score, stored in the WAvg column.
  • AggAvg is used to compute the unweighted average test score, stored in the Avg column.
  • AggCount is used to compute the number of tests in each group.
  • Test results are grouped by Period.
from deephaven.TableTools import newTable, stringCol, intCol, doubleColfrom deephaven import ComboAggregateFactory as caf
source = newTable(    stringCol("Period", "1", "2", "2", "2", "1", "2", "1", "2", "1"),    stringCol("Subject", "Math", "Math", "Math", "Science", "Science", "Science", "Math", "Science", "Math"),    intCol("Test", 55, 76, 20, 90, 83, 95, 73, 97, 84),    intCol("Weight", 1, 2, 1, 3, 2, 1, 4, 1, 2),)
result = source.by(caf.AggCombo(caf.AggWAvg("Weight", "WAvg = Test"), caf.AggAvg("Avg = Test"), caf.AggCount("NumTests")), "Period")

Related documention#