Open Refine: Blanking Down Only Within Records

This is the first in what will be a series of posts deriving from my most recent use of OpenRefine. This post is primarily for intermediate or advanced OpenRefine users, so I won’t be going over fundamentals. Beginners who are already familiar with the power of “Blank down” and “Transform” should also be ok.

One of the great things about OpenRefine is that you can take a spreadsheet with a repeating key field, apply the “Blank down” function on that column, and unlock a “record” experience. I find this really helpful when I need to combine MARC data with item data, a 1:many relationship. I can facet the holding item library to a particular campus, for example, and see entire records, not just the item row.

While only the key needs to be blanked down to create the record view, I can improve my experience working with the data by blanking down other repeating fields so I’m only seeing one instance of any given field.

The problem is that while keys don’t repeat, other data may. If I’ve got a column for notes and I’m working on a set of related records, multiple adjacent records may have the same note text. Blanking down means that the note text is only left in the first record of the sequence, until there’s a record where that field is genuinely blank or which has a different text.

I’ve previously handled this problem by counting how many rows were impacted by my initial blank down and noting how many were impacted by blank downs on each subsequent column. If the number is higher, I undo my action and simply leave the field duplicated in each row of the record. But I don’t like it.

The Solution

While working on a massive data review project this spring, I became frustrated with repeating data which I couldn’t blank down. I decided to search around online to see if others have found ways to handle it. I found the solution in a 10-year-old thread on the OpenRefine Google Group:

Once you’ve turned your rows into records, apply the following cell transform to each column you want to blank down:

value + " - " + row.record.index

Now, perform the “Blank down” operation.

Get your original content back with a second cell transform:

value.replace(/ - \d+$/,'')

The (Brief) Explanation

It’s important to note here that you must have already performed the initial “Blank down” operation on your column with keys to turn your rows into records. Otherwise, each row will be treated as its own record. Once you have records, though, they’ll be treated as records by this transformation whether you’re currently viewing the data as rows or records.

The first transformation uses the simple string join GREL syntax to join the original value, a " - “, and that row’s record index.

Something like:

Collection Name
Pennsylvania German broadsides and Fraktur
Pennsylvania German broadsides and Fraktur
Pennsylvania German broadsides and Fraktur
Pennsylvania German broadsides and Fraktur
Pennsylvania German broadsides and Fraktur
Pennsylvania German broadsides and Fraktur
Pennsylvania German broadsides and Fraktur
Pennsylvania German broadsides and Fraktur
Pennsylvania German broadsides and Fraktur

becomes:

Collection Name
Pennsylvania German broadsides and Fraktur - 0
Pennsylvania German broadsides and Fraktur - 0
Pennsylvania German broadsides and Fraktur - 0
Pennsylvania German broadsides and Fraktur - 0
Pennsylvania German broadsides and Fraktur - 1
Pennsylvania German broadsides and Fraktur - 1
Pennsylvania German broadsides and Fraktur - 2
Pennsylvania German broadsides and Fraktur - 2
Pennsylvania German broadsides and Fraktur - 2

and when I apply the blank down function, I get:

Collection Name
Pennsylvania German broadsides and Fraktur - 0



Pennsylvania German broadsides and Fraktur - 1

Pennsylvania German broadsides and Fraktur - 2

(This is just one column view, there’s an assumed leftmost column with the record IDs.)

The second transformation is a simple regex replace, looking for the delimiter (” - “) and a string of one or more numbers up to the end of the cell value. Because we’ve put a right-anchor on it, it should only match the addition we made, even if the string happens to contain its own " - 92951” (unless you’ve turned on repeating).

This simple pair of transformations and the ability to blank down without losing data has really improved my experience of working with the large data exports.

It’s a bit cumbersome to repeat them each time, so stay tuned for my next blog post: “I can’t believe I’ve been using OpenRefine for a dozen years and only just learned how easy it is to repeat a set of operations.”