Group and ungroup data

This guide shows you how to group and ungroup table data in Deephaven.

To compare grouping with Deephaven's aggregation operations, and to choose between them, see Aggregations in Deephaven.

This guide uses a table of apple data called apples created using new_table. Many of the grouping and ungrouping examples use this table. If you are unfamiliar with creating tables from scratch using new_table, see the guide on creating new tables.

Use the code below to create the apples table:

group_by

The group_by method groups rows by the values in one or more grouping columns, called keys. Rows that share the same key values form one group. For each group, group_by collects every non-key column into a Deephaven vector, and the values in each vector keep their order from the input table.

If you call group_by with no grouping columns, it puts all rows in one group. The result has a single row, and each column holds one vector of all its values:

If you pass one grouping column, group_by groups rows into vectors by each unique value in that column:

If you pass more than one grouping column, group_by groups rows into vectors by each unique combination of values in those columns:

To group data by conditions on columns, first add columns that hold the conditions, then group by those new columns. The example below uses update_view to add the Class and Diet columns. To choose between update_view and the other column selection methods, see Choose the right column selection method.

agg.group

The agg.group aggregation collects the values of each listed column into a vector, one vector per group. You pass it to agg_by, which forms the groups from its key columns. agg.group is one of the combined aggregations that agg_by accepts.

Use agg.group instead of group_by when you want the grouped vectors in the same agg_by call as other aggregations, such as a sum or an average of another column. Unlike group_by, which collects every non-key column, agg.group collects only the columns you list, and you must list at least one.

In this example, we group the Color values by Type and calculate the average weight and calories of each type in the same call:

If you don't supply the by argument to agg_by, all rows form a single group, and agg.group groups every value of each column you list:

ungroup

The ungroup method is the opposite of group_by. It expands each array or vector cell into one row per element. With no arguments, ungroup expands every array or vector column. When you pass one or more column names, it expands only those columns.

The example below calls ungroup with no arguments on apples_by_class_and_diet, the table from the group_by example above. In the result:

• Each row holds one apple, with a single value in each cell.
• Rows are ordered by group rather than in their original order.
• The Class and Diet columns remain.

The example below uses ungroup to expand only the Color column in apples_by_class_and_diet. The Type, WeightGrams, and Calories columns still contain vectors:

Ungroup vectors and Java arrays

The columns you ungroup don't have to come from group_by. ungroup also expands Java arrays that you create in a formula, and it can expand a vector column and an array column in the same call.

The example below uses the empty_table method to create a table with two columns and one row. Each column contains a Java array with three elements. The ungroup method expands both columns into three rows.

The example below creates column X as a vector with group_by and column Z as a Java array. It then uses ungroup to expand both columns at the same time.

Ungroup arrays of different lengths

The ungroup method cannot expand a row that contains arrays of different lengths.

The example below creates a table with two columns and one row.

Each column in the above table contains a Java array, but one has three elements and the other has two. Since the arrays are not the same size, calling ungroup with no column names results in an error.

The error message includes Array sizes differ at row key 0 (position 0), X has size 3, Z has size 2.

To ungroup arrays of different lengths, ungroup each column separately:

Null values

group_by keeps null values. Each null becomes an element of the group's vector, and the UI displays it as empty. When ungroup expands the vector, each null element becomes a null cell in the corresponding column.

The example below uses the empty_table method and the ternary operator to create a table with two columns and five rows. The first and second rows of column Z contain null values. group_by keeps the nulls in the Z vector, and ungroup turns them back into null cells.

A cell that holds a null array, rather than an array of nulls, produces no rows when you ungroup it. The example below uses the empty_table method to create a table with one column and one row, where the single cell holds a null Java array. Calling ungroup on this table results in an empty table with one column.

Related documentation

• Aggregations in Deephaven
• Create a new table
• Choose the right column selection method
• Formulas in query strings
• Filters in query strings
• Operators in query strings
• Arrays
• Arrays vs vectors
• Combined aggregations
• Dedicated aggregations
• agg_by
• agg.group
• empty_table
• group_by
• new_table
• ungroup