Data can be organized in different ways, for example, in a short/wide or tall/skinny format, but still contain the same information. Often, it is easier to visualize data organized in a tall/skinny format, that is, when the values are collected in just a few value columns. Unpivoting is one way to transform data from a short/wide to a tall/skinny format, so the data can be presented the way you want it in the visualizations.
- In the Data panel, click to select the columns to combine.
- Right-click on one of the chosen columns and select Unpivot from the pop-up menu.
Example: Combining multiple temperature inputs to an average temperature
This example shows unpivoting of a very simple data table. In the original data table, there are three columns and four rows. Each row contains a city, a morning temperature and an evening temperature for each city:
While this is certainly useful data, you may want to determine the average temperature of all the cities for the whole day instead.
In the Data panel, click to select the Morning temperature and Evening temperature columns, then right-click and select Unpivot.
You can change the names of the new columns in the expanded data table, if desired. In this example, the name of the new value column is changed to "Temperature", and the new category column is called "Type of measurement".
After unpivoting the data, there is one row for each measurement in the data table.
Now, you can easily show an average value of the temperature in each city, for example, in a bar chart.
Example: Combining sales data from several desks into a single column
In this example, a larger data set containing data on the sales of entrance tickets for a museum is used. The original data table shows data for each of the five ticket counters (desks) and the number of tickets they have sold to adults, children and senior citizens each day. The data is organized in a short/wide format, that is, the spreadsheet has many columns with similar data.
If you are more interested in analyzing ticket sales in general, rather than needing to know which counter sold how many tickets to whom, you can unpivot the data. This way, you can combine the Desk columns into a single column, and merge all ticket sales numbers to another column.
In the data table below, the same data is organized in the tall/skinny format. The values from the desks have been combined in one column, called "Tickets", and "Desk" is the new category column showing from which desk the tickets were sold.
By doing an analysis of the new data table, you can easily show that Thursdays are the days when we sell the least amount of tickets.