- Have you ever needed to transform columns to rows and not know how?
- Have you wondered what the difference is between pivot and unpivot?
Last week’s post focused on the pivot transformation, which can be used to aggregate and transform rows to columns. This week, we’ll focus on the unpivot transformation, which can be used to transform columns to rows. If you were working in Excel, you would use the transpose function to do this, but in Excel that can be very time consuming, whereas in Spotfire, it’s quick and easy.
In the data set shown below, oil, gas and water are separated into three columns. Note I have a column called GasProd, which is native to the table, and a column called c.Gas Prod (BOE) that is a calculated column. The goal is to transform the c.Gas Prod (BOE), OilProd and WaterProd into one column that named Product and another called Value.
To accomplish the goal, an unpivot transformation will be performed with the following steps:
- Go to the Insert menu > select Transformations
- Set the data table to the table of interest (NOTE: you may choose to create a duplicate of the original table to transform so that a copy of the original is preserved)
- Set the ‘Transformations:’ drop down to Unpivot and click the Add button as shown here:
- Rather than starting with the Columns to pass through, begin with Columns to transform. Columns to transform are the Oil Prod, c.Gas (BOE) and Water Prod columns that we want to combine into a single column. Note the c.Gas (BOE) column is not present in the list as shown here: This is because only native columns OR columns that have been added via a transformation are eligible for transformation. Therefore, at this point in the process, it is necessary to delete the calculated column c.Gas (BOE) and add it back via a Calculated Column transformation. After this is done, and we return to the same place in the menus, the column appears as shown here:
- Move OilProd, WaterProd and c.GasProd (BOE) from left to right under Columns to transform.
- All other columns should be moved from left to right under Columns to pass through, unless they are not needed, and in which case they may be left as they are in the dialog.
- The Category column should be called ‘Product’ and the Value column can be called ‘Value’. Make sure that the data types are appropriate. String for the Product and Real for the value makes sense, but we wouldn’t want the value to be String as well.
- Click OK and click OK.
This screenshot shows the end result. The oil, gas and water columns have been transformed into two columns.
Guest Spotfire blogger residing in Whitefish, MT. Working for SM Energy’s Advanced Analytics and Emerging Technology team!