Keyed transpose

This guide shows you how to use keyedTranspose to transform data from a long format to a wide format by pivoting column values into new column names. This is useful when you need to reshape data for analysis, reporting, or visualization.

When to use 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. An aggregation 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 occurrences for each Date-Level combination.

Multiple row keys

You can specify multiple columns as row keys 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 simultaneously. When you do this, column names are prefixed with the aggregation name:

The resulting columns will be named like TotalSales_North, TotalSales_South, AvgRevenue_North, and AvgRevenue_South.

Initial groups for ticking tables

When working with ticking (live updating) tables, you may want to ensure all expected columns exist from the start, even if no data has yet arrived. Use the initialGroups parameter:

Even though the source only has INFO logs from NodeId 10, the result will include columns for all Level-NodeId combinations specified in initGroups.

Column naming

The keyedTranspose operation follows specific rules for naming output columns:

This example demonstrates each of the column naming scenarios described above:

In this example:

• Normal: Standard column name (single aggregation, single column-by).
• 1234: Invalid characters (-, ., /) are removed.
• column_123: Numeric value is prefixed with column_.
• INFO and INFO2: Duplicate names get suffixes.
• WARN: Additional standard column name.
• Sum_Normal, Count_Normal: Multiple aggregations prefix the column name.
• INFO_10, WARN_10: Multiple column-by values are joined with underscores.

Sanitize data before transposing

To maintain control over column names, clean your data values before using keyedTranspose:

Simple examples

Sales by region and product

Time-series metrics

Survey responses

Best practices

• Performance: keyedTranspose creates new columns dynamically. This can create tables with many columns for very high-cardinality data (many unique values in columnByColumns).
• Ticking tables: Use initialGroups to ensure consistent column structure when working with live data. Column limits: Be mindful of the number of unique values in your columnByColumns — each becomes a separate column.
• Aggregation choice: Choose aggregations that make sense for your data. Common choices include AggCount, AggSum, AggAvg, AggFirst, and AggLast.

Related documentation

• Multi-aggregation guide
• keyedTranspose
• Javadoc