Campaign Contributions per State

The purpose of this tutorial is to help you visualize and analyze individual campaign contributions per State to the 2012 elections.
In order to do this, we have collected the following information:

State Boundaries: Fusion Tables file with a geometry column containing the boundaries for each state. Source: Google Fusion Tables.
Educational Attainment in 2009: Percentage of population 25 years old and over with Bachelor Degree or more per State in the year 2009. Source
States flags and other basic data: Online images of the State flags, Population estimate for July 1, 2012, Census population: April 1, 2010, Census population: April 1, 2000, Seats in U.S. House: 2013–2023, Presidential Electors 2012–2020, 2010 Census Pop. per House seat, 2000 Census Pop. per House seat, 2000 Census Pop. per Pres. Elector, Percent of total U.S. pop., 2010. Source Individual Campaign Donations - Federal Contributions per State (2010-2012)
This dataset has been initially geocoded by the address of the contributor. The date range that we have selected goes from 10/2/2010 (mid-term elections) to 10/6/2012 (presidential elections).Then we have aggregated the contributions by State, and calculated the total amount of donations as well as the total number of donations per State and by political party of the recipient (Independent, Democrat, Republican, All parties)Source: Influence Explorer
Basic 2011 Census Data: Total Population, Population Density (per Square Mile), Median Age, Family Households, Average Family Size, Median Household Income, Average Household Income. Source: Stanford Geospatial Center.

Explore the Data

Spend a couple of minutes getting familiar with each dataset and think about ways that you would like to visualize the data. Some of the datasets open up directly in Fusion Tables. Some are in Google Docs. Make sure you are logged in with your gmail account to be able to download and create your own datasets. Check the source to see the original table format.

Open the State flags and other basic data table. This is a Google Doc where some of the numeric fields are displayed as text. In order to fix this dataset, we will use Google Refine. Download a copy of this table by clicking on File - Download As - Comma Separated Values (.csv) and save the file on your computer.

Google Refine

Google Refine is a free, downloadable tool that allows you to clean, discover, and organize messy data and link it to other databases. Please download the program directly to your computer following this link:

Create a new project by choosing the following: Get data from This Computer. Click on the Browse button and navigate to the location where you saved the csv file. Click Next. Your screen should look something like this:

Separate the columns by commas:

Uncheck the button that stores blank rows:
Click the Create Project button:            

Now you are ready to manipulate the table in Google Refine. Take a look at the Seats in the US House column in the middle of the table.

We want to have the numeric values for this column in the right format. In order to do this, click on the down arrow next to the column name (Seats in U.S. House) and select the following: Edit cells - Transform:

In the Expression box, type the following:  value.replace("!","")

Click OK. In the same column, click again on the down arrow and select Edit cells - Transform - Common Transforms - To Number. Click OK.

Remember you can undo any step of the process by clicking the Undo/Redo button. Repeat this process for similar columns. Look at the State or Territory column. We want to subtract the first two characters. Click on the drop-down arrow - Edit cells - Transform and type the following expression: value.substring(2). This removes the first two characters of the column.

Click OK. Click on Export - Comma-separated value and save your new table. We will bring this into Fusion Tables for data visualization.

Google Fusion Tables

Google Fusion Tables (part of Google Drive) is a free service for data management, integration and collaboration. You can easily upload data sets from CSV, KML and spreadsheets, and visualize the data using a variety of tools. You can also merge data from multiple tables and conduct detailed discussions about the data (on rows, columns and even cells). You can easily visualize large data sets on Google Maps and embed visualizations on other web pages.
In this part of the tutorial, we will bring the table modified in Google Refine and merge it with other tables containing State information.

Go to Fusion Tables. Click on Create a new table. Press the Choose File button and upload the table you have previously created. If you cannot find it, you can download a copy here
Follow the defaults, your table should look like this:

Click Next. Fill in the text boxes regarding name, attribute data, attribution page link and description. Metadata is very crucial information that shouldn't be underestimated. Click Finish.
Explore the different fields in your new table. Notice the State Flag column. Instead of the URL, it is automatically displayed as an image. This is one of the many advantages of using Fusion Tables.

Editing Data in Fusion Tables

Click on File - Change Columns. Here you can delete columns and change the format and order of existing columns. Change the name of some of the columns as you consider appropriate. Change the State or Territory column to "State" and move it to the front of the table clicking on the up arrow.

 To edit a row of records, hover your mouse over the record you want to edit until a small window with three symbols appears on top. Choose the pencil graphic as indicated below.
Let's say we want to only see the states most populated in 2012. Click on the Filter button and select Population estimate for July 1, 2012.

Fusion Tables already displays the minimum and maximum values for that column. Type 25,000,000 as the minimum value and 40,000,000 as the maximum value. Click the Find button to display your results.

Remove your filter by clicking  the x button next to the filter query.

Cards View

You can create a "card" view of each record in your table. Click on the Cards 1 tab.

Here you can have a preview of a cards format of your table that you can embed on any webpage of your choice. To customize this view, click on the down arrow right next to Cards 1 and select Change card layout.

In the Automatic view, uncheck the two Rank columns as shown below. Click Save.You will see that those columns are removed from the card view.

Open the Change Card Layout window again, and click on the Custom tab. Here you can customize the text color, size, order, etc of the data displayed for each record using basic Html code. 
Experiment changing the columns names and removing the bold brackets (<b></b> from some columns). See the results every time you make a change to make yourself familiar.
Finally, in the Custom tab, copy and paste the following

<div class="googft-info-window"

    style="font-family: sans-serif; width: 330px; height: 300px; overflow-y:auto">

   <div style="text-align:center"><img src="{State Flag}" style="width: 180px;" /></div>

    <h2 class="color: brown"><P ALIGN=Center>{State}</h2>

