Exercise 7: Open Refine

Author
Affiliations

Julien A. Raemy

docuteam SA

University of Bern

Published

February 1, 2026

Modified

November 9, 2026

Open Refine

NoteInstructions

In this exercise you clean a museum collection export with OpenRefine (Parts A and B), then link a list of names to an authority file (Part C). Work alone or in pairs.

A. Getting started

  1. Install OpenRefine (https://openrefine.org/download) and start it. It opens in your browser, usually at http://127.0.0.1:3333/.
  • OpenRefine runs in your browser but is a local application: no data leaves your machine.
  • Every action is recorded in the Undo / Redo tab on the left. If something goes wrong, click an earlier step to go back to it.

B. Cleaning the Powerhouse Museum Collection

The Powerhouse Museum (Sydney) published an export of its collection: about 75,000 objects, with categories entered by many different people over many years.

  1. Import: Create a new project with Get data from Web Address (URLs) and paste https://zenodo.org/records/17047254/files/phm_collection_adapted.tsv. Make sure “Tab-separated values” is selected, then create the project.

  2. Split multi-valued cells: The Categories column holds several values separated by a pipe (|). Use Edit cells > Split multi-valued cells with | as the separator.

  3. Inspect with a facet: On Categories, choose Facet > Text facet. How many distinct categories are there? Sort the facet by name and look for values that differ only in capitalisation or spacing.

  4. Cluster and merge: Click Cluster in the facet panel.

    • With the default method (key collision / fingerprint), review the proposed clusters, tick Merge? for the correct ones and click Merge selected & re-cluster.
    • Switch the keying function to ngram-fingerprint and repeat. Which new variants does it catch?
    • How many distinct categories are left?

  5. Extract a year: Production Date mixes single years (1984) and ranges (1950 - 1960).

    • On Production Date, choose Edit column > Add column based on this column, name it Year and use the GREL expression value.find(/\d{4}/)[0].
    • Convert it with Edit cells > Common transforms > To number, then open Facet > Numeric facet on Year.
    • Look at the histogram: which years cannot be right? What would you do with them?

  6. Export: Rejoin the categories with Edit cells > Join multi-valued cells (separator |), then Export > Comma-separated value.

  • Step 3: After splitting, an object takes up several rows. Switch between rows and records (top left) to count either categories or objects.
  • Step 5: Fingerprint ignores case and punctuation, so it merges Clothing and Dress with Clothing and dress. Variants such as Transport-Air and Transport - Air can slip through, because removing the hyphen glues the words together in one case but not in the other. ngram-fingerprint also ignores spaces, so it catches these. Always check a cluster before merging it: similar strings are not always the same thing.
  • Step 6: find returns every match of the regular expression as a list, and [0] keeps the first one. \d{4} means “four digits in a row”. Empty cells simply stay empty.

C. Reconciling Names with Biblissima

Biblissima is an authority file for medieval and Renaissance manuscripts and their people, places and works. This small file (fewer than 20 rows) lists names related to manuscripts.

  1. Import: Create a new project from https://raw.githubusercontent.com/emmamorlock/workshop/refs/heads/main/exercices/handouts/biblissima.csv. The first column (----) holds names; Type tells you whether a row is a person, a place, a descriptor or a shelfmark.

  2. Keep persons and clean names: Use a Text facet on Type to keep only person. On the name column, apply Edit cells > Common transforms > Trim leading and trailing whitespace and Collapse consecutive whitespace.

  3. Reconcile: On the name column, choose Reconcile > Start reconciling…, then Add standard service… with https://data.biblissima.fr/reconcile/en/api. Choose the type Human and start reconciling. Check the candidates, then use Reconcile > Add column with URLs of matched entities to keep the Biblissima URIs.

  • Trim whitespace before reconciling: " Charles" and "Charles" are different strings for the service, which lowers the match rate.
  • Reconciliation disambiguates: “Charles I” could be several historical figures. Use YearBirth and YearDeath to choose the right candidate.
  • The resulting URIs are persistent identifiers: your local list is now linked to an authority file (see Exercise 3).

More information and tutorials on OpenRefine can be found on the following links:

References

Back to top