{ "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "from IPython.core.display import display, HTML" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We'll pull population density for counties from [this Census ESRI API](https://covid19.census.gov/datasets/21843f238cbb46b08615fc53e19e0daf_1)." ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "county_pop_df = pd.read_csv('https://opendata.arcgis.com/datasets/21843f238cbb46b08615fc53e19e0daf_1.csv'\n", " ,usecols=['GEOID','NAME','State','B01001_001E','B01001_calc_PopDensity']\n", " ,dtype={'GEOID':str})" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "1. Filter out Puerto Rico _(but support Puerto Rican self-determination!)_,\n", "1. sort the records by their calculated population density,\n", "1. and then add a cumulative sum column." ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "county_pop_df = county_pop_df[county_pop_df['State']!='Puerto Rico']\n", "county_pop_df.sort_values(by='B01001_calc_PopDensity', ascending=False, inplace=True)\n", "county_pop_df['cumcount']=county_pop_df['B01001_001E'].cumsum()" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
GEOIDNAMEStateB01001_001EB01001_calc_PopDensitycumcount
185836061New York CountyNew York163248027819.8048011632480
185136047Kings CountyNew York260074714475.0254394233227
183036005Bronx CountyNew York143787213202.5153055671099
186836081Queens CountyNew York22985138158.4371537969612
22306075San Francisco CountyCalifornia8700447168.1904718839656
\n", "
" ], "text/plain": [ " GEOID NAME State B01001_001E \\\n", "1858 36061 New York County New York 1632480 \n", "1851 36047 Kings County New York 2600747 \n", "1830 36005 Bronx County New York 1437872 \n", "1868 36081 Queens County New York 2298513 \n", "223 06075 San Francisco County California 870044 \n", "\n", " B01001_calc_PopDensity cumcount \n", "1858 27819.804801 1632480 \n", "1851 14475.025439 4233227 \n", "1830 13202.515305 5671099 \n", "1868 8158.437153 7969612 \n", "223 7168.190471 8839656 " ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "county_pop_df.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "It's surprisingly maddening to find a good source for county-level election data. We're going to use [this one](https://github.com/tonmcg/US_County_Level_Election_Results_08-20/), though we need to repair the FIPS codes so they're 5-character strings." ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
votes_demvotes_goptotal_votesstate_abbrcounty_namecombined_fipsfips
093003130413246588AKAlaska201302013
193003130413246588AKAlaska201602016
293003130413246588AKAlaska202002020
393003130413246588AKAlaska205002050
493003130413246588AKAlaska206002060
\n", "
" ], "text/plain": [ " votes_dem votes_gop total_votes state_abbr county_name combined_fips \\\n", "0 93003 130413 246588 AK Alaska 2013 \n", "1 93003 130413 246588 AK Alaska 2016 \n", "2 93003 130413 246588 AK Alaska 2020 \n", "3 93003 130413 246588 AK Alaska 2050 \n", "4 93003 130413 246588 AK Alaska 2060 \n", "\n", " fips \n", "0 02013 \n", "1 02016 \n", "2 02020 \n", "3 02050 \n", "4 02060 " ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "election_results_df = pd.read_csv('https://raw.githubusercontent.com/tonmcg/US_County_Level_Election_Results_08-20/master/2016_US_County_Level_Presidential_Results.csv'\n", " ,usecols=['votes_dem','votes_gop','total_votes','state_abbr','county_name','combined_fips']\n", " ,dtype={'combined_fips':str,'votes_dem':'int64','votes_gop':'int64','total_votes':'int64'})\n", "election_results_df['fips'] = election_results_df['combined_fips'].apply(lambda x: '0' + x if len(x)==4 else x)\n", "election_results_df.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "There are two Census FIPS that mismatch between the tables; we'll coerce them to matchin values." ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [], "source": [ "election_results_df.loc[election_results_df['fips']=='46113','fips'] = '46102' #Oglala Lakota County\n", "election_results_df.loc[election_results_df['fips']=='02270','fips'] = '02158' #Kusilvak Census Area" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Join them and see if anything went wrong. There's one county that's still mismatched, but, with only 75 people, it isn't going to affect any of our election results. Out of exhaustion, we'll leave it be." ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
NAMEStateB01001_001EB01001_calc_PopDensitycumcountvotes_demvotes_goptotal_votesstate_abbrcounty_namecombined_fips
GEOID
15005Kalawao CountyHawaii752.414868320317763NaNNaNNaNNaNNaNNaN
\n", "
" ], "text/plain": [ " NAME State B01001_001E B01001_calc_PopDensity cumcount \\\n", "GEOID \n", "15005 Kalawao County Hawaii 75 2.414868 320317763 \n", "\n", " votes_dem votes_gop total_votes state_abbr county_name combined_fips \n", "GEOID \n", "15005 NaN NaN NaN NaN NaN NaN " ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "joined = county_pop_df.set_index('GEOID').join(election_results_df.set_index('fips'))\n", "joined[pd.isna(joined['votes_dem'])]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Define a function that will run an election for an arbitrary number of district. To do so, we break the density-sorted dataframe by using `pd.cut` on the cumulative population field. Then we write a nice little HTML table to output the results." ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "def run_election(number_districts):\n", " joined['electorate'] = pd.cut(joined['cumcount'], bins=number_districts, labels=range(1,number_districts+1))\n", " results_table = joined.groupby(by='electorate').sum()\n", " results_table['pctdem'] = results_table['votes_dem']/results_table['total_votes']\n", " results_table['pctgop'] = results_table['votes_gop']/results_table['total_votes']\n", " display(HTML('

Election with {} districts

'.format(number_districts)))\n", " \n", " html_table = ''\n", " html_table += ''\n", " for i,r in results_table.iterrows():\n", " most_dense = joined[joined['electorate']==i].iloc[0]\n", " least_dense = joined[joined['electorate']==i].iloc[-1]\n", " most_dense_printable = '{}, {}'.format(most_dense['NAME'], most_dense['State'])\n", " least_dense_printable = '{}, {}'.format(least_dense['NAME'], least_dense['State'])\n", " \n", " winner = 'D' if r['pctdem'] > r['pctgop'] else 'R'\n", "\n", " html_table += ''.format(i, f\"{int(r['B01001_001E']):,}\", most_dense_printable, least_dense_printable, round(r['pctdem']*100,2),round(r['pctgop']*100,2), winner)\n", " \n", " html_table +='
DistrictTotal PopulationDensest CountyLeast Dense CountyDem %Rep %Winner
{}{}{}{}{}{}{}
'\n", " display(HTML(html_table))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Run the election with 11 districts ..." ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/html": [ "

Election with 11 districts

" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
DistrictTotal PopulationDensest CountyLeast Dense CountyDem %Rep %Winner
130,773,474New York County, New YorkWinchester city, Virginia70.8425.38D
229,255,155Dallas County, TexasTarrant County, Texas62.8532.64D
329,020,695Orleans Parish, LouisianaContra Costa County, California62.2433.22D
429,291,928Sacramento County, CaliforniaDavis County, Utah56.0438.66D
528,872,991Monroe County, New YorkDakota County, Minnesota50.3344.35D
629,423,618Clark County, WashingtonOrange County, New York46.3248.81R
729,293,887Kalamazoo County, MichiganSpalding County, Georgia43.7150.89R
829,351,626Shawnee County, KansasJefferson County, Tennessee39.2655.36R
929,140,053Jackson County, GeorgiaDickson County, Tennessee36.6258.44R
1029,255,578Bowie County, TexasNottoway County, Virginia32.8662.2R
1129,224,025Cherokee County, TexasYukon-Koyukuk Census Area, Alaska33.4859.72R
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "run_election(11)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "How about 9?" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/html": [ "

Election with 9 districts

" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
DistrictTotal PopulationDensest CountyLeast Dense CountyDem %Rep %Winner
137,299,101New York County, New YorkDuPage County, Illinois69.3626.7D
234,210,307DeKalb County, GeorgiaClayton County, Georgia63.1132.31D
336,929,333Middlesex County, MassachusettsHillsborough County, Florida59.2635.89D
435,956,241Monmouth County, New JerseyVanderburgh County, Indiana51.6343.23D
532,504,452Hampden County, MassachusettsKalamazoo County, Michigan46.6448.25R
638,893,609Maricopa County, ArizonaSaratoga County, New York42.7951.48R
735,685,254Hinds County, MississippiMoore County, North Carolina37.4757.77R
835,677,509Lapeer County, MichiganAllen County, Kentucky34.3460.65R
935,747,224Bonneville County, IdahoYukon-Koyukuk Census Area, Alaska33.4659.97R
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "run_election(9)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "With 17?" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/html": [ "

Election with 17 districts

" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
DistrictTotal PopulationDensest CountyLeast Dense CountyDem %Rep %Winner
120,084,946New York County, New YorkCook County, Illinois78.8317.9D
219,239,594Union County, New JerseyFredericksburg city, Virginia60.9134.62D
318,684,112Passaic County, New JerseyColonial Heights city, Virginia63.9931.35D
418,614,150Tarrant County, TexasRadford city, Virginia63.4131.88D
518,562,096Macomb County, MichiganSalt Lake County, Utah57.2137.4D
619,226,969Santa Clara County, CaliforniaHartford County, Connecticut57.1538.61D
719,387,526Travis County, TexasErie County, New York50.3344.5D
818,888,412Galveston County, TexasChatham County, Georgia49.7744.9D
918,836,123Hamilton County, TennesseeManatee County, Florida46.9747.75R
1018,990,427Marin County, CaliforniaDouglas County, Colorado44.6749.78R
1118,936,579Winnebago County, WisconsinHampshire County, Massachusetts42.3652.46R
1218,869,503Benton County, ArkansasSangamon County, Illinois39.854.72R
1318,979,897Windham County, ConnecticutAlbemarle County, Virginia35.9959.3R
1418,883,361Cass County, MissouriStokes County, North Carolina36.7458.04R
1518,908,564Pike County, PennsylvaniaCherokee County, Oklahoma33.3761.7R
1618,909,925Coshocton County, OhioBuena Vista County, Iowa32.2762.89R
1718,900,846Breckinridge County, KentuckyYukon-Koyukuk Census Area, Alaska33.6958.97R
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "run_election(17)" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/html": [ "

Election with 21 districts

" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
DistrictTotal PopulationDensest CountyLeast Dense CountyDem %Rep %Winner
114,861,227New York County, New YorkEssex County, New Jersey80.6416.47D
215,912,247Cook County, IllinoisWinchester city, Virginia63.1732.35D
314,800,056Dallas County, TexasNewport News city, Virginia61.6234.28D
416,320,929Los Angeles County, CaliforniaWestchester County, New York64.9730.27D
515,597,047Hennepin County, MinnesotaMacomb County, Michigan63.531.64D
615,628,239Norfolk County, MassachusettsProvidence County, Rhode Island58.9936.55D
715,318,996Fairfield County, ConnecticutHillsborough County, Florida57.0537.61D
815,117,251Monmouth County, New JerseyClarke County, Georgia52.5242.3D
915,533,076Bucks County, PennsylvaniaWashington County, Oregon50.744.46D
1015,060,023Hamilton County, IndianaGenesee County, Michigan48.6845.9D
1115,417,696Greenville County, South CarolinaCleveland County, Oklahoma48.1546.57D
1215,585,913Sedgwick County, KansasLeon County, Florida46.0649.04R
1315,316,510Putnam County, New YorkCumberland County, Maine41.3352.86R
1415,323,833Erie County, PennsylvaniaSaratoga County, New York40.953.15R
1515,186,794Hinds County, MississippiTompkins County, New York39.5755.79R
1615,404,525Clackamas County, OregonBoone County, Indiana36.0259.16R
1715,316,483Belknap County, New HampshireBarry County, Michigan35.3859.55R
1815,234,397Fulton County, New YorkPutnam County, Indiana35.7359.36R
1915,336,889Santa Fe County, New MexicoPreston County, West Virginia32.3262.59R
2015,320,870Huntingdon County, PennsylvaniaEdwards County, Illinois32.9162.26R
2115,310,029Franklin County, ArkansasYukon-Koyukuk Census Area, Alaska33.6558.71R
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "run_election(21)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.8.5" } }, "nbformat": 4, "nbformat_minor": 4 }