Can OpenRefine easily do One Hot Encoding?

user_231718 0
Viewed 66

I have a dataset like a multiple choice quiz result. One of the fields is semi-colon delimited. I would like to break these in to true/false columns.

Input

Student Answers
Alice B;C
Bob A;B;D
Carol A;D

Desired Output

Student A B C D
Alice False True True False
Bob True True False True
Carol True False False True

I've already tried "Split multi-valued cells" and "Split in to several columns", but these don't give me what I would like.

I'm aware that I could do a custom grel/python/jython along the lines of "if value in string: return true" for each value, but I was hoping there would be a more elegant solution.

Can anyone suggest a starting point?

openrefine one-hot-encoding
1 Answers

GREL in OpenRefine has a somehow limited number of datastructures, but you can still build simple algorithms with it.

For your encoding you need two datastructures:

  1. a list (technical array) of all available categories.
  2. a list of the categories in the current cell.

With this you can check for each category, whether it is present in the current cell or not.

Assuming that the number of all available categories is somehow assessable, I will use a hard coded list ["A", "B", "C", "D"].

The list of categories in the current cell we get via value.split(/\s*;\s*/).

Note that I am using an array instead of string matching and use splitting with a regular expression considering whitespace. This is mainly defensive programming and hopefully the algorithm will still be understandable.

So let's wrap this all together into a GREL expression and create a new column (or transform the current one):

with(
    value.split(/\s*;\s*/),
    cell_categories,
    forEach(
        ["A", "B", "C", "D"],
        category,
        if(cell_categories.inArray(category), 1, 0)))
.join(";")

You can then split the new column into several columns using ; as separator.

The new column names you have to assign manually (sry ;).

Update: here is a more elaborate version to automatically extract the categories.

The idea is to create a single record for the whole dataset to be able to access all the entries in the column "Answers" and then extract all available categories from it.

  1. Create a new column "Record" with content "Record".
  2. Move the column "Record" to the beginning.
  3. Blank down the column "Record".
  4. Add a new column "Categories" based on the column "Answers" with the following GREL expression:
if(row.index>0, "",
    row.record.cells["Answers"].value
        .join(";")
        .split(/\s*;\s*/)
        .uniques()
        .sort()
        .join(";"))
  1. Fill down the column "Categories".
  2. Add a new column "Encoding" based on the column "Answers with the following GREL expression:
with(
    value.split(/\s*;\s*/),
    cell_categories,
    forEach(
        cells["Categories"].value.split(";"),
        category,
        if(cell_categories.inArray(category), 1, 0)))
.join(";")
  1. Split the column "Encoding" on the character ;.
  2. Delete the columns "Record" and "Categories".
user_165929910
Related