Skip to content

Instantly share code, notes, and snippets.

@fitnr

fitnr/2012_results.mk

Created November 10, 2016 22:14
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save fitnr/b7072147a722050650ab082514ff1fda to your computer and use it in GitHub Desktop.
Save fitnr/b7072147a722050650ab082514ff1fda to your computer and use it in GitHub Desktop.
Download ZIP
Raw
2012_results.mk
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 > $@
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment