Skip to content

Instantly share code, notes, and snippets.

@fitnr

fitnr/2012_results.mk

Created November 10, 2016 22:14
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save fitnr/b7072147a722050650ab082514ff1fda to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save fitnr/b7072147a722050650ab082514ff1fda to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. fitnr created this gist Nov 10, 2016.
    54 changes: 54 additions & 0 deletions 2012_results.mk
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,54 @@
    states = fl la nc ok va \
    al ga ma nd or vt \
    ar hi md ne pa wa \
    az ia me nh ri wi \
    ca id mi nj sc wv \
    co il mn nm sd wy \
    ct in mo nv tn \
    dc ks ms ny tx \
    de ky mt oh ut

    results = CREATE TABLE "results" ( \
    fips VARCHAR(5), \
    county VARCHAR(20) NOT NULL, \
    candidate VARCHAR(79) NOT NULL, \
    votes INTEGER NOT NULL )

    results_2012.csv: results_2012.db
    sqlite3 -csv -header $< "select a.fips GEOID, a.county county, a.votes Obama, b.votes Romney, c.votes total \
    FROM countytwoparty a \
    LEFT JOIN countytwoparty b ON (a.fips = b.fips) \
    LEFT JOIN totals c ON (a.fips = c.fips) \
    WHERE a.candidate = 'Obama' AND b.candidate = 'Romney' AND a.fips != ''" | \
    iconv -f WINDOWS-1252 -t UTF8 > $@

    results_2012.db: results_2012_raw.csv election-2012-results/data/ak_precincts.csv
    @rm -f $@
    sqlite3 $@ '$(results)'
    sqlite3 -csv -header $@ '.import $< results'
    sqlite3 -csv -header $@ '.import $(filter %ak_precincts.csv,$^) aktmp'

    sqlite3 $@ "CREATE TABLE twoparty AS SELECT fips, sum(votes) votes FROM results \
    WHERE candidate LIKE '%Romney%' OR candidate LIKE '%ROMNEY%' \
    OR candidate LIKE '%Obama%' OR candidate LIKE '%OBAMA%' \
    GROUP BY fips; \
    CREATE TABLE totals AS SELECT fips, sum(votes) votes FROM results \
    GROUP BY fips; \
    INSERT INTO totals SELECT '02' fips, SUM(votes) FROM aktmp; \
    CREATE TABLE countytwoparty AS \
    SELECT r.fips fips, county, 'Obama' candidate, SUM(r.votes) votes \
    FROM results r LEFT JOIN twoparty A ON (A.fips=r.fips) \
    WHERE r.candidate LIKE '%Obama%' OR r.candidate LIKE '%OBAMA%' \
    GROUP BY r.fips; \
    INSERT INTO countytwoparty \
    SELECT r.fips fips, county, 'Romney' candidate, SUM(r.votes) votes \
    FROM results r LEFT JOIN twoparty A ON (A.fips=r.fips) \
    WHERE r.candidate LIKE '%Romney%' or r.candidate LIKE '%ROMNEY%' \
    GROUP BY r.fips; \
    INSERT INTO countytwoparty \
    SELECT '02' fips, 'Alaska' county, candidate, SUM(votes) FROM aktmp \
    WHERE candidate in ('Obama', 'Romney') GROUP BY candidate; \
    DROP TABLE aktmp;"

    results_2012_raw.csv: $(foreach x,$(states),election-2012-results/data/$x.csv)
    csvstack $^ | tail +2 > $@