Explore, Clean and Integrate Data with OpenRefine

OpenRefine first imports a file from your computer or from the Internet and then creates a project from it. After data cleaning/manipulation, you can then export the data to a specific file format and use it.

Importing is the first step when working with OpenRefine. The following formats are supported for importing in OpenRefine: CSV, TSV, JSON, XML, Microsoft Excel spreadsheets (.xslx, .xls), HTML tables (.html), Google Spreadsheets (online).

After you open an unknown data source, one of the first things you do is explore it. So you want to "see" the data content and also determine the data types of the columns. Data types are important because they allow for various operations. Some basic data types are text, number, date/time, boolean (true/false) and enumeration (e.g. red, yellow, green).

Facets are an important feature of OpenRefine that go beyond spreadsheets and traditional database tools. They show the data variance in a particular column. Faceting allows us to get a better picture of the entire data set and view the data from a broader perspective.

A facet groups values (e.g. of text or numeric data type) that are in a column and allows the user to filter and edit the values in different cells simultaneously, which works like a browsing mechanism.

Typically, facets are created for a particular column by clicking on the column, selecting the Facet option, and then clicking on one of the facet alternatives.

For example, if you select text facet, the entire contents of the column cells are compared to display general information about the values in that column. You can then either manually enter a new value for the facet entry or use the OpenRefine clustering option, which will automatically take care of the facet.

You can also filter specific column values in the data set by choosing the Text Filter option in the column dropdown menu. This creates a text box where you can insert the text you want to filter the column values.

Clustering is another important feature for grouping similar data which helps you identify data inconsistencies and misspellings. This is common in many data sets.

Clustering can be done by choosing a Facet on the column you want to cluster and then choosing the method you want to cluster your column values with.

As you can see in the above image, this is an example for the Zurich city where the city’s name was purposefully misspelled a couple of times. The clustering function finds those misspellings and offers us options on how to treat these types of misspellings.

OpenRefine provides some fast transformation operations that are useful when dealing with data. You can either use one of the preset transformations (ready to use) or use the GREL language to implement your own transformation function (requires light programming skills; see the separate chapter below).

Some of string operations include:

When using these operations correctly, one can quickly turn potential messy data into clean and machine-readable information.

Using String Operations on OpenRefine can be done by clicking the Edit Cells option on the column dropdown menu. When using string operations, you can choose one of the preset transformations offered by OpenRefine or write your own using the OpenRefine’s GREL language, e.g. value.toDate() expression (see section below).

Another key feature of OpenRefine is the Split functionality that is useful for splitting column values into multiple columns. All you have to do is specify the character in-between the words and then OpenRefine will split those strings into multiple columns for you. This is very common when working with messy data, a lot of data within one column makes much more sense if split into multiple columns.

Splitting fields in OpenRefine is done by clicking on Edit Column and then Split into several columns…​. This will let you specify a separator character(s) or a field length for separating your column values.

Another option, which is sort of the opposite of splitting fields, is called Join Fields and is used when we want to join fields with a separator (or without). This is pretty self-explanatory.

The joining of fields in OpenRefine is done by clicking on edit column and then then Join columns…​. You can then select the columns you want to join and set the separator between the contents of each column. You can write the results in the same column or create a new column.

A common scenario when working with data is to obtain data from multiple sources. Even if the incoming data ultimately serves a common purpose, the structure of the data in the different sources may not be identical. An example of this would be receiving two customer lists with the same attributes but different column names, for example. Although from a human point of view it looks as if they are the same data, this is not the case for computers. Different techniques have to be applied to integrate or rather "merge" these different sources of similar data.

There are usually three types of cases when joining datasets:

In cases 1 and 2, a common column called "key" is required. Typical keys are identifiers such as postal codes, postal addresses (see section Geocoding further down the worksheet), country codes, municipality codes, ISBN, etc.

Some of the above cases will be applied in exercises 2 and 3. The first case will be applied in exercise 2 and the second case in exercise 3. For the third case, there is no exercise on this worksheet that demonstrates it. OpenRefine does not currently provide a direct solution for this case.

