Page tree

 

Support | BlogContact Us | 844.332.2821

 

Contents:

The cloud-based version of Trifacta Wrangler is now available! Read all about it, and register for your free account.

Contents:


pivot table summaries data that is sourced from another table. Using pivot tables, you can calculate aggregating functions, such as sums, maximums, and averages for one or more columns of data. Optionally, these sums can be performed across groups of values from one column and broken out in columns based on the values in another. In Trifacta Wrangler, a pivot table is composed of the following basic elements:

Pivot table elementDescription
Column labelsList of one or more columns whose values are represented as the columns in the generated pivot table.
Row labelsList of one or more columns whose values become the rows in the generated pivot table.
Values

Also known as facts, these values are one or more aggregation formulas, which are calculated in the following manner:

"Show me the value of this formula computed by each row value for every value represented in the generated table."

NOTE: If your aggregation does not include the kind of transformation listed above, in which the data is pivoted from rows into columns, you can use the Group By transformation. See Create Aggregations.

Building a Pivot Table

Pivot tables are very powerful tools for summarizing and visualizing large-scale volumes of data. In Trifacta Wrangler, search for pivot table in the Search panel to create one.

NOTE: A pivot table completely replaces the source table. Data that is not captured in the pivot definition is lost.

In your flows, you may find it useful to create your pivot tables in independent recipes that are chained from your primary recipe. For more information, see Create Branching Outputs.

Example Data

Pivot tables are perhaps best explained by example. The following table snippet captures transactional data from a number of stores for a range of products across a set of dates. Transactional values include total sales, quantity, and cost (POS_SalesPOS_Qty, and POS_Cost):

DailyStore_NbrPOS_SalesPOS_QtyPOS_CostPRODUCT_DESC
2/8/1317074.97ACME LAWN GARDEN BAG CLEAR
2/7/13210.6298.37ACME COOKIES CHOC CHIP
2/7/132000ACME SANDWICH BAG
2/7/1327.0865.58ACME SODAS SALTED
2/7/1323.9222.82ACME SCENTED OIL REFILL-CTRY SUN
2/7/13213.44710.36ACME LARGE FUDGE GRAHAMS COOKIES
2/7/132000ACME SUGAR ICE WAFERS VANILLA
2/7/1333.1622.86ACME ZOO ANIMAL FRUIT SNACKS 6'S
2/7/1333.1622.78ACME WAFERS SUGER ICE
2/7/1333.1622.82ACME SCENTED OIL REFILL-CTRY SUN
2/7/1336.3245.92ACME RICE CRACKERS ONION
2/2/1391503016.2ACME FROSTED OATMEAL COOKIE SQUA
2/2/1393.524.86ACME FRUIT SNACK CASTLE ADVENTRS
2/2/1399098.37ACME COOKIES CHOC CHIP
2/2/1393063.24ACME ASSORTED COOKIES DRP
2/2/1397076.51ACME KITCHEN BAG
2/2/1391701715.81ACME SNACK BAGS RESEALABLE
2/2/1392042.16ACME CHEDDARY SN CRACKERS/PROCES
2/2/1396.528.98ACME RICE CRACKERS TERIYAKI
2/2/1391.531.62ACME COOKIE MAPLE LEAF CREME
2/2/1393063.24ACME RICE CHIPS CHEDDAR
2/1/1371903820.52ACME FROSTED OATMEAL COOKIE SQUA
2/1/1372021.86ACME COOKIES CHOC CHIP
2/1/1371010.82ACME DIGESTIVE RICH TEA BISCUITS
2/1/1371202412.96ACME ASSORTED COOKIES DRP
2/1/1371201211.16ACME KITCHEN BAG
2/1/1379098.37ACME SNACK BAGS RESEALABLE
2/1/1371010.71ACME FUDGE MINT COOKIES SQUARES
2/1/1379.51910.26ACME CHEDDARY SN CRACKERS/PROCES
2/1/1371010.82ACME COOKIES MAPLE CREAM
2/1/1374084.32ACME COOKIE MAPLE LEAF CREME

Available Aggregations

The Pivot data transformation supports use of any aggregation function. For more information, see Aggregate Functions.

Simple Pivot Table

From the above, suppose you are interested in the sales from each store for each product. You can use the following transformation to compute these aggregated calculations:

Transformation Name Pivot table
Parameter: Column labels Store_Nbr
Parameter: Row labels PRODUCT_DESC
Parameter: Values SUM(POS_Sales)
Parameter: Max number of columns to create 500

