Page tree

Trifacta SaaS



Contents:

   

Contents:


You can convert columns into rows of values. A conversion transformations 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.

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. 

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.