Page tree

The cloud-based product Trifacta Free is available now! Register for your free account.

 

Contents:


You can convert columns into rows of values. The Convert transformation extracts the values from a specified column or columns and turns the column name and each extracted value into key-value pairs.  

  • Unpivot can be applied to one or more columns.
  • Often, this transformation is applied to datasets containing pivoted or aggregated data. For more information, see Pivot Data.

NOTE: Depending on the number of source columns, an unpivot operation can significantly increase the number of rows in your dataset.

Single-column Unpivot

When you unpivot a single column of data, the column is separated into two new columns in your dataset:

New column nameValues
keyAll values are the name of the source column.
valueEach row contains one of the row values from the source column.


NOTE: These columns replace the source column in the dataset. To retain the source column, create a copy of it first and then unpivot the copied column.

Source:

The following example contains a very simple set of data: 

NamefavoriteColorfavoriteDessert
Annaredice cream
Bellapinkcookies
Calliebluepie

Transformation:

You can unpivot these columns one-by-one into row data:

Transformation Name Unpivot columns
Parameter: Columns favoriteColor
Parameter: Group size 1

Results:

The new unpivoted columns are placed at the end of the dataset, and the source column is removed.

NamefavoriteDessertkeyvalue
Annaice creamfavoriteColorred
BellacookiesfavoriteColorpink
CalliepiefavoriteColorblue

Multi-column Unpivot

This example turns the data from multiple columns into a single set of key-value pairs, where the key is the column name associated with the source of the data in the value column.

Source:

The following dataset shows student test scores per test. Each row represents the scores of individual students. 

StudentIdtest1Scoretest2Scoretest3Score
001757977
002848186
003798287
004929492

Transformation:

You can use the following transformation to turn the dataset into one row per student-test combination:

Transformation Name Unpivot columns
Parameter: Columns test1Score, test2Score, test3Score
Parameter: Group size 1

Results:

The results are as follows:

StudentIdkeyvalue
001test1Score75
002test2Score79
003test3Score77
001test1Score84
002test2Score81
003test3Score86
001test1Score79
002test2Score82
003test3Score87
001test1Score92
002test2Score94
003test3Score92

You can then rename the key and value columns as needed. See Rename Columns.

Ranges

You can specify a range of columns in your dataset. In the previous example, you can specify the three test score columns using the following value in the Columns textbox:

test1Score~test3Score

All three columns are unpivoted.

Wildcards

NOTE: You can use the asterisk ( * ) wildcard in the Columns textbox to apply the unpivot to the entire dataset, which generates a key and a value column, containing all column-row entries from the source columns. However, unpivoting a large number of columns can significantly increase the number of rows in your dataset.

This page has no comments.