OpenRefine: API Basics and Repeat Operations

OpenRefine has an API. Not just for reconciliation, but an API you can use to perform actions from creating/deleting a project to blanking down columns and mass editing. Even some of the more experienced users I’ve asked were surprised by this.

I think if you’d asked me before this month, I’d have said “Maybe?” I’ve spent enough time in the documentation to see it mentioned, but hadn’t spent any time investigating.

Like many OpenRefine users, much of what I do involves looking at the data, considering what I need to do next, performing an action or two, evaluating the outcome, repeat. That doesn’t lend itself well to API work. But sometimes, as I’ve mentioned in the previous blog posts, I’ve been doing a lot of repeating tasks at the start of projects. Once those are performed, I need to visit the project sheet and assess the data. So when I was first trying to figure out how to repeat operations, I decided to learn more about the API.

API Basics

Because OpenRefine runs on a local server (or you could host an instance on an actual server), you can send a pretty straightforward set of GET or POST actions at that URL. You can even create projects using the API, with appropriate parameters, though I haven’t yet teased that out. Data has to be sent as multipart/form-data.

While I’m going to be using the “perform operations” function, which is documented, I noticed a lot of actions in the CSRF writeup that were missing in the official API documentation. When I have some free time – if I have some free time – I may try these out.

Authentication

Even if you’re running it locally, you’ll need to get a CSRF token for POST requests which change the data in anyway.

To handle this in my fuller Python script, I wrote/repurposed the following code:

def auth_me():
  '''auth function broken out'''
  response = requests.get(server + "/command/core/get-csrf-token", params={"project":project_id})
  access = response.json()["token"]
  return access

Performing Operations With the API

Performing operations is a simple POST. This is my very simple Python, which only requires requests and json libraries (there are actual OpenRefine clients, but the Python client’s GitHub page was archived by its owner so I didn’t want to rely on it). The code is commented, but essentially I:

1. Set the server
2. Set the project ID
3. Define and perform the auth
4. Paste in the operations copied as described in the previous post and dumps it into JSON. Critically, however, I had to change false to False or Python got mad. I don’t know if it would be better to put “false” in quotes, that’s another thing to test more (maybe using a case which should be True, or where false is more noticeable). I didn’t need to repeat and this works.
5. Using requests, post to perform-operations url. Pass on parameters of the project ID, the csrf key, and the actual operations I want to perform. For the sake of code lenght, this is a much shorter set of operations than I was actually performing.
6. Process the result and print something which helps me understand if it was successful or not.

import requests, json

## set variable on the off-chance it's a different server sometime
server = "http://127.0.0.1:3333"

## could be set as an argument. could also be redone as a list of IDs and iterate through them to perform the same functions (adjustments needed below)
project_id = "18117329352"

## gets new auth code each time, I don't know when they expire
def auth_me():
  '''auth function broken out'''
  response = requests.get(server + "/command/core/get-csrf-token", params={"project":project_id})
  access = response.json()["token"]
  return access

## now do actual auth
key = auth_me()

## simple pasted in entire output and changed "false" to "False" and then threw into a json.dumps
post_operations=json.dumps([
  {
    "op": "core/blank-down",
    "engineConfig": {
      "facets": [],
      "mode": "row-based"
    },
    "columnName": "Catalog Key",
    "description": "Blank down cells in column Catalog Key"
  },
  {
    "op": "core/text-transform",
    "engineConfig": {
      "facets": [],
      "mode": "row-based"
    },
    "columnName": "Title",
    "expression": "grel:value + \" - \" + row.record.index",
    "onError": "keep-original",
    "repeat": False,
    "repeatCount": 10,
    "description": "Text transform on cells in column Title using expression grel:value + \" - \" + row.record.index"
  }
])

## now perform the operation
perform_operations = requests.post(server + "/command/core/apply-operations",
  params=
    {
      "project":project_id,
      "operations":post_operations,
      "csrf_token":key
      })

## oh my god tell me what happened
if perform_operations.status_code == 200:
  if perform_operations.json()["code"] == "ok":
    print("Operations performed successfully")
  else:
    print(json.loads(perform_operations.json()))
else:
  print("Status code:",perform_operations.status_code)

Evaluating Efficiency

As a one-off, I don’t think using the API for this purpose is going to be more efficient than simply downloading a copy of the operations I want to replicate and uploading the file to each new project.

What would tip it over into efficiency for me would be if I could figure out how to create projects, do those as a batch, get the IDs, and then run a second step to perform all the operations on them as a list.

As I said at the beginning, I think a major reason everything from repeating functions to how to use the API isn’t more widely-known among regular practitioners like myself is that we’re so rarely doing something with this kind of repeated process. I don’t think I’ll find the API helpful for most things I do. But when I’m on a big project like this one? I’ll keep exploring.