Cleaning More Messy Data: Tutorial Assignment (Week 9)

The Techniques

For my midterm, I decided to use both of the Tate datasets, one that listed Tate artworks that were from the years 1950-55 and another that listed non-UK Tate artists who were born between 1900-19. I wanted to plot the artworks at the place of birth of the work’s artist. To achieve that, I had to learn how to remove rows in a dataset in OpenRefine if a column’s values did not match any of the values in a column from a different dataset. Then, I learned how to create a new column based on the values from a different dataset. This tutorial will go through the step-by-step process on how to complete these important techniques.

The Techniques’ Uses in Context

This technique could be used in other digital humanities projects for cleaning up messy datasets that rely on information from another dataset. For example, you may have a dataset that lists the highest performing campuses for the SATs in Texas and another dataset that includes basic information about campuses in the Houston Independent School District (HISD), including data like the school’s address, name, or demographics. You could check if any of the schools in the HISD dataset are present in the SAT dataset and analyze any patterns you may notice. For my midterm, I used these techniques to remove artworks from the artwork dataset that were not made by the artists in the Tate artists dataset and create a new column that included the artists’ places of birth. 

Tutorial

1. Import both datasets into OpenRefine. Determine which dataset you will be cleaning.
   •  In my case, the Tate Artworks dataset is checking if the values in the artist column match with the values in the name column in the Tate Artist dataset.

2. Add a custom text facet to the column that you will be checking.
   • Click the dropdown by the column name > Facet > Custom text facet…
   • In my case, I will be checking the artist column in the Tate Artworks dataset.

3. In the box add this GREL function: value.cross(“otherProjectName”, “columnName”).length()
   • If the value is present in the other dataset, the output will be 1. If the value is not present, the output will be 0.

4. In the facet, click 0.

5. Click the dropdown by All > Edit rows > Remove matching rows. Then, click exclude.

6. Next, to create a new column based on the values from a different dataset, click the dropdown for the column you are checking > Edit column > Add column based on this column…

7. Add this GREL function: cell.cross(“otherProjectName“,”columnName”)[0].cells[“otherColumnName“].value. Remember to add a column name!

Now, you should have a clean dataset!

More Resources

You can find more information about these features here:

1. OpenRefine Documentation for GREL Functions
2. Tutorial in OpenRefine Google group