Open Refine with Environmental Data

The purpose of this tutorial is to introduce you to a powerful, yet user-friendly tool that allows you to clean, analyze, and visualize your tabular data. In this example we are interested in looking at the relationship between reported direct emissions and stationary combustion in major facilities across the US.

Exploring the Data: Greenhouse Gas (GHG) Emissions from Large Facilities

The following dataset named GHGRP_data_2015_8_13_16.xlsx has been downloaded and edited from the EPA Flight portal at: https://ghgdata.epa.gov/ghgp/main.do#. It contains all the Greengouse Gas Emissions reported to the EPA from Large Facilities during 2015. Download this table to your computer.

Open the table and spend a few minutes getting familiar with the structure and the meaning of each field. Think about relations, patterns, questions that you would like to solve or visualize with this data. The spreadsheet that we will be working with is called Direct Emitters.

Do you see any problems with the way the data has been collected?

Remember where you saved your table. We will be using it in our next step with Open Refine.

Transforming your data with OpenRefine

OpenRefine is a free, powerful tool that allows you to clean, discover, organize messy data and link it on the fly to other databases.

The main reasons for using OpenRefine are:

- It's free, open source, and your data is stored locally so you can clean your data without any privacy issues

- It combines the power of scripting with the simplicity of spreadsheets, which makes it more interactive and experimental than other tools

- User-friendly design that allows for easily detecting and fixing disparities in your data

File formats supported by OpenRefine are: Comma-Separated Values (CSV), Excel documents (.xls, .xlsx), Open Document Format spreadsheets (.ODS), JSON and XML. If you need other formats for your data, you can add them by way of OpenRefine extensions.

You can find excellent resources at: openrefine.org and download the program here.

Importing data

Launch OpenRefine. This should automatically open your web browser to the correct address: 127.0.0.1:3333.

In OpenRefine, on the right-hand side, select Get data from This Computer and click the Browse... button. Locate the GHG data mentioned above and click Open.

Press the Next button to preview your data.

In the screen that appears, we will make sure that we make sure to:

- Direct Emitters is the worksheet to import

- Check Ignore first 3 line(s) at the beginning of file

- Uncheck Store blank rows (bottom right corner of your screen).

If your data looks correct, press the Create Project button on the top right corner.

Basics of Open Refine

One big difference between OpenRefine and Excel is the unit of interaction. In spreadsheets, cells are the main units of interaction. However, OpenRefine works with columns and rows instead. Editing usually happens one column at a time or across rows that meet a specific criteria.

The second main difference is that spreadsheets are primarily used for performing calculations. The goal of OpenRefine is to explore your data, look for patterns, and transform it.

Let's get familiar with common operations. For example, change the number of records displayed by selecting 50 records on the Show menu at the top center of your screen.

We have decided that the FRS Id column is not important and want to delete the column. For that, click on the drop-down arrow at the left of the column title. Select Edit column > Remove this column.

We want to have the Facility Name as the first column that appears. To move the column, click the drop-down arrow next to it, select Edit column > Move column to beginning. Rename the column Industry Type (sectors) to Sectors_Industry_Type by selecting Edit column > Rename this column. Under Enter new column name, type: Sectors_Industry_Type. Click OK.

On your own: remove the column named Column at the end of the table.

Tip: to remove several columns at the same time, click on All at the top left corner of your table. Select Edit columns > Re-order/remove columns. In the window that appears, drag the columns to the right hand-side that you want to remove. For example,all the Petroleum and Natural Gas Systems columns.

Converting to Title-case

Take a look at the City column. Notice some cities are in uppercase while others are in title case. Let's transform the entire column to title case by clicking on the arrow next to City, select Edit cells > Common transforms > To titlecase. Notice the results. Repeat the same operation for the County column.

Keeping Track of Changes: the Undo/Redo tab

Open Refine keeps track of any operation or change you have made to your table. On the top left corner of the program, select the Undo/Redo tab. Notice each operation has a number and the last one is highlighted. To undo that operation, click on the one before. Notice the results. To redo it, click on the last operation again.

This tab is very useful for two additional reasons:

- You can see the number of cells affected by each operation

- You can see the code used for each operation, extract it and apply it to a different column or dataset.

Let's put this into practice. Undo the last operation on County. Click the Extract button and notice the code for each operation. Leave checked only the text for the column City. Copy that code. Close that window.

Click the Apply button. Paste the code. Change the column name City to County. Click Perform Operations and see the results. Cool way to get started in programming, don't you think?

Transforming your data with GREL

Removing Characters from a String

Look at the County column. Notice some records have the word county added. We want to keep the county names consistent so we will be removing the word county whenever we have it.

To clean the names, click the inverted triangle to the left of County, select Facet > Custom Text Facet. In the window that appears, under Expression, type: value.contains("County"). Click OK. Notice in the Facet/Filter tab that you have 2 choices:

- False - cells that do not contain the word County

- True - cells that contain the word County. These are the ones we want to work with. Select those in the Facet tab and notice only those records appear on the table on the right. How many records fit that condition? Now we will perform the operation of subtracting the word County out of those records.

Click on the inverse triangle to the left of the County column. Select Edit cells > Transform. In the Expression box, type: value.substring(0,-6) and notice the results

This function returns the text of the column minus the last six characters starting from the end.

Notice how you don't have any records that fill the true condition anymore. Remove the County facet by clicking on the X on the top left corner.

Try other combinations such as substring(1) and substring(0,4) to understand how this function works. For more information about working with text functions, refer to the GREL String Functions reference.

How would you subtract the first two characters of each State (Ca)?

GREL is the General Refine Expression Language (GREL) used to write transformation expressions in OpenRefine. However, you can also use Jython (implementation of Python designed to run on the Java platform) and Clojure (dialect of Lisp).

Type again value.substring(2) to get the full names on the State column. Click OK. Undo this last step.

Removing White Spaces

One big problem with geocoding names in the presence of white trailing spaces at the beginning or the end of the name. In your table, hover over the Collin cell and press edit. Notice there is a space after Collin. These are very hard to find, so instead of trying to find them manually, let's clean the entire column with a simple function. Click Cancel.

For expression type: value.trim(). Remember to make a cell transformation, go to Edit cells > Transform.

You cannot notice the difference in this window. Click OK.