Keyed transpose

This guide shows you how to use keyed_transpose 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. This works like a pivot table, flattened into a single Deephaven table that you can use in further table operations like any other.

keyed_transpose works on real-time data as well as static data. When the source is a ticking table, the result keeps updating as new rows arrive, but its set of columns is fixed when the operation runs. Ticking tables and initial groups shows how to plan for that.

When to use a keyed transpose

Use keyed_transpose 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.
  • Compare series side by side: Give each metric, sensor, or symbol its own column, with one row per timestamp.
  • Prepare data for visualization: Produce the one-column-per-series wide format that some charts expect.

Basic usage

The simplest use case involves specifying:

  1. A source table.
  2. One or more aggregations to apply.
  3. Columns whose values identify each output row (row_by_cols). This guide calls these the row-by columns.
  4. Columns whose values become new column names (col_by_cols). This guide calls these the column-by columns.

In this example:

  • Each unique Date becomes a row.
  • Each unique Level value (INFO, WARN, ERROR) becomes a column.
  • The operation groups the source rows by each combination of Date and Level. Each combination is a group. The operation applies the agg.count_ aggregation to each group and puts the count in that group's cell.
  • A combination with no rows in the source, such as 2025-08-06 and INFO, has no group, so its cell is null.

Multiple row-by columns

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 column-by columns

You can also specify multiple column-by columns. The operation creates one output column for each unique combination of their values and joins the values with an underscore:

The result has one column for each combination of Level and NodeId in the source: INFO_10, WARN_10, and INFO_20. For how these names interact with the other naming rules, see Column naming.

Multiple aggregations

You can apply multiple aggregations at once. When you do, the operation prefixes each result column name with the output column name of the aggregation that produced it:

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

The operation adds this prefix only when you pass two or more aggregations. It counts aggregations, not output columns:

Column naming

The keyed_transpose operation builds each output column name in two steps. First, it builds a base name from the column-by values and the aggregations:

ScenarioBase nameExample
Single aggregation, single column-by columnValue from the column-by columnINFO, WARN
Multiple column-by columnsValues joined with underscoresINFO_10, WARN_20
Two or more aggregationsAggregation output column name, an underscore, then the valueCount_INFO, Sum_WARN

Then it cleans up the base name by applying these rules, in this order:

  1. Reserved words: A Java keyword or literal, or i, ii, k, in, or not, gets the prefix column_. For example, class becomes column_class.
  2. Invalid characters: The operation removes every character other than ASCII letters, digits, _, and $. For example, Type-A becomes TypeA.
  3. Leading digits: A name that starts with a digit after step 2 gets the prefix column_. For example, 123 becomes column_123, and 1-2.3/4 becomes column_1234.
  4. Duplicate names: A name that repeats an earlier one gets a numeric suffix, starting at 2. For example, a second INFO becomes INFO2.

With a single aggregation and a single column-by column, two kinds of value need care:

  • A null column-by value becomes the reserved word null, so its column is named column_null.
  • A value with no valid characters, such as an empty string or --, can't become a column name, so the operation fails.

This example applies three combinations of aggregations and column-by columns to the same source. Each result appears in its own tab:

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: The two aggregations add their output column names as prefixes (see Multiple aggregations).
  • 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.

Duplicate names and reserved words

Duplicate names occur when different column-by values produce the same output name, or when a single aggregation produces several output columns (see Multiple aggregations).

Repeated values don't cause duplicate names. In the three-scenario example under Column naming, the operation groups the two INFO rows for each RowKey together, so they produce one INFO column.

The following example shows a duplicate name and a reserved word:

  • INFO and IN.FO both become INFO after the operation removes the .. The first column keeps the name, and the second gets the suffix 2.
  • The value class is a Java keyword, so it is a reserved word and gets the column_ prefix.

A generated name that matches a row-by column name doesn't get a suffix. Instead, it causes an error. To avoid the error, rename the row-by column with rename_columns or sanitize the values first.

Sanitize data before transposing

To control the column names yourself, clean the values before you call keyed_transpose. 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, keyed_transpose sets its output columns once, at the moment 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 when the operation runs and you don't pass an initial_groups table with rows, keyed_transpose has no column-by values to build columns from, and it raises an error.
  • 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 initial_groups 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 the table passed as initial_groups creates a group for every combination it lists, the ERROR cells hold a count of 0 rather than null:

The time table adds a row every second, so the result changes as you watch it. To see a result that stays put, let's swap the time table for an input table, a table that changes only when you add rows to it. The input table starts empty, and the code then adds four rows, none of them ERROR:

The result has one row for each NodeId and a column for each Level in init_groups. The ERROR column holds 0 for both nodes, and so does the WARN cell for node 20.

Handle new column-by values

By default, a ticking result fails with an error when the source receives a column-by value that has no column. This happens when:

  • A value was in neither the source nor initial_groups when the operation ran.
  • With several column-by columns, a combination of values is new.
  • Without initial_groups, a value comes back after all of its rows were removed from the source.

To ignore such values instead, set new_column_behavior to NewColumnBehaviorType.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 the initial_groups table. With IGNORE, the result keeps the INFO, WARN, and ERROR columns and leaves out the DEBUG entries:

To see the effect without waiting for ticks, this version uses an input table instead. Two of the four rows it adds have the level DEBUG:

The source table shows all four rows, but the result has only the INFO, WARN, and ERROR columns. Each node has a count of 1 in one column and 0 in the others, so neither DEBUG row is counted.

Common patterns

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

Time-series metrics

This example turns one row per metric reading into one row per Timestamp, with a column for each Metric. Each timestamp has one reading per metric, so agg.last carries that reading into its cell:

Survey responses

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

Best practices