Documentation

    Transpose

    Turn columns such as monthly sales into rows so you can work with all months in one column. Keep identifiers alongside each value and skip columns you don't need.

    Turn months into rows

    Manual Input has Tea and Coffee with Jan and Feb columns. Transpose keeps item as a Key and uses both months as Data, producing four rows.

    The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.

    Result
    itemNameValue
    TeaJan10
    TeaFeb20
    CoffeeJan30
    CoffeeFeb
    1. Change Feb from Data to Skip. Only Jan is transposed, so the output has two rows.
    2. Change Feb to Key. Its original value repeats beside each Jan result instead of becoming a separate row.
    3. Change Feb back to Data to restore four rows.
    4. Select the Select block and select its output port. It renames Name to month and Value to amount.

    When to use

    Use Transpose when values are spread across columns, such as one column per month, and you need them in one column. To go the other way, use Cross Tab.

    Read the output

    Transpose produces four rows with item, Name and Value. Tea has a Jan row containing 10 and a Feb row containing 20. Coffee has a Jan row containing 30 and a Feb row containing NULL.

    Each item repeats because it's a Key. The month names come from the Data column headings, and their values become text. Missing cells still produce rows.

    The final Select block renames Name to month and Value to amount, giving the result the columns item, month and amount. Renaming doesn't change the values or their types.

    To turn values in a column into separate columns, use Cross Tab.

    To group and total the resulting rows, use Summarize.

    Inputs and outputs

    Inputs

    1 port
    InRequired

    Accepts: Tables, Geospatial data.

    Accepts one connection on this port.

    Outputs

    1 port
    Out

    Configure the block

    Choose a role for each input column. If a chosen key column is missing from the input, the block reports an error. A missing data column is skipped with a warning, and its settings are kept.

    Role

    Data turns a column into Name and Value rows. Key repeats its original value beside each result. Skip excludes the column. At least one column must remain Data. Default: Data.

    Transpose FAQs

    Are missing values dropped?
    No. A missing cell still produces a row with NULL in Value.
    What type is Value?
    Value is text, so source columns of different types can share one output column. Numeric source values also become text.
    Can I skip every data column?
    No. The block reports an error if no column has the Data role.

    Prepare your data. Build it in Strata.

    Strata opens in your browser, with no account.