Data Visualization Exercise

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

1. Open your 5 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 2007

% Minority 2012

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”

Demographic Change 2007-2012 in Iowa City High Schools

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

Racial Change in Iowa City High Schools from 2007 to 2012

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://epress.trincoll.edu/dataviz/chapter/create-point-map-gft/ 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.  Now we need to figure out how to embed it on a wordpress page.  I am still working on mastering this – tune in next week!

[https://www.google.com/fusiontables/embedviz?q=select+col4+from+1F8Cx4zRiHG6xOb5VX7nS5WoZGu0cIp_g13AcaQz_&viz=MAP&h=false&lat=41.72055705444701&lng=-91.64341881689455&t=1&z=10&l=col4&y=2&tmplt=2&hml=GEOCODABLE]

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s