Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r079

D toc

D s transforms

Excerpt

Reshapes the data by merging one or more columns into key and value columns. Keys are the names of input columns, and value columns are the cell values from the source. 

Rows of data are duplicated, once for each input column.  

 The unpivot column can be applied to multiple columns in the same transform. All columns are un-pivoted into the same same key and value columns. When this transform is applied to two columns, the number of rows in the dataset is doubled.

This transform is the opposite of the the pivot transform transform, which converts a set of column values into distinct columns. See See Pivot Transform .

D s
snippetBasic

Single- or multi-column example:

You can specify single columns or comma-separated sets of columns. 

D code

unpivot col: FirstName, MiddleInitial

Output: Converts Converts the values in the columns columns FirstName and  and MiddleInitial into separate  into separate key and  and value columns columns.

Column range example: 

You can also specify ranges of columns using the tilde (~) operator:

D code

unpivot col:Column1~Column20

Output:  Converts all of the values in columns between between Column1 and  and Column20 into  into key and  and value columns columns.

D s
snippetParameters

D code
unpivot col: column_ref [groupEvery: int_num]
TokenRequired?Data TypeDescription
unpivotYtransformName of the transform
colYstringName of source column or columns
groupEveryNstringIf specified, this parameter defines the number of individual key-value pairs to store in each generated column. Default is 1.

D s lang notes

col

Identifies the column or columns to which to apply the transform. You can specify one or more columns.

Include Page
col_multi Parameter
col_multi Parameter

  • Column ranges are supported:

    d-lang-syntax

    myColumn1~myColumn5

Info

NOTE: For the col value, you can use the asterisk ( * ) wildcard 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.

d-s
snippetusage

Required?Data Type
YesString (column name)

...

productNameproductColorproductSize
WhizbangredM
Whizbangred, blueL
WhizbanggreenM
BangwhizredS
BangwhizblueM
BangwhizredS

TranformTranformation:

After you have created a header, if necessary, add the following transformtransformation:

d-

...

trans
RawWrangletrue
Typestep
WrangleTextunpivot col:productColor
p01NameColumns
p01ValueproductColor
SearchTermUnpivot columns

Results:

productNameproductSizekeyvalue
WhizbangMproductColorred
WhizbangLproductColorred, blue
WhizbangMproductColorgreen
BangwhizSproductColorred
BangwhizMproductColorblue
BangwhizSproductColorred

...

Note how each instance of a value results in a separate row; duplicate values are included. For a single-column column unpivot, this transform results in the same number of rows as the source.

  • Since the value is treated as a string, the value value red, blue is treated as one value.

Now, edit the transform transformation you just added. Replace it with the following, which includes the the productSize key as part of the transformtransformation:

d-

...

trans
RawWrangletrue
Typestep
WrangleTextunpivot col:productColor,productSize
p01NameColumns
p01ValueproductColor,productSize
SearchTermUnpivot columns

Results:

productNamekeyvalue
WhizbangproductColorred
WhizbangproductSizeM
WhizbangproductColorred, blue
WhizbangproductSizeL
WhizbangproductColorgreen
WhizbangproductSizeM
BangwhizproductColorred
BangwhizproductSizeS
BangwhizproductColorblue
BangwhizproductSizeM
BangwhizproductColorred
BangwhizproductSizeS

Row keys alternate based on the order in which the source columns are specified in the transform. Since the transform specifies two columns, the number of key-value pairs is doubled, which results in a dataset that has twice as many rows as the source.

Example - Basic Pivot with groupEvery

Tranformation:

From the previous example, modify the the unpivot transform to be the following:

d-

...

trans
RawWrangletrue
Typestep
WrangleTextunpivot col:productColor,productSize groupEvery:2
p01NameColumns
p01ValueproductColor,productSize
p02NameGroup size
p02Value2
SearchTermUnpivot columns

Results:

productNamekey1value1key2value2
WhizbangproductColorredproductSizeM
WhizbangproductColorred, blueproductSizeL
WhizbangproductColorgreenproductSizeM
BangwhizproductColorredproductSizeS
BangwhizproductColorblueproductSizeM
BangwhizproductColorredproductSizeS


D s also
labelwrangle_transform_unpivot