Transposing

Overview

These functions were created to solve common problems with reshaping your data: pivoting cells from a row into a column, or pivoting cells from a column into a row. You can also transpose from a repeated set of values into multiple columns.

Transpose cells across columns into rows

Imagine personal data with addresses in this format:

NameStreetCityState/ProvinceCountryPostal code
Jacques Cousteau23, quai de ContiParisFrance75270
Emmy Noether010 N Merion AvenueBryn MawrPennsylvaniaUSA19010

You can transpose the address information from this format into multiple rows. Go to the “Street” column and select “Transpose” → “Transpose cells across columns into rows.” From there you can select all of the five columns, starting with “Street” and ending with “Postal code,” that correspond to address information. Once you begin, you should put your project into records mode to associate the subsequent rows with “Name” as the key column.

A screenshot of the transpose across columns window.

One column

You can transpose the multiple address columns into a series of rows instead:

NameAddress
Jacques Cousteau23, quai de Conti
Paris
France
75270
Emmy Noether010 N Merion Avenue
Bryn Mawr
Pennsylvania
USA
19010

You can include the column-name information in each cell by prepending it to the value, with or without a separator:

NameAddress
Jacques CousteauStreet: 23, quai de Conti
City: Paris
Country: France
Postal code: 75270
Emmy NoetherStreet: 010 N Merion Avenue
City: Bryn Mawr
State/Province: Pennsylvania
Country: USA
Postal code: 19010

Two columns

You can retain the column names as separate cell values, by selecting “Two new columns” and naming the key and value columns.

NameAddress partAddress
Jacques CousteauStreet23, quai de Conti
CityParis
CountryFrance
Postal code75270
Emmy NoetherStreet010 N Merion Avenue
CityBryn Mawr
State/ProvincePennsylvania
CountryUSA
Postal code19010

Transpose cells in rows into columns

Imagine employee data in this format:

Column
Employee: Karen Chiu
Job title: Senior analyst
Office: New York
Employee: Joe Khoury
Job title: Junior analyst
Office: Beirut
Employee: Samantha Martinez
Job title: CTO
Office: Tokyo

The goal is to sort out all of the information contained in one column into separate columns, but keep it organized by the person it represents:

NameJob titleOffice
Karen ChiuSenior analystNew York
Joe KhouryJunior analystBeirut
Samantha MartinezCTOTokyo

By selecting “Transpose” → “Transpose cells in rows into columns...” a window will appear that simply asks how many rows to transpose. In this case, each employee record has three rows, so input “3” (do not subtract one for the original column). The original column will disappear and be replaced with three columns, with the name of the original column plus a number appended.

Column 1Column 2Column 3
Employee: Karen ChiuJob title: Senior analystOffice: New York
Employee: Joe KhouryJob title: Junior analystOffice: Beirut
Employee: Samantha MartinezJob title: CTOOffice: Tokyo

From here you can use “Cell editing” → “Replace” to remove “Employee: ”, “Job title: ”, and “Office: ”, or use GREL functions with “Edit cells” → “Transform...” to clean out the extraneous characters: value.replace('Employee: ', ''), etc.

If your dataset doesn't have a predictable number of cells per intended row, such that you cannot specify easily how many columns to create, try “Columnize by key/value columns.“

Columnize by key/value columns

This operation can be used to reshape a dataset that contains key and value columns: the repeating strings in the key column become new column names, and the contents of the value column are moved to new columns. This operation can be found at “Transpose” → “Columnize by key/value columns.”

A screenshot of the Columnize window.

Consider the following example, with flowers, their colours, and their International Union for Conservation of Nature (IUCN) identifiers:

FieldData
NameGalanthus nivalis
ColorWhite
IUCN ID162168
NameNarcissus cyclamineus
ColorYellow
IUCN ID161899

In this format, each flower species is described by multiple attributes on consecutive rows. The “Field” column contains the keys and the “Data” column contains the values. In the “Columnize by key/value columns” window you can select each of these from the available columns. It transforms the table as follows:

NameColorIUCN ID
Galanthus nivalisWhite162168
Narcissus cyclamineusYellow161899

Entries with multiple values in the same column

If a new row would have multiple values for a given key, then these values will be grouped on consecutive rows, to form a record structure.

For instance, flowers can have multiple colors:

FieldData
NameGalanthus nivalis
ColorWhite
ColorGreen
IUCN ID162168
NameNarcissus cyclamineus
ColorYellow
IUCN ID161899

This table is transformed by the Columnize operation to:

NameColorIUCN ID
Galanthus nivalisWhite162168
Green
Narcissus cyclamineusYellow161899

The first key encountered by the operation serves as the record key, so the “Green” value is attached to the “Galanthus nivalis” name. See the Row order section for more details about the influence of row order on the results of the operation.

Notes column

In addition to the key and value columns, you can optionally add a column for notes. This can be used to store extra metadata associated to a key/value pair.

Consider the following example:

FieldDataSource
NameGalanthus nivalisIUCN
ColorWhiteContributed by Martha
IUCN ID162168
NameNarcissus cyclamineusLegacy
ColorYellow2009 survey
IUCN ID161899

If the “Source” column is selected as the notes column, this table is transformed to:

NameColorIUCN IDSource: NameSource: Color
Galanthus nivalisWhite162168IUCNContributed by Martha
Narcissus cyclamineusYellow161899Legacy2009 survey

Notes columns can therefore be used to preserve provenance or other context about a particular key/value pair.

Row order

The order in which the key/value pairs appear matters. The Columnize operation will use the first key it encounters as the delimiter for entries: every time it encounters this key again, it will produce a new row, and add the following key/value pairs to that row.

Consider for instance the following table:

FieldData
NameGalanthus nivalis
ColorWhite
IUCN ID162168
NameCrinum variabile
NameNarcissus cyclamineus
ColorYellow
IUCN ID161899

The occurrences of the “Name” value in the “Field” column define the boundaries of the entries. Because there is no other row between the “Crinum variabile” and the “Narcissus cyclamineus” rows, the “Color” and “IUCN ID” columns for the “Crinum variabile” entry will be empty:

NameColorIUCN ID
Galanthus nivalisWhite162168
Crinum variabile
Narcissus cyclamineusYellow161899

This sensitivity to order is removed if there are extra columns: in that case, the first extra column will serve as the key for the new rows.

Extra columns

If your dataset contains extra columns, that are not being used as the key, value, or notes columns, they can be preserved by the operation. For this to work, they must have the same value in all old rows corresponding to a new row.

In the following example, the “Field” and “Data” columns are used as key and value columns respectively, and the “Wikidata ID” column is not selected:

FieldDataWikidata ID
NameGalanthus nivalisQ109995
ColorWhiteQ109995
IUCN ID162168Q109995
NameNarcissus cyclamineusQ1727024
ColorYellowQ1727024
IUCN ID161899Q1727024

This will be transformed to:

Wikidata IDNameColorIUCN ID
Q109995Galanthus nivalisWhite162168
Q1727024Narcissus cyclamineusYellow161899

This actually changes the operation: OpenRefine no longer looks for the first key (“Name”) but simply pivots all information based on the first extra column's values. Every old row with the same value gets transposed into one new row. If you have more than one extra column, they are pivoted as well but not used as the new key.

You can use “Fill down” to put identical values in the extra columns if you need to.

Last updated on by allanaaa