Multi-aggregation

This guide shows you how to collect summary information for groups of data using multi-aggregation, also called combined aggregation: several aggregations applied in a single operation.

When working with data, you often need to break the data into subgroups and then perform calculations on each group. For example, a large multinational corporation may want to know its average employee salary by country, or a teacher might want to analyze test scores for various classes.

Breaking a table into subgroups and then performing several calculations on each subgroup in one operation is known as "combined aggregation." The name joins two ideas: each calculation is an aggregation, which usually summarizes the data within a group, and several of them run together in one operation (combined).

To compare multi-aggregation with the other ways Deephaven summarizes data, and to choose between them, see Aggregations in Deephaven.

Why use combined aggregations?

Deephaven provides many dedicated aggregations, such as max_by and min_by. These are good options when you need only one type of aggregation. When you need more than one aggregation, or a custom calculation with agg.formula, combined aggregations are more flexible, and more efficient because they group the data once instead of once per calculation.

Syntax

Define the aggregations in a list, such as agg_list, then apply them to data with the agg_by method:

Most aggregations take the columns to aggregate in cols. Write a column as "OutputColumn = InputColumn" to put the result in a new column named OutputColumn, or give only the input column's name to keep that name. The by argument names the columns that define the groups.

What aggregations are available?

A number of built-in aggregations are available:

  • agg.abs_sum - Sum of absolute values for each group.
  • agg.avg - Average value for each group.
  • agg.count_ - Number of rows for each group.
  • agg.count_distinct - Number of unique values for each group.
  • agg.count_where - Number of rows for each group that pass a set of filters.
  • agg.distinct - Vector of unique values for each group.
  • agg.first - First value for each group.
  • agg.formula - Custom formula for each group.
  • agg.group - Vector of values for each group.
  • agg.last - Last value for each group.
  • agg.max_ - Maximum value for each group.
  • agg.median - Median value for each group.
  • agg.min_ - Minimum value for each group.
  • agg.partition - Subtable of the rows in each group.
  • agg.pct - Percentile of values for each group.
  • agg.sorted_first - First value for each group after sorting by the order_by column in ascending order.
  • agg.sorted_last - Last value for each group after sorting by the order_by column in ascending order.
  • agg.std - Sample standard deviation for each group.
  • agg.sum_ - Sum of values for each group.
  • agg.unique - The single unique non-null value for each group, or null if the group has more than one distinct value or only nulls.
  • agg.var - Sample variance for each group.
  • agg.weighted_avg - Weighted average for each group.
  • agg.weighted_sum - Weighted sum for each group.

Example data

The examples on this page use the following table of math and science test results for classes in periods 1 and 2. The Weight column holds the weight of each test in the final grade, which the weighted average example uses.

Apply a single aggregation

Although designed for multiple, simultaneous aggregations, agg_by can also apply a single aggregation. Unlike avg_by, which averages every column not named in by, agg_by lets you choose the column to average and name the result, so it works even when other columns hold strings.

The following example uses agg_by to group and average the test scores in three ways:

  • By Period, to see if students perform better in one period or the other.
  • By Subject.
  • By Subject and Period, to see the average test score for each combination of subject and period.

Combine first and last values

This example finds the first and last test results for each subject and period. agg.first returns the first test value, and agg.last returns the last test value. Grouping by Subject and Period produces four rows, one for each combination.

Combine weighted and unweighted averages with a count

In this example, each test carries a different weight in the final grade.

  • The weights are in the Weight column.
  • agg.weighted_avg takes the weight column, Weight, in its wcol argument and computes the weighted average test score into the WAvg column.
  • agg.avg computes the unweighted average test score into the Avg column.
  • agg.count_ has no input column. Its col argument names the output column, NumTests, which holds the number of tests in each group.
  • agg_by groups the rows by Period and applies the three aggregations to each group.