Clean Data Made Easy With Free Tools


We see so many stories these days about big data and its vast, unstructured nature that sometimes it's easy to forget that just a little organization makes it possible to reveal new ideas.

Simple tools for cleaning unformatted data can help remind us.

Google Refine, formerly Freebase Gridworks, is one such tool. Google Refine downloads and operates from your desktop, though the interface is through a standard browser. The user can upload data into a file project, and then separate text threads that appear throughout the dataset for placement in separate columns, rows, or in any tabular arrangement. Refine permits this editing over a large scale of data, as you can see in the video below. Google even encourages users to approach Refine differently from a spreadsheet.

The feature I like is that Refine saves each edit, much like Apple’s Time Machine backup software. Users can move back and forth to compare tabular changes. A user can use a thread separation methodology in another project by saving it as a JSON file, then importing it.

That leads to another nice aspect. You don't need expert knowledge of code language to use Google Refine. A much more valuable skill is imagination. Paramount is knowing how to refine data based on one’s understanding of what needs to be separated or combined.

Let's consider, for example, a GM analyst who is matching data related to the Chevrolet Corvette. If he understands that a 7.0 liter V-8 engine and a 427 cubic inches V-8 engine have some similar references and meanings, he can group data with a focus on the similarity. Google Refine maintains a few features that permit the user to focus on the purpose behind the dataset rather than how to express the arrangement of the data in a specific technical language.

Google has recently updated Refine with a new format. The improvements do not radically change the user interface, unlike updates to Google Analytics and Google YouTube Analytics, as I discuss in a previous post, Google Spruces Up YouTube Analytics.

Additional tools for refining data include DataWrangler, created by the Visualization Group at Stanford University.

Wrangler Demo Video from Stanford Visualization Group on Vimeo.

The DataWrangler tool also permits users to manipulate text strings, but it approaches the task differently than Google Refine. First, it operates online, instead of on your desktop -- which may rule out its use for companies that prefer to keep data manipulations inhouse, even though developers say no data is saved. Second, DataWrangler makes suggestions on a highlighted row. It also permits exports as a Python or JavaScript file, alongside the expect CSV file options.

Developers need to do some work on some features that aren't fully intuitive yet. For example, initially it would appear that you can only select one of three example datasets before realizing you can edit and paste your intended set.

Tools like Google Refine and DataWrangler will not eliminate challenges of finding and arranging data. But they do make the data cleaner when imported. As a result, analysts will be able to envision how a table can potentially be created, and ultimately how a dataset can potentially help your business.

Do you have a data cleansing tool of choice? Share on the message board below.

Pierre DeBois, Founder, Zimana

Pierre DeBois is the founder of Zimana, a small business analytics consultancy that reviews data from Web analytics and social media dashboard solutions, then provides recommendations and Web development action that improves marketing strategy and business profitability. He has conducted analysis for various small businesses and has also provided his business and engineering acumen at various corporations such as Ford Motor Co. He writes analytics articles for AllBusiness.com and Pitney Bowes Smart Essentials and contributes business book reviews for Small Business Trends. Pierre looks forward to providing All Analytics readers tips and insights tailored to small businesses as well as new insights from Web analytics practitioners around the world.

Clustering: Knowing Which Birds Flock Together

Analytics pros from many different industries employ clustering when classification is unclear. Here's how they do it.

How Analytics Can Help Marketers Reuse Content to Boost Sales

As content marketing becomes important, aging content becomes a concern. Here are some ways of using analytic reporting to develop content ideas and to align content with customers in the sales cycle.


Re: Master source
  • 2/7/2012 6:40:04 AM
NO RATINGS

Hi Daniel,

Exactly right  - Refine is a desktop solution viewed through a broswer, so given Google's past with its products, usage can seem like its online (The user downloads the program).  Data Wrangler is online, provided vis Stanford U.

I am seeing other products that operate with a private cloud aspect - it will be interesting to see how this niche develops.  It reminds me of BMW and how it has tried to cover a number of niches within their product lines.  We seem to be in the era of addressing a niche, with inspiration on and offline. :-)

Re: Master source
  • 2/7/2012 6:34:40 AM
NO RATINGS

Shawn,

You're right. Tools like Data Wrangler and Google Refine can empower.  These tools are essentially adhoc in purpose, so there's an implication within organizations to communicate with care, ensuring that information discoverded from their usage is shared.

Re: Master source
  • 2/1/2012 12:18:39 AM
NO RATINGS
1 saves

Pierre, you mean in user computer. Sorry, I thought it's just like other Google cloud based online products (Doc's, health etc).

Re: Master source
  • 1/31/2012 9:34:20 PM
NO RATINGS

Hi Pierre,

These tools definitely sound interesting. I know some get antsy about BI-as-a-service being readily available, but I've always believed that more information empowers. It sounds like these technologies are just the thing to place more BI power in the hands of all employees for the benefit of an entire organization.  

Re: Master source
  • 1/31/2012 8:46:47 PM
NO RATINGS

Hi Daniel,

The files created can be exported, so the table created can be ported.  For Google Refine, the application resides on the computer, even though you are using a browser for the interface, so there is not portability for the application.

Re: different tools
  • 1/30/2012 10:49:07 AM
NO RATINGS

I would have to agree, Maryam. That's one of the worries I have when we start talking about easy accessibly of BI-as-a-service offerings.

Re: Master source
  • 1/30/2012 10:46:20 AM
NO RATINGS

Joe, when you suggest there be "one final arbiter 'handling' the data in a uniform way" are you thinking along the lines of creating a master file/single source of truth, as I talk about in this recent blog about master data management? Or did you have something less formalized in mind for this level of data/tool/user?

Re: Master source
  • 1/30/2012 5:24:30 AM
NO RATINGS

One article points out that upto 80 percent of data analysis efforts are spent on cleaning the data. In light of this he proposes data marketplaces as a means of obtaining common data. Something i agree with as a way of reducing data cleaning effort as a whole and leaving it for some firms to specialize.

However when push comes to shove and you really must collect the data yourself from raw source, such tools as google refine come in handy. The positive thing is that the learning curve for Google tools is not too steep, enabling even companies with a limited budget for analytics experts to handle a good chunk of their analytics by themselves

Re: Master source
  • 1/30/2012 5:23:11 AM
NO RATINGS
1 saves

Pierre, I would like to know, whether such open source tools are portable, I mean import and export to different environments and analytical tools.

Thanks
  • 1/29/2012 11:51:12 PM
NO RATINGS

Thanks for posting this Pierre. I didn't know there were free tools available that were also easy to use.

Page 1 / 3   >   >>
INFORMATION RESOURCES
ANALYTICS IN ACTION
CARTERTOONS
VIEW ALL +
QUICK POLL
VIEW ALL +