OpenRefine also offers functions for validating your data against another data set. It can be done by creating a Custom Text Facet using the GREL cell.cross() function that matches values from two different columns. There is no native/straight-forward way for validating data sets but it can be achieved by using functions that behave in the same fashion as the GREL cell.cross() function.

For deduplicating the data, one of the ways you can achieve it is for example by using Clustering by key collision to find similar data. Then you can manually assess duplicate data and choose what to do with them after the clustering process is finished. This is usually a pretty straight-forward and efficient way of deduplicating a data set.

When you are done with working with the data and you have the data in the desired format, the last step is exporting that data into a specific file format.

The following formats are supported for exporting in OpenRefine: CSV, TSV, HTML table, Microsoft Excel spreadsheet (.xlsx, .xls), ODF spreadsheet (.ods), and some other custom exporters.

For this task, the data from the fill address_list_original.xlsx is used.

In order to create a project (the actual workspace where we work with the data), you need to:

We will refine the data set to filter out only customers from the Zurich canton and merge their addresses to a new column containing the full address.

After you have finished the above tasks, you can export your data set (project) by clicking on Export  Excel 2007+ and OpenRefine will export your current data set into a new Excel spreadsheet.

For this task the data from the files address_list_original.xlsx & address_list_scrambled.xlsx are used.

When you have downloaded both of the excel spreadsheets, create the project by:

The target dataset is the same as the one from the first exercise and the purpose here is to integrate another data set i.e. the source dataset (with similar column names and values) into the target dataset including correct mapping and successful data integration and deduplication.

After creating the project, you will see a new column called File corresponding to the file that the record belongs to. This column File is also called the key column when merging two datasets because it is the column we rely upon for differentiating where the data is coming from (see the first case in the Joining datasets from different sources chapter earlier in the worksheet).

The dataset shows a sum of all rows from both files containing all columns from both files. After the records from the first file (original/source record) are over, the display of the records from the second file (encrypted/source record) starts. You will also notice that some columns have the same name (on purpose) and therefore each record contains data about these columns.

Below are some screenshots that show what the data looks like and how the records evolve from the first to the second file.

First, we need to check the column names (from both files) and find out which ones of them belong to one-another. This part needs to be done manually.

When we find out the two columns corresponding to the same thing, we need to join them together. This will basically bring all the values from both of the files into a single column. Make sure that you leave the separator as an empty string when joining the columns.

You will see that in the columns that you joined, the first column will also take the values from the second column and we will have a value for each of our rows (from both of the files).

Now do the same thing for all of the remaining columns and you will have a dataset with consistent columns.

When you have joined all the right columns, proceed by removing the other columns i.e. from the second file. This should leave you with a dataset containing all the data from both of the files, but with consistent and standardized column names. (Some columns can have the same name in both of the files, this was left on purpose).

The next step is to deduplicate the data. Some data from the second file is also present on the first file and this means that they are duplicates and we need to remove them and only keep one of the entries. There is also other data on the second file which is not present on the first file (i.e. not duplicate) so we shouldn’t remove these.

For a record to be considered duplicate, we will be considering the following condition: If the first name, last name and birth date are the same, then we are talking about the same person, i.e. duplicate.

In order to do this, we need to use the Duplicates facet feature which is located under Facet  Customized facets  Duplicates facet.

This facet returns false if the value is not duplicate and true if the value is a duplicate. The idea in our case is to create three duplicate facets which check for duplicates on the Firstname AND Lastname AND Date_Birth column.

Go ahead and create three facets for the three columns and select the true value on each one of them. This will return the columns that are now duplicates on all three values of the selected columns (our target).

Now all duplicate records remain, i.e. same first name, same last name and same date of birth. The next task is to sort the records alphabetically by one of the three columns (e.g. Firstname). After sorting the column, select Sort and then Reorder rows permanently. This is necessary to use the next function, which hides the duplicate records (except the first one).

When you have permanently re-ordered the rows (necessary step), go ahead and select the column you sorted and click on Edit Cells  Blank down. This will hide all the duplicate records (besides from the first one), which is exactly what we need.