<p>This US State has <b><em>{Seats in U.S. House, 2013-2023}</em></b> seats in the US House of Representatives during the time period of 2013 to 2023.</p>

  <p>It has a total estimated population of <b>{Population estimate forJuly 1, 2012}</b> in July 1, 2012.</p>


If you get an error message when you copy this text in your custom tab, substitute the column names (in brackets) for the ones you have in your own table. Change the card layout to 4 columns. Your results should look something like this:

You can embed this card view in any website of your choice. Simply click the down arrow in Cards 1 tab, and select Publish. You will get a message like this:

In order to publish, your table cannot be private. To make it public, click on Change visibility, select Change and click  the option Anybody with the link. Select the Publish option again and copy and paste the HTML code to embed in a website. Make sure your website is in HTML mode.

Basic Map View

You can create a map of your records if one of your columns has geographic information. Click on the Map of State tab.

Fusion Tables automatically detects which columns have geographic information (highlighted in yellow in the Rows view). When you click Map of State, Fusion Tables starts geolocating your data based on the geographic information of the most left column with the yellow highlighted fields. In this case, State.

Fusion Tables finds the location associated with each State, its capital. However, when we visualize states, we want to use polygons, instead of points, to represent each State. To do this, we will merge this table with another table that has States boundaries.

Merging Tables

State Boundaries: is a Fusion Table with a geometry column containing the boundaries for each state. Open this file in Fusion Tables by clicking on the link above. 

Notice the columns and compare them with the columns you have in your previous table (click on the Rows tab). Do you find a common column on both tables with a unique identifier for each row (in this case, State?). You guessed it right, it is the State name ("name" in the boundaries table and "State" in the flags table).
Fusion Tables will merge both of them based on this common field. In your flags table, click on File, Merge and select State_Boundaries as the table you want to merge to.
Click Next. Make sure your following screen looks like this:

Click Next. On the next screen, select all the columns you want to merge (leave the default). Click Merge. Click View Table.

Follow the same procedure and merge the new table with the following tables:
A copy of the final fusion table can be found here:
Use this table for the following exercises in this tutorial.

Customizing the Map View

Using the Master Table from the exercise before, go to the Map of geometry tab. You will notice immediately that now Fusion Tables represents each State as a polygon, and not as a point.
The default view is with all States (rows) having the same symbol. To change this, click on the small down arrow next to Map of geometry and choose Change map styles.

In the Change map styles Window, choose Polygons, and the Buckets option. Follow the settings below and choose the column of your choice.
Change the info window for your map, following the steps before. Copy and paste the same text you used for the cards view in the custom window. 

<div class="googft-info-window"
    style="font-family: sans-serif; width: 330px; height: 300px; overflow-y:auto">
   <div style="text-align:center"><img src="{State Flag}" style="width: 180px;" /></div>
    <h2 class="color: brown"><P ALIGN=Center>{name}</h2>
<p>This US State has <b><em>{Seats in U.S. House, 2013-2023}</em></b> seats in the US House of Representatives during the time period of 2013 to 2023.</p>
  <p>It has a total estimated population of <b>{Population estimate forJuly 1, 2012}</b> in July 1, 2012.</p>

Publish your map (down arrow - Publish) and embed it in your website. Congratulations! Your map should look something like this:

Interactive Charts

Fusion Tables offers a variety of interactive charts that will extend your analysis and enhance the communication of your results. To add a chart, click on the box with a + sign. Select Add chart.

Continuous Variable Chart

The default chart allows you to plot two different variables as points in a scatter chart. In this case, we want to analyze the relationship between the total number of contributors per state and the average educational attainment.
For the Y axis, choose Number_Contributors_perState_all. For the X Axis, choose % Bachelor Degree. If you want to, you can customize your chart by clicking the Change appearance button. Click Done and Publish your chart. It should look something like this:

Categorical Chart

Start a new chart by clicking on the + sign. Rename it Categorical Chart. Select column chart in the chart category. We want to analyze the amount of contributors to the Republican and Democrat party per State, but only for the most populous states.
First, we will filter the data. Under filter columns, choose Population estimate for July 1, 2012. Insert 10,000,000 as the minimum value and 40,000,000 as the maximum. Click Find. Only 7 states are displayed now.
Under Category, choose name.
Values: select Total_Number_Contrib_Dem and Total_Number_Contributor_Rep. Sort by name. Change the appearance of your chart so republicans are red and democrats are blue. Publish your map and embed it in your website. Below is an example of how it should look like.

Pie Chart

The purpose of this chart is to see the distribution of contributors per State, looking at the top State contributors and see how this distribution changes for democrats and for republicans.
Let's start with the republicans. First, we will filter the data. Select the Total_Amount_Contributed_perState_All and choose 100,000,000 as the minimum value and 700,000,000 as the maximum value. This filters the data to just the top 7 States.
In the Configure pie chart window, under Category, select name. Value: Total_Number_Contributors_Rep_per_State. Sort by name. The chart should look like this: 

Repeat the same process for the democrats. Compare each chart.

Network Graph

Our goal is to visualize the relationship between States and the number of seats in the house.
Add a new chart to your tabs. Choose Network graph in the graphics options tree and follow the settings below:

Your graph should look something like this:

Google Trends & Google Correlate

There will be a live demo to showcase these two nifty tools. You can experiment yourself at:

SelectionFile type iconFile nameDescriptionSizeRevisionTimeUser

View Download
  239k v. 1 Jan 29, 2013, 6:35 PM Patricia Carbajales

View Download
  10k v. 1 Jan 29, 2013, 12:17 PM Patricia Carbajales