Unpivot

Unpivot is a powerful tool to convert table columns into rows. Specifically, it transforms multiple columns into two columns: unpivot_table_metric and unpitvot_table_value . Column names are placed in the former columns and their corresponding values are in the latter column. Notice that Unpivot is not the exact reverse of the Pivot Table action, since it doesn't do any aggregation or produce a summary table. That is, Unpivot doesn't merge several values into one value. It merely changes the format of a table by transforming a wide table into a long table.

Below is an example table that shows how often people wear masks in two counties of New York during COVID-19. Never, rarely, sometimes, frequently, and always refers to the proportion of the population in different levels of mask-wearing frequency. We can combine them together by applying the Unpivot action, so the table becomes a longer table with fewer columns (from 7 columns to 4 columns).

Unpivot example - frequency of mask-wearing

How to apply Unpivot?

There are two options under Unpivot in Acho:

• Columns to unpivot

  You can click the dropdown menu to choose one or more columns to unpivot.

Unpivot Panel - Choosing columns to unpivot

• Columns to keep

  For the rest of the columns that are not used for unpivoting, you can decide whether to keep them or not. This option is optional. You can specify 0 or more columns to keep.

Unpivot Panel - Choosing columns to keep

Data types for unpivoted columns

1. If unpivoted columns are all in the same type, the unpivot_table_valuecolumn will have the same data type. Take the mask-wearing data as an example. Since all input columns are in a float type, the type of the resulting column is also Float.

2. If unpivoted columns have mixing types (such as integer and string), the unpivot_table_valuecolumn will result in a string type. Below is an example to unpivot columns that have mixing types(integer, string, and date).

When to use Unpivot?

1. When you have multiple columns that share the same characteristics (for example, never, rarely, sometimes, frequently, and always refers to the frequency of mask-wearing), you want to combine them together.

2. When you have multiple columns that indicate distinct metrics (such as the number of confirmed cases and deaths of COVID-19), you want to create a line chart or column chart to compare these metrics at the same time.

Using Unpivot to create a split-line chart.