Transposing Rows in Openrefine

Question

I am using Openrefine (openrefine-2.6-rc.2) running on Windows and opening with Chrome browser (65.033225.181

I have data in text format (.txt) that I have imported into Openrefine for cleaning and processing. The data entries reside in rows under one column. I would like to "transpose" (pivot) the items in the rows so they appear in columns

Following is an example of the current state:

Column 1

Mary Smith
Company Name IBM
Location New York

John Davis
Company Name Lockheed-Martin
Location Los Angeles

Jane Segal
Company Name Microsoft
Location Boston

Ideally, by transposing the entries the result would look like this:

Last Name   First Name  Company Name    Location
Smith       Mary         IBM            New York
Davis       John         Lockheed       Los Angeles
Segal       Jane         Microsoft      Boston

Just not sure how to do this in Openrefine

Answer 1

When creating your Open Refine project, make sure that empty rows are not imported.

You can delete them later, but it's a little more complicated (see screencast).

Then, just :

1° Apply the function Transpose -> Transpose cells in rows into columns, with a value of 3.

2° Delete the words "Company Name" and "Location" using a Transform with formulas like value.replace('Company Name', '').trim() and value.replace('Location', '').trim()

3° Rename the columns.

Here is a visual tutorial.