Now you need to face the lines after spaces. This is a custom OpenRefine facet that you can find at Facet  Customized Facets  Facet by blank (null or empty string).

For the facet created with the facet by blank option, select true so that only the duplicate records (our target) are left. Now click on the All column and select Edit Rows  Remove matching rows. This will remove all rows from the current selection (the duplicate records).

Remove all facets by clicking on Remove All and you will have only unique records.

On the Phone column (or Phone_Number) there are some phone numbers with country code and some without. There’s also spaces on some of the numbers and not on others.

Write a text transform function using GREL on the Phone column that will standardize all it’s column values. You can do this by either adding the country code to all of them (who don’t have it) or by removing it from the ones who have it. You also need to remove spaces from the values that have them, or add spaces to the ones who don’t, it’s up to you.

After you finish this last task, you will have integrated, validated and deduplicated a source dataset into a target dataset successfully.

Export the data into the MS Excel 2007+ (.xslx) format in order to finish this exercise.

For this task, the data from the file address_list_original.xlsx is used.

After you have the right dataset, open the OpenRefine program and create the project using the dataset you just downloaded.

With OpenRefine we will do the Nominatim API requests, fetch the data, parse the lat/lon out of it, then put it into the right columns.

We need to concatenate address columns to one single column Full_Address in order to be ready for the geocoding call. We can do this using column joins and other functions, as follows:

Now, we have to call a Geocoding API using OpenRefine which will give us more information about our address, including lat/lon attributes.

Now that we have the JSON object containing information about our customer’s locations, we can use that JSON object to create new columns out of it, such as lat/lon.

In order to do this, we need to:

Now you have a clean dataset with lat and lon coordinates for the customer addresses retrieved using OpenRefine and geocoding APIs. There may be some locations that were not found by the API. In this case, you can use a facet to filter out the empty values, and then manually modify these addresses to find out what is wrong with them, and then make the API request again.

HTML is the standard markup language for web pages. It is not a programming language but rather an markup language which describes the structure of a web page.

Web pages are written in HyperText Markup Language (HTML). HTML is the standard language for documents designed to be displayed in a web browser.

The Document Object Model (DOM) is the data representation of the elements/objects that contribute to the content of a HTML document. The DOM plays a crucial part in web scraping since that can be used to access elements inside a web page (just like the DOM selector methods in JavaScript).

A key feature of OpenRefine is the General Refine Expression Language, short GREL. GREL is an expression language specific to OpenRefine, which is similar to JavaScript and is convenient for performing different functions such as:

GREL is going to be used in our case to access the HTML DOM and select the appropriate data out of the HTML page for scraping.

There exist multiple ways for bringing the HTML content of a web page into OpenRefine, however for this exercise, we are going to use the Clipboard option of OpenRefine. Proceed by copying the link of the web page and then paste it into the Clipboard text area of OpenRefine’s initial page.

Configure your parsing options (leave them as default), name your project, click on Create Project and move on to the next step.

Now you will have one column and one cell simply containing the same link you copied into OpenRefine.

Now click on the dropdown of that column and select Edit Column  Add column by fetching URLs…​. Simply give a name to the new column, e.g. html or html_content, leave the expression as value and click on OK.

Now a new column will be created containing the whole pure HTML content of the web page we selected. We are going to use this column to extract the necessary information we need for the castles data.

Now we are going to use GREL’s HTML parsing functions to parse the HTML and place it into the appropriate columns.

We are going to use a forEach iterative loop from GREL to loop through the appropriate elements and only extract the data about the castles. In the loop, we are going to use GREL’s HTML parsing functions and basic CSS selectors logic to select the needed HTML elements.

When you have extracted all the necessary information for the castles, clean up your data set by removing the HTML content columns and other unnecessary data/columns. You will be left with a table containing the castles and fortresses of Switzerland, which you scraped from Wikipedia using OpenRefine.

There are multiple ways you can extract HTML content but as soon as you can receive all the information you need effectively, all of them are valid.