{ "cells": [ { "cell_type": "code", "execution_count": 14, "metadata": { "scrolled": false }, "outputs": [ { "data": { "text/html": [ "
" ], "text/plain": [ "" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "map_2019_SP_Vote_byPrecinct(5)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": 13, "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", "
Reg_Vote_11/7/19Total_VoteTrash_TotalSchoolBoard_Vote
Counting1
1165509541205464634893.5
\n", "
" ], "text/plain": [ " Reg_Vote_11/7/19 Total_Vote Trash_Total SchoolBoard_Vote\n", "Counting1 \n", "1 165509 54120 54646 34893.5" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#Run Code\n", "import pandas as pd\n", "import numpy as np\n", "import warnings\n", "warnings.filterwarnings('ignore')\n", "import matplotlib as mpl\n", "import matplotlib.pyplot as plt\n", "%matplotlib inline \n", "import requests # library to handle requests\n", "import folium\n", "import seaborn as sns\n", "import scipy.stats as stats #used to get correlation coefficient\n", "\n", "\n", "#load Data\n", "P19= pd.read_csv('Data/SaintPaul_2019_Ward_Precinct.csv')\n", "P19[\"Precinct1\"]= P19[\"Precinct\"].str.replace(\"Saint Paul \", \"\") \n", "P19['Tot_LastVote']= P19.Cand_1st_LastVote + P19.Cand_2nd_LastVote + P19.Cand_3rd_LastVote +P19.Cand_4th_LastVote + P19.Cand_5th_LastVote + P19.Cand_6th_LastVote\n", "P19['LastVote%']=round(P19.Tot_LastVote/ P19.Total_Vote ,4)*100\n", "\n", "\n", "#Group up by the Ward Table\n", "Features=['Ward', 'Precinct','Reg_Vote_10/1/19', 'Reg_Vote_11/7/19', 'Tot_Early/Absentee_Vote', 'Total_Vote','SchoolBoard_Vote','Min_DFL(endorse)','Trash_No','Trash_Yes', 'Trash_Total','NO_Cand1st_Vote ','No_Cand_Align', 'YES_Cand1st_Vote ', 'Yes_Cand_Align', 'Trash_Vote_Diff','1st_Undervote', 'Total_Ballot','2nd_Undervote', '2nd_Raw_Undervote', '3rd_Undervote','3rd_Raw_Undervote', 'Cand_1st_Vote','Cand_1st_2ndVote', 'Cand_1st_3rdVote','Cand_1st_Cond_3rdVote_Surplus', 'Cand_2nd_Vote','Cand_2nd_2ndVote', 'Cand_2nd_3rdVote','Cand_1st_LastVote','Cand_2nd_LastVote','Cand_3rd_LastVote','Cand_4th_LastVote','Cand_5th_LastVote','Cand_6th_LastVote'] \n", "\n", "W19=P19[Features].groupby(['Ward']).sum()\n", "W19=W19.reset_index()\n", "W19['Early/Abs_Vote%']= round(W19['Tot_Early/Absentee_Vote']/ W19.Total_Vote,4)*100\n", "W19['VoterReg_Election']= W19['Reg_Vote_11/7/19'] - W19['Reg_Vote_10/1/19']\n", "W19['Tot_Vote%']=round(W19.Total_Vote/ W19['Reg_Vote_11/7/19'] ,4)*100\n", "W19.insert(17, \"LastElection_Vote%\", [12.73, 18.78, 17.89 ,19.85 ,17.81,10.43,8.59], True)\n", "W19['LastElection_Diff%']=W19['Tot_Vote%']- W19['LastElection_Vote%']\n", "W19['Winner_1stVote%']=round(W19.Cand_1st_Vote/ W19.Total_Vote ,4)*100\n", "W19['SchoolBoard_Vote%']=round(W19.SchoolBoard_Vote / W19.Total_Vote ,4)*100\n", "W19['DFL_Vote%']=round(W19['Min_DFL(endorse)']/W19.SchoolBoard_Vote ,4)*100\n", "W19['Trash_No%']=round(W19.Trash_No/W19.Trash_Total ,4)*100\n", "W19['1st_Undervote%']= round(W19['1st_Undervote']/W19.Total_Ballot ,4)*100\n", "W19['2nd_Undervote%']= round(W19['2nd_Undervote']/W19.Total_Ballot ,4)*100\n", "W19['2nd_Cond_Undervote%']= round(W19['2nd_Raw_Undervote']/W19.Total_Vote ,4)*100\n", "W19['3rd_Undervote%']= round(W19['3rd_Undervote']/W19.Total_Ballot ,4)*100\n", "W19['3rd_Cond_Undervote%']= round(W19['3rd_Raw_Undervote']/(W19.Total_Ballot- W19['2nd_Undervote']) ,4)*100\n", "W19['Winner_Cond_2ndVote%']= round(W19.Cand_1st_2ndVote/(W19.Total_Vote- W19.Cand_1st_Vote) ,4)*100\n", "W19['Winner_Cond_3rdVote%']= round(W19.Cand_1st_3rdVote/(W19.Total_Vote- W19.Cand_1st_Vote - W19.Cand_1st_2ndVote) ,4)*100\n", "W19['Winner_Cond_LastVote%']= round(W19.Cand_1st_LastVote/(W19.Cand_1st_Vote) ,4)*100\n", "W19['Tot_LastVote']= W19.Cand_1st_LastVote + W19.Cand_2nd_LastVote + W19.Cand_3rd_LastVote +W19.Cand_4th_LastVote + W19.Cand_5th_LastVote + W19.Cand_6th_LastVote\n", "W19['Tot_LastVote%']= round(W19['Tot_LastVote']/W19.Total_Vote ,4)*100\n", "\n", "\n", "#UnderVote Ward\n", "Features= ['Ward', 'Reg_Vote_11/7/19', 'VoterReg_Election', 'Total_Vote', 'Tot_Vote%', 'Total_Ballot', '1st_Undervote', '1st_Undervote%', '2nd_Undervote','2nd_Undervote%', '2nd_Cond_Undervote%','3rd_Undervote', '3rd_Undervote%','3rd_Cond_Undervote%', 'Tot_LastVote', 'Tot_LastVote%']\n", "Und19= W19[Features]\n", "Und19.columns= ['Ward', 'Reg_Vote', 'VoterReg_Election', 'Total_Vote', 'Tot_Vote%', 'Total_Ballot', '1st_Undervote', '1st_Undervote%', '2nd_Undervote','2nd_Undervote%', '2nd_Cond_Undervote%','3rd_Undervote', '3rd_Undervote%','3rd_Cond_Undervote%', 'Tot_LastVote','Tot_Cond_LastVote%']\n", "\n", "#RCV Brekadown Ward level\n", "Features= ['Ward','Total_Vote','Cand_1st_Vote', 'Winner_1stVote%','Cand_1st_2ndVote', 'Winner_Cond_2ndVote%', 'Cand_1st_3rdVote', 'Winner_Cond_3rdVote%', 'Cand_1st_Cond_3rdVote_Surplus', 'Cand_1st_LastVote' ,'Winner_Cond_LastVote%']\n", "Win19= W19[Features]\n", "Win19.columns= ['Ward','Tot_Vote','1st_Vote', '1stVote%','2ndVote', 'Cond_2ndVote%', '3rdVote','Cond_3rdVote%' ,'Ballot_3rdSurplus', 'LastVote', 'Cond_LastVote%']\n", "\n", "\n", "#Ward Aggregate Info\n", "Features= ['Ward','Total_Vote','Reg_Vote_11/7/19']\n", "PW_19=W19[Features]\n", "PW_19.columns= ['Ward','Total_WardVotes','Total_WardRegistered']\n", "\n", "\n", "#Join the Ward Table\n", "P_19=P19.set_index('Ward').join(PW_19.set_index('Ward'))\n", "\n", "#Determine Weight city council Variables/other \n", "P_19['Projected_Weight']= round(P_19['Reg_Vote_11/7/19'] / P_19.Total_WardRegistered, 4) *100\n", "P_19['Actual_Weight']= round(P_19.Total_Vote / P_19.Total_WardVotes, 4) *100\n", "P_19['Representation']= P_19.Actual_Weight - P_19.Projected_Weight\n", "#Determine Weight trash \n", "P_19['C_Projected_Weight']= round(P_19['Reg_Vote_11/7/19'] / 165509, 4) *100\n", "P_19['T_Actual_Weight']= round(P_19.Trash_Total / 54646, 4) *100\n", "P_19['T_Representation']= P_19.T_Actual_Weight - P_19.C_Projected_Weight\n", "#Determine Weight Schoolboard \n", "P_19['S_Actual_Weight']= round(P_19.SchoolBoard_Vote / 34894, 4) *100\n", "P_19['S_Representation']= P_19.S_Actual_Weight - P_19.C_Projected_Weight\n", "\n", "#Reset Values\n", "P_19=P_19.reset_index()\n", "\n", "#Create Convenient General Table\n", "\n", "GFeatures= ['Ward','Precinct1','Reg_Vote_11/7/19', 'VoterReg_Election', 'Total_Vote', 'Early/Abs_Vote%', 'LastCouncil_Vote%','Tot_Vote%', 'Representation', '1st_Undervote%', '2nd_Cond_Undervote%', 'LastVote%']\n", "G19=P_19[GFeatures]\n", "G19.columns=['Ward','Precinct','Reg_Voters', 'Reg_ElectDay', 'Tot_Vote', 'Early_Vote%', 'LastCouncil_Vote%','Tot_Vote%', 'Representation', '1st_UnderVote%', '2nd_Cond_UnderVote%', 'LastVote%']\n", "\n", "#Candidate\n", "C1Features= ['Ward','Precinct1','Reg_Vote_11/7/19', 'Tot_Vote%','Cand_1st_Vote', 'Winner_Vote%','Cand_1st_2ndVote', 'Cand_1st_Cond_2ndVote%', 'Cand_1st_3rdVote','Cand_1st_Cond_3rdVote%', 'Cand_1st_Cond_3rdVote_Surplus', 'Cand_1st_LastVote%']\n", "C119=P_19[C1Features]\n", "C119.columns=['Ward','Precinct','Reg_Voters','Tot_Vote%', '1stVote', '1stVote%','2ndVote', 'Cond_2ndVote%', '3rdVote','Cond_3rdVote%', 'Ballot_Surplus','Cond_LastVote%']\n", "\n", "C2Features= ['Ward','Precinct1','Reg_Vote_11/7/19','Total_Vote', 'Tot_Vote%','Cand_2nd_Vote', 'Cand_2nd_Vote%','Cand_2nd_2ndVote', 'Cand_2nd_Cond_2ndVote%', 'Cand_2nd_3rdVote','Cand_2nd_Cond_3rdVote%', 'Cand_2nd_LastVote%']\n", "C219=P_19[C2Features]\n", "C219.columns=['Ward','Precinct','Reg_Voters','Tot_Vote','Tot_Vote%', '1stVote', '1stVote%','2ndVote', 'Cond_2ndVote%', '3rdVote','Cond_3rdVote%','Cond_LastVote%']\n", "\n", "C3Features= ['Ward','Precinct1','Reg_Vote_11/7/19','Total_Vote', 'Tot_Vote%','Cand_3rd_Vote', 'Cand_3rd_Vote%','Cand_3rd_2ndVote', 'Cand_3rd_Cond_2ndVote%', 'Cand_3rd_3rdVote','Cand_3rd_Cond_3rdVote%', 'Cand_3rd_LastVote%']\n", "C319=P_19[C3Features]\n", "C319.columns=['Ward','Precinct','Reg_Voters','Tot_Vote','Tot_Vote%', '1stVote', '1stVote%','2ndVote', 'Cond_2ndVote%', '3rdVote','Cond_3rdVote%','Cond_LastVote%']\n", "\n", "C4Features= ['Ward','Precinct1','Reg_Vote_11/7/19','Total_Vote', 'Tot_Vote%','Cand_4th_Vote', 'Cand_4th_Vote%','Cand_4th_2ndVote', 'Cand_4th_Cond_2ndVote%', 'Cand_4th_3rdVote','Cand_4th_Cond_3rdVote%', 'Cand_4th_LastVote%']\n", "C419=P_19[C4Features]\n", "C419.columns=['Ward','Precinct','Reg_Voters','Tot_Vote','Tot_Vote%', '1stVote', '1stVote%','2ndVote', 'Cond_2ndVote%', '3rdVote','Cond_3rdVote%','Cond_LastVote%']\n", "\n", "C5Features= ['Ward','Precinct1','Reg_Vote_11/7/19','Total_Vote', 'Tot_Vote%','Cand_5th_Vote', 'Cand_5th_Vote%','Cand_5th_2ndVote', 'Cand_5th_Cond_2ndVote%', 'Cand_5th_3rdVote','Cand_5th_Cond_3rdVote%', 'Cand_5th_LastVote%']\n", "C519=P_19[C5Features]\n", "C519.columns=['Ward','Precinct','Reg_Voters','Tot_Vote','Tot_Vote%', '1stVote', '1stVote%','2ndVote', 'Cond_2ndVote%', '3rdVote','Cond_3rdVote%','Cond_LastVote%']\n", "\n", "C6Features= ['Ward','Precinct1','Reg_Vote_11/7/19','Total_Vote', 'Tot_Vote%','Cand_6th_Vote', 'Cand_6th_Vote%','Cand_6th_2ndVote', 'Cand_6th_Cond_2ndVote%', 'Cand_6th_3rdVote','Cand_6th_Cond_3rdVote%', 'Cand_6th_LastVote%']\n", "C619=P_19[C6Features]\n", "C619.columns=['Ward','Precinct','Reg_Voters','Tot_Vote','Tot_Vote%', '1stVote', '1stVote%','2ndVote', 'Cond_2ndVote%', '3rdVote','Cond_3rdVote%','Cond_LastVote%']\n", "\n", "\n", "#Create Convenient Trash Table\n", "TFeatures= ['Ward','Precinct','Trash_No', 'Trash_Total', 'Trash_No%', 'Trash_Vote_Diff', 'NO_Cand1st_Vote ','No_Cand_Align']\n", "T19=P_19[TFeatures] \n", "\n", "#Final csv Convenient Saves\n", "#Features=['Ward','Precinct','Reg_Vote_11/7/19', 'Total_Vote', 'Tot_Vote%', \"LastElection_Vote%\",'1st_Undervote%']\n", "#TW19.to_csv(r'SP2019WardBase.csv',index=False)\n", "\n", "\n", "#Aggregate Numbers for filling out certain values\n", "W19['Counting1']=1\n", "Features=['Reg_Vote_11/7/19', 'Total_Vote','Trash_Total','SchoolBoard_Vote','Counting1']\n", "W19[Features].groupby(['Counting1']).sum()\n", "\n", "\n" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "#Map City Council Results\n", "\n", "def map_2019_SP_Vote_byPrecinct(Ward=0):\n", " #setup\n", " sp_geo = r'Shapefiles/SaintPaul_VotingPrecincts.json'\n", " \n", " \n", " if Ward==0:\n", " B=P_19\n", " A='City Council'\n", " zo= 12\n", " else: \n", " B= P_19[(P_19['Ward'] == Ward)]\n", " A= 'Ward ' + str(Ward) +' '\n", " zo=13\n", " \n", " \n", " #Set up Variable Zoom Locations\n", " data = [[0, [44.948530, -93.096058]], [1, [44.958326, -93.122926]], [2, [44.933159, -93.115535]], [3, [44.918270, -93.176131]], [4, [44.961494, -93.176991]], [5, [44.978117, -93.106602]], [6, [44.978945, -93.047281]], [7, [44.942040, -93.033178]]] \n", "\n", " #44.951655, -93.096323\n", " \n", " # Create the pandas DataFrame \n", " Loc = pd.DataFrame(data, columns = ['Ward', 'Location']) \n", " Loc.iloc[Ward,1:]\n", " \n", " \n", " # generate a new map\n", " SP_map = folium.Map(location=Loc.iloc[Ward,1], zoom_start=zo,tiles='cartodbpositron') #tiles=\"OpenStreetMap\")\n", "\n", " SP_map.choropleth(\n", " geo_data=sp_geo,\n", " data=B,\n", " columns=['Precinct','Tot_Vote%'],\n", " key_on=\"feature.properties.Precinct\",\n", " fill_color='YlOrRd', \n", " fill_opacity=0.5, \n", " line_opacity=0.2,\n", " legend_name='2019 Saint Paul ' + A +' Total Vote%',\n", " highlight= True\n", " )\n", " \n", " # display map\n", " return SP_map" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
" ], "text/plain": [ "" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "map_2019_SP_Vote_byPrecinct()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "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.7.3" } }, "nbformat": 4, "nbformat_minor": 2 }