Below is the code I am using to generate the crunched numbers for Albany County, for example my 2021 Albany County Election Spreadsheet

Below is the code I am using to generate the crunched numbers for Albany County, for example my 2021 Albany County Election Spreadsheet. Text is converted using pdftotext -layout 2021GeneralRecanvass.txt which converts the PDF to a structured text file for easier parsing. The Excel Writer library is used for extensive formatting of the file for easy reading and parsing. View this as a separate page for syntax highlighting.

001from io import StringIO
002import re
003import pandas as pd
004import numpy as np
005 
006# paths
007# electionResultText = '/home/andy/Desktop/2021GeneralRecanvass.txt'
008# enrollmentXLS = '/home/andy/enroll/nov21-Enrollment/AlbanyED_nov21.xlsx'
009# outputPath = '/tmp/2021_albany_county_races.xlsx'
010 
011# electionResultText = '/home/andy/Documents/GIS.Data/election.districts/2020GeneralRecanvass.txt'
012# enrollmentXLS = '/home/andy/enroll/nov20-Enrollment/AlbanyED_nov20.xlsx'
013# outputPath = '/tmp/2020_albany_county_races.xlsx'
014 
015electionResultText = '/home/andy/Documents/GIS.Data/election.districts/2019'
016enrollmentXLS = '/home/andy/enroll/nov19-Enrollment/AlbanyED_nov19.xlsx'
017outputPath = '/tmp/2018_albany_county_races.xlsx'
018 
019# number to excel column letter
020def letter(colNum):
021    import math
022     
023    if colNum > 26:
024        return chr(ord('@')+math.floor(colNum/26))+chr(ord('@')+colNum%26)
025    else:
026        return chr(ord('@')+colNum)
027 
028pd.set_option("display.max_rows", None)
029pd.set_option("display.max_columns", None) # show everything when previewing
030 
031with open(electionResultText) as f:
032    data = f.read()
033 
034enroll = pd.read_excel(enrollmentXLS, header=4)
035enroll = enroll[(enroll['STATUS']=='Active')][['ELECTION DIST', 'TOTAL']].dropna()
036enroll['ELECTION DIST'] = enroll['ELECTION DIST'].astype(str).str.replace('  ',' ')
037enroll['ELECTION DIST']=enroll['ELECTION DIST'].str.strip()
038enroll['Enrollment'] = enroll['TOTAL']
039enroll.drop(labels=['TOTAL'], axis=1, inplace=True)
040 
041# array containing election result dataframes
042er = {}
043     
044# split each race which is divided by ten or more equal signs
045for raceData in re.split('={10,}',data):
046 
047    # blank out old data frame
048    df = None
049 
050    # split lines
051    rows = raceData.split('\n')
052 
053    race = ""
054    candidates = {}
055    startAt = 0
056     
057    for i, line in enumerate(rows):
058        # find race name
059        if re.search('VOTES\s*?PERCENT', line):
060            race = rows[i+1]
061         
062        race = race.replace('  ',' ')
063        race = race.rstrip()
064        #print(race)
065         
066        # find candidate names
067        for result in re.findall('(\d\d)\s*?=\s*?(\w.*?)\d', line):
068            candidates[int(result[0])] = result[1].rstrip()
069        
070        # find start at location for CSV reader
071        if re.findall('-{5,}', line):
072            startAt = i+3
073            break
074     
075    # skips enrollment stats, as the don't have candidates
076    if not candidates or not 2 in candidates:
077        continue
078     
079    if not race:
080        continue
081         
082    #print(race)
083     
084    df = pd.read_csv(
085        StringIO(raceData),
086        header=None,
087        skiprows=startAt,
088        sep='(?<=\d)\s{1,}(?=\d)',
089        engine='python',
090        on_bad_lines='warn')
091     
092    df=df[df[0].str.contains('^\d{4}').fillna(False)] # ONLY ROWS STARTING WITH 4-DIGIT ED CODE
093     
094    df.reset_index(drop=True, inplace=True) # reset index so we can use it in formulas
095 
096    df=df.rename(candidates,axis=1) # rename columns
097     
098    df.iloc[:,1:]=df.iloc[:,1:].apply(pd.to_numeric) # make sure all columns are numeric
099    df.iloc[:,1:]=df.iloc[:,1:].convert_dtypes('int32') # cast columns to int32
100 
101    # crunching
102    df.insert(1, 'Ballot', df.iloc[:,1:].convert_dtypes('int32').sum(axis=1)) # add total column
103     
104    df.insert(2, 'Blanks', df['OVER VOTES'].convert_dtypes('int32')) # add blank
105    df.drop(labels=['OVER VOTES'], axis=1, inplace=True)
106 
107    df['Blanks']+=df['UNDER VOTES'].convert_dtypes('int32') # add under votes
108    df.drop(labels=['UNDER VOTES'], axis=1, inplace=True)
109 
110    df.insert(3, 'Canvas', '=F'+(df.index+2).map(str)+'-G'+(df.index+2).map(str)) 
111    df.insert(4, 'TO %', '=(F'+(df.index+2).map(str)+'/E'+(df.index+2).map(str)+')')
112    df.insert(5, 'DO %', '=(G'+(df.index+2).map(str)+'/F'+(df.index+2).map(str)+')')
113         
114    # create check columns
115    df['CHECK'] = '=F'+(df.index+2).map(str)+'-G'+(df.index+2).map(str)
116    df['CHECK %'] = '=0'
117     
118    # add percent columns
119    for i, col in enumerate(df.columns[6:-2]):
120        try:
121            # check columns
122            if (len(df.columns)-i-2) > 0:
123                # add to check column
124                df['CHECK'] += '-'+letter(i+i+11)+(df.index+2).map(str)
125 
126                # add check percent
127                df['CHECK %'] += '+'+letter(i+i+12)+(df.index+2).map(str)
128 
129            # add percent
130            df.insert(i+i+7, col+' %', '=('+letter(i+i+11)+(df.index+2).map(str)+'/H'+(df.index+2).map(str)+')')
131        except:
132            pass
133 
134    # add T W E Columns, drop combined field
135    df[['ED Code','Municipality','String','Ward','ED']]=df[0].str.extract('(\d\d\d\d)\s*?(.*?)(\s*?WARD\s*(\d*))?\s*?ED\s*(\d*)')
136 
137    # temporary string used for data merge with enrollments
138    df['ELECTION DIST'] = df['Municipality'].str.strip().replace('  ',' ') + \
139    ' '+df['Ward'].fillna(0).astype(str).str.zfill(3)+df['ED'].str.zfill(3).astype('str')
140 
141    # merge on ED Key
142    df=df.merge(enroll, on='ELECTION DIST', how='left')
143    df=df.drop(0, axis=1)
144    df=df.drop(labels=['ELECTION DIST','String'],axis=1)
145     
146    # move columns to proper order
147    cols = list(df.columns)
148    df = df[cols[-5:]+cols[:-5]]
149     
150    df['Municipality']=df['Municipality'].str.title()
151    df['Municipality'] = df['Municipality'].str.strip().replace('  ',' ') # remove whitespace around muni column
152     
153    # array with election result dataframes
154    er[race]=df.fillna(0)
155     
156# write file
157ew = pd.ExcelWriter(outputPath)
158 
159for i, race in enumerate(er):
160    raceStr = str(i+1)+' '+race
161    if (len(race)>28):
162        raceStr = str(i+1)+' '+race[:10] + '...' + race[-10:]
163     
164    # disble header, manually write, as pre-defined headers can't be formatted
165    er[race].fillna(0).to_excel(ew,sheet_name=raceStr, index=False)
166     
167    headForm = ew.book.add_format({'text_wrap': 1, 'font_family': 'Arial', 'bold': True ,
168                                   'valign': 'vcenter', 'align': 'center', 'bg_color': '#CCCCCC'})
169    for colnum, value in enumerate(er[race].columns.values):
170        ew.sheets[raceStr].write(0, colnum, value, headForm)
171     
172    ew.sheets[raceStr].set_row(0,50)
173     
174    # set column width for all columns to 20, freeze panes
175    bodyForm = ew.book.add_format({'text_wrap': 1, 'font_family': 'Arial', 'num_format': '#,##0', 'valign': 'vcenter', 'align': 'right'})
176    bodyPerForm = ew.book.add_format({'text_wrap': 1, 'font_family': 'Arial', 'num_format': '0.0%', 'valign': 'vcenter', 'align': 'right'})
177    muniForm = ew.book.add_format({'text_wrap': 1, 'font_family': 'Arial', 'valign': 'vcenter', 'align': 'center'})
178    ew.sheets[raceStr].set_column('A1:A9999', 6, bodyForm)
179    ew.sheets[raceStr].set_column('B1:B9999', 15, muniForm)
180    ew.sheets[raceStr].set_column('C1:D9999', 4, bodyForm)
181    ew.sheets[raceStr].set_column('E1:H9999', 10, bodyForm)
182    ew.sheets[raceStr].set_column('I1:J9999', 10, bodyPerForm)
183     
184    for colNum in range(11,40):
185        if colNum%2:
186            ew.sheets[raceStr].set_column(letter(colNum)+'1:'+letter(colNum)+'9999', 12, bodyForm)
187        else:
188            ew.sheets[raceStr].set_column(letter(colNum)+'1:'+letter(colNum)+'9999', 12, bodyPerForm)
189 
190    ew.sheets[raceStr].freeze_panes('E2')
191         
192ew.save()

Leave a Reply

Your email address will not be published. Required fields are marked *