Working YAML and OpenRefine, Again

Last year Minghui and Alessandra did a pile of research into the authors and contributors of all our books. The result was a big, ugly spreadsheet^[1] with contributor names, notes, and name-authority file links for Edit16, LOC, and VIAF. There are a lot of notes, because even with authority files, there are still quandaries, especially in how these people relate to our old books.

I worked through a number of hassles processing this spreadsheet, which I’ll document here in case it helps anyone.

Spreadsheets and web links: Aaargh!

As you probably are aware, you can put a link in a spreadsheet cell. But did you know that there are at least two ways this works, and depending on which way, it can cause more or less hassle? The nice way is if you manually “Insert Hyperlink,” which results in a formula in the cell that says something like =HYPERLINK("https://edit16.iccu.sbn.it/autore/CNCA003094","Sophocles"). If your links look like that, you’re golden. But they probably aren’t like that: much more likely, a piece of linked text was copy-pasted into the spreadsheet, which mysteriously embeds the link into the text, but in an inaccessible way. By the way, this isn’t specific to Excel, or Google Sheets, or LibreOffice Spreadsheets; they all work pretty much identically, for better or worse.

I spent a good chunk of the morning trying to figure out how to expose the URLs for these links – because the URLs are the part I actually wanted! But if they’re these “rich-text” embeds (which probably means it’s handled at the level of your operating system’s clipboard rather than in the spreadsheet itself), there’s no easy answer.

The web is full of recipes for how to extract them – mostly involving VBA (for Excel) or Google Scripts (for Sheets). I hacked away at one of these for the better part of an hour before giving up and installing a third-party extension in my Google Sheets that will “extract URLs” (as well as all my personal information, possibly). No, you can’t just export as CSV and get the URLs that way; that would be nice, but no. The extension I used converted the rich text links to the aforementioned HYPERLINK formulae, which I could then convert to raw URLs with a combination of a REGEX function (versions of which exist in Google Sheets and Excel and LibreOffice, as far as I can tell) and then splitting data to columns. Not quite for the faint of heart, but I managed.

Rediscovering OpenRefine

I used OpenRefine to parse spreadsheet data last year, and I loved it. So I turned to it again this year. I saved my final, properly fleshed-out spreadsheet as .xslx and imported that into OpenRefine. That parsed fine – OpenRefine is robust and smart. Once I had it, I gave it a quick once-over to see if everything looked right (that is, more or less the same as the original spreadsheet). Then I used OpenRefine’s flexible “Templating…” Export tool, which by default is already set up to produce a JSON-format version of your rows and columns. OpenRefine is smart enough to recognize column headers and use those as data keys.

As I had done last year, I simply modified OpenRefine’s default JSON template to make it produce YAML instead (and trimmed some of the unnecessary columns at the same time). Because YAML and JSON are almost identical structurally, this mostly just means removing all the curly braces and commas in the JSON, and using indentation and hyphens instead: YAML is designed to be human-readable, unlike JSON. My final OpenRefine export template ended up looking something like this:

- handle: {{cells["Handle (YearAuthor)"].value}}
  author: {{jsonize(cells["Author (short)"].value)}}
  edit16author: {{jsonize(cells["Edit16author"].value)}}
  edit16url: {{cells["Edit16Link"].value}}
  comments: {{jsonize(cells["Alessandra's commments"].value)}}
  locauthor: {{jsonize(cells["LOC"].value)}}
  locurl: {{cells["LOC URL"].value}}
  viafauthor: {{jsonize(cells["VIAF link"].value)}}
  viafurl: {{cells["VIAF URL"].value}}
  note: {{jsonize(cells["Note"].value)}}
  note-p: {{jsonize(cells["Column 16"].value)}}
  note-q: {{jsonize(cells["Column 17"].value)}}
  note-r: {{jsonize(cells["Column 18"].value)}}
  ---

The bracketed-and-quoted values for the “cells” are the Column headers that OpenRefine automatically recognized and grabbed. The “jsonize” function basically serves to sanitize the data and wrap it in quotation marks, which is nice if that cell contains wacky free-text with strange bibliographer-convention punctuation strewn through it :-)

YAML makes the day brighter

My templated export from OpenRefine produces a giant (~7000-line) YAML file, which starts like so:

---
authordata:
- handle: 1495Theocritus
  author: "Theocritus"
  edit16author: "Theocritus"
  edit16url: https://edit16.iccu.sbn.it/autore/CNCA003096
  comments: "This is the first book in our collection"
  locauthor: "Theocritus"
  locurl: https://lccn.loc.gov/n50033430
  viafauthor: "Theocritus"
  viafurl: http://viaf.org/viaf/95161348
...

The original spreadsheet had data on roughly 400 contributors, so that means that the ‘handle’ field, which identifies each book, will be repeated across several of the contributor records. Also, these people may appear multiple times across the whole Collection. Theocritus here only appears once, in his own book, but you can imagine that “Aldus Manutius” appears as a contributor a whole bunch of times. This is what databases are for: keeping “many-to-many relationships” (like books to authors) under control. But Aldus@SFU is a flat-file project, so we have no database management system except for these YAML files – and the logic that we put into the 11ty static website generator to assemble the data into a viewable product. So there’s a bit more care and attention we need to take here, because it’s not all abstracted and automated. But the same best practices still apply.

Having 7000 lines of raw YAML is also a bit scary, especially given the presence of weird bibliographer/librarian conventions within it. So I needed to give it at least a once-over with a validator. I found a super easy-to-use web tool – https://www.yamllint.com – that takes a pastebomb of YAML and produces bright-red error messages if there’s anything out of sorts. It took me half-a-dozen passes and some light editing to get my dataset to pass this test… not bad at all. That’s because if there’s a problem with your YAML formatting, it’s generally pretty easy to see.

That website is, as far as I can tell, a web front-end around yamllint, (lint is a venerable Unix debugging tool, so yamllint is a YAML debugger), a command-line tool which I also installed, and which will give you piles more feedback if you ask for it. I also installed a general-purpose YAML tool called yq which will parse, check, fold, spindle, and mutilate YAML (and other formats, such as JSON) as needed. These are handy tools to have on hand; YAML is nice because it is so easy to read and check by hand, but if you have thousands of lines of it, it helps to have some tools.

The lovely thing about YAML data, though, is that it is supported by a thousand different tools. Our website, built with the 11ty static-site generator, speaks YAML natively, so all this data is going to get fed straight into it, and turned into nicely contextualized, human-readable information. There’s loads of editorial work to be done first, though.