A few weeks ago two new data sets appeared on the Open Data Canada website, WD - Grants and Contributions over $25,000 and Project Geo-Information, which "provides geographical information on projects issued by or on behalf of Western Economic Diversification Canada." Not particularly well-named data sets, but that's another issue. I thought it would be a fun exercise to combine the two, since the geo information is kinda worthless without the actual project data to go with it.
First I imported both files into Google Sheets to see if I could merge the data sets there. I found an add-in called Merge Sheets that should've done it, but perhaps the files were too large, so no dice. Knock yourself out if you want to try (note the two sheet tabs at the bottom).
So I ended up using VLOOKUP in Excel to combine the two files. I always have a hard time wrapping my head around VLOOKUP, but I got it done. Had to save the result as a .csv in order to import to other tools; if saved as .xlsx there are errors as the formula continues to reference a separate sheet. Maybe if I'd worked with them together rather than as separate files. Anyhoo, the .csv worked when I then brought that new file into Google Fusion Tables for mapping fun.
And the end result is:
Seeing it on a map immediately makes me wonder why some big money is being spent in Ontario. I could also immediately saw one record had an incorrect lat/lon assigned to it, putting a BC project in ON, but I fixed that, so it doesn't appear on the above map.
Next up, if I make the time, is to assign a different icon to different types of projects, or perhaps different sizes / colours for different grant amounts. And explore what other online mapping tools might make this easy to work with now that the full data set exists.
