Skip to Main Content

Cleaning Spreadsheet Data with OpenRefine

This guide accompanies the Galter Health Sciences Library class of the same name, or can be used on its own to learn the basic functions of OpenRefine. The class and guide are adapted from Library Carpentry OpenRefine, Copyright 2016-2019

Common Transformations in OpenRefine

Transformations are a powerful aspect of OpenRefine. Some common transformations are pre-loaded into the system such as trimming leading and training whitespace, changing all text in a cell to Title case (capitalizing the first letter of all words), Upper case, or Lower case, or transforming the format of the values in cells to number, date, or text.

Screenshot of the menu path to Common Transformations in OpenRefine

To demonstrate how the trimming whitespace transformation works, go to the Other_Diagnosis column in the sample dataset and, from the drop down arrow, choose Facet, then Text Facet. There should be 938 choices. Scroll down the list until LOW BACK PAIN is near the top.

Screenshot showing data with extra whitespace in OpenRefine

Notice that there are two seemingly identical entries for LOW BACK PAIN. The only difference is that the one with only two occurrences has an extra space at the end. By running the Trim leading and trailing whitespace transformation outlined above, the extra space will automatically be deleted and the incorrect entries will be joined up with the others, to make a total of 120 instances of LOW BACK PAIN.