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

Clustering Data in OpenRefine

The previous exercise, Faceting, showed how to isolate individual data values for the purpose of examining and cleaning them. Though an extremely useful feature of OpenRefine, it can be time consuming to clean individual data points this way in large datasets. This is where OpenRefine's Clustering feature can be extremely helpful.

To begin, choose the Other_Diagnosis column from the sample dataset. From the drop down arrow at the top choose Edit Cells, then Cluster and edit.

Screenshot showing the menu path to clustering in OpenRefine

In the results screen, OpenRefine has automatically brought together the terms that seem most related into clusters. Here you can quickly review the groupings and select the term that should be used according to your data standards or conventions. As you make your selections click the Merge checkbox next to them, and when you've made all your selections click 'Merge Selected & Re-cluster' at the bottom of the screen to search for any additional matches.

Screenshot of the Cluster and Edit screen in OpenRefine

  • It can be helpful to experiment with the Method and Keying function (algorithm) options at the top of the Cluster & Edit screen. Different algorithms for generating the matches can work better or worse, depending on the data type.
  • What populates in New Cell Value on the right will always be the value that appeared the most times. This is only a suggestion, and is not necessarily the correct format for the data. You can replace this with one of the other related values by clicking that value, and you can also manually enter your own New Cell Value.