#Run Code
import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings('ignore')
import matplotlib as mpl
import matplotlib.pyplot as plt
%matplotlib inline
import requests # library to handle requests
import folium
import seaborn as sns
import scipy.stats as stats #used to get correlation coefficient
#load Data
P19= pd.read_csv('Data/SaintPaul_2019_Ward_Precinct.csv')
P19["Precinct1"]= P19["Precinct"].str.replace("Saint Paul ", "")
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
P19['LastVote%']=round(P19.Tot_LastVote/ P19.Total_Vote ,4)*100
#Group up by the Ward Table
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']
W19=P19[Features].groupby(['Ward']).sum()
W19=W19.reset_index()
W19['Early/Abs_Vote%']= round(W19['Tot_Early/Absentee_Vote']/ W19.Total_Vote,4)*100
W19['VoterReg_Election']= W19['Reg_Vote_11/7/19'] - W19['Reg_Vote_10/1/19']
W19['Tot_Vote%']=round(W19.Total_Vote/ W19['Reg_Vote_11/7/19'] ,4)*100
W19.insert(17, "LastElection_Vote%", [12.73, 18.78, 17.89 ,19.85 ,17.81,10.43,8.59], True)
W19['LastElection_Diff%']=W19['Tot_Vote%']- W19['LastElection_Vote%']
W19['Winner_1stVote%']=round(W19.Cand_1st_Vote/ W19.Total_Vote ,4)*100
W19['SchoolBoard_Vote%']=round(W19.SchoolBoard_Vote / W19.Total_Vote ,4)*100
W19['DFL_Vote%']=round(W19['Min_DFL(endorse)']/W19.SchoolBoard_Vote ,4)*100
W19['Trash_No%']=round(W19.Trash_No/W19.Trash_Total ,4)*100
W19['1st_Undervote%']= round(W19['1st_Undervote']/W19.Total_Ballot ,4)*100
W19['2nd_Undervote%']= round(W19['2nd_Undervote']/W19.Total_Ballot ,4)*100
W19['2nd_Cond_Undervote%']= round(W19['2nd_Raw_Undervote']/W19.Total_Vote ,4)*100
W19['3rd_Undervote%']= round(W19['3rd_Undervote']/W19.Total_Ballot ,4)*100
W19['3rd_Cond_Undervote%']= round(W19['3rd_Raw_Undervote']/(W19.Total_Ballot- W19['2nd_Undervote']) ,4)*100
W19['Winner_Cond_2ndVote%']= round(W19.Cand_1st_2ndVote/(W19.Total_Vote- W19.Cand_1st_Vote) ,4)*100
W19['Winner_Cond_3rdVote%']= round(W19.Cand_1st_3rdVote/(W19.Total_Vote- W19.Cand_1st_Vote - W19.Cand_1st_2ndVote) ,4)*100
W19['Winner_Cond_LastVote%']= round(W19.Cand_1st_LastVote/(W19.Cand_1st_Vote) ,4)*100
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
W19['Tot_LastVote%']= round(W19['Tot_LastVote']/W19.Total_Vote ,4)*100
#UnderVote Ward
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%']
Und19= W19[Features]
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%']
#RCV Brekadown Ward level
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%']
Win19= W19[Features]
Win19.columns= ['Ward','Tot_Vote','1st_Vote', '1stVote%','2ndVote', 'Cond_2ndVote%', '3rdVote','Cond_3rdVote%' ,'Ballot_3rdSurplus', 'LastVote', 'Cond_LastVote%']
#Ward Aggregate Info
Features= ['Ward','Total_Vote','Reg_Vote_11/7/19']
PW_19=W19[Features]
PW_19.columns= ['Ward','Total_WardVotes','Total_WardRegistered']
#Join the Ward Table
P_19=P19.set_index('Ward').join(PW_19.set_index('Ward'))
#Determine Weight city council Variables/other
P_19['Projected_Weight']= round(P_19['Reg_Vote_11/7/19'] / P_19.Total_WardRegistered, 4) *100
P_19['Actual_Weight']= round(P_19.Total_Vote / P_19.Total_WardVotes, 4) *100
P_19['Representation']= P_19.Actual_Weight - P_19.Projected_Weight
#Determine Weight trash
P_19['C_Projected_Weight']= round(P_19['Reg_Vote_11/7/19'] / 165509, 4) *100
P_19['T_Actual_Weight']= round(P_19.Trash_Total / 54646, 4) *100
P_19['T_Representation']= P_19.T_Actual_Weight - P_19.C_Projected_Weight
#Determine Weight Schoolboard
P_19['S_Actual_Weight']= round(P_19.SchoolBoard_Vote / 34894, 4) *100
P_19['S_Representation']= P_19.S_Actual_Weight - P_19.C_Projected_Weight
#Reset Values
P_19=P_19.reset_index()
#Create Convenient General Table
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%']
G19=P_19[GFeatures]
G19.columns=['Ward','Precinct','Reg_Voters', 'Reg_ElectDay', 'Tot_Vote', 'Early_Vote%', 'LastCouncil_Vote%','Tot_Vote%', 'Representation', '1st_UnderVote%', '2nd_Cond_UnderVote%', 'LastVote%']
#Candidate
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%']
C119=P_19[C1Features]
C119.columns=['Ward','Precinct','Reg_Voters','Tot_Vote%', '1stVote', '1stVote%','2ndVote', 'Cond_2ndVote%', '3rdVote','Cond_3rdVote%', 'Ballot_Surplus','Cond_LastVote%']
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%']
C219=P_19[C2Features]
C219.columns=['Ward','Precinct','Reg_Voters','Tot_Vote','Tot_Vote%', '1stVote', '1stVote%','2ndVote', 'Cond_2ndVote%', '3rdVote','Cond_3rdVote%','Cond_LastVote%']
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%']
C319=P_19[C3Features]
C319.columns=['Ward','Precinct','Reg_Voters','Tot_Vote','Tot_Vote%', '1stVote', '1stVote%','2ndVote', 'Cond_2ndVote%', '3rdVote','Cond_3rdVote%','Cond_LastVote%']
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%']
C419=P_19[C4Features]
C419.columns=['Ward','Precinct','Reg_Voters','Tot_Vote','Tot_Vote%', '1stVote', '1stVote%','2ndVote', 'Cond_2ndVote%', '3rdVote','Cond_3rdVote%','Cond_LastVote%']
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%']
C519=P_19[C5Features]
C519.columns=['Ward','Precinct','Reg_Voters','Tot_Vote','Tot_Vote%', '1stVote', '1stVote%','2ndVote', 'Cond_2ndVote%', '3rdVote','Cond_3rdVote%','Cond_LastVote%']
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%']
C619=P_19[C6Features]
C619.columns=['Ward','Precinct','Reg_Voters','Tot_Vote','Tot_Vote%', '1stVote', '1stVote%','2ndVote', 'Cond_2ndVote%', '3rdVote','Cond_3rdVote%','Cond_LastVote%']
#Create Convenient Trash Table
TFeatures= ['Ward','Precinct','Trash_No', 'Trash_Total', 'Trash_No%', 'Trash_Vote_Diff', 'NO_Cand1st_Vote ','No_Cand_Align']
T19=P_19[TFeatures]
#Final csv Convenient Saves
#Features=['Ward','Precinct','Reg_Vote_11/7/19', 'Total_Vote', 'Tot_Vote%', "LastElection_Vote%",'1st_Undervote%']
#TW19.to_csv(r'SP2019WardBase.csv',index=False)
#Aggregate Numbers for filling out certain values
W19['Counting1']=1
Features=['Reg_Vote_11/7/19', 'Total_Vote','Trash_Total','SchoolBoard_Vote','Counting1']
W19[Features].groupby(['Counting1']).sum()