vimeo.com/.../508797388

So let's start the
OpenRefine desktop

application, which opens up
a new browser window and

runs on a default port on
the local machine.

We can create a new
project, open or import an

existing project,

or just change the language
settings if needed.

Let's download the stock start zip
file and browse through the file

from within the OpenRefine
Create Project menu.

And click next.

This imports the contents of
the file into application

memory. Note that the tool
can work both with compressed

and uncompressed files.

Since the stock data is in the
semi-structured json format, we

will select the root node of the
json tree, which transforms the

data into a tabular format with
rows and columns.

We can choose to preserve empty strings

and perform basic cleanup such as

trimming of whitespaces and
parsing numerical values.

These operations can also be
performed at a later stage.

We will provide a project
name and proceed with

project creation.

Once the project has
been created,

we can click All → Edit columns
and remove some of the unwanted

columns from the data set.

This helps reduce the memory
usage, especially for large

data sets. We'll also go
ahead and reorder some of

the columns in the table.

Now the data set is lighter
with only those columns that

will be used potentially for
further analysis. Just like

in a spreadsheet, we could
choose to sort different

columns. Here we'll go ahead
and sort the volume column

in the descending order.

Financial data sets, such as
stock prices, are constantly

changing. What if our data is
outdated, and we wanted to fetch

the latest data from the stock
market? Using OpenRefine, one

could augment the data using
online web services.

However, calling some of these
web services could be very

time-consuming. So to
demonstrate this feature,

let's work with a smaller
subset of the stocks data and

load it into a new project.

Look for the file stocks_demo.csv in the
session folder,

and load it in OpenRefine.

We will also create a new
OpenRefine project

and call it StocksDemo.

On the ticker column menu, click
Edit column → Add columns by

fetching URLs. This opens up an
expression box where we could

enter the URL of an online web
service which could be invoked

to augment the data.

This online service provides an
API that returns slightly

delayed stock prices.

A good practice is to test
the web service from a

browser client with a known
ticker symbol and validate

the service response.

We will add this URL and append
the ticker symbol as a variable

parameter for each row.

To do this, copy the URL into

the expression box, and insert
codes as shown here.

Now the URL is different for
each row with the ticker symbol

as the symbol parameter.

We'll give a new name to the
column and click OK.

This could take a few minutes
to run depending on the speed

of the web service.

We now have a new column with
the latest stock data.

Notice that the results are in
the XML format and need to be

parsed. Please refer to the
OpenRefine tutorials for some

help with parsing XML and json
results.

For now, here is the
syntax for extracting the stock

price from the XML document.

After a quick review of the
results, we are ready to export

the data to another data
processing tool in our pipeline.

In the next example we will use
OpenRefine for cleaning up a

sample messy data file.

So let's download the
Customers-messy.zip file

from the session folder and store it on

the local computer. The first
step is to visually inspect

the raw data file using a text
editor or Excel and get

familiar with the attributes
and contents of the data.

As before, we will load this
data file into OpenRefine and

create a new project.

Once the data has been
loaded, we'll do a cursory

check on the columns,

and change the character
encoding to UTF 8.

If needed, we could also change
options such as column

separators or add a missing

header row. In our case, we'll
keep the default settings.

So let's give the clean-up
project a name and click on

the Create Project button.

This creates a new OpenRefine
project with a copy of the

original data set. The next step
in the cleanup process is to

visually inspect the data and
check each column for errors,

formatting issues, or missing values.

We can perform common
operations like remove

whitespace from text. For this,
let's click on a column header.

Let's say contact first name,

Edit cells → Trim leading and

trailing whitespace. We can
perform the same operation

on multiple columns.

We could also do basic
concatenation operations like

in a spreadsheet. Let's say we
need to combine the first and

last names of the contact into
one column. For this we'll

click on Edit column →

Add column based on this column.

On the expression box, we
will take the current value

of the contactFirstName.

Add a space followed by the
value of the contact last name.

Let's check to see the results

and assign a name to the new
column, such as

contactFullName. And hit OK.

The newly-created column has
been added to our data set.

A nice thing about OpenRefine is
that you can undo your past

operations, so if you made a
mistake you can go back to a

previous step and correct it.
This makes it really easy to

make incremental changes without
losing time over

saving multiple copies of the data.

Let's go ahead and remove the
column that we just created.

Just like with spreadsheet
applications, we could also

perform other simple, column-based
operations such as

changing the case of the text to
title, upper, or lowercase.

Faceting is a powerful feature
in many data cleaning tools. The

facet summarizes the cells in
that column to give you a bigger

picture on the column. It also
allows for filtering down to

just a subset of the rows that
you want to change in bulk.

Let's see how this works.

Let's create a text facet
on the country column

by clicking on the column's
dropdown menu and

invoking Facet → Text facet. We
can see that

the country names
are not consistent.

For instance, United States
is spelled as US, USA, or

United States, with some
extra characters.

Also, Netherlands is spelled
both with and without the

determiner "the." France is
spelled using the uppercase as

well as lowercase characters.

Clustering allows us to
automatically group and edit

different but similar values.
Let's click on the Cluster

command on the left frame.

We now have the opportunity to
see all the similar words

grouped together and perform a

merge operation. Let's merge all
text referring to the United

States as USA, United Kingdom as
UK, and so on.

The Merge and Re-Cluster
button confirms the

operation. There are no more
obvious clusters that are

left. However, we still
haven't resolved the issue

with Netherlands. For this
we will choose another

clustering operation which
uses a distance metric.

The country column is now fixed,
and there are no more

clusters to be found. However,
we still have one error.

The country Norway has a space
character. See if we can find

a way to fix this error.

Let's now go ahead and use the
tool to clean up a numerical

column like the credit limit.
We'll first apply a numerical

transformation on the column so
that all the numbers are

recognized. Some values in this
column cannot be parsed into

numbers. This is because of the
presence of alphanumeric values,

such as the USD or the dollar

symbol. We can correct this by
applying the Numeric facet.

On the left we see a chart with
the distribution of the data.

Let's filter down to the non-numeric
values only and change

the values of the fields from
the expression box.

This screen allows us to
view the original values as

well as the outcomes when
the expression is applied.

We will involve the replace
function to replace or

substitute the unwanted
characters with blanks. So let's

remove the USD, the dollar, and
the comma symbols.

As you can see, we can make
multiple function calls in a

single line. This is also known

as operator chaining. Now
all the Alpha characters

have been removed.

When we click OK, the tool still
does not see them as numbers.

For this we will need to invoke
the toNumber function to

convert the values within the
facet into numerical values.

So far we have tested these
operations on a numeric facet.

To apply these changes to the
data set in the project we will

copy this expression and apply
to a column transform. This

ensures that all the values in
the credit limit column have

been converted into numbers.

Once we're done with cleaning up
the rest of the data, we are

ready to export the file into a
format of our choosing. For this

example, let's export it as a
CSV or a comma-separated file

and open it to view the results.

All the columns now have clean
data. The data set is now

ready for storage in a
database or for further analysis.