5 ms·

Using Google Refine to Clean a Data Set

69 points by craig552uk 14y ago

richardv 14y ago
I found this really helpful. Not so much to do with the actual article, but to do with actual making me aware of Google Refine. Installation was a breeze. I couldn't find any instructions, but it was as simple as downloading for Linux, extracting, the running the shell script. http://code.google.com/p/google-refine/downloads/detail?name=google-refine-2.5-r2407.tar.gz&can=1&q= http://code.google.com/p/google-refine/downloads/detail?name... The application automatically opens in a new Chrome window. From here, I grabbed a data dump from one of our external providers. We work with a lot of providers who are really technologically challenged. I'd love to be able to say, here you are.. here is our API, start pushing your content to us. But in practice they don't even know what their XML feeds do. We need their data, but getting a consistent dataset from them when they seem to change their format regularly is a pain! And when importing only 10 or so items at a time it's excruciatingly painful. Today I learnt how easy that can be with Google Refine!
- craig552uk 14y ago
  I'm glad you found it useful Richard. Tip: GR can have a bit of a wobble from time-to-time, usually restarting the process will sort things out.
- bockris 14y ago
  Also check out Data Wrangler. http://vis.stanford.edu/wrangler/ http://vis.stanford.edu/wrangler/ It focuses on more mechanical transformations but has the ability to save the steps to a program which you can then use in a process pipeline. (disclaimer, I haven't played with it in a few months so this is from memory)
  - craig552uk 14y ago
    GR also allows you to export/import steps to reuse. Though I think DataWrangler is easier to integrate in to an automated pipeline.
    - etrain 14y ago
      Yeah, Wrangler will turn your "manipulation" steps into scripts in Python/MapReduce/Javascript.
guard-of-terra 14y ago
I wonder why they won't let you to open local files without passing their content via browser. Should be very useful when run locally.
- eli 14y ago
  It is local, it's just the browser is the interface. Or am I missing something?
  - guard-of-terra 14y ago
    You can't select a local file. You have to upload file even if it's local.
    - pavedwalden 14y ago
      It's been a while since I used refine, but IIRC it just ran on localhost and I didn't have to upload anything.
      - guard-of-terra 14y ago
        You run it on localhost but you upload files from your localhost into your localhost. Which is the point.
- craig552uk 14y ago
  Google's all about the web. Even local apps are web apps in their world. Which isn't to say that's a bad idea...
  - guard-of-terra 14y ago
    That doesn't justify passing 700 megabyte files via my browser when they could be simply opened being local.
    - 14y ago
dpcx 14y ago
This seems, on the surface at least, very similar to what ScraperWiki is trying to do, by converting messy publicly available data in to a more structured format. Am I correct in that understanding, or did I miss the boat?
- craig552uk 14y ago
  ScraperWiki is a data aggregator/publisher. Google Refine is a general purpose tool for cleaning up data sets.
chucknelson 14y ago
Not very impressive for people who work with data sets often and probably have tools like SAS or Excel, but good to know it exists as a free alternative.
- guard-of-terra 14y ago
  This thingy opens 2.3 million lines file in something under a minute. Excel will choke, I guess. Still unix toolset - awk, grep, sort - beats both for most tasks and huge data sets.
  - craig552uk 14y ago
    Yeah, GR chocked on a 7GB file the other day, had to chop it up in the shell before importing each in turn. That's when the export/import processing steps feature comes in handy.
- craig552uk 14y ago
  SAS has comparable features (though I prefer R for analysis). Excel is pretty poor for working with moderately sized or larger data sets. Google Refine is nice for cleaning up and pre-processing data files before exporting elsewhere for analysing.
- danso 14y ago
  As far as I know, Excel has no such comparable tools for data cleanup. Excel doesn't even have built in regexes. On the other hand, Refine is not built for doing calculations. So there's really no comparison except that both programs display data in a tabular format.
frankc 14y ago
Is this worth looking into for someone who already knows perl, R and the unix zoo? Or is it more targetted at people who don't deal with data on a regular basis?
- craig552uk 14y ago
  It's another string to the bow - so why not? Have a quick look over the screen casts. If you're familiar with those tools you'll map the concepts pretty quickly. http://code.google.com/p/google-refine/ http://code.google.com/p/google-refine/
- rictic 14y ago
  One place where Refine distinguishes itself compared to those tools are its quick visualizations of subsets of the data. In the article the author is able to tell that a column is always blank, and thus worth dropping, but Refine can do a lot more there.
danso 14y ago
As a data analyst-type-person, I can't recommend enough the use of Google Refine. When someone told me about it, I thought "that's dumb, I would just write a cleaning/regex script and connect to my DB"...but tried it out anyway, because my colleague is a much better power programmer than I am. That's how good Refine is...it adds an extra, GUI-driven step to the workflow, but it's so well executed that it makes data exploration (and cleaning) effortless. I wrote a tutorial awhile back about how I used it in an investigative reporting project: http://www.propublica.org/nerds/item/using-google-refine-for-data-cleaning http://www.propublica.org/nerds/item/using-google-refine-for...

