Magically Reformat Data to Get it How You Need it

Dec 12, 2008

One of the more painful parts of analysis or visualization is that you have to get the data in a proper format. Real data almost never comes how you want it. Magic/Replace from DabbleDB lets you reformat data via their spreadsheet interface and a few sprinkles of magic. The solution is really quite elegant.

You copy and paste CSV or TSV from a spreadsheet and submit. You then see a column editor and a preview window. This is where the magic happens. In the column editor, you can edit a column so that it fits a certain format and Magic/Replace will show you a preview of what the others will look like. For example, say you have a column of phone numbers and they’re in the (555) 555-5555 format, but what you really want is 555-555-5555. Change a single row, and voila, Magic/Replace does the rest. It really is “data cleanup for everyone” – not that the data were dirty to begin with.

[Thanks, Jose]

2 Comments

  • Looks very nice!
    But IMHO, if you’ve got the time, it’s better to just learn regular expressions and work with a decent text editor that can apply them. Once you get the hang of it, it’s as powerful and fast as anything.

  • It does look nice. Dabbledb look interesting. You may also want to check out Mass Edit from MIT. It seems to do everything at least in the Magic/Replace video and is non-corporate. I came across it quite a while ago when I was checking out their SIMILE project (also some great free tools) and was very impressed http://groups.csail.mit.edu/uid/mass-edit/

Favorites

The Best Data Visualization Projects of 2011

I almost didn’t make a best-of list this year, but …

19 Maps That Will Blow Your Mind and Change the Way You See the World. Top All-time. You Won’t Believe Your Eyes. Watch.

Many lists of maps promise to change the way you see the world, but this one actually does.

Top Brewery Road Trip, Routed Algorithmically

There are a lot of great craft breweries in the United States, but there is only so much time. This is the computed best way to get to the top rated breweries and how to maximize the beer tasting experience. Every journey begins with a single sip.

Years You Have Left to Live, Probably

The individual data points of life are much less predictable than the average. Here’s a simulation that shows you how much time is left on the clock.