Mapping Data with Google Sheets (on a Map!)

I have long wondered if student success is tied to where the student lives.  (Why?  Read in this post.)

With between 50 and 120 students at a time, sitting down with a map, charting out each student’s location and then linking their assessment results to that pin has seemed a bit arduous.  Google Maps has made finding the location a bit easier, but never could I figure out how to look up more than a single location at a time (and without having to manually input that, to boot).

After trying out Tableau for a EdX online course (the excellent Data, Analytics and Learning MOOC offered by UTArlingtonX–free and still informative three years after it closed) I was intrigued by its mapping promise.  Below is my journey, as I could not figure out how to get Tableau to do it.

Assessment Map Sheet

My original data table

To work, Tableau needed latitude and longitude data (it probably did it for me, but…)  I had street addresses, with each element in a different column (number, street, town, etc.).  Here is a sample data table.

I got an add-on from Awesome Table.  The program seems to do a whole lot, but I only wanted the latitude and longitude.  On this screenshot note two things.  First, that I was able to create a single address from multiple columns.  That’s what you see happening in the middle.

pasted image 0
pasted image 0 (1)Second, once I had that column it created my latitude and longitude.  You can see that column here (yes, it’s all blurry because I can’t figure out an efficient way to drop screenshots in WordPress, so blur).

Note that in the box on the right it offers you to create a map.  It will, indeed, drop pins on a map.  While I could figure out how to put my data in the pin, I could not figure out how to color code my pins.  That’s what I wanted–to have a range of colored pins so I could see the clusters effortlessly–and then click on individual pins for details.

Then I found Claire Miller, a data journalist who was looking to provide a map of clinics in Wales that still took NHS and those that did not.  You can find her informative blog here.  She tipped me off to Google Tables.  As Claire wrote in 2012, Google Tables was in beta and I hadn’t remember seeing it in years (I assumed it was folded into Google Sheets, because they always struck me as close, mainly because I don’t really know what Google Tables does).  Now called “Fusion Tables” I had to add it to my Drive.

pasted image 0 (2)

I’ll save you the boring steps of linking your spreadsheet and get to the map.  Table will open to Rows 1, so click on “Map of Latitude”.

Ironically, the “Location” choice (on the left) was set for latitude–and it didn’t work after all of that Awesome Table stuff!  When I chose “Full Address” from my Awesome Table merge a few steps ago it did.  Great.  Pins.

pasted image 0 (3)Here is the fun part.  Click on “Feature Styles” (left) and this box comes up.  Claire created a column in her spreadsheet with a command of what pin to place (middle tab) as her’s was a yes-no binary.  I used “Buckets” on the right for a range.  A second benefit is that as I update data I don’t have to update those commands–Buckets reads the new data.  As my scale was a 1-4, that became the range.  Be sure to choose the column you are taking data from (I forgot in doing it here and was flummoxed for a moment as it mapped zip codes).

pasted image 0 (4)Not done.  Click on “Change Info Window” (left) and this box of info will come up.  Here, you choose what data is useful to you or the user.  For example, I unchecked our state, town, zip and such because all of the students live in the same town.  All I really care about is name, specific street address and score.  (You can move the order of the codes manually if info order matters to you).

pasted image 0 (5)And here is the map.

With eight data points, it is not very exciting.  I chose small pins because I usually map a while grade level.  But, I click on that one pin and Nancy’s relevant data comes up.

This data is not very telling (it is random demo data), but the maps for my class are a bit more telling.

Again, you can read about what you might do with this here:  Read in this post.