openrefinegrel

Open Refine--create new column by looking up values from a pair of columns

I have a table in OpenRefine with columns A, B, and C like this:

A | B | C | D ---|---|---|--- a | 1 | b | 2 b | 2 | | c | 3 | a | 1 d | 4 | c | 3 I want to create a column D by fetching the values from B corresponding to those in C, using A as an index. Hope that makes sense? I'm not having much luck figuring out how to do this in GREL.

Solution

  • You can use the 'cross' function to look up values across the project. Cross is usually used to look up values in a different OpenRefine project/file, but actually it works the same if you point it back at the same project you are already in.

    So - from Col C, you can use "Add new column based on this column" with the GREL:

    cell.cross("Your project name","Col A")

    You'll get back an array of 'rows' - and if the same value appears in Column A multiple times you could get multiple rows.

    To extract a value from the array you can use something like:

    forEach(cell.cross("Your project name","Col A"),r,r.cells["Col B"].value).join("|")

    The final 'join' is necessary to convert the array into a string which is required to be able to store the result (arrays can't be stored directly)