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.
Transpose interactive example
Loading interactive example…
The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.
| item | Name | Value |
|---|---|---|
| Tea | Jan | 10 |
| Tea | Feb | 20 |
| Coffee | Jan | 30 |
| Coffee | Feb |
- Change
FebfromDatatoSkip. Only Jan is transposed, so the output has two rows. - Change
FebtoKey. Its original value repeats beside each Jan result instead of becoming a separate row. - Change
Febback toDatato restore four rows. - Select the Select block and select its output port. It renames
NametomonthandValuetoamount.
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.