Cleaning Data with OpenRefine: Transposing Data

This is how the data looks before transposition. For this example, we will be combining several of these columns into one.

To transpose columns into rows:

Select the column you would like to begin with. Click the down arrow next to the column header and on the dropdown menu, go to Transpose > Transpose cells across columns into rows.

In the popup window:

1. Select the first column you would like affected by this transformation from  the From Column list.
2. Select the last column you would like affected by this transformation form the To Column list.
3. Pick to transpose into either One column or Two new columns.
   1. One column will replace the current column, merging the current column title and the current column values into a single value using a separator of your choosing.
   2. Two new columns will replace the current column with two new columns:
      1. One using the column names of the current column and all columns to the right in the grid view
      2. One using the current column values
4. Select “Fill down in other columns” to automatically fill in the associated data in the new rows in all the columns appearing to the left of the current column in the grid view.

In this example, we are going to transpose the columns Release year, Genre, Duration, Country, and Content Rating into one column and title it Release Information. I select the start and end columns, type in a title for the new column, and leave the Fill Down option unchecked.

The data from several columns has now been transposed into a singular column.

This is how the data looks before transposition. The Release Information column is holding several types of data.

To transpose rows into columns:

Select the stacked column you would like to split up. Click the down arrow next to the column header and on the dropdown menu, go to Transpose > Transpose cells in rows into columns.

In the popup window, enter the number of rows you would like to transpose and click OK. For this example, we will choose 5.

The data from the singular column has now been broken out into several columns for more granular analysis. The new columns can now be renamed according to their content.