Reformatting, de-duping, merging, and filtering are just some of the functions that go under the broad category of data wrangling. It’s all the scrubbing and cleaning that data scientists apply to raw data before it’s ready for real analysis. Even The New York Times ran an article about this less glamorous side of Big Data, referring to wrangling as ‘janitor work’.
We prefer to see it instead as an important first step to understanding and gaining insights into the datasets you’ll be dealing with. However, this doesn’t have to be a tedious manual task.
There are some great free tools available that can make this part of data science less of a chore. Cindy and I scoured the Internet to put together the following list of powerful wrangleware.
Ever had to convert a table data embedded in a PDF into a spreadsheet? There should be a better way to do this than pasting raw PDF into Excel, and then spending hours forcing the messy data into the right columns. The very smart Tabula does this task automatically. It’s available as a Github project. It’s great for marketers, data journalists, financial analysts, as well as data scientists.
OpenRefine was a Google code project that now lives on as open source software. Its friendly GUI is very good at letting you describe and then manipulate data. It was meant for non-data scientist to use directly, but it has a powerful set of programmable expressions for more sophisticated tasks.
3. “R” packages
R is an important programming language for data scientists. It has serious support of statistical and probability functions, and excels at handling slabs of numeric data, unlike general purpose languages. R can be extended through a series of libraries or packages so you don’t have to reinvent the data wrangle wheel. R programmers have used the functions in the popular dlpr and tidyr packages to help them tame unruly data. There’s a good overview of how to wrangle with R, courtesy of the folks at ComputerWorld.
If you’re new to R and want to give it a try, there’s a great interactive tutorial over at Code School.
Highly recommended by top analysts, visualizers and data scientists, DataWrangler is an interactive tool for data cleaning. It takes messy, real-world data and transforms it into data tables. Then you can export to Excel, Tableau, R, etc. The goal: spend less time manually formatting and more time analyzing your data.
csvkit can help convert data – from Excel to CSV, JSON to CSV, Query with SQL and much more! Simply put, csvkit will make your data wrangling life easier.
6. Python and Pandas
Python of course is an excellent language for data manipulation. Add on the Pandas library, which includes its DataFrame object, and data scientists can quickly perform even more complex operations. For example, merging, joining, and transforming huge hunks of data with a single Python statement.
7. Mr. Data Converter
Mr. Data Converter is straight forward – it takes Excel data and transforms it to web-friendly formats like HTML, JSON and XML.