Keyed transpose

This guide shows you how to use keyedTranspose to reshape a table from long format to wide format. In long format, each row holds one observation, such as one log entry with a Date and a Level. In wide format, each distinct value of a category column, such as each Level, becomes its own column.

When to use a keyed transpose

Use keyedTranspose when you need to:

• Pivot data from long to wide format: Convert rows of categorical data into columns.
• Create cross-tabulations: Build summary tables with aggregated values.
• Reshape time-series data: Transform data where categories are in rows into a format where they become columns.
• Prepare data for visualization: Many charts require data in wide format.

Basic usage

The simplest use case involves specifying:

1. A source table.
2. One or more aggregations to apply.
3. Columns to use as row keys (rowByColumns).
4. Columns whose values become new column names (columnByColumns).

In this example:

• Each unique Date becomes a row.
• Each unique Level value (INFO, WARN, ERROR) becomes a column.
• The Count aggregation counts the rows for each combination of Date and Level. A combination with no rows in the source, such as 2025-08-06 and INFO, is null.

Multiple row keys

You can specify multiple row-by columns to create more granular groupings:

Each unique combination of Date and Server creates a separate row in the output.

Multiple aggregations

You can apply multiple aggregations at once. When you do, the operation prefixes each output column name with the aggregation's output column name:

The resulting columns are TotalSales_North, AvgRevenue_North, TotalSales_South, and AvgRevenue_South. The operation groups the columns for each Region value together and orders them in the order you list the aggregations.

Column naming

The keyedTranspose operation builds each output column name from the values in the columnByColumns columns. It first builds a base name from those values and the aggregations, as shown in the first three rows of the following table. It then cleans up the name by applying the remaining rules in order:

The operation adds the aggregation-name prefix only when the list contains two or more aggregations. A single aggregation with several output columns, such as AggSum("Sales", "Revenue"), gets no prefix, so its names collide and receive numeric suffixes (North, North2). To get prefixed names, pass one aggregation per column, such as List.of(AggSum("Sales"), AggSum("Revenue")).

This example applies three combinations of aggregations and column-by columns to the same source:

In this example:

• Normal: The value is a valid column name, so the operation keeps it unchanged.
• column_1234: The operation removes the invalid characters (-, ., /). The result starts with a number, so the operation adds the column_ prefix.
• column_123: The value starts with a number, so the operation adds the column_ prefix.
• Sum_Normal, Count_Normal: With multiple aggregations, the operation prefixes each name with the aggregation's output column name.
• Sum_123, Count_123: The operation adds the aggregation prefix before it cleans up the name, so the name starts with a letter and doesn't get the column_ prefix.
• INFO_10, WARN_10: The operation joins the values of the two column-by columns with an underscore.

The operation aggregates the two INFO rows for each RowKey into one group before it transposes, so they produce one INFO column, not two.

Duplicate names and reserved words

Duplicate names occur when different column-by values produce the same output name. In this example, INFO and IN.FO both become INFO after the . is removed. The first column keeps the name, and the second gets the suffix 2. The value class is a reserved word (a Java keyword), so it gets the column_ prefix:

Sanitize data before transposing

To control the column names yourself, clean the values before you call keyedTranspose. Without cleaning, Type-A becomes TypeA. This example replaces the hyphen with an underscore, so the columns are Type_A and Type_B:

Ticking tables and initial groups

When the source is a ticking table (a table that updates as new data arrives), keyedTranspose fixes its output columns when the operation runs. The result still adds, removes, and updates rows, but it never adds columns. This has two consequences:

• If the source has no rows yet, keyedTranspose has no column-by values to build columns from, and it throws an exception.
• A column-by value that first appears after the operation runs has no column to go in. By default, the result fails with an error. See Handle new column-by values.

To create the columns up front, pass the initialGroups parameter a table that lists every combination of row-by and column-by values that should appear in the result. The table must contain all of the row-by and column-by columns. Each distinct combination of row-by values in it creates a row in the result, even before the source has data for it.

In this example, the source starts empty and never contains an ERROR row, but the result has an ERROR column from the start. Because initGroups creates a group for every combination it lists, the ERROR cells hold a count of 0 rather than null:

Handle new column-by values

By default, a ticking result fails with an error if the source receives a column-by value that was in neither the source nor initialGroups when the operation ran. To ignore such values instead, use the six-argument overload and pass KeyedTranspose.NewColumnBehavior.IGNORE. The result then has no column for the new value, and the rows that contain it don't contribute to any cell.

In this example, every fourth log entry has the level DEBUG, which is not in initGroups. With IGNORE, the result keeps the INFO, WARN, and ERROR columns and leaves out the DEBUG entries:

Common patterns

The following examples apply a single aggregation and a single column-by column to common long-format datasets.

Time-series metrics

Survey responses

The aggregation doesn't have to be numeric. This example uses AggFirst to place each text answer in its question's column:

Best practices

• Column count: keyedTranspose creates one output column per unique combination of column-by values, times the number of aggregation output columns. Avoid high-cardinality column-by columns, which produce very wide tables.
• Initial groups: For a ticking source, use initialGroups to create every expected column when the operation runs.
• New column-by values: Pass KeyedTranspose.NewColumnBehavior.IGNORE if the source can receive column-by values that aren't in initialGroups.
• Aggregation choice: Choose aggregations that make sense for your data. Common choices include AggCount, AggSum, AggAvg, AggFirst, and AggLast.

Related documentation

• Multi-aggregation
• Table types
• keyedTranspose
• Javadoc