Wednesday, April 6, 2016

Tips on cleaning your data for mapping

Offering a few tips for teams that want to put their data on a map:

1. Bring your data to a flat excel table. Avoid having any merged cells in the header. Keep the headers in the first row only; from row 2 onwards your data should start. Example: If you had a super-header of "Expenses", and sub-columns of 2011 & 2012, then turn that into two distinct columns titles like "Expenses_2011", "Expenses_2012".

2. Even for the data rows, please clean this up. Un-merge the cells, repeat common information or delete that column (like, first column in your file is useless - there's only one state right?).

3. Make a new latitude column and a longitude column in your table. You have to assign lat-long to each row.

4.For bulk finding lat-long, this tool might help:
Copy-paste the locations column in the textbox, and it searches with google maps and takes the lat-long of the top match. Disclaimer: zero guarantees for accuracy! Note: wait for it as it cycles through each location. Once it's done, you can copy-paste the output CSV textbox and paste>import wizard in excel, or save as a .csv file and open it in excel / calc, etc.

5. To manually find lat-long of a location by clicking on a map, here's a good tool:

6. Once your data is simplified and ready to be machine-readable, you can now upload the excel/spreadsheet to a mapping service. I'll recommend two: CartoDB :, and Google MyMaps:

Example: Recently for Pune we got a little data about accidents, and some entries had lat-longs. So I was able to use my free account on to make this:
Its nowhere near publishing levels because the data we've gotten isn't proper. But this could give you an idea.

No comments:

Related Posts with Thumbnails