Schools Point Mapping Tutorial

In this exercise, we are going to take our data on demographic change from the suburban schools, and share it in two ways.  First as an image on excel graphs and second, on a map via interactive googlemaps.

1. Open your 25 year demographic change file we used for an earlier exercise in Excel.

2. Create a new file and copy & paste the following columns. When you are pasting numbers, right click to Paste Special and click on Values – otherwise, your numbers may get modified.

School name

% Minority 1993

% Minority 1999

% Minority 2003

% Minority 2009

% Minority 2014

3. Reformat both columns to display as a percentage.

4. Using charts, create a Clustered column chart.  Save it as a picture, and upload it, via add media to a blog entry on our wordpress site.  Name it “Your names Data Exercise”

Here is an example from some work I did on high schools in my home town, Iowa City, Iowa.

5. Then create a line chart.  You may need to select under “Chart” “Switch Plot.”  This should give you a line chart of your schools that looks something like this:

IowaCitychangeover5yrs

Congratulations!  You’ve created two types of potential data for your research paper.  Next we’re going to create an interactive google map.

INSTRUCTIONS FOR CREATING AN INTERACTIVE GOOGLE MAP:

2. Go back to your original excel file.  Create a new column to the right of Location City.  This is going to be your Geocoding column which we will call “Location.”

3. You want to create an address that combines Street, City and State address together.  Here is what my Excel formula looks like to combine them:

=C3&”, ” &D3&”, ” &B3

Modify as necessary depending on the column name.  Here is what one of my completed school locations cells looks like:

1528 MALL DR, IOWA CITY, Iowa

4. Save this location column to your new excel file you created above.  Remember to select “Paste Special” and “Values.”

5. Follow Jack Dougherty and contributors Datavizualization tutorial for creating an interactive google map: http://www.datavizbook.org/content/map/point-gft/index.html to set up google fuzion tables.

6. Upload your excel document as a google fuzion table.

7. Check that your new location column is not blank.

8. Give your document a proper title and description.  I’m calling mine: “Demographic Change in Iowa City High Schools, 2007-2012” and description: “NCES Education Statistics on Demographic change in Iowa City from 2007-2012.”

9. When you first get your fuzion table open, it may have several columns highlighted yellow.  That means it isn’t clear which one is your geolocation column.

10. Go back to following Jack’s instructions for creating the map.

11. Click on Info Window and decide which information you would like to show by school.

Congratulations!  You have created a google map.  The wordpress.com website doesn’t allow us to host a picture of it, but we can link create a text link to it here.

Advertisements