Spotfire Transformations — Pivot

  • Have you always wanted to understand what all of the transformation options do but aren’t quite sure about the menus?
  • Do you want to learn more about the pivot transformation?
  • Do you need to aggregate tables?
  • Do you need to transform rows to columns or columns to rows?

I am starting a 6 week series covering all of the Transformation options in Spotfire.  This week, I will discuss the pivot transformation, and the remaining transformation options will be discussed as shown below.

Week 2 – Unpivot
Week 3 – Normalization
Week 4 – Data function
Week 5 – Calculate new column and Calculate and replace column
Week 6 – Change column names, Change data types, and Exclude columns


Pivot transformations are used to aggregate data and also to change what constitutes a row and/or a column.  It’s a useful function for changing the granularity of a table from one that is very granular with a lot of records to one that is less granular with fewer records.

In this post, I will start by describing what each of the menu options do, and then I will show two examples.  The first example will demonstrate how to use the pivot transformation to aggregate a table.  I will demonstrate with a production data table containing one record per well per day.  It will be transformed into a table with one record per well, and the gas and oil columns add up all production for each well.  The second example will demonstrate how to change what constitutes a row and/or column.  I will use the same starting table, and I will end up with a new table that has one record per well, but there will be a column for each month of production.

Just in case you are unfamiliar, transformations are accessed via the Insert menu under Transformations.  The user must select which transformation they want to perform from a drop down menu and then click the Add button, as shown here:

After clicking Add, this menu will appear:

 Row identifiers — this selection defines the row.  In this example, we want one record per API.  Use keys or other identifiers here.

Column titles — this selection specifies how data should be split up into columns.  In this example, the columns are staying the same, so no selection is made

Values and aggregation methods — this selection specifies which columns should be aggregated and how.  In this example, we want to add up oil and gas for each API.  When you first click the selector, you won’t see an option for an aggregation.  This only appears after a column is chosen.

Column naming patterns — this selection specifies how the columns should be named.  Even though it looks and functions like a drop down, the user may also use this selector like a  text box.  Place the cursor in the text box and delete any unnecessary naming.  In this example, I have chosen to show only the Value, which is Gas Prod and Oil Prod.  The preview pane will update and show the end result.

Transfer columns and aggregation methods — this selection is where the user should specify any other columns of data they would like in the data table.  For categorical columns, use the Unique Concatenate method.

Transfer column naming pattern — same as column naming pattern.


Now for examples!

Example 1 — The table currently contains one record per well per day.  We will transform it to contain one record per well, and we will add up all the production for each well in the OilProd and GasProd columns.  Note, this is dummy data.

This is the starting data set :

Here are the selections made in the Pivot dialog:

  Row identifiers — The API is the row identifier.

Column titles — The columns themselves will not change, so no selection has been made.

Values and aggregation methods — Here we specify that the OilProd and GasProd columns should be added up.

Column naming patterns — The columns will be names based on the values only (GasProd and OilProd)

Transfer columns and aggregation methods — I would also like to see the Reservoir, Field and Well Name fields.  I am using the UniqueConcatenate aggregation.  If Concatenate were used, I would see multiple values for a record.

  Transfer column naming pattern — The transfer columns should only carry across their existing name, so %T was selected.

 

The result looks like this:


Example 2 — We will start with the same table and transform it to have one record per well, but our columns will change.  In this example, we will have one column for each month of production.  Note that a calculated column has been added via transformations to calculate the production month.

Here are the selections made in the Pivot dialog:

  Row identifiers — The API is the row identifier.

Column titles — The columns should be defined by the production month.

Values and aggregation methods — Here we specify that the OilProd and GasProd columns should be added up.  Because we have specified that the columns should be defined by the production month, oil and gas will only be added up for each column/month.

Column naming patterns — The columns will be names based on the values (GasProd and OilProd) and the column titles

Transfer columns and aggregation methods — I would also like to see the Reservoir, Field and Well Name fields.  I am using the UniqueConcatenate aggregation.  If Concatenate were used, I would see multiple values for a record.

  Transfer column naming pattern — The transfer columns should only carry across their existing name, so %T was selected.

The result looks like this:

I have found pivot transformations to be especially useful when working with cubes and for rearranging data in general.  If you have any comments or questions, you may use the comments dialog below or email me directly at julie@bigmountainanalytics.com.  Thank you!

Guest Spotfire blogger residing in Whitefish, MT.  Working for SM Energy’s Advanced Analytics and Emerging Technology team!

One thought on “Spotfire Transformations — Pivot

Leave a Comment

Your email address will not be published. Required fields are marked *