Reverse Transpose Node

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

Table of Contents

• Feature Overview
  • Example
• Video Tutorial
• Related Articles

Feature Overview

The Reverse Transpose node is a way to restructure data from a wide format (data stored in columns) to a long format (data stored in rows). This is usually done when the dataset contains several columns that contain similar data for different categories. Those columns can then be reorganized into a single column with multiple rows for each category the columns previously represented.

Example

Consider a dataset containing four columns: A column containing an Employee name, and a column for the amount of sales each employee made during the months January, February, and March.

Suppose it was preferable to have all the sales amounts in one column so that aggregate statistics could calculated on all sales. It would also be important to retain which month each sale was made in, so a column containing the months would also be desired.

In the Reverse Transpose node, the columns that contain the values that you want to all be organized into a single column should all be added to the Reverse Transpose Columns section. In this example, we want all the sales amounts to be organized into a single column, so Sales_January, Sales_February and Sales_March are all added to this section. This will be a new column containing these values, so we also need to name this new column. This is done in the Reverse Transpose into Column section.

Finally, because we also want to retain the month values which were previously stored in the column names, we can add and Identity Column by selecting the box at the top of the window. The Identity Column adds another column containing the previous column names' values. So because the column names were previously the months the sales were made, we will call the Identity Column "Month". 

You can see what values are going to populate the Identity Column by looking at the Identity Value column under the Reverse Transpose Columns section.

Note: the Identity Values are the old column names by default, however the values can be changed by double clicking and typing a new name. This can create a cleaner dataset.

Once all this is set up, click the Add button to memorize this work before saving the node and running the job.

The Reverse Transpose process will result in a reduction of columns and an addition of a rows. Sticking with our example. Our output will now have a new column titled "Month" which retains which month a sale was made, and will shift all the data that was in the old columns into a single consolidated column - "Sales."

Let's take a look at these two data sets side by side to compare the difference:

Before Reverse Transpose

After Reverse Transpose

Video Tutorial