transpose

data management

slug: step-transpose

Purpose

Converts rows into columns (and vice versa) for easier analysis or visualization. Optionally uses a column’s values as new column headers.

When to Use

• Pivot datasets where row values are better represented as column headers
• Prepare data for charts or reports that require wide format
• Reorient datasets for analysis where each variable should become a row

How It Works

1. Extracts input dataset and optional metadata (PDV and extras).

2. If an id column is specified, its values are used as column names in the transposed dataset.

3. Calls a transpose() function to swap rows and columns.

4. Builds a new dataset with:

   • name column representing original variable names
   • Either default column names (col0, col1, …) or names from the id column

5. Updates PDV metadata and flags the dataset as transposed in extras.

Important: Any formatting, labels, or options applied to the original columns are carried forward to the resulting transposed rows. For example, a column formatted as a dollar amount will maintain that formatting in the transposed row labeled by that column.

Parameters

Optional

• id (string) – Column whose values will become the new column headers. If omitted, default column names col0, col1, etc., are used.

Input Requirements

• Any non-empty dataset; ideally rectangular (same number of columns in each row)
• If using id, the column must exist and contain unique or meaningful values

Output

• data: Transposed dataset as an array of rows
• pdv: Updated PDV metadata (e.g., hides original id column and renames name label to Properties)
• extras: Includes 'transposed' => true flag
• outputType: 'array'

Example Usage

steps:
  - loadInline:
      data:
        # Young Segment
        - {user: bob,      age: 22, income: 38000, spend: 800,  segment: 'Young'}
        - {user: sam,      age: 25, income: 45000, spend: 1200, segment: 'Young'}
        - {user: sally,    age: 29, income: 56000, spend: 1800, segment: 'Young'}
        - {user: betty,    age: 31, income: 60000, spend: 2000, segment: 'Young'}

        # Mid Segment
        - {user: tim,      age: 34, income: 67000, spend: 2100, segment: 'Mid'}
        - {user: mike,     age: 38, income: 74000, spend: 2600, segment: 'Mid'}
        - {user: lulu,     age: 41, income: 79000, spend: 2800, segment: 'Mid'}
        - {user: charlie,  age: 44, income: 83000, spend: 3000, segment: 'Mid'}

        # Senior Segment
        - {user: romeo,    age: 46, income: 87000, spend: 3100, segment: 'Senior'}
        - {user: tango,    age: 50, income: 95000, spend: 3700, segment: 'Senior'}
        - {user: alpha,    age: 55, income: 102000, spend: 4200, segment: 'Senior'}
        - {user: foxtrot,  age: 60, income: 110000, spend: 4800, segment: 'Senior'}

      attributes:
         user: { label: Who }
         age: { label: Years Old }        
         income: { hidden: false, label: Income Level, type: number, options: {format: dollar, decimal: 0} }
         spend: { hidden: false, label: Spend Amount, type: number, options: {format: dollar, decimal: 0} }
         segment: { label: Segment Group }

      output: testData

  - print: 

  - transpose:
  - print:

  - transpose: {dataset: testData, id: user}
  - print:

Explanation:

• The first transpose flips the dataset without using a specific id; columns become rows with default column names (col0, col1, …).
• The second transpose uses the user column values as column headers.
• Formatting, labels, and other attributes applied to columns (like income formatted as dollars) carry over to the resulting rows.

Notes & Best Practices

• Use when preparing data for reporting, visualization, or pivot-style analysis.
• Be mindful of datasets with a very large number of unique id values — transposed dataset may become very wide.
• The extras.transposed flag can be used by downstream steps like table builders or visualization steps to correctly interpret the PDV.