69 points by craig552uk 14y ago

I found this really helpful. Not so much to do with the actual article, but to do with actual making me aware of Google Refine. Installation was a breeze. I couldn't find any instructions, but it was as simple as downloading for Linux, extracting, the running the shell script. http://code.google.com/p/google-refine/downloads/detail?name=google-refine-2.5-r2407.tar.gz&can=1&q= http://code.google.com/p/google-refine/downloads/detail?name... The application automatically opens in a new Chrome window. From here, I grabbed a data dump from one of our external providers. We work with a lot of providers who are really technologically challenged. I'd love to be able to say, here you are.. here is our API, start pushing your content to us. But in practice they don't even know what their XML feeds do. We need their data, but getting a consistent dataset from them when they seem to change their format regularly is a pain! And when importing only 10 or so items at a time it's excruciatingly painful. Today I learnt how easy that can be with Google Refine!

craig552uk 14y ago
I'm glad you found it useful Richard. Tip: GR can have a bit of a wobble from time-to-time, usually restarting the process will sort things out.
bockris 14y ago
Also check out Data Wrangler. http://vis.stanford.edu/wrangler/ http://vis.stanford.edu/wrangler/ It focuses on more mechanical transformations but has the ability to save the steps to a program which you can then use in a process pipeline. (disclaimer, I haven't played with it in a few months so this is from memory)
- craig552uk 14y ago
  GR also allows you to export/import steps to reuse. Though I think DataWrangler is easier to integrate in to an automated pipeline.
  - etrain 14y ago
    Yeah, Wrangler will turn your "manipulation" steps into scripts in Python/MapReduce/Javascript.

guard-of-terra 14y ago

I wonder why they won't let you to open local files without passing their content via browser. Should be very useful when run locally.

eli 14y ago
It is local, it's just the browser is the interface. Or am I missing something?
- guard-of-terra 14y ago
  You can't select a local file. You have to upload file even if it's local.
  - pavedwalden 14y ago
    It's been a while since I used refine, but IIRC it just ran on localhost and I didn't have to upload anything.
    - guard-of-terra 14y ago
      You run it on localhost but you upload files from your localhost into your localhost. Which is the point.
craig552uk 14y ago
Google's all about the web. Even local apps are web apps in their world. Which isn't to say that's a bad idea...
- guard-of-terra 14y ago
  That doesn't justify passing 700 megabyte files via my browser when they could be simply opened being local.
  - 14y ago

dpcx 14y ago

This seems, on the surface at least, very similar to what ScraperWiki is trying to do, by converting messy publicly available data in to a more structured format. Am I correct in that understanding, or did I miss the boat?

craig552uk 14y ago
ScraperWiki is a data aggregator/publisher. Google Refine is a general purpose tool for cleaning up data sets.

chucknelson 14y ago

Not very impressive for people who work with data sets often and probably have tools like SAS or Excel, but good to know it exists as a free alternative.

guard-of-terra 14y ago
This thingy opens 2.3 million lines file in something under a minute. Excel will choke, I guess. Still unix toolset - awk, grep, sort - beats both for most tasks and huge data sets.
- craig552uk 14y ago
  Yeah, GR chocked on a 7GB file the other day, had to chop it up in the shell before importing each in turn. That's when the export/import processing steps feature comes in handy.
craig552uk 14y ago
SAS has comparable features (though I prefer R for analysis). Excel is pretty poor for working with moderately sized or larger data sets. Google Refine is nice for cleaning up and pre-processing data files before exporting elsewhere for analysing.
danso 14y ago
As far as I know, Excel has no such comparable tools for data cleanup. Excel doesn't even have built in regexes. On the other hand, Refine is not built for doing calculations. So there's really no comparison except that both programs display data in a tabular format.

frankc 14y ago

Is this worth looking into for someone who already knows perl, R and the unix zoo? Or is it more targetted at people who don't deal with data on a regular basis?

craig552uk 14y ago
It's another string to the bow - so why not? Have a quick look over the screen casts. If you're familiar with those tools you'll map the concepts pretty quickly. http://code.google.com/p/google-refine/ http://code.google.com/p/google-refine/
rictic 14y ago
One place where Refine distinguishes itself compared to those tools are its quick visualizations of subsets of the data. In the article the author is able to tell that a column is always blank, and thus worth dropping, but Refine can do a lot more there.

danso 14y ago

As a data analyst-type-person, I can't recommend enough the use of Google Refine. When someone told me about it, I thought "that's dumb, I would just write a cleaning/regex script and connect to my DB"...but tried it out anyway, because my colleague is a much better power programmer than I am. That's how good Refine is...it adds an extra, GUI-driven step to the workflow, but it's so well executed that it makes data exploration (and cleaning) effortless. I wrote a tutorial awhile back about how I used it in an investigative reporting project: http://www.propublica.org/nerds/item/using-google-refine-for-data-cleaning http://www.propublica.org/nerds/item/using-google-refine-for...