Skip to main content
Version: Python

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 max_by and min_by. 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

Aggregators are defined in an agg_list, then applied to data by the agg_by method:

The general syntax follows:

from deephaven import agg

agg_list = [
agg.avg(cols="SourceColumns..."), # first aggregation
agg.last(cols=["InputColumn = OutputColumn"]), # second aggregation
]

result = source.agg_by(agg_list, by=["GroupingColumns..."]) # apply the aggregations to data

What aggregations are available?

A number of built-in aggregations are available:

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, aggregators can also be used for a single aggregation. In this first example, we group and average the test scores by Period.

from deephaven import new_table
from deephaven.column import string_col, int_col, double_col
from deephaven import agg as agg

source = new_table([
string_col("Period", ["1", "2", "2", "2", "1", "2", "1", "2", "1"]),
string_col("Subject", ["Math", "Math", "Math", "Science", "Science", "Science", "Math", "Science", "Math"]),
int_col("Test", [55, 76, 20, 90, 83, 95, 73, 97, 84])
])

result = source.agg_by([agg.avg(cols=["AVG = Test"])], by=["Period"])

The data can also be grouped and averaged by Subject.

from deephaven import new_table
from deephaven.column import string_col, int_col, double_col
from deephaven import agg as agg


source = new_table([
string_col("Period", ["1", "2", "2", "2", "1", "2", "1", "2", "1"]),
string_col("Subject", ["Math", "Math", "Math", "Science", "Science", "Science", "Math", "Science", "Math"]),
int_col("Test", [55, 76, 20, 90, 83, 95, 73, 97, 84]),
])

result = source.agg_by([agg.avg(cols=["AVG = Test"])], by=["Subject"])

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

from deephaven import new_table
from deephaven.column import string_col, int_col, double_col
from deephaven import agg as agg


source = new_table([
string_col("Period", ["1", "2", "2", "2", "1", "2", "1", "2", "1"]),
string_col("Subject", ["Math", "Math", "Math", "Science", "Science", "Science", "Math", "Science", "Math"]),
int_col("Test", [55, 76, 20, 90, 83, 95, 73, 97, 84]),
])

result = source.agg_by([agg.avg(cols=["AVG = Test"])], by=["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 agg.first to return the first test value and agg.last to return the last test value. The results are grouped by Subject and Period, so there are four results in this example. ])

from deephaven import new_table
from deephaven.column import string_col, int_col, double_col
from deephaven import agg as agg

source = new_table([
string_col("Period", ["1", "2", "2", "2", "1", "2", "1", "2", "1"]),
string_col("Subject", ["Math", "Math", "Math", "Science", "Science", "Science", "Math", "Science", "Math"]),
int_col("Test", [55, 76, 20, 90, 83, 95, 73, 97, 84]),
])

agg_list=[
agg.first(cols=["FirstTest = Test"]),
agg.last(cols=["LastTest = Test"]),
]

result = source.agg_by(agg_list, by=["Subject", "Period"])

Example 3

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

  • The weights are in the Weight column.
  • agg.weighted_avg is used to compute the weighted average test score, stored in the WAvg column.
  • agg.avg is used to compute the unweighted average test score, stored in the Avg column.
    • agg.count_ is used to compute the number of tests in each group.
  • Test results are grouped by Period.
from deephaven import new_table
from deephaven.column import string_col, int_col, double_col
from deephaven import agg as agg

source = new_table([
string_col("Period", ["1", "2", "2", "2", "1", "2", "1", "2", "1"]),
string_col("Subject", ["Math", "Math", "Math", "Science", "Science", "Science", "Math", "Science", "Math"]),
int_col("Test", [55, 76, 20, 90, 83, 95, 73, 97, 84]),
int_col("Weight", [1, 2, 1, 3, 2, 1, 4, 1, 2]),
])

agg_list=[
agg.weighted_avg(wcol="Weight", cols=["WAvg = Test"]),
agg.avg(cols=["Avg = Test"]),
agg.count_(col="NumTests")
]

result = source.agg_by(agg_list, by=["Period"])