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 newTable. Many of the grouping and ungrouping examples use this table. If you are unfamiliar with creating tables from scratch using newTable, see the guide on creating new tables.

Use the code below to create the apples table:

groupBy

The groupBy 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, groupBy 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 groupBy 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, groupBy groups rows into vectors by each unique value in that column:

If you pass more than one grouping column, groupBy 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 updateView to add the Class and Diet columns. To choose between updateView and the other column selection methods, see Choose the right column selection method.

AggGroup

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

Use AggGroup instead of groupBy when you want the grouped vectors in the same aggBy call as other aggregations, such as a sum or an average of another column. Unlike groupBy, which collects every non-key column, AggGroup 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 pass any grouping columns to aggBy, all rows form a single group, and AggGroup groups every value of each column you list:

ungroup

The ungroup method is the opposite of groupBy. 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 applesByClassAndDiet, the table from the groupBy 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 applesByClassAndDiet. The Type, WeightGrams, and Calories columns still contain vectors:

Ungroup vectors and Java arrays

The columns you ungroup don't have to come from groupBy. 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 emptyTable 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 groupBy and column Z as a Java array. It then uses ungroup to expand both columns at the same time.

Ungroup arrays of different lengths

By default, 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:

Alternatively, call ungroup(true) to pad the shorter arrays with null values. The true value sets the nullFill parameter:

Null values

groupBy 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 emptyTable 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. groupBy 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 emptyTable 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
• aggBy
• AggGroup
• emptyTable
• groupBy
• newTable
• ungroup