#Imports
import pandas as pd
import geopandas as gpd
#Read in filtered voters
gdfVoter = gpd.read_file('.\data\WAKE\SubsetVoterFeatures.shp')
gdfVoter.columns
Index(['res_street', 'res_city_d', 'state_cd', 'zip_code', 'race_code', 'ethnic_cod', 'party_cd', 'gender_cod', 'precinct_a', 'ncid', 'st_address', 'city', 'zip', 'latitude', 'longitude', 'Oct17', 'Nov12', 'Nov18', 'Nov17', 'Nov16', 'MECE', 'index_righ', 'STATEFP10', 'COUNTYFP10', 'TRACTCE10', 'BLOCKCE', 'BLOCKID10', 'PARTFLG', 'HOUSING10', 'POP10', 'P003001', 'P003003', 'P010001', 'P010004', 'GEOID10', 'PctBlack', 'PctBlack18', 'geometry'], dtype='object')
#Compute # voters in each block
#grpBlock = gdfVoter.groupby('BLOCKID10')
gdfVoter['BLOCKID10'].value_counts()
371830509001015 781 371830506001002 741 371830521022000 614 371830528064023 449 371830509002012 405 371830528063011 391 371830541063000 385 371830521013005 333 371830528033014 319 371830528064009 315 371830528064031 304 371830528033000 299 371830521011018 283 371830521021030 275 371830541062005 275 371830521021040 269 371830528064015 267 371830521021019 244 371830541121007 243 371830520023000 240 371830528062052 236 371830528064006 235 371830528063002 233 371830528071008 229 371830505002001 225 371830521011021 222 371830521011002 209 371830540142000 201 371830530091010 191 371830520023002 183 ... 371830524062014 1 371830543013019 1 371830528062019 1 371830528032057 1 371830532072009 1 371830541063019 1 371830520012009 1 371830501001088 1 371830540182010 1 371830534171002 1 371830528033019 1 371830535123011 1 371830541132037 1 371830508002014 1 371830528031030 1 371830534202036 1 371830536101049 1 371830530091009 1 371830534202041 1 371830539004031 1 371830509001003 1 371830528033020 1 371830507001012 1 371830544023017 1 371830527012012 1 371830531073059 1 371830544031002 1 371830509001000 1 371830509001016 1 371830543013032 1 Name: BLOCKID10, Length: 1246, dtype: int64
#List each block and the number of voters in each MECE
dfMECE = (gdfVoter.pivot_table(index='BLOCKID10',columns='MECE',aggfunc={'ncid':'count'})
.fillna(0)
.droplevel(0,axis=1)
.reset_index())
dfMECE.columns = ['BLOCKID10','MECE1','MECE2','MECE3','MECE4','MECE5']
dfMECE['Total']=dfPivot.iloc[:,0:5].sum(axis=1)
dfMECE.to_csv('./data/WAKE/BlockMECE.csv',index=False)
# Remove blocks with fewer than 50 black voters
dfMECE['BHOUSE'] = dfMECE['HOUSING10']*dfMECE['P010004']/dfMECE['P010001']
dfMECE.head()
--------------------------------------------------------------------------- KeyError Traceback (most recent call last) ~\AppData\Local\ESRI\conda\envs\votesquad\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance) 2656 try: -> 2657 return self._engine.get_loc(key) 2658 except KeyError: pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc() pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc() pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item() pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item() KeyError: 'HOUSING10' During handling of the above exception, another exception occurred: KeyError Traceback (most recent call last) <ipython-input-35-3faac993b05b> in <module> 1 # Remove blocks with fewer than 50 black voters ----> 2 dfMECE['BHOUSE'] = dfMECE['HOUSING10']*dfMECE['P010004']/dfMECE['P010001'] 3 dfMECE.head() ~\AppData\Local\ESRI\conda\envs\votesquad\lib\site-packages\pandas\core\frame.py in __getitem__(self, key) 2925 if self.columns.nlevels > 1: 2926 return self._getitem_multilevel(key) -> 2927 indexer = self.columns.get_loc(key) 2928 if is_integer(indexer): 2929 indexer = [indexer] ~\AppData\Local\ESRI\conda\envs\votesquad\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance) 2657 return self._engine.get_loc(key) 2658 except KeyError: -> 2659 return self._engine.get_loc(self._maybe_cast_indexer(key)) 2660 indexer = self.get_indexer([key], method=method, tolerance=tolerance) 2661 if indexer.ndim > 1 or indexer.size > 1: pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc() pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc() pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item() pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item() KeyError: 'HOUSING10'