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:
|Jacques Cousteau||23, quai de Conti||Paris||France||75270|
|Emmy Noether||010 N Merion Avenue||Bryn Mawr||Pennsylvania||USA||19010|
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.
You can transpose the multiple address columns into a series of rows instead:
|Jacques Cousteau||23, quai de Conti|
|Emmy Noether||010 N Merion Avenue|
You can include the column-name information in each cell by prepending it to the value, with or without a separator:
|Jacques Cousteau||Street: 23, quai de Conti|
|Postal code: 75270|
|Emmy Noether||Street: 010 N Merion Avenue|
|City: Bryn Mawr|
|Postal code: 19010|
You can retain the column names as separate cell values, by selecting “Two new columns” and naming the key and value columns.
|Jacques Cousteau||Street||23, quai de Conti|
|Emmy Noether||Street||010 N Merion Avenue|
Transpose cells in rows into columns
Imagine employee data in this format:
|Employee: Karen Chiu|
|Job title: Senior analyst|
|Office: New York|
|Employee: Joe Khoury|
|Job title: Junior analyst|
|Employee: Samantha Martinez|
|Job title: CTO|
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:
|Karen Chiu||Senior analyst||New York|
|Joe Khoury||Junior analyst||Beirut|
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 1||Column 2||Column 3|
|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|
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.”
Consider the following example, with flowers, their colours, and their International Union for Conservation of Nature (IUCN) identifiers:
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:
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:
This table is transformed by the Columnize operation to:
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.
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:
|Color||White||Contributed by Martha|
If the “Source” column is selected as the notes column, this table is transformed to:
|Name||Color||IUCN ID||Source: Name||Source: Color|
|Galanthus nivalis||White||162168||IUCN||Contributed by Martha|
|Narcissus cyclamineus||Yellow||161899||Legacy||2009 survey|
Notes columns can therefore be used to preserve provenance or other context about a particular key/value pair.
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:
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:
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.
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:
This will be transformed to:
|Wikidata ID||Name||Color||IUCN ID|
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.