Open Refine: Repeating Groups of Operations (the Easy Way)

I’m currently working on a project which involves a lot of spreadsheets exported from our ILS using the same process. To get started with each in OpenRefine, I load the spreadsheet as a project and apply the same 7 manipulations, including the steps for blanking down within records I previously described. These are OpenRefine-specific, not something I could do during export or before adding to OpenRefine.

After doing the same 7 operations on the 20th spreadsheet, I started wondering about reproducibility. Surely, there’s a way to do the same set of actions without all those clicks?

This question led me on a 3-hour journey learning how to manipulate projects with the API… only to realize in the process that there was a much, much simpler way. Today I’ll be sharing the simpler way, but I will write a post about the API, since when I brought it up in a group chat with plenty of regular OpenRefine users, no one else was familiar with it either.

Short and Sweet: Reusing Operations in OpenRefine

OpenRefine logs all of your actions in the “Undo/Redo” tab on the left sidebar. Despite visiting this area many times to undo and redo my actions, I had never experimented the “Extract” or “Apply” buttons above the list of operations performed. They’d faded into the background.

Yeah, it turns out those are really useful.

When you click Extract, you’re presented with some options: you can select which functions you want to export, see and copy the code, or export it to a text file. Apply is the inverse: just paste in the code or upload an exported text file and run the result.

For a lot of power users, this info may be enough to get you oriented and experimenting. But if you’re interested in more (we’re still on the simple stuff) or want a walkthrough with screenshots and sample code, carry on!

Operational Background

For some reference, I’m going to describe the data I have and the operations I was performing. I’ll also include some sample data for you to practice with below.

My spreadsheets have the following columns:

• Catalog Key
• Title
• 856
• Barcode
• Item Type
• Library
• Home Location

They are based around the Item represented in the second half, so the first half just repeats bibliographic data for each entry. As described in the previous post I needed to get this data into the shape of an OpenRefine record by “blanking down” the Catalog Key. Then, for easier visual processing, I did the same to the Title and 856 fields.

Multi-value 856s are represented by multi-line entries in the cell, so I can blank down the field without losing any data. So I wanted to apply cell transformations to both those fields to make them unique within a record. Then blank that down. Then re-transform to set them back to the original data. Each of those actions involves navigating a menu. If you try to do this manually, you’ll see why I started wondering about duplication.

Longer Reuse Walkthrough With Screenshots

Until you’re very familiar with the syntax of operations or have some to reuse and update, the first step will be creating a project in OpenRefine. Operations are performed based on column name, so you will need to be sure that the column names in this data source are the same as the ones you plan to reuse it on.

Next, perform the series of actions you plan to repeat on other projects. While you can also perform actions you don’t plan to export and reuse, the operations shouldn’t depend on anything you’re not exporting. If you need to perform a manual action in the middle, like something which needs visual review, you’ll need to export the sets of steps on either side of it into two phases.

Once you’ve completed the actions you wish to repeat, click the “Undo/Redo” subsection of the left sidebar.

Click “Extract.”

In the window that pops up (modal), select which functions you want to export. The preview on the side will update with the relevant code. Use the Export button to generate a text file and save it somewhere appropriate.¹

The easiest way to test how these functions work is to simply use the Undo to bring your project back to the last step before the sequence you exported.

Now click the “Apply” button in that same area.

That opens a popup where you can either upload the same text file you exported or paste raw JSON.

Load or paste to see the operations:

Click “Run operations”

Voila!

As long as the other projects for which you’re using it have the same column names and data that can be parsed the same way by these operations, you should be able to save time in the future. In my case, I am running the same report against different sets of record IDs in the system and getting an item-focused export, so my data should always look as consistent as any data from the ILS does².

Much of the time, my OpenRefine projects are different enough that knowing this wouldn’t have been useful. But with an ILS migration on the horizon, I suspect I’m going to be doing many OR-based analyeses of different sets of records for the same things. I’m hoping this can make it a little less tedious.

Sample Data

If you’d like to test on a sample project, I’ve made one for you!

• CSV file - some very basic data.
• Operations file - a file containing the JSON of operations.

Simply:

1. import the CSV into OpenRefine with its standard defaults,
2. go to the Undo/Redo, where you’ll see “0. Create Project”,
3. click Apply,
4. Browse and upload the history.json file,
5. Run Operations

and you should see something that looks like this: