Columns To Rows
Columns to Rows allows the data conversion from wide format to long format.
In other words, it allows the stacking of multiple column values into a single column.
Quickstart
Let us look at the following sample data:
| Country | 2000 | 2005 | 2010 |
|---|---|---|---|
| China | 1,242,612,300 | 1,307,593,000 | 1,339,724,852 |
| India | 1,040,000,000 | 1,103,371,000 | 1,182,105,564 |
| United States | 281,421,923 | 295,520,000 | 309,349,689 |
The same data can also be represented in the following format:
| Country | Year | Population |
|---|---|---|
| China | 2000 | 1,242,612,300 |
| China | 2005 | 1,307,593,000 |
| China | 2010 | 1,339,724,852 |
| India | 2000 | 1,040,000,000 |
| India | 2005 | 1,103,371,000 |
| India | 2010 | 1,182,105,564 |
| United States | 2000 | 281,421,923 |
| United States | 2005 | 295,520,000 |
| United States | 2010 | 309,349,689 |
The first format of representation of data is called the wide format since the years in the data are distributed across the columns. The second format is called the long format because the years in the data are distributed across the rows. where each year of data is in a separate observation. Data in the long format is easier to use for some types of analysis.
If the data is in long format:
- You can rank values within each category and see them in relation to one another.
- Filter easily across different categories.
- Create summary more easily by grouping or crosstabing the data.
The Columns to Rows task can convert wide format to long format by completing the following steps:
- Go to Transform > Reshape, Group & Aggregate.
- Click on the Columns To Rows option.
- Select all the columns that you want to convert from wide format to long format.
- Name the Label and Value columns appropriately.
- Click APPLY.
Supported Options
The following options are supported:
- Column Selection: You can select the columns that you want to convert to long format. You can tweak the labels attached to each column. Only the columns of matching types can be stacked and the resulting value column produced also has the same type.
- Label: The name of the column where all the labels are stacked.
- Value: The name of the column where all the column values are stacked.