Using OpenRefine: With this book on OpenRefine, managing and cleaning your large datasets suddenly got a lot easier! With a cookbook approach and free datasheets included, you'll quickly and painlessly improve your data managing capabilities.

In this recipe, you will learn how to sort data as a visual aid and how to reorder rows permanently as a prerequisite for more advanced operations.

Because sorted values are easier to explore and manipulate, sorting data is certainly something you will want to do at some point when working with OpenRefine; you can either use the sorted values as a visual aid or reorder your records permanently. In order to sort your data by their Record ID, for instance, choose Sort... in the column menu to access the following window:

Cell values can be sorted according to their type: text (case-sensitive or not), numbers, dates, or Boolean values. For each type, we are offered two possible sorting orders:

Moreover, we can select where errors and blanks will be stored in relation to the valid values by dragging...

One of the functionalities of OpenRefine that you will use most often is faceting. Facets do not affect the values of your data, but they allow you to get useful insights of your dataset; you can think of facets as various ways to look at your data, just like the facets of a gemstone that still have to be refined. Facets also allow you to apply a transformation to a subset of your data, as they allow you to display only rows corresponding to a given criterion.

In this recipe, we will explore the various ways of faceting data depending on their values and on your needs: text facets for strings, numeric facets for numbers and dates, a few predefined customized facets, and finally how to use stars and flags. Most of the power of OpenRefine lies in the ability to combine these different types of facets.

In this recipe, you will learn what duplicates are, how to spot them, and why it matters.

The only type of customized facet that we left out in the previous recipe is the duplicates facet. Duplicates are annoying records that happen to appear twice (or more) in a dataset. Keeping identical records is a waste of space and can generate ambiguity, so we will want to remove these duplicates. This facet is an easy way to detect them, but it has a downside; it only works on text strings, at least straightforwardly (to learn how to tweak it to work on integers as well, have a look at Appendix, Regular Expressions and GREL).

Too bad then; we cannot use a duplicate facet on the Record ID column. The next best thing is to run it on the registration numbers, which are an internal classification of objects in the collection, though they are not as reliable as the IDs, since they have an extrinsic meaning for collection managers. Anyway, let's give it a try by navigating...

In this recipe, you will learn about filters that allow you to search for values displaying some patterns.

When you want to find rows matching a certain string, it is easier to rely on a simple text filter than on cumbersome facets. Let's start with a simple example. Suppose you want to filter all titles relating to the United States. Navigate to Object Title | Text filter and watch the filter box open on the left, in the same tab where facets appear. Now type in USA. OpenRefine tells you that there are 1,866 matching rows. Select the case sensitive checkbox to eliminate happenstance matches, such as karakusa and Jerusalem, and we are down to 1,737 rows:

Still, we cannot be sure that there is no noise left in these matches; there could be occurrences of JERUSALEM in capital letters for instance. To get around this problem, we could try to add spaces to either side of USA, but at the risk of losing cases, such as [USA] or /USA, along with occurrences of the...

In this recipe, you will learn how to use OpenRefine's built-in transformations to modify subsets of your data.

When playing with facets and filters, we have essentially displayed the data in various ways, but we have not really affected them. Now comes the time to modify your data at last, and this means entering the powerful Edit cells menu. While we already used Blank down in order to detect duplicates, other transformations, such as splitting and joining multi-valued cells or clustering and editing values, are more advanced, so we will delay their discussion until the next chapter. Other transforms are easier to grasp; however, we will focus now on those available through the Common transforms submenu pictured in the following screenshot:

Trimming whitespace at the beginning and end of a string is a good first step to improve the quality of your data. It will ensure that their identical values will not differ by leading and trailing whitespace...

In this recipe, you will learn how to suppress problematic rows that have been previously singled out through the use of facets and filters.

Detecting duplicates or flagging redundant rows is fine, but it is only part of the job. At some point, you will want to cross the mark between data profiling (or analysis) and data cleaning. In practice, this means that rows that have been identified as inappropriate during the diagnosis phase (and probably flagged as such) will need to be removed from the dataset, since they are detrimental to its quality.

To remove rows, be sure to have a facet or filter in place first, otherwise you will remove all rows in the dataset. Let's start from the clean project again (import it for a second time or toggle the Undo / Redo tab and select 0. Create project to cancel all modifications) and see what we can do to clean up this dataset. Also, check that OpenRefine shows your data as rows, not records.

We will first remove the rows...

During the course of this chapter, we have learned how to master the basics of OpenRefine in order to analyze and fix datasets, essential components of data profiling and cleaning.

Analyzing data involved sorting and the use of various facets, but also the application of text filters and the detection of duplicates.

Fixing data was accomplished through reordering, cell transformations, and deletion.

In the next chapter, we will bring our understanding of the inner workings of OpenRefine to another level by venturing into advanced data operations.