data cleaning – OUseful.Info, the blog…

Here’s a quick walkthrough of how to use OpenRefine to prepare a simple data file. The original data can be found on a web page that looks like this (h/t/ The Gravesend Reporter):

Take a minute or two to try to get your head round how this data is structured… What do you see? I see different groups of addresses, one per line, separated by blank lines and grouped by “section headings” (ward names perhaps?). The ward names (if that’s what they are) are uniquely identified by the colon that ends the line they’re on. None of the actual address lines contain a colon.

Here’s how I want the data to look after I’ve cleaned it:

Can you see what needs to be done? Somehow, we need to:

– remove the blank lines;
– generate a second column containing the name of the ward each address applies to;
– remove the colon from the ward name;
– remove the rows that contained the original ward names.

If we highlight the data in the web page, copy it and paste it into a text editor, it looks like this:

We can also paste the data into a new OpenRefine Project:

We can use OpenRefine’s import data tools to clean the blank lines out of the original pasted data:

But how do we get rid of the section headings, and use them as second column entries so we can see which area each address applies to?

Let’s start by filtering to data to only show rows containing the headers, which we note that we could identify because those rows were the only rows to contain a colon character. Then we can create a second column that duplicates these values.

Here’s how we create the new column, which we’ll call “Wards”; the cell contents are simply a duplicate of the original column.

If we delete the filter that was selecting rows where the Column 1 value included a colon, we get the original data back along with a second column.

Starting at the top of the column, the “Fill Down” cell operation will fill empty cells with the value of the cell above.

If we now add the “colon filter” back to Column 1, to just show the area rows, we can highlight all those rows, then delete them. We’ll then be presented with the two column data set without the area rows.