Use Google Refine to Massage Your Data

Productivity Sauce
Google Refine is an immensely powerful tool for dealing with "messy" data, and it sports a myriad of advanced features for massaging and analyzing complex data sets. However, that doesn't mean Google Refine can't be used to solve more mundane problems.
Of course, before you can put Google Refine to some use, you have to install it. First off, make sure that the Java Runtime Environment is installed on your machine. Grab then the latest version of Google Refine from the project's Web site and unpack the downloaded .tar.gz archive. In the terminal, switch to the resulting directory and start the application using the following commands:
cd google-refine ./refine
This starts the application and automatically opens it in the default browser.
To see what you can accomplish with Google Refine, let's take a look at a real world example. If you, like me, are an avid book reader and Kindle 3 user, you might have hundreds of highlights stored in the My Clipping.txt file. Having all the highlights in a single text file is not very practical, so it would be great to convert data in the My Clipping.txt file into a tab-separated format, so you can import them into a database. The problem is that the data in the My Clipping.txt file is not particularly well structured. Here, take a look at how highlights are stored in the file:
The Shallows: What the Internet Is Doing to Our Brains (Nicholas Carr) - Highlight Loc. 2106-9 | Added on Saturday, October 23, 2010, 11:24 PM It is the very fact that book reading “understimulates the senses” that makes the activity so intellectually rewarding. By allowing us to filter out distractions, to quiet the problem-solving functions of the frontal lobes, deep reading becomes a form of deep thinking. The mind of the experienced book reader is a calm mind, not a buzzing one. When it comes to the firing of our neurons, it’s a mistake to assume that more is better. ========== The Shallows: What the Internet Is Doing to Our Brains (Nicholas Carr) - Highlight Loc. 2117-18 | Added on Saturday, October 23, 2010, 11:26 PM If working memory is the mind’s scratch pad, then long-term memory is its filing system. ==========
This is where Google Refine can come in rather handy, allowing you to massage the data into a usable form with a minimum of effort. Here's how it works. Start with creating a new project in Google Refine. Choose the My Clipping.txt file as the project's data file, untick the Split into columns check box, set Header lines to 0, and press the Create Project button. This creates a new project, where each entry in the My Clipping.txt file is treated as a separate row.
Fortunately, Google Refine provides an elegant way of turning rows into columns. Click on the menu next to the Column entry and choose the Transpose | Cell in rows into columns command. When prompted, specify the number of rows you want to transpose (in this case, it's 4), and press OK. Google Refine then processes the rows and moves them into the appropriate columns. The hardest part is over, and there are just a few simple things left for you to do.
Obviously, Column 4 is useless, so you can safely delete it using the Edit Column | Remove this column command. It also makes sense to give the columns more descriptive names, which can be done using the Edit Column | Rename this column command. And you can rearrange columns using the Edit Column | Move column to beginning / Move column to end / Move column left / Move column right commands. Finally, you can use the Export button to save the massaged data in a variety of formats.
Comments
comments powered by DisqusIssue 269/2023
Buy this issue as a PDF
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs
Subscribe to our ADMIN Newsletters
Find SysAdmin Jobs
News
-
Kubuntu Focus Announces XE Gen 2 Linux Laptop
Another Kubuntu-based laptop has arrived to be your next ultra-portable powerhouse with a Linux heart.
-
MNT Seeks Financial Backing for New Seven-Inch Linux Laptop
MNT Pocket Reform is a tiny laptop that is modular, upgradable, recyclable, reusable, and ships with Debian Linux.
-
Ubuntu Flatpak Remix Adds Flatpak Support Preinstalled
If you're looking for a version of Ubuntu that includes Flatpak support out of the box, there's one clear option.
-
Gnome 44 Release Candidate Now Available
The Gnome 44 release candidate has officially arrived and adds a few changes into the mix.
-
Flathub Vying to Become the Standard Linux App Store
If the Flathub team has any say in the matter, their product will become the default tool for installing Linux apps in 2023.
-
Debian 12 to Ship with KDE Plasma 5.27
The Debian development team has shifted to the latest version of KDE for their testing branch.
-
Planet Computers Launches ARM-based Linux Desktop PCs
The firm that originally released a line of mobile keyboards has taken a different direction and has developed a new line of out-of-the-box mini Linux desktop computers.
-
Ubuntu No Longer Shipping with Flatpak
In a move that probably won’t come as a shock to many, Ubuntu and all of its official spins will no longer ship with Flatpak installed.
-
openSUSE Leap 15.5 Beta Now Available
The final version of the Leap 15 series of openSUSE is available for beta testing and offers only new software versions.
-
Linux Kernel 6.2 Released with New Hardware Support
Find out what's new in the most recent release from Linus Torvalds and the Linux kernel team.
Dragging and dropping Columns is supported as well
Click the drop-down arrow on first column ALL --> Edit columns --> Reorder columns...
You then have a popup dialog box to drag and drop the columns themselves. Great for those datasets that have many, many columns to deal with.
And if you have too many columns and run into visibility hell, you can always "hide" columns using Collapse this column and then "unhide" all the columns with ALL --> View --> Expand all columns...