Getting to Know your Dataset with the OpenRefine Facets


Originally published by Tony Hirst, Open University lecturer and data storyteller at the Open Knowledge Foundation, on blog.ouseful.info under a Creative Commons Attribution licence.


One of the many ways of using OpenRefine is as a toolkit for getting a feel for the range of variation contained within a dataset using the various faceting options. In the sense of analysis being a conversation with data, this is a bit like an idle chit-chat/'getting to know you' phase, as a precursor to a full blown conversation.

Faceted search or faceted browsing/navigation typically provides a set of search filters to a list of search results that limits or restricts the displayed results to ones that fulfill certain conditions. In a library catalogue, the facets might refer to metadata fields such as publication date, thus allowing a user to search within a given date range, or publisher:


Facets in a library catalogue

Where the facet relates to a categorical variable – that is, where there is a set of unique values that the facet can take (such as the names of different publishers) – a view of the facet values will show the names of the different publishers extracted from the original search results. Selecting a particular publisher, for example, will then limit the displayed results to just those results associated with that publisher. For numerical facets, where the quantities associated with the facet relate to a number or date (that is, a set of things that have a numerical range), the facet view will show the full range of values contained within that particular facet. The user can then select a subset of results that fall within a specified part of that range.

In the case of OpenRefine, facets can be defined on a per column basis. For categorical facets, Refine will identify the set of unique values associated with a particular faceted view that are contained within a column, along with a count of how many times each facet value occurs throughout the column. The user can then choose to view only those rows with a particular (facet selected) value in the faceted column. For columns that contain numbers, Refine will generate a numerical facet that spans the range of values contained within the column, along with a histogram that provides a count of occurrences of numbers within small ranges across the full range.

So what faceting options does OpenRefine provide?


Here’s how they work (data used for the examples comes from Even Wholesale Drug Dealers Can Use a Little Retargeting: Graphing, Clustering & Community Detection in Excel and Gephi and JSON import from the Twitter search API):

Exploring the set of categories described within a column using the text facet:


Faceted views also allow you to view the facet values by occurrence count, so it’s easy to see which the most popular facet values are:


You can also get a tab separated list of facet values:


Sometimes it can be useful to view rows associated with particular facet values that occur a particular number of times, particulalry at the limits (for example, very popular facet values, or uniquely occurring facet values):


You can look at the range of numerical values contained in a column using the numeric facet:


You can look at the distribution over time of column contents using the timeline facet:


Faceting by time requires time-related strings to be parsed as such; sometimes, Refine needs a little bit of help in interpreting an imported string as a time string. So for example, given a “time” string such as Mon, 29 Oct 2012 10:56:52 +0000 from the Twitter search API, we can use the GREL function toDate(value,"EEE, dd MMM y H:m:s") to create a new column with time-cast elements.


(See GRELDateFunctions and the Java SimpleDateFormat class documentation for more details.)

You can get a feel for the correlation of values across numerical columns, and explore those correlations further, using the scatterplot facet.


This generates a view that creates a set of scatterplots relating to pairwise combinations of all the numerical columns in the dataset:


Clicking on one of these panels allows you to filter points within a particular area of the corresponding scatter chart (click and drag a rectangular area over the points you want to view), effectively allowing you to filter the data across related ranges of two numerical columns at the same time:


A range of customisable faceting options are also provided, that allow you to define your own faceting functions:

  • the Custom text… facet;
  • the Custom Numeric… facet.

More conveniently, a range of predefined customized facets are provided that provide shortcuts to “bespoke” faceting functions:


So for example:

  • The word facet splits strings contained in cells into single words, counts their occurrences throughout the column, and then lists unique words and their occurrence count in the facet panel. This faceting option thus provides a way of selecting rows where the contents of a particular column contain one or more specified words. (The user defined GREL custom text facet ngram(value,1) provides a similar (though not identical) result – duplicated words in a cell are identified as unique by the single word ngram function; see also split(value," "), which does seem to replicate the behaviour of the word facet function.)
  • The duplicates facet returns boolean values of true and false; filtering on true values returns all the rows that have duplicated values within a particular column; filtering on false displays all unique rows.
  • The text length facet produces a facet based on the character count of strings in cells within the faceted column; the custom numeric facet length(value) achieves something similar; the related measure, word count, can be achieved using the custom numeric facet length(split(value," "))

Note that facet views can be combined. Selecting multiple rows within a particular facet panel provides a Boolean 'OR' over the selected values (that is, if any of the selected values appear in the column, the corresponding rows will be displayed). 'AND' conditions, even within the same facet, create a separate facet panel for each 'AND'-ed condition.