Skip to content

Instantly share code, notes, and snippets.

@michellemho

michellemho/election.md

Last active November 18, 2016 02:19
Show Gist options
  • Save michellemho/5e9d7c4dddda4764791c7cd0df9ad162 to your computer and use it in GitHub Desktop.
Save michellemho/5e9d7c4dddda4764791c7cd0df9ad162 to your computer and use it in GitHub Desktop.
Download ZIP
Who Voted for Who?
Raw
election.md

1. Go to each public dataset, and click on the download button to get file:

Download a Shapefile for US Counties

Alternatively, you can just use the links above to connect the dataset in the next step as you add each layer.

2. Add each file into a new CARTO map as a new layer

  • The only layer with geometries will be the US Counties layer
  • Rename the layers or the underlying datasets. Because we reference the table names in SQL and CartoCSS code below, it's probably best to rename the underlying datasets exactly as I have here.
  • You can do this by clicking into each layer (the little colored box with letters "A", "B", or "C") and opening the connected dataset.

image

image

  • Notice that the FIPS code (geoid) for the county shapes are stored as strings. Change to numeric to match the other datasets.

image

3. Calculate county winner

  • In this dataset, the "winner" column indicates the overall state winner. We need to do some calculations on a new column in order to find the winner for each county.
  • In the elections_result table (in data view with SQL toggled on), add a new column (string type) called "county_winner". Run the following SQL script, which defines a new function called "max_of" and updates the new column.
create or replace function max_of(vals numeric[], names text[]) returns text as $$
DECLARE
max_val numeric;
BEGIN
 max_val = (select  max(a) from unnest(vals) a);
 for i in 0..array_length(names,1) loop 
  if vals[i] = max_val then
    return names[i];
  end if;
 end loop;
END
$$
LANGUAGE plpgsql;

UPDATE election_results
SET county_winner = max_of(array[dem::numeric, gop::numeric], array['dem', 'gop'])

4. Calculate margins of victory

  • We now have each county's winner, but we would also like to have the margin of victory.
  • Add three new numeric columns: "dem_and_gop", "pct_dem", and "pct_gop". For simplicity's sake, we're going to only calculate margins of victory and winner for these two parties and ignore the other parties.
  • Run the following SQL script. Notice that we add "nullif" to handle situations where there were zero votes recorded for both dem and gop parties.
UPDATE election_results
SET dem_and_gop = dem+gop;
UPDATE election_results
SET pct_dem = dem/nullif(dem_and_gop,0);
UPDATE election_results
SET pct_gop = gop/nullif(dem_and_gop,0);

5. Join the county shapes layer with the election results

County Shapes Layer > Add Analysis > Join columns from 2nd layer

  • Select the election results layer as the second layer, and use foreign keys "geoid" and "fips"
  • Use geometries from the county shapes layer, and keep the name column from county shapes and all new columns from election results we calculated just now, as well as "gop" and "dem" to get raw vote counts.
  • Style by equal interval values for either pct_dem or pct_gop to visualize margin of victory. Select a custom diverging color palette with colors that make sense.

image

  • Alternatively, you could style with CartoCSS. Toggle on CartoCSS and enter the following script which ramps equal intervals on margin of victory depending on the county winner. If there is no winner, the color will be gray. Polygon-gamma helps smooth gaps between shapes and polygon-opacity controls the transparency. Feel free to customize as you wish! Be warned that after applying CartoCSS, you can't switch back to styling with the Builder Style wizard without clearing changes. Also, CARTO legends are finicky at the moment and won't really work right now.
#layer {

  polygon-gamma: 0.4;
  polygon-opacity: 0.9;

  [pct_dem > .5] {
  polygon-fill: ramp([pct_dem], (#CCFDFE, #6193C7, #9CC0E3, #006AAB), equal);
  }
  [pct_gop > .5] {
   polygon-fill: ramp([pct_gop], (#FBD0D0, #E99D99, #CF615D, #B02029), equal);
  }

  polygon-fill: #d3d3d3;
}

6. Adding in education and demographic data

  • In order to add demographic context to this election, we have prepared a dataset from the CARTO Data Observatory that contains educational attainment for US counties. We have chosen to focus on white male and white female educational attainment for this exercise. You can grab more census variables from another data repository like the Data Observatory, Social Explorer (http://www.socialexplorer.com/), or directly from the Census if you like.

  • We want to add several widgets to our map that show white male educational attainment. However, these are raw numbers currently. Let's divide by the population to get percentages. In this exercise, I chose to get percentages for males and ran the script below. Before, we added columns using the CARTO dataview interface. This is an alternative method for adding columns and setting the data type using SQL.

ALTER TABLE education_by_race_county 
ADD COLUMN male_less_than_HS_pct numeric,
ADD COLUMN male_HS_pct numeric,
ADD COLUMN male_some_college_pct numeric,
ADD COLUMN male_bachelors_pct numeric;


UPDATE education_by_race_county
SET male_less_than_HS_pct = 100 * total_male_less_than_high_school_diploma/(total_male);
UPDATE education_by_race_county
SET male_HS_pct = 100 * total_male_high_school_graduate_includes_equivalency/(total_male);
UPDATE education_by_race_county
SET male_some_college_pct = 100 * total_male_some_college_or_associates_degree/(total_male);
UPDATE education_by_race_county
SET male_bachelors_pct = 100 * total_male_bachelors_degree_or_higher/(total_male);
  • On the election_results layer (which should now have polygons), join the education data layer just like we did in step 5. Keep all columns from both layers.

7. Add widgets to make it interactive

  • Let's choose to add histogram widgets to explore and query the map by educational attainment
  • From the Builder pane that displays all your layers: Widgets > Add > Histogram; and select all the male education percentage variables we calculated in the step above
  • Four new widget boxes should appear on the right side of your map. Rename the widgets to easier-to-read names by using the drop-down menu for each widget.
  • Let's also add a total count of votes to both parties. Widgets > Add > Formula; and select the "dem" and "gop" variables. Once these are widgets are added, edit them to be "sum" operation and rename accordingly.

image

8. Add legends, map title or description, and publish!

  • Toggle on legends, scroll wheel zoom, and other map components in the CARTO blue bar
  • From the title dropdown in the Builder Pane, select "Edit metadata" to give your map a new name, add a description, and add tags.
  • In the Builder pane that displays all your layers: hit "Share" or "Public". Select "Publish" or "Update" and your new map will be ready to share! Anyone can now view your map with the link.

image

Your final map should now look something like this:

https://michellemho.carto.com/builder/2585e4f8-ab7f-11e6-be52-0ef7f98ade21/embed

There's a lot more to learn (styling, analysis, data, exporting, syncing...) with CARTO! Check out www.carto.com/learn for tutorials.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment