Chris Essig

Walkthroughs, tips and tricks from a data journalist in eastern Iowa

Archive for November 2011

Graph: Hispanic population growing in Iowa

leave a comment »

This graph I put together with a weekend story breaks down Iowa’s Hispanic population by county. It was based almost entirely off a JavaScript chart walkthrough put together by Michele Minkoff, Interactive Producer for the Associated Press. So check it out now because it’s FANTASTICO!

I did make a couple of minor tweaks, which may be helpful for others so I will outline them here.

My initial, final product looked like this. Go ahead and roll over the bars like the blue one, for instance, which represents the amount of Hispanic people in Iowa who said they were also white. Notice how the bars in 2000 and 2010 are both the same length, even though the value of the 2000 bar (38,000) is almost half of the 2010 bar (80,000).

The data was retrieved from I just selected “Iowa,” then “County,” then “Hispanic or Latino origin by race.” You can then download a CSV of the data and chop it up as you see fit. Also click an option after “Browse data for…” on the page for a great breakdown of what each of the headers in the CSV files means. Here’s Iowa’s page, for instance.

The Javascript file that makes these graphs calls a JSON file containing information retrieved from My JSON file initially looked like this:

  1. headers = [“White”,”Black / African American”,”American Indian / Alaska Native”,”Asian”,”Native Hawaiian / Other Pacific Islander”,”Some other race”,”Two or more races”]
  2.  allCountyData = [
  3. [“Iowa”,38296,1109,1034,290,121,35317,6306,80438,2242,2503,497,206,54000,11658],
  4. (Enter data for every county in Iowa here)

The headers represent the different races of Hispanic people. The line for “Iowa” represents the amount of people per particular race in first 2000 and then 2010. 38296 = Number of Hispanic people in 2000 who said they were White, 1109 said they were black, etc. 80438 = Number of Hispanic people in 2010 who said they were White, 2242 said they were black, etc.

Here’s my tweak: Instead of having both 2000 and 2010 data on the same line, I broke this out onto two separate lines. So my new JSON file includes this:

  1. allCountyData = [
  2.   [“Iowa”,38296,1109,1034,290,121,35317,6306],
  3. (Enter data for every county in Iowa here)

And this:

  1. allCountyData2 = [
  2.   [“Iowa”,80438,2242,2503,497,206,54000,11658],
  3. (Enter data for every county in Iowa here)

From there, I needed to edit the Javascript file to call the data contained in allCountyData2. Here’s my Javascript file. Go ahead and click on it. I know you want to. And scroll down to “function changeGraph(stateText)” and notice how it is calling both selectedData, which includes the value for everything in allCountyData (in the JSON file), AND selectedData2, which contains the value for everything in allCountyData2 (in the JSON file). The “drawVisualization();” inside the function also contains two arguments now: “selectedData, selectedData2.”

As a result, the “function drawVisualization()” at the very top of the Javascript file must also contain two arguments: “newData, newData2.” Initially, my drawVisualization function (here’s my first Javascript file) contained just one argument because it was only calling newData, which contains data from allCountyData. At the very end of the function, it broke newData (allCountyData) into two because remember, allCountyData contained data for both 2000 and 2010:

  1. //the first half of our data is for 2000, so we fill the row in with appropriate numbers
  2. //we start at 1 to leave out the years, remember data structured this way starts at 0
  3. for (var i = 1; i <=(newData.length-1)/2; ++i) {
  4. data.setValue(0, i, newData[i]);
  5. data.setFormattedValue(0,i, numberFormat(newData[i]));
  6. }
  7. //now, the second half of the data is for 2010, so we’ll fill that in
  8. for (var i = 1; i<=(newData.length-1)/2; ++i) {
  9. data.setValue(1, i, newData[i]);
  10. data.setFormattedValue(1,i, numberFormat(newData[i+(newData.length-1)/2])); 
  11. }

I needed to change that last “for” function because we no longer wanted newData to be broken in half. Instead, we want to call that second argument (newData2) in the drawVisualization function because it calls the data from allCountyData2:

  1. //the first half of our data is for 2000, so we fill the row in with appropriate numbers
  2. //we start at 1 to leave out the years, remember data structured this way starts at 0
  3. for (var i = 1; i <=(newData.length-1); ++i) {
  4. data.setValue(0, i, newData[i]);
  5. data.setFormattedValue(0, i, numberFormat(newData[i]));
  6. }
  7. //now, the second half of the data is for 2010, so we’ll fill that in
  8. for (var i = 1; i<=(newData2.length-1); ++i) {
  9. data.setValue(1, i, newData2[i]);
  10. data.setFormattedValue(1, i, numberFormat(newData2[i])); 
  11. }

If I’m not mistaken, that’s all I did. If you are interested, my HTML file is here and my CSS file is here. Again, Michele Minkoff deserves all the credit in the world for putting together her great walkthrough. So get over there and check it out!

Written by csessig

November 28, 2011 at 11:29 pm

Map Mania

with one comment

Here are a few maps I’ve worked on in the last couple of months:

1. City breakdown of property taxes in Iowa

– This map (shown above) includes property tax rates for every city in Iowa (900+) dating back to 2010 and color-codes them based on their rate. I won’t go into too much detail on how I went about doing it; instead, I’ll refer you to this great Poynter tutorial on how to make a heat map using Google Fusion Tables (which is what the above map is based off of). When you’re done with that, check out their more recent post on mapping data by county, cities, etc. using what is known as shapefiles (Don’t worry. I didn’t know what they were either until I read the post).

For this map, I first found tax rates for every city in Iowa on the Iowa Department of Management’s website. These rates were contained in several spreadsheets (City Tax Rates FY12, City Consolidated Tax Rates FY12, FY 11, etc.).For each spreadsheet I used (six in all: three for city tax rates dating back to 2010 and three for consolidated tax rates dating back to 2010), I basically went in and chopped out all the information in the spreadsheets I didn’t need (Example spreadsheet), which would up being most of it. I then merged the six spreadsheets by city name using Google Fusion Tables.

I then found the shapefile that maps every city in Iowa on the Census site, which contained the geographical data that Google uses when it maps out the cities. I then merged that spreadsheet with the one containing all the tax rates and pulled it into Google Fusion Tables.

Google did the heavy lifting so to speak and mapped out all the cities on the map. I then told FT to map the cities based on their tax rate. The final product is cities with higher tax rates are red; cities with lower tax rates are yellow. The legend is HTML/CSS, which I copied heavily off of the Chicago Tribune News App and their wonderful map walkthrough. When you get the hang of Fusion Tables, check it out. Their maps are much prettier than mine.

The property tax map is on my (barren) Github account. Please copy anything you find helpful.

2. 2008 Flood Buyouts

Compared to the above map, the flood buyout map was a piece of cake to make. We got a spreadsheet from the city of Cedar Falls that listed every home that was offered a buyout from the federal government and their address. Besides mapping shapefiles (like above), Google can also map simple addresses (obviously). So Google can map a spreadsheet of addresses as well.

3. Road to the Dome: A look at the prep teams in the semifinals

I think high school football is popular everywhere. The Cedar Valley is certainly no exception. In Iowa, we have six football classes based on size of the schools. Each year, the semifinals and final rounds of each class are played at the UNI Dome in Cedar Falls. This package breakdowns each of the 24 teams in the semifinals and maps them based on their school address. Readers can then click on each pointer on the map and read more about the team.

From a technical standpoint, this is the first map I’ve put together where a info box DOESN”T open up when a reader clicks on a point on the map. Instead, the information on the teams opens in a table format to the right of the map. This uses Google’s API; I borrowed heavily off of this map + chart example provided by Google. The main difference being is I called a table visualization instead of a chart visualization in the example.

The table below the map (the one where you can select a class and see the team’s playoff schedule) was borrowed heavily off this Google table example.

Here’s the table I ended up with on Google Fusion Tables. If you’re looking for the map source, go to this webpage and click view source. This is where the map is housed.

My Google-powered Javascript is pasted below for anyone who is curious:

google.load('visualization', '1', {'packages':['table']});

function initialize() { 
 var map = new google.maps.Map(document.getElementById('map_canvas'), {
 center: new google.maps.LatLng(42.3, -453.4),
 zoom: 7,
 minZoom: 5,
 maxZoom: 11,
 mapTypeControl: false,
 streetViewControl: false,
 mapTypeId: google.maps.MapTypeId.TERRAIN

 var layer = new google.maps.FusionTablesLayer({
 query: {
 select: 'mappable_address',
 from: 2102557
 suppressInfoWindows: true

 // Add a listener to the layer that constructs a chart from
 // the data returned on click
 google.maps.event.addListener(layer, 'click', function(e) {

 var data = new google.visualization.DataTable();
 data.addColumn('string', 'About:');
 data.addColumn('string', e.row['School'].value);
 ['Class', e.row['Class'].value],
 ['Mascot', e.row['Mascot'].value],
 ['Address', e.row['Address'].value],
 ['League', e.row['League'].value],
 ['League Record', e.row['League Record'].value],
 ['Regular Season Record', e.row['Season Record'].value],
 ['Playoffs - Round 1', e.row['Round 1'].value],
 ['Playoffs - Round 2', e.row['Round 2'].value],
 ['Quarterfinals', e.row['Q-Finals'].value],
 ['Semifinals', e.row['Semifinals'].value]

 var chart = new google.visualization.Table(document.getElementById('chart'));
 var options = {
 'title': e.row['School'].value + ' ',
 chart.draw(data, options);

function changeData(team) {
 var whereClause = "";
 if(team) {
 whereClause = " WHERE 'Class' = '" + team + "'";
 var queryText = encodeURIComponent("SELECT 'School', 'Round 1', 'Round 2', 'Q-Finals', 'Semifinals' FROM 2102557" + whereClause);
 var query = new google.visualization.Query('' + queryText);


function getData(response) {
 var table = new google.visualization.Table(document.getElementById('visualization'));

Written by csessig

November 10, 2011 at 10:20 pm