Cleaning Your Data Withopenrefine: Short Description

Cleaning Your Data WithOpenRefine: Short Description

We need data for everything. For homework, for work, for our personal lives and for our hobbies. Unfortunately, data is often messy, incomplete and disorganized data. Spelling mistakes, blank entries, inconsistent values (USA vs. United States of America vs. America, etc…) and simple errors are only a few of the many problems that we face when using data in our lives. In short, messy data is all around us.

Because of this, cleaning up data to make it useable and useful is an inevitable and important task. A tool calledOpenRefine (formerly known as GoogleRefine) is something that can assist in that task.

So what is OpenRefine? It is free and open-source software that uses your internet browser as the graphical interface (but does not require connection to the internet) and allows you to work with and clean your data using a spreadsheet model, much like Excel. Working on Windows, Mac or Linux, you can use OpenRefine for datasets with a few rows/records to a few hundred thousand.

Downloading OpenRefine

You can download OpenRefine from the link below

What can OpenRefine do?

Find and Replace

Need to replace the value “USA” with “United States of America”? That is easily done with OpenRefine

Filter

Display only a selected value

Clustering

Find and change similar values with one move (Banana vs. Bananaa vs. Bannana)

Display all Values and Number of Times Each One Appears

In one step, find every single value in a column and the number of times it appears

Keeping Track of Every Step and Easy Reversal of Steps

OpenRefine keeps track of all your steps, so you can always roll back if you made a mistake.

…And Numerous Other Things to Make Your Data More Useable

Exporting Your Data Out of OpenRefine

When you are finished in OpenRefine, you can export data out of OpenRefine from a variety of formats including, Excel, CSV, TSV, HTML. You also can export OpenRefine projects to allow others to see exactly what steps you took with the data within OpenRefine.

In addition, with some customization, you can export your data into other formats, such as JSON.

Importing Data into OpenRefine

OpenRefine accepts numerous file formats including Excel (.xls, .xlsx) CSV, TSV, XML, JSON, RDF Triples, Google Spreadsheets and Google Fusion Tables. It can also import archived files (.zip, .tgz, .tar.gz, etc…) and the files can be on your local machine or on the web.

Some General Principles When Using OpenRefine

Think in patterns: identify the common characteristics of the cells or rows that you want to change…

And then…

Use facets and filters to isolate them, then invoke a single command to change them all. Facets are tools within OpenRefine that summarize the cells with the column that satisfy the criteria given.

For example, using the text filter on a column would count out each unique value within the column and the # of time it appears.

You can create a custom facet to list out the number of time a specific value appears.

Links to Tutorials/Guides and other OpenRefine Resources

A step-by-step guide to using OpenRefine using a sample dataset

An excellent series of video tutorials put out by Google when it was known as GoogleRefine. A good introduction

The OpenRefine community is quite active and there are a number of resources out there if you have any questions.

Need Additional Assistance with OpenRefine or a Consultation?

Please contact Simon O’Riordan – Metadata Analyst – Woodruff Library