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]

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/