In the above transformation:

  • The Column labels entry specifies the column whose values make up the calculated columns of the pivot table. The calculation is performed across each of these values. In this case, each column contains calculations for separate store numbers.
  • The Row labels entry specifies the column whose values define the grouping of the calculations. In this case, the sum of the sales column is performed for each product description value for each store.
  • The Values entry specifies the aggregation function to compute for each cell in the new table. In this case, you are generating the sum of sales for each product description in each store.
  • By default, this transformation generates a maximum of 50 new columns. However, if the column used for your Column labels contains more than 50 values, you may want to raise this value.

    NOTE: Avoid creating datasets wider than 2500 columns. Very wide datasets can cause performance degradation.

Results:

PRODUCT_DESCsum_POS_Sales_1sum_POS_Sales_2sum_POS_Sales_3sum_POS_Sales_7sum_POS_Sales_9
ACME LAWN GARDEN BAG CLEAR700000
ACME COOKIES CHOC CHIP010.6202090
ACME SANDWICH BAG00000
ACME SODAS SALTED07.08000
ACME SCENTED OIL REFILL-CTRY SUN03.923.1600
ACME LARGE FUDGE GRAHAMS COOKIES013.44000
ACME SUGAR ICE WAFERS VANILLA00000
ACME ZOO ANIMAL FRUIT SNACKS 6'S003.1600
ACME WAFERS SUGER ICE003.1600
ACME RICE CRACKERS ONION006.3200
ACME FROSTED OATMEAL COOKIE SQUA000190150
ACME FRUIT SNACK CASTLE ADVENTRS00003.5
ACME ASSORTED COOKIES DRP00012030
ACME KITCHEN BAG00012070
ACME SNACK BAGS RESEALABLE00090170
ACME CHEDDARY SN CRACKERS/PROCES0009.520
ACME RICE CRACKERS TERIYAKI00006.5
ACME COOKIE MAPLE LEAF CREME000401.5
ACME RICE CHIPS CHEDDAR000030
ACME DIGESTIVE RICH TEA BISCUITS000100
ACME FUDGE MINT COOKIES SQUARES000100
ACME COOKIES MAPLE CREAM000100

Conditional Aggregations

Suppose you are interested in only in the sum of sales for store numbers 1-3. To capture a more limited dataset, you can use the SUMIF aggregation function:

Transformation Name Pivot table
Parameter: Column labels Store_Nbr
Parameter: Row labels PRODUCT_DESC
Parameter: Values SUMIF(POS_Sales, Store_Nbr<4)
Parameter: Max number of columns to create 500

Most aggregation functions have a conditional (*IF) variant. See Aggregate Functions.

Multiple Aggregation Levels

None of the axes of a pivot table is limited to a single dimension. You can have multiple Column labels, Row labels, and Values (formulas). In the following transformation, aggregations have been further broken out by date, and an additional formula (Value) has been added.

NOTE: Adding multiple Column labels and Values can greatly expand the width of the dataset. Generally, adding Row labels does not expand the total count of rows.


Transformation Name Pivot table
Parameter: Column labels Store_Nbr
Parameter: Row labels1 Date
Parameter: Row labels2 PRODUCT_DESC
Parameter: Values1 SUM(POS_Qty)
Parameter: Values2 SUM(POS_Sales)
Parameter: Max number of columns to create 500

Results:

NOTE: Following results table is incomplete. Some columns have been omitted for space reasons.

DailyPRODUCT_DESCsum_POS_Qty_1sum_POS_Sales_1sum_POS_Qty_2sum_POS_Sales_2sum_POS_Qty_3sum_POS_Sales_3
2/8/13ACME LAWN GARDEN BAG CLEAR7700000
2/7/13ACME COOKIES CHOC CHIP00910.6200
2/7/13ACME SANDWICH BAG000000
2/7/13ACME SODAS SALTED0067.0800
2/7/13ACME SCENTED OIL REFILL-CTRY SUN0023.9223.16
2/7/13ACME LARGE FUDGE GRAHAMS COOKIES00713.4400
2/7/13ACME SUGAR ICE WAFERS VANILLA000000
2/7/13ACME ZOO ANIMAL FRUIT SNACKS 6'S000023.16
2/7/13ACME WAFERS SUGER ICE000023.16
2/7/13ACME RICE CRACKERS ONION000046.32
2/2/13ACME FROSTED OATMEAL COOKIE SQUA000000
2/2/13ACME FRUIT SNACK CASTLE ADVENTRS000000
2/2/13ACME COOKIES CHOC CHIP000000
2/2/13ACME ASSORTED COOKIES DRP000000
2/2/13ACME KITCHEN BAG000000
2/2/13ACME SNACK BAGS RESEALABLE000000
2/2/13ACME CHEDDARY SN CRACKERS/PROCES000000
2/2/13ACME RICE CRACKERS TERIYAKI000000
2/2/13ACME COOKIE MAPLE LEAF CREME000000
2/2/13ACME RICE CHIPS CHEDDAR000000
2/1/13ACME FROSTED OATMEAL COOKIE SQUA000000
2/1/13ACME COOKIES CHOC CHIP000000
2/1/13ACME DIGESTIVE RICH TEA BISCUITS000000
2/1/13ACME ASSORTED COOKIES DRP000000
2/1/13ACME KITCHEN BAG000000
2/1/13ACME SNACK BAGS RESEALABLE000000
2/1/13ACME FUDGE MINT COOKIES SQUARES000000
2/1/13ACME CHEDDARY SN CRACKERS/PROCES000000
2/1/13ACME COOKIES MAPLE CREAM000000
2/1/13ACME COOKIE MAPLE LEAF CREME000000

Group By

If you wish to maintain the original dataset values, you can perform aggregation calculations within a single column. For more information, see Create Aggregations.

Values to Columns

Similar to pivot, the Convert values to columns transformation converts individual values within a column to independent columns in the dataset. For each row, if the value represented by the column is present in the original data, one value is added (e.g. Yes). If it's missing, another value is inserted (e.g. No).

Tip: This type of conversion can be useful for preparing data for machine learning systems. You can convert the presence or absence of specific values in a row to 1 or 0, respectively.

In the following, the values in the Store_Nbr column have been converted to individual columns:

Transformation Name Convert values to columns
Parameter: Column Store_Nbr
Parameter: Fill when present Yes
Parameter: Max number of columns to create 250

In the above:

  • Fill when present identifies the string literal value to insert if the row contains the column's value (Yes).
  • Fill when missing identifies the string literal value to insert if the row does not contain the column's value (empty).
  • Max number of columns to create places a limit on the total number of columns that the application is permitted to create. In this case, the limit is set to 250 since the known number of stores is 250.

    Tip: It's a good habit to set limits on the maximum number of columns to create. Data can become sparse or unwieldy if limits are not considered.

Results:

DailyStore_NbrPOS_SalesPOS_QtyPOS_CostPRODUCT_DESCcolumn_1column_2column_3column_9column_7
2/8/1317074.97ACME LAWN GARDEN BAG CLEARYes    
2/7/13210.6298.37ACME COOKIES CHOC CHIP Yes   
2/7/132000ACME SANDWICH BAG Yes   
2/7/1327.0865.58ACME SODAS SALTED Yes   
2/7/1323.9222.82ACME SCENTED OIL REFILL-CTRY SUN Yes   
2/7/13213.44710.36ACME LARGE FUDGE GRAHAMS COOKIES Yes   
2/7/132000ACME SUGAR ICE WAFERS VANILLA Yes   
2/7/1333.1622.86ACME ZOO ANIMAL FRUIT SNACKS 6'S  Yes  
2/7/1333.1622.78ACME WAFERS SUGER ICE  Yes  
2/7/1333.1622.82ACME SCENTED OIL REFILL-CTRY SUN  Yes  
2/7/1336.3245.92ACME RICE CRACKERS ONION  Yes  
2/2/1391503016.2ACME FROSTED OATMEAL COOKIE SQUA   Yes 
2/2/1393.524.86ACME FRUIT SNACK CASTLE ADVENTRS   Yes 
2/2/1399098.37ACME COOKIES CHOC CHIP   Yes 
2/2/1393063.24ACME ASSORTED COOKIES DRP   Yes 
2/2/1397076.51ACME KITCHEN BAG   Yes 
2/2/1391701715.81ACME SNACK BAGS RESEALABLE   Yes 
2/2/1392042.16ACME CHEDDARY SN CRACKERS/PROCES   Yes 
2/2/1396.528.98ACME RICE CRACKERS TERIYAKI   Yes 
2/2/1391.531.62ACME COOKIE MAPLE LEAF CREME   Yes 
2/2/1393063.24ACME RICE CHIPS CHEDDAR   Yes 
2/1/1371903820.52ACME FROSTED OATMEAL COOKIE SQUA    Yes
2/1/1372021.86ACME COOKIES CHOC CHIP    Yes
2/1/1371010.82ACME DIGESTIVE RICH TEA BISCUITS    Yes
2/1/1371202412.96ACME ASSORTED COOKIES DRP    Yes
2/1/1371201211.16ACME KITCHEN BAG    Yes
2/1/1379098.37ACME SNACK BAGS RESEALABLE    Yes
2/1/1371010.71ACME FUDGE MINT COOKIES SQUARES    Yes
2/1/1379.51910.26ACME CHEDDARY SN CRACKERS/PROCES    Yes
2/1/1371010.82ACME COOKIES MAPLE CREAM    Yes
2/1/1374084.32ACME COOKIE MAPLE LEAF CREME    Yes

Your Rating: Results: PatheticBadOKGoodOutstanding! 2 rates

This page has no comments.