Pivot Transform

When you pivot data, the values from the selected column or columns become new columns in the dataset, each of which contains a summary calculation that you specify.
  • This calculation can be based on all rows for totals across the dataset or based on group of rows you define in the transform.
  • When your data has been pivoted, the unused columns are dropped, leaving only the operative data for faster evaluation.
  • Transform accepts one or more columns as inputs to the pivot.

Tip: This transform is very useful in Preview mode for quick discovery and analysis.

Basic Usage

pivot col: Dates value:SUM(Sales) group: prodId

Output: Reshapes your dataset to include a ProdId column and new columns for each distinct value in the Dates column. Each distinct ProdId value is represented by a separate row in the reshaped dataset. Within each row, the new columns contain a sum of sales for the product for each date in the dataset.

Parameters

pivot col:column_ref value: FUNCTION(arg1,arg2) [group: group_col] [limit:int_num]

TokenRequired?Data TypeDescription
pivotYtransformName of the transform
colNstringSource column name or names
valueNstringExpression containing the aggregation function or functions used to pivot the source column data
groupNstringColumn name or names containing the values by which to group for calculation
limitNinteger (positive)Maximum number of unique values in a group. Default is 50.

For more information on syntax standards, see Language Documentation Syntax Notes.

col

Identifies the column or columns to which to apply the transform.

Single-column example:

The specified column contains the values that become new columns in the dataset.

pivot col: autoBrand value:AVERAGE(autoPrice) group:State

Output: Reshapes the dataset to contain the State column followed by columns for each distinct value in the autoBrand column. Each value in those columns is the average value in the autoPrice column, as grouped by State value.

Multi-column example:

For multiple input columns, the transform generates new columns for each combination of the inputs. See the example below.

Usage Notes:

Required?Data Type
NoString (column name)

value

For this transform, the value parameter contains the aggregation function and its parameters, which define the set of rows to which the function is applied.

NOTE: For the value parameter, you can only use aggregation functions. Nested functions are not supported. For more information, see Aggregate Functions.

Usage Notes:

Required?Data Type
NoString containing a list of aggregation functions each containing one column reference

group

For this transform, this parameter specifies the column whose values are used to group the dataset prior to applying the specified function. You can specify multiple columns as comma-separated values.

Usage Notes:

Required?Data Type
NoString (column name)

limit

The limit parameter defines the maximum number of unique values permitted in a group. If it is not specified, the default value for this parameter is 50.

NOTE: Be careful setting this parameter too high. In some cases, the application can run out of memory generating the results, and your results can fail.

Usage Notes:

Required?Data Type
No. Default value is 50.Integer (positive)

Examples

Example - Basic Pivot

Source:

The following dataset contains information about sales across one weekend and two states for three different products.

DateStateProdIdSales
3/9/16CABig Trike 9000500
3/9/16NVFast GoKart 5000200
3/9/16CASuperQuick Scooter700
3/9/16CAFast GoKart 5000900
3/9/16NVBig Trike 9000300
3/9/16NVSuperQuick Scooter250
3/10/16NVFast GoKart 500050
3/10/16NVBig Trike 9000400
3/10/16NVSuperQuick Scooter150
3/10/16CABig Trike 9000600
3/10/16CASuperQuick Scooter800
3/10/16CAFast GoKart 50001100

Transform 1: Sum of sales by State for each Date

Apply this transform in Preview only, just so you can see the results:

pivot col:Date value:SUM(Sales) group:State

Cancel the transform update.

Statesum_Sales_03/09/2016sum_Sales_03/10/2016
CA21002500
NV750600


Transform 2:
Sum of Sales by Date for each State

pivot col:State value:SUM(Sales) group:Date

Datesum_Sales_CAsum_Sales_NV
03/09/20162100750
03/10/20162500600

Cancel the transform update again.

Transform 3: Sum of Sales by product ID for each State

pivot col:State value:SUM(Sales) group:ProdId

ProdIdsum_Sales_CAsum_Sales_NV
Big Trike 90001100700
Fast GoKart 50002000500
SuperQuick Scooter1500300

Example - Multi-column Pivot

The pivot transform supports the ability to use the values in multiple columns to specify the columns that are generated. When two or more columns are used in the pivot, columns containing values of all possible combinations from the source columns are generated.

In the following source data, sales data on individual products is organized by brand, product name, and month:

BrandProductMonthSales
Super-coolPaper towelsJanuary95
Super-coolNapkinsJanuary113
Just-coolPaper towelsJanuary99
Just-coolTissuesJanuary88
Super-coolPaper towelsFebruary108
Super-coolNapkinsFebruary91
Just-coolPaper towelsFebruary85
Just-coolTissuesFebruary105
Super-coolPaper towelsMarch81
Super-coolNapkinsMarch92
Just-coolPaper towelsMarch112
Just-coolTissuesMarch104

Transform:

If you wanted to create summary sales information for each product by month, you might choose to create a pivot on the Product column. However, if you look at the column values, you might notice that the Paper towels product is available for both the Super-cool and Just-cool brands. In this case, you must perform a multi-column pivot on these two columns, like the following:

pivot col:Brand,Product value:AVERAGE(Sales) group:Month

Results:

Monthaverage_Sales_Just_cool_Paper_Towels average_Sales_Just_cool_Paper_Tissues average_Sales_Super_cool_Napkins average_Sales_Super_cool_Paper_Towels
January998811395
February8510591108
March1121049281

Example - Aggregate Values

You can use the pivot transform to perform aggregation calculations on values in a column.

Source:

In the following table, you can review test results from three different tests for 10 students:

StudentIdTestIdScore
s001t00198
s001t00298
s001t00387
s002t00192
s002t00296
s002t00379
s003t00199
s003t00276
s003t00394
s004t00193
s004t00299
s004t00380
s005t00179
s005t00280
s005t00384
s006t00193
s006t00274
s006t00389
s007t00186
s007t00281
s007t00397
s008t00182
s008t00273
s008t00379
s009t00196
s009t00297
s009t00379
s010t00180
s010t00279
s010t00375

Transform:

For each student, you're interested in a student's average score and maximum score. You create the following pivot transform, which groups computations of average score and maximum score columns by studentId value.

pivot group: StudentId value: average(Score),max(Score) limit: 50

For the generated average column, you want results rounded to two decimal places:

set col: average_Score value: round(average_Score, 2)

Results:

StudentIdaverage_Scoremax_Score
s00194.3398
s0028996
s00389.6799
s00490.6799
s0058184
s00685.3393
s0078897
s0087882
s00990.6797
s0107880

Was this page helpful? Let us know how we did:

Send feedback about...

Google Cloud Dataprep Documentation