Skip to main content

University Library, University of Illinois at Urbana-Champaign

OpenRefine

A free, open source, powerful tool for working with messy data.

Data Shaping

OpenRefine includes functions which allow you to reshape your data, transposing information between rows and columns to visualize the information in a different arrangement.

Rows into Columns

To transpose rows into columns:

  1. Go to the column with the data you would like to separate into different columns.
  2. Click the arrow button in the column header and select “Transpose.”
  3. Select “Transpose cells in rows into columns.”

Process for transposing rows into columns

  1. In the pop-up window, enter the number of rows you would like to transpose and click OK.

NOTE: If you enter “2,” OpenRefine will create two columns, pulling cell values from rows in sets of two. For example: (Row 1, Column A) and (Row 2, Column A) will become (Row 1, Column A) and (Row 1, Column B) respectively.

Example of transposing rows into columns

Helpful Tips:

  • This function works best when you have multiple values in a single cell which have been split following the instructions for Creating a New Row Based on a Separator.
  • This function can be useful for visualizing and separating out compound pieces of information, similarly to Creating a New Column Based a Separator or Field Length.
  • All transformations of rows into columns are tracked in the Undo/Redo tab, so you can experiment with transposing rows into columns until you are comfortable with the result.

Columns into Rows

To transpose columns into rows:

  1. Go to the column you would like to begin transposing into rows.
  2. Click the arrow button in the column header and select “Transpose.”
  3. Select “Transpose cells across columns into rows.”

Process for transposing cells across columns into rows

  1. In the pop-up 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.

Transposing cells across columns into rows window

  1. Click “Transpose.”

Example of transposing across rows into columns

Helpful Tips:

  • If there are specific columns you would like to transpose into rows, consider moving them next to each other by following the instructions for Reordering Columns.
  • Since transposing columns to rows does not preserve record associations (see Records and Rows), make sure you have a unique identifier column not being included in the transformation. This way, when you select “Fill down in other columns.” the unique identifier can be used to associate related data.
  • All transformations of columns into rows are tracked in the Undo/Redo tab, so you can experiment with transposing columns into rows until you are comfortable with the result.

5/22/2018 - Brinna Michael