<jupyter_start><jupyter_text>We imported the General Election results from the [DC Board of Elections](https://electionresults.dcboe.org/election_results/2012-General-Election) for the years 2012-2018, inclusive. Earlier historic election results are available, but would require dealing with ANC boundary changes.<jupyter_code>dc_2012 = pd.read_csv('Data/November_6_2012_General_and_Special_Election_Certified_Results.csv')
dc_2014 = pd.read_csv('Data/November_4_2014_General_Election_Certified_Results.csv')
dc_2016 = pd.read_csv('Data/November_4_2014_General_Election_Certified_Results.csv')
dc_2018 = pd.read_csv('Data/November_6_2018_General_Election_Certified_Results.csv')<jupyter_output><empty_output><jupyter_text>2012-2016 had consistent headers. 2018 was renamed to be consistent.<jupyter_code># rename the 2018 dataframe headers
dc_2018 = dc_2018.rename(columns = {'ElectionDate':'ELECTION_DATE',  'ElectionName': 'ELECTION_NAME', 'ContestNumber': 'CONTEST_ID',
                'ContestName': 'CONTEST_NAME', 'PrecinctNumber': 'PRECINCT_NUMBER', 'WardNumber': 'WARD',
                'Candidate': 'CANDIDATE', 'Party': 'PARTY', 'Votes': 'VOTES'}, index=str)
dc_2012.dtypes
# initial merge
dc_2012_2016 = pd.concat([dc_2012, dc_2014, dc_2016], sort=False, axis = 0)<jupyter_output><empty_output><jupyter_text>2012-2016 and 2018 had slightly different formats for expressing CONTEST_NAME. We extracted the name of each ANC Single Member District from the CONTEST_NAME column and created a new column for the SMD.<jupyter_code># add the appropriate SMD column for the various years
dc_2012_2016['SMD'] = dc_2012_2016.CONTEST_NAME.str[-4:]
dc_2018['SMD'] = dc_2018.CONTEST_NAME.str[6:10]
dc_2012_2018 = pd.concat([dc_2012_2016, dc_2018], sort=False, axis = 0)
# filter for just the results that include the name "ANC" or "ADVISORY NEIGHBORHOOD COMMISSIONER"
anc_only =  dc_2012_2018[(dc_2012_2018['CONTEST_NAME'].str.contains("ANC")) | (dc_2012_2018['CONTEST_NAME'].str.contains("ADVISORY NEIGHBORHOOD COMMISSIONER")) ]
anc_only.ELECTION_DATE = anc_only.ELECTION_DATE.apply(pd.to_datetime)
anc_only.shape
anc_only.dtypes
anc_only.groupby(['ELECTION_DATE', 'SMD', 'CANDIDATE']).VOTES.sum()
# verify ward 8 against current officeholders
anc_only[anc_only.WARD==8].groupby(['ELECTION_DATE', 'SMD', 'CANDIDATE']).VOTES.sum()
#with pd.option_context("max.rows", 300):
    #print(dc_2012_2018.CONTEST_NAME.value_counts())
df = anc_only.groupby(['ELECTION_DATE', 'SMD', 'CANDIDATE']).VOTES.sum()
df = df.reset_index()
grouper = df.groupby(['ELECTION_DATE', 'SMD'])
# Number of candidates in each SMD ANC race. Usually if there are 2 "candidates" in the race, the winner was unopposed as the other "candidate"
# were the pile of write-ins.
grouper.CANDIDATE.count()
# there are 296 SMDs as per https://thedcline.org/2018/08/14/districts-296-anc-races-draw-as-many-as-five-candidates-but-two-thirds-are-uncontested/
df.SMD.value_counts()
df
df['WARD'] = df.SMD.str[:1]
df.tail()
# save progress
df.to_csv('anc_electoral_history_2012_2018.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12532552083333334, 'fraction_numerical': 0.0712890625, 'mean_word_length': 6.89974293059126, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 13, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '543336', 'license': 'MIT', 'n_tokens_mistral': 1263, 'n_tokens_neox': 1063, 'n_words': 226, 'path': 'scripts/anc_elections.ipynb', 'repo_name': 'amackcrane/ancElectionHistory', 'size': '3072'}