OpenRefine columnwise scripting

Question

I spent some time Googling, but couldn't find anything useful.

How to select all the values of a single column in OpenRefine in a script?

It seems that all the operations are row-wise

In particular, I want to find highest and lowest values in a column

score 0 · Answer 1 · edited May 23 '17 at 12:08

Facets will give you a list of all the values in a column - so the simplest way of getting the lowest/highest values in the column is to make a facet on the column and see the resulting highest/lowest in the facet to get the answer.

However I'm not sure if this meets your criteria for selecting the values 'in a script'. By this I assume you mean you want to be able to access the lowest/highest values in a GREL expression?

You can do this, but you have to force OpenRefine to treat all the rows in project as part of a single record. The easiest way to do this is usually to add a column at the start of the project which is empty except for the first cell which contains a value.

Once you've done this you can access all the values in a column by using syntax like:

row.record.cells["Column name"].value

See also my answer to OpenRefine - Fill between cells but not at the end of the list which uses the same technique

Further explanation:

Create a new column at the start of your project and put a single value in the very first cell in that column
Switch to Record mode At this point you should have a single 'Record' in your project - e.g.

At this point using the syntax like row.record.cells["Column 1"].value gives you an array of all the values in "Column 1". You can then use GREL expressions to manipulate this - including sorting or comparing values.

Could you elaborate on "force OpenRefine to treat all the rows in project as part of a single record"? — Boris Mocialov, Oct 27 '15 at 14:15

score 0 · Answer 2 · answered Oct 27 '15 at 13:52

0

By default, OpenRefine functionality are limited for computation. The Stats Extension make basic stats per column (min, max, average, medium ...).

answered Oct 27 '15 at 13:52

magdmartin

1,712
3
20
43

Looks interesting, only I am looking for a faster simpler solution – Boris Mocialov Oct 27 '15 at 14:19
That will be the simplest. Just need to install the plugin – magdmartin Oct 27 '15 at 19:36

score 0 · Answer 3 · answered Oct 28 '15 at 15:12

A Text Facet has an nice undocumented option to gives you aggregated results in a column that you can just copy and paste.

Click on the "X choices" in the upper left corner of the Text Facet box.
This will bring up a separate dialog that contains the values along with the count of each value in that column.

(If your looking to just get ALL the values of a single column, then use Export -> Custom Tabular Exporter and then Select and Order Columns to Export by clicking on checkboxes, then click on Download tab to choose your export format and then click Download button.)

OpenRefine columnwise scripting

3 Answers3