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.

Advertisements

Why Mapping Data Matters

I am going to post on how to use Google Tables to map student data, but I wanted to explain why you might want to do it separately.  You, I am sure, can find a dozen uses for mapping data.  Here are a few of mine:

Knowing the Community: I do not live where I teach, and rarely leave the building (I go a few hundred meters to the market for lunch, and drive the main street on my way elsewhere).  Mapping out my students helps me understand where people live.  It seems like a small thing, but clicking about on clusters and rural areas helps me understand my community–the trailer parks, housing developments, rural farms and deep woods.  I can see divisions and lifestyles just from geography, as it blends with what they talk about in class.

Resources: It also helps me understand who has resources and who does not.  Some of our most needy students do not have easy access to the library, stores (for supplies) and rely on the late bus if they want to participate in anything after school (and I can see who will be on it for an hour because of how far away they live).  From this I know for whom basketball is a sacrifice, and who can stay after to finish up a project before zipping across the street to their home.

Clusters: It is common for adults to make assumptions about where people live.  It’s a social class bias.  As many teachers are middle class, from middle class backgrounds, they just don’t know.  The local trailer parks get a lot of abuse based on those biases.

In The Tipping Point, Malcolm Gladwell makes hay about studies showing community matters more than family in shaping children and their values (the more intellectual among you probably read the actual studies).  That doesn’t stop us from blaming the family for student results, but it also calls into question where students live–is the neighborhood a problem, and can the school counter-act that influence?

This is where my interest in maps started.  I cannot help but think this stems from my own biases, and I’m unsure how helpful this line of questioning is.

Focused Interventions: Ten years ago our old principal wanted to reach out to families and make the school a bigger part of the community.  We wound up having an ice cream social the day before school started.  cache_240_240_0_100_100_16777215_new-framework-cover-golden-lampGreat, except that Ruby Payne’s A Framework for Understanding Poverty makes clear how adults who were unsuccessful in school are reluctant to come for such events (or parent conferences, etc.).  As non-threatening as it is, our ice cream social tends to be mostly middle and upper class parents of successful students.

In reading about the Civil Rights movement, I noticed how they organized where people lived.  They didn’t ask people to come, but went to them.  They used churches, meeting halls and the living rooms of trusted members of THAT community.  I wonder, for example, if tutoring and summer school might be off campus–in the heart of these low scoring clusters?  Perhaps the administrator might hold a parent group meeting somewhere other than the school?  Do those far flung places have churches or halls in which we can hold classes?  Perhaps rent an apartment or trailer for a summer month?  When you see that half of the students in need live with a kilometer from each other, but ten kilometers from the school, you have to wonder if the mountain needs to go to Mohammad.

Note: Know Your Students: Of course, knowing more about your students adds to this.  For example, one of my students lives with a grandmother who does not drive in a rural home–she relies on the bus a lot.  To keep her after school is a big thing.  But to keep her in for recess denies her the one chance she has to be social and make connections.  I try and find alternative times to support her.  This is very different from the girl who lives in the development with five other classmates, a short walk from school.  Awareness matters.  By combining this map with my personal knowledge I craft my responses to their needs.

Check out my post on mapping data here.