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?Transform BuilderData TypeDescription
pivotYPivot columnstransformName of the transform
colYColumnstringSource column name or names
valueYFunctionsstringExpression containing the aggregation function or functions used to pivot the source column data
groupNGroup bystringColumn name or names containing the values by which to group for calculation
limitNMax columns to createinteger (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
YesString (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
YesString 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

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

Send feedback about...

Google Cloud Dataprep Documentation