Magically Reformat Data to Get it How You Need it

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]


  • 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


Where Bars Outnumber Grocery Stores

A closer look at the age old question of where there are more bars than grocery stores, and vice versa.

Think Like a Statistician – Without the Math

I call myself a statistician, because, well, I’m a statistics graduate student. However, the most important things I’ve learned are less formal, but have proven extremely useful when working/playing with data.

Watching the growth of Walmart – now with 100% more Sam’s Club

The ever so popular Walmart growth map gets an update, and yes, it still looks like a wildfire. Sam’s Club follows soon after, although not nearly as vigorously.

Life expectancy changes

The data goes back to 1960 and up to the most current estimates for 2009. Each line represents a country.