When Everything You Have Looks Like OpenRefine

This is not an OpenRefine tutorial. As I was trying to explain the process I took to read and code my sabbatical research, I realized that it could be best described as getting galaxy brained with OpenRefine and pandoc. I thought it might make an interesting blog post – less as a how-to or advice and more as an example of what real-world problem-solving looks like in libraries, or at least in my life. The alternative title could be “What It’s Like Inside My Weird Brain.”

The Problem

I got 267 responses to my 2024 survey (if you’re wondering why outputs haven’t yet been published, that is one contributing factor). Because I was trying to understand morale and migration, I’d allowed for free text responses along with controlled value fields. And, if you’re not aware, people have a lot of feelings about migrations. As a researcher, this is great!

These long textual responses were a great way of developing a fuller understanding of why a person answered the way they did. But when it came to assessing the data, this meant I would need to code all 267 responses or at least review and see if anything could be coded. Otherwise, I would be losing a ton of information in the statistical portions of analysis. And reading these in a spreadsheet client sounded miserable. I experimented with importing the sheet into NVivo, which seemed like a natural fit for coding, but didn’t feel I could properly map in my data. That might well be a skill issue, I’ve only used NVivo for interview transcripts, which are a completely different thing.

The Approach

What I needed was to be able to turn each response into its own pages. I’d set up the question in the output so that I had context for each answer. For example:

Which ILS did you previously use? Sirsi Dynix Symphony

My first thought: “oh, I could do this with Python.” The original Qualtrics export was in CSV. I’ve processed CSVs in Python for ages. I could output each response in markdown and use pandoc to transform them into a PDF. I then planned to read that PDF with my ReMarkable and take notes both on the sheet and in a running document.

The second half worked great. Simple markdown is simple. Pandoc is easy enough to use, although I did have to fuss around a bit to get a font I liked (and on my Linux machine). And ReMarkable remains the best way I have of reading and taking notes on PDFs.

But when I started messing with it in Python, I quickly got annoyed. It was doable, but it wasn’t easy. I felt like there should be something more straightforward.

And that’s when I remembered OpenRefine templates. Just before my sabbatical started, I’d been working on a project where I turned a spreadsheet into JSON through OpenRefine’s templating system. It’s a batch process I do once a year and I’ve put energy into, well, refining it. Because I only do it once a year, I also have enough documentation to go from my annual “wait how does this work again?” to getting it done. I imported the survey CSV into OpenRefine and started tackling it.

The Steps

When you go to the export templating area, OpenRefine helpfully outputs all existing fields in a sample template. So even though I’d have to hack it to pieces, I had a startiing list to work from. I kept my key nearby so I had a clue what Q22 might mean. I used the value split, replace, or forNonBlank processes on each line, grouping some together, and previewed results periodically to make sure I was on the right track.

It wasn’t the fastest thing I’ve ever done, but it took less than an hour to write, revise, and put out a final export. Just so there’s something useful in this post, this is the template I ended up with.

## {{cells["ResponseId"].value}}

**How many years have you worked in libraries?** {{cells["Q4"].value}}

**What kind of library do you work in?** {{cells["Q5"].value}} {{forNonBlank(cells["Q5_6_TEXT"],c,":" + c.value,"")}}

**How is your job classified?** {{cells["Q6"].value}} {{forNonBlank(cells["Q6_3_TEXT"],c,":" + c.value,"")}}

**How would you describe your job status?** {{cells["Q7"].value}} {{forNonBlank(cells["Q7_3_TEXT"],c,":" + c.value,"")}}

**If you regularly use the ILS (back-end system), what kind of tasks do you use it to perform? (choose all that apply)** {{cells["Q8"].value.replace(",",", ")}}

{{forNonBlank(cells["Q8_10_TEXT"],c,":" + c.value,"")}}

**How much time each week do you estimate that you spend using the ILS?** {{cells["Q9"].value}}

**If you regularly use your library's public online catalog or discovery platform as part of your work, which kind of tasks do you perform or support using these?** {{cells["Q10"].value.replace(",",", ")}} {{forNonBlank(cells["Q10_10_TEXT"],c,":" + c.value,"")}}

**How much time each week do you estimate that you spend using the online catalog and/or discovery platform?** {{(cells["Q11"].value)}}

**Which ILS did you previously use?** {{cells["Q13"].value.replace(",",", ")}} {{forNonBlank(cells["Q13_12_TEXT"],c,":" + c.value,"")}}

**Which ILS do you now use?** {{cells["Q14"].value.replace(",",", ")}} {{forNonBlank(cells["Q14_10_TEXT"],c,":" + c.value,"")}}

**When did your ILS migration complete? (please specify the year or month/year):** {{cells["Q15"].value}}

**About how many years had you (personally, not your institution) used the previous system?** {{cells["Q16"].value}}

**How would you describe degree to which the workflows of your primary job responsibilities have changed since the migration:** {{cells["Q18"].value.replace(",",", ")}} {{forNonBlank(cells["Q18_4_TEXT"],c,":" + c.value,"")}}

**How do you feel overall about any changes to your workflows?** {{cells["Q19"].value.replace(",",", ")}} {{forNonBlank(cells["Q19_4_TEXT"],c,":" + c.value,"")}}

**Please describe some challenges you experienced during the first 6 months post-migration:** {{cells["Q20"].value}}

**Did these challenges continue to impact your work at the 18-month mark post-migration (as well as you remember)?** {{cells["Q21"].value}}

**How were these challenges resolved?** {{cells["Q22"].value.replace(",",", ")}} {{forNonBlank(cells["Q22_6_TEXT"],c,":" + c.value,"")}}

**If you have any comments or reflections on either the resolution or on challenges you still experience, please provide them below:** {{cells["Q23"].value}}

**Were there aspects of your library's new ILS which substantially improved your ability to get things done or were features you'd always wanted?** {{cells["Q24"].value}}
	
**Please describe any aspects of your new ILS which substantially improved your ability to get things done or were features you'd always wanted:** {{cells["Q25"].value}}

**How would you summarize the impact of the ILS migration on your ability to complete your work as of today:** {{cells["Q26"].value}}

**Do you feel that your direct supervisor has/had reasonable expectations of the work you'd accomplish during the first 6 months post-migration?** {{cells["Q27"].value}}

**How would you describe your unit's current morale compared to unit morale prior to the migration:** {{cells["Q28"].value}}

**Is there anything you'd like to add to your assessment of the migration's impact on your unit's morale:** {{cells["Q29"].value}}

**How would you describe your own current morale compared your morale prior to the migration:** {{cells["Q30"].value}}

**Is there anything you'd like to add to your assessment of the migration's impact on your own morale:** {{cells["Q31"].value}}

Because there’s a lot of personal stuff in it, I can’t share a full sample response, but it came out like:

## R_7M9zOZyDpTDwXnj

**How many years have you worked in libraries?** more than 10 years

**What kind of library do you work in?** Academic 

**How is your job classified?** Librarian 

**How would you describe your job status?** Full-time 

**If you regularly use the ILS (back-end system), what kind of tasks do you use it to perform? (choose all that apply)** Cataloging

Pandoc was a bit harder, perhaps partly due to some settings in Linux, but ended up being something like:

pandoc markdown_survey_export.md --pdf-engine=xelatex -V 'mainfont:DejaVuSans.ttf' -V 'mainfontoptions:Extension=.ttf, UprightFont=*, BoldFont=*-Bold' -o Survey_Responses.pdf

Results

The resulting PDF was 231 pages long. It was well-formatted reading with one header for each entry and bolded questions. I spent a lot of time reading through it (twice), noting factors described in the free-text, condensing them into a codebook, and then applying appropriate terms to each entry.

Discussion

Ok, the header is a little tongue in cheek. This isn’t an article. I should be revising a nearly-complete article right now.

When I was trying to explain this, I realized it’s a good example of how I’ve experienced tech librarianship, from my early metadata days to things I do now. Can I write scripts? Yes, I do so all the time. I wrote a small processing script in Python earlier this week (last week now) and I can’t even recall that it was for because it was so ordinary and took maybe 10 minutes to get right. But I also use the things I know. Sometimes it’s easier to clean up a CSV by opening it in a text editor and performing a series of regular expression find and replace operations. Sometimes I use OpenRefine to … create PDFs? Sometimes I write journal articles and blog posts in Joplin.¹

It’s possible to take this too far. Can you share an image or dataset via Word or Powerpoint? Yes. But please don’t. I’d encourage those who aren’t as comfortable with these whacky decisions to feel your way out and assess outputs using the following criteria:

• Is it in the appropriate format?
• Is there a loss in quality?

…I was trying to think of a third to be traditional, but this is all that came to mind (I’m down to update it if someone thinks of a third or fourth). I’d say a loss in usability, but I think appropriate format (image should be JPG/PNG/TIFF/BMP/etc., dataset should be some kind of dataset format) and quality cover that.

Should I end this on an encouraging note? If you do this already, you’re not as weird as you think you are. If you’re not doing things because you don’t have time to buckle into the “right” tools, are there things which let you get the output you need while, taps the sign, don’t take the result off the rails too? Give it a go!