Chapter 14. Pivoting Rows to Columns
In the previous chapter, we covered the first type of pivoting: columns to rows. In this chapter, we will look at pivoting data in the opposite direction—rows to columns (sometimes known as unpivoting). First we’ll look at when to pivot rows into columns and then how to actually apply this technique in Prep Builder.
When to Use a Rows-to-Columns Pivot
The Rows to Columns pivot option was added to Prep Builder in a later version of the tool, and it was a welcome addition to avoid a workaround for a common use case. Data frequently comes from sources where multiple measures or dimensions are held in a single column. To make analysis easier in Tableau Desktop, you need to separate these out into an individual column per measure or dimension. If you don’t separate them, it is technically still possible to analyze the data in Tableau Desktop (Figure 14-1).
However, if you want to create a scatterplot comparing two metrics, you can’t do so without creating multiple calculations, or duplicating data fields and filtering them. Remember, the aim of data preparation is to make the analysis easier for end users.
Figure 14-1. Visualizing the single data field of metrics
Figure 14-2 shows the visualization we want to achieve.
Figure 14-2. The data visualization after pivoting from rows to columns
How to Pivot Rows to Columns
Let’s take a look at how to pivot rows to columns.
-
In a Pivot step, use the drop-down menu at the top right of the Pivoted Fields pane to select the Rows to Columns pivot option (Figure 14-3).
Figure 14-3. Selecting the Rows to Columns pivot option
-
Adding the Measures field to the Pivoted Fields pane will display each category in the data (Figure 14-4) and create a new column in the field.
Figure 14-4. Setting the field to create new column headers
-
Add the field that contains the values for your new column headers into the lower part of the Pivoted Fields pane, under the “Field to aggregate for new columns” header (Figure 14-5).
Figure 14-5. Setting the values for the new headers
You will need to choose how multiple rows will be aggregated. The aggregation will happen at the level of whatever remains in the left-hand pane of the step. This is the equivalent of a Group By (see Chapter 15) for the fields in the left-hand pane.
Now we have the data in an easier form to analyze. Figure 14-6 shows the resulting scatterplot.
Figure 14-6. Scatterplot of the pivoted data
Summary
Getting used to pivoting data takes some time, but practice makes perfect! If you are using data that has been sourced from an Excel spreadsheet and has been held in a pivot table, the Rows to Columns pivot option will enable you to convert the data into the form that works best with Desktop.