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.

Maker Space, Lending Libraries and 21st Century Learning

Maker space has gone from mainstream nerd to mainstream.  Along with Project Based Learning (the other PBL), educators have embraced the maker space movement as a 21st century education must.  As with any movement, the push often comes before the need is evident.  Some good, earnest work at our school lead to naught as our librarian and tech educator failed to start a fire.  Below is my suggestion for expanding our library’s function and supporting PBL and the maker movement.</

As we are contemplating and experimenting with Project Based Learning we are running into a few issues.  My thoughts turned to the Maker Space.  From another conversation I had with our librarian, it sounds like it is not being utilized.  Based on our classroom needs I wanted to offer a few thoughts that might help teachers in their efforts to do projects and encourage "hands on" and exploitative learning–the ideal of the maker space movement.

Lending Library of Tools: As libraries move to expand beyond books, I have read about ones that lend tools.  It makes sense–I use my post hole digger about once every five years; if the library had it I’d still have access and so would the entire community.  I have a box of 10 saws and a few hammers in my classroom from our long-gone tech program, but no one in the school knows it, thinks about it, and if they did borrow it I’d worry they’d never come back.  Still, I only use them a few times a year–they are an underutilized resource.  Put a bar code on them and now they are public, in the database and tracked.

There are so many tools classroom teachers search for; glue guns, longer tape measures and Phillips screwdrivers, are always in demand.  These are not tools most classrooms would need on a regular basis, but when you need them, you need them.  Put them together and bar code them for inventory control.

Depot of Free Consumables: When a teacher needs cardboard, shoe boxes, yogurt containers and such the all-staff email goes out.  In that one week window a whole classroom project hinges on our ability to consume and remember to bring in a certain number of 2 liter bottles.  While teachers could do a better job of planning, when we respond to student needs we often need to turn on a dime–suddenly, shoebox dioramas make sense.  There are constant (cardboard) and perennial (yogurt cups) needs that could be slowly collected–I toss a single good shoebox out about every month.  A single shelving unit of dedicated tubs for such things would go far.

Store of Emergency Cost Consumables: I am often hit up for duct tape.  I have a lot, because when you need it you need it, plus the borrowing.  There is nothing more frustrating than needing supplies.  A project grinds to a half for want of a glue stick.  Or blue paint.  As a community, we are good about sharing.  And our Art teacher is generous, although her budget is limited and each request costs her interruptions of class and time in general.  Still, a basic stockroom of project based supplies would help support the maker ideal.  This would not be a “borrow”, but a store.  And users would be charged in their supplies account for the replacement.  It would be a lifeline, though.

I know the first stumbling block for this re-imagining is space.  Then money.  Management, too.  If the idea is worth pursuing, though, we can work those details out.  If we are serious about project based learning and letting the students lead their own learning, our school is going to have to be ready to provide the resources necessary–and we are going to be hard pressed in our individual classrooms to provide for every scenario.