Search code examples
openrefine

Transposing to records with different lengths

I have a problem similar to Transpose rows into columns with OpenRefine (variable number of rows) and I found the solution provided there to be very useful (I hadn't really used the records mode before). However, in my case I have a column of attribute names that I'd like to transpose into new column names. So for instance if I start with

ID, Attribute, Value
1, height, 150mm
1, width, 20mm
2, height, 200mm
2, color, blue
3, mass, 5g

After blanking down (on ID) and going to records mode I get a nice view of the records, but I don't know how I'd go about transposing this to

ID, height, width, color, mass
1, 150mm, 20mm, ,
2, 200mm, , blue,
3, , , , 5g

which would be useful for me for for further refining (e.g. making a facet on 'width', say).

Solution

  • The function you are looking for is Columnize by key/value column... openrefine Columnize

    It will create one new column for each value in your key column in your case the Attribute column and move the Value in its respective column.

    enter image description here

    I recorded a short (50sec) walkthrough video based on the example you provided: https://youtu.be/IUGubwfiJ6o

    I am using this GREL expression row.record.cells[columnName].value[0] to move all the value on the same records (more details on how to use ColumnName here)