Skip to main content

Transpose Node

Help Center Manager - Edify
  • Updated

About:  This article introduces the Transpose node, a Compile node within Construct.
Location: Node panel

Table of Contents

Feature Overview

Transpose node icon

The Transpose node is a way to restructure data from a long format (data stored in rows) to a wide format (data stored in columns). This is usually done when the dataset contains a column that has categorical values. Each of those categories can become its own column and the associated records can then be reorganized to fall under the category column they are associated with. 

Example

Consider a dataset containing three columns: A column containing an Employee name, a column for the month, and a column for the amount of sales each employee made.

Sample dataset before transpose

Suppose it was preferable to have each month be its own column so that comparisons could be made across months. It would be important to retain which month each sale was made in and who the associated employee was.
In the Transpose node, the column containing the categories that you want to become the new column names should be added to the Transpose By section. After adding the column to the Transpose By section, you need to click the Values button and either manually enter (using the blue plus sign) the categories values that will become the new column names, or click Get Values.

Configuring a transpose node

Transpose By Values

Note: The number of Values that are added is the number of new columns that will be created per variable in the Transpose Columns section.

The column(s) that contain the values you want to be reorganized under the new columns should be added to the Transpose Columns section. Multiple variables can be added to the Transpose Columns section, however this will quickly increase the new column count. This can be noted in the bottom right corner of the configuration window.

Any columns that are not included in the Transpose By or Transpose Columns sections will remain in the original format. Sometimes it is best to remove unused columns before transposing so that the output can more easily be flattened.

Note: Selecting the check box to Aggregate after transpose will aggregate by any columns left in the Input Columns list.

In this example, the resulting output still contains an unchanged “Person” column. But in place of the original “Month” and “Sales” columns are three new columns that capture the transposed data: “Sales_January,” “Sales_February,” and “Sales_March.”

Sample dataset after transpose and aggregate

mceclip0.png

Note that if the Aggregate after transpose option had not been selected the output would have looked like this:

Sample dataset after transpose without aggregate

mceclip1.png

Alternatively, a separate Aggregate node can be used after the Transpose node to flatten the file and accomplish this.

 

Video Tutorial

Recently viewed articles

Comments

0 comments

Article is closed for comments.