Recipe 5 – adding derived columns
Sometimes you do want to transform the contents of a cell, but also keep the original value. While you can go back to a cell's previous state using the Undo / Redo tab if something went wrong, it could be useful to see both the original value and the transformed value at the same time. Therefore, OpenRefine enables you to add a column based on another one.
Suppose we want to have a separate field that counts the number of categories per record. Click on the Categories dropdown, click on Edit column and Add column based on this column… A dialog very similar to the cell transformation dialog pops up; however, this time it additionally asks for a column name. Enter Category Count
into the New column name field. Now, we have to create the expression that will count the number of categories. Since all categories are separated by a character (a vertical bar, or a comma if you followed the last recipe), we can simply split them and count the number of segments....