me2020.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 329,
   "id": "respiratory-symphony",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import electioncleaner as EC\n",
    "import csv\n",
    "pd.options.display.max_columns = 100\n",
    "pd.options.display.max_rows = 800"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 330,
   "id": "ordinary-expert",
   "metadata": {},
   "outputs": [],
   "source": [
    "# converts to long form, removes double counting\n",
    "def format_data(df, office):\n",
    "    df = df.rename(columns={\"TOWN\":\"MUNICIPALITY\",\"CTY\":\"COUNTY\",\"DIST\":\"DISTRICT\"})\n",
    "    df = df.iloc[1:]\n",
    "    if isinstance(df.iloc[0,df.columns.get_loc(\"MUNICIPALITY\")+1],str): #if party/writein available, add to candidate\n",
    "        df.columns = (df.columns+'-'+df.iloc[0,:].fillna('')).str.strip('-')\n",
    "    df['MUNICIPALITY'] = df['MUNICIPALITY'].astype(str)\n",
    "    df = df[~((df.MUNICIPALITY.str.contains('Total'))|(df.MUNICIPALITY.str.contains('nan'))|(df.MUNICIPALITY.str.contains('Tottal'))|(df.MUNICIPALITY.str.contains('total')))]\n",
    "    df = df.drop(columns={col for col in list(df.columns) if 'Unnamed' in col})\n",
    "    if 'DISTRICT' in df.columns:\n",
    "        i_d = df.columns[:3].tolist()\n",
    "        val = df.columns[3:].tolist()\n",
    "    else:\n",
    "        i_d = df.columns[:2].tolist()\n",
    "        val = df.columns[2:].tolist()\n",
    "    df = pd.melt(df, id_vars=i_d, value_vars=val,value_name='votes',var_name='candidate')\n",
    "    df['votes'] = df['votes'].astype(int)\n",
    "    df['office'] = office\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 331,
   "id": "manual-hypothesis",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# created separate format function to deal with one raw file that was different than the rest\n",
    "def format_acf(df,office):\n",
    "    df = df.rename(columns={\"TOWN\":\"MUNICIPALITY\",\"CTY\":\"COUNTY\",\"DIST\":\"DISTRICT\"})\n",
    "    if isinstance(df.iloc[0,df.columns.get_loc(\"MUNICIPALITY\")+1],str):\n",
    "        df = df.iloc[1:]\n",
    "    df= df.iloc[:-1]\n",
    "    df['MUNICIPALITY'] = df['MUNICIPALITY'].astype(str)\n",
    "    df = df.drop(columns={col for col in list(df.columns) if 'Unnamed' in col})\n",
    "    i_d = df.columns[:4].tolist()\n",
    "    val = df.columns[4:].tolist()\n",
    "    df = pd.melt(df, id_vars=i_d, value_vars=val,value_name='votes',var_name='candidate')\n",
    "    df['votes'] = df['votes'].astype(int)\n",
    "    df['office'] = office\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 332,
   "id": "current-prague",
   "metadata": {},
   "outputs": [],
   "source": [
    "def fix_office(df):\n",
    "    old_titles=sorted(list(df['office'].unique()))\n",
    "    office_titles = ['Finance Committee','County Commissioner',\n",
    "                     'Judge of Probate','Budget Committee','US PRESIDENT',\n",
    "                     'Register of Probate', 'US HOUSE','STATE HOUSE','Sheriff','STATE SENATE','US SENATE']\n",
    "    office_dict = dict(zip(old_titles, office_titles))\n",
    "    df = df.replace(office_dict)\n",
    "    df['office']=df['office'].str.upper()\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 333,
   "id": "likely-stylus",
   "metadata": {},
   "outputs": [],
   "source": [
    "def split_candidate_party(df):\n",
    "    df['candidate'] = df['candidate'].str.upper().str.replace('WRITE-IN','WRITEIN').str.replace('PREFERRED CANDIDATE- ','')\n",
    "    cand_party_list=[i.split('-') for i in df['candidate']]\n",
    "    cand_party_list=[i+[''] if len(i)<2 else i for i in cand_party_list]\n",
    "    cand = [' '.join(reversed(i[0].split(', '))) for i in cand_party_list]\n",
    "    party = [i[1] for i in cand_party_list]\n",
    "    df['party_detailed'] = party\n",
    "    df['party_detailed'] = df['party_detailed'].str.replace('DEMOCRATIC','DEMOCRAT',regex=False)\n",
    "    df['candidate']= cand\n",
    "    df['candidate'] = df['candidate'].str.replace('.','',regex=True).str.replace('  ',' ',regex=True)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 334,
   "id": "threatened-translation",
   "metadata": {},
   "outputs": [],
   "source": [
    "def fix_writein(df):  \n",
    "#     df['writein'] = np.where((df['candidate'].str.contains('WRITE'))|(df['party_detailed'].str.contains('WRITE')),\n",
    "#                             True, False)\n",
    "    df['candidate'] = df['candidate'].str.replace('\\(WRITEIN\\)','',regex=True)\n",
    "    df['party_detailed'] = np.where(df['party_detailed'].str.contains('WRITE'),'',df['party_detailed'])\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 335,
   "id": "published-biology",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_party_simplified(x):\n",
    "    if x in ['DEMOCRAT','REPUBLICAN','NONPARTISAN',\"LIBERTARIAN\"]: return x\n",
    "    if x == '': return ''\n",
    "    else: return \"OTHER\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 336,
   "id": "everyday-philadelphia",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def fix_district(x):\n",
    "    if x == 'I': return '001'\n",
    "    if x == 'III': return '003'\n",
    "    if x>0: return str(x).split('.0')[0].zfill(3)\n",
    "    else: return ''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 337,
   "id": "cutting-donna",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_mode(x):\n",
    "    if x == 'STATE UOCAVA': return 'UOCAVA'\n",
    "    else: return 'TOTAL'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 338,
   "id": "spectacular-aaron",
   "metadata": {},
   "outputs": [],
   "source": [
    "def fix_county(df):\n",
    "    county_fips=pd.read_csv('/Users/declanchin/Desktop/MEDSL/2020-precincts/help-files/county-fips-codes.csv')\n",
    "    county_fips = county_fips[county_fips['state']=='Maine']\n",
    "    absentee_index=list(df[df['MUNICIPALITY']=='STATE UOCAVA'].index)\n",
    "    for i in absentee_index:\n",
    "        df['COUNTY'].iloc[i] = df['COUNTY'].iloc[i-1]\n",
    "    df['candidate'] = np.where((df['MUNICIPALITY']=='STATE UOCAVA'),df['candidate']+' - UOCAVA TOTAL',df['candidate'])\n",
    "    df['MUNICIPALITY'] = df['MUNICIPALITY'].replace('STATE UOCAVA','')\n",
    "    old_titles=sorted(df['COUNTY'].unique())\n",
    "    new_titles = sorted(county_fips['county_name'].unique())\n",
    "    county_dict = dict(zip(old_titles, new_titles))\n",
    "    df = df.replace(county_dict)\n",
    "    df = df.rename(columns={\"DISTRICT\":'district','COUNTY':'county_name','MUNICIPALITY':'precinct'})#fix column names\n",
    "    df=df.merge(county_fips, on='county_name')\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 339,
   "id": "reduced-channel",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_dataverse(x):\n",
    "    if x =='US PRESIDENT': return 'PRESIDENT'\n",
    "    if x == 'US HOUSE': return 'HOUSE'\n",
    "    if x =='US SENATE': return 'SENATE'\n",
    "    if x in ['STATE SENATE', 'STATE HOUSE']: return 'STATE'\n",
    "    else: return 'LOCAL'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 340,
   "id": "superb-navigator",
   "metadata": {},
   "outputs": [],
   "source": [
    "def fix_candidate(x):\n",
    "    if 'BLANK (NO CANDIDATE)' in x: return x.replace('BLANK (NO CANDIDATE)','UNDERVOTES')\n",
    "    if 'BLANK' in x: return x.replace('BLANK','UNDERVOTES')\n",
    "    if 'OTHERS' in x: return x.replace('OTHERS','WRITEIN')\n",
    "    if x=='ROQUE DE LA FUENTE': return 'ROQUE \"ROCKY\" DE LA FUENTE'\n",
    "    if 'III ' in x: \n",
    "        if 'UOCAVA' in x: \n",
    "            return ('').join(x.replace(' - UOCAVA TOTAL','').split('III '))+' III' + ' - UOCAVA TOTAL'\n",
    "        else: return ('').join(x.split('III '))+' III'\n",
    "    if 'II ' in x: \n",
    "        if 'UOCAVA' in x: \n",
    "            return ('').join(x.replace(' - UOCAVA TOTAL','').split('II '))+' II' + ' - UOCAVA TOTAL'\n",
    "        else: return ('').join(x.split('II '))+' II'\n",
    "    if 'IV ' in x: \n",
    "        if 'UOCAVA' in x: \n",
    "            return ('').join(x.replace(' - UOCAVA TOTAL','').split('IV '))+' IV' + ' - UOCAVA TOTAL'\n",
    "        else: return ('').join(x.split('IV '))+' IV'\n",
    "    if 'SR ' in x: \n",
    "        if 'UOCAVA' in x: \n",
    "            return ('').join(x.replace(' - UOCAVA TOTAL','').split('SR '))+' SR' + ' - UOCAVA TOTAL'\n",
    "        else: return ('').join(x.split('SR '))+' SR' \n",
    "    if 'JR ' in x: \n",
    "        if 'UOCAVA' in x: \n",
    "            return ('').join(x.replace(' - UOCAVA TOTAL','').split('JR '))+' JR' + ' - UOCAVA TOTAL'\n",
    "        else: return ('').join(x.split('JR '))+' JR' \n",
    "    else: return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 342,
   "id": "general-pierce",
   "metadata": {},
   "outputs": [],
   "source": [
    "# loops through all raw files, and applies the appropriate function to convert to long format\n",
    "# \n",
    "# most of the data (non-statewide) is reported in stacked tables for different districts in a given race.\n",
    "# see documentation in loop\n",
    "\n",
    "path_to_raw = '/Users/declanchin/Desktop/MEDSL/2020-precincts/precinct/ME/raw/'\n",
    "statewide_files = ['ussenator1120.xlsx','repcongress1120.xlsx','presandvisecnty1120.xlsx']\n",
    "all_files = [f for f in os.listdir(path_to_raw) if '1120' in f]\n",
    "df_list = []\n",
    "for file in all_files:\n",
    "    if file in statewide_files:\n",
    "        if file == 'repcongress1120.xlsx':\n",
    "            dist1 = pd.read_excel(path_to_raw+file,sheet_name=0)\n",
    "            dist2 = pd.read_excel(path_to_raw+file,sheet_name=1)\n",
    "            df = pd.concat([format_data(dist1,file),format_data(dist2,file)])\n",
    "            df_list = df_list + [df]\n",
    "        else:\n",
    "            df = pd.read_excel(path_to_raw+file)\n",
    "            df = format_data(df,file)\n",
    "            df_list = df_list + [df]\n",
    "    else:\n",
    "        # this for loop parses through the stacked data and returns a distinct dataframe for each one within\n",
    "        # a given excel file. doneso thru utlizing the index of null rows that separate stacked tables.\n",
    "        # **altered state senate raw data by adding null rows in between stacked tables**\n",
    "        stacked_df=pd.read_excel(path_to_raw+file)\n",
    "        separator_index=list(stacked_df.index[stacked_df.isna().all(axis=1)])\n",
    "        xl = pd.ExcelFile(path_to_raw+file)\n",
    "        stack_list = []\n",
    "        for i in np.arange(len(separator_index)+1):\n",
    "            if i ==0:\n",
    "                df = xl.parse(0, skipfooter= (len(stacked_df)-separator_index[0]))\n",
    "                stack_list = stack_list + [df]\n",
    "            elif i in np.arange(len(separator_index)):\n",
    "                df = xl.parse(0, skiprows=(separator_index[i-1]+2),skipfooter= len(stacked_df)-separator_index[i])\n",
    "                stack_list = stack_list + [df]\n",
    "            else:\n",
    "                df = xl.parse(0, skiprows=(separator_index[i-1]+2))\n",
    "                stack_list = stack_list + [df]\n",
    "        stack_list = [i for i in stack_list if len(i)>0] #removed df created as a result of multiple null rows\n",
    "        if file == 'acf1120.xlsx':\n",
    "            stack_formated = [format_acf(i,file) for i in stack_list] \n",
    "            df_list = df_list + stack_formated\n",
    "        else: \n",
    "            stack_formated = [format_data(i,file) for i in stack_list]\n",
    "            df_list = df_list + stack_formated\n",
    "df=pd.concat(df_list).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 343,
   "id": "swiss-piece",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/core/indexing.py:1637: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  self._setitem_single_block(indexer, value, name)\n"
     ]
    }
   ],
   "source": [
    "#run all functions\n",
    "df=fix_office(df)\n",
    "df=split_candidate_party(df)\n",
    "df=fix_writein(df)\n",
    "df['party_simplified'] = df.party_detailed.apply(get_party_simplified)\n",
    "df['DISTRICT'] = df.DISTRICT.apply(fix_district)\n",
    "#retain info in \"AREA\" field and place in the district field (only for acf)\n",
    "df['DISTRICT']= np.where(df['AREA'].notnull(),df['DISTRICT'].astype(str) + ', AREA ' + df['AREA'].astype(str).str.strip('\\.0'),\n",
    "                            df['DISTRICT'])\n",
    "df = df.drop(columns = \"AREA\")\n",
    "df['mode'] = df.MUNICIPALITY.apply(get_mode)\n",
    "df = fix_county(df)\n",
    "df['candidate'] = df.candidate.apply(fix_candidate)\n",
    "df=df[~df['candidate'].str.contains('TBC')] #drop totals (double count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 344,
   "id": "complimentary-supervision",
   "metadata": {},
   "outputs": [],
   "source": [
    "# add year, stage, state, date, office, jurisdiction,special\n",
    "jurisdiction_fips = pd.read_csv('/Users/declanchin/Desktop/MEDSL/2020-precincts/help-files/jurisdiction-fips-codes.csv')\n",
    "jurisdiction_fips = jurisdiction_fips[jurisdiction_fips['state']=='Maine'].drop(columns='state')\n",
    "df['jurisdiction_name'] = df['precinct'].str.upper()\n",
    "df=df.merge(jurisdiction_fips, on='jurisdiction_name', how='left')\n",
    "df['jurisdiction_fips'] = df['jurisdiction_fips'].fillna('').astype(str).str.strip('\\.0')\n",
    "\n",
    "df['jurisdiction_fips'] = np.where(df['mode']=='UOCAVA', df['county_fips'],df['jurisdiction_fips'])\n",
    "df['jurisdiction_name'] = np.where(df['mode']=='UOCAVA', df['county_name'],df['jurisdiction_name'])\n",
    "\n",
    "df['year']= 2020\n",
    "df['state'] = 'MAINE'\n",
    "df['date']= '2020-11-03'\n",
    "df['readme_check'] = 'FALSE'\n",
    "df['writein'] = EC.series_r_bool((df['candidate'].str.contains('WRITE'))|(df['party_detailed'].str.contains('WRITE')))\n",
    "df['stage']='GEN'\n",
    "df['magnitude']=1\n",
    "df['special'] = np.where(((df['office']=='COUNTY COMMISSIONER')&(df['district']=='002')&(df['county_name']=='ANDROSCOGGIN')),\n",
    "                        'TRUE','FALSE')\n",
    "df['candidate'] = df['candidate'].str.replace('  ', ' ')\n",
    "# state codes\n",
    "state_codes = pd.read_csv('/Users/declanchin/Desktop/MEDSL/2020-precincts/help-files/merge_on_statecodes.csv')\n",
    "state_codes = state_codes[state_codes['state']=='Maine']\n",
    "state_codes['state'] = state_codes['state'].str.upper()\n",
    "df=df.merge(state_codes, on='state', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 345,
   "id": "concrete-lancaster",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-345-74dcb1d48e9e>:43: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  j_p_mismatches_unique['jurisdiction']=j_p_mismatches_unique['jurisdiction'].str.upper()\n",
      "<ipython-input-345-74dcb1d48e9e>:44: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  j_p_mismatches_unique['precinct']=j_p_mismatches_unique['precinct'].str.upper()\n"
     ]
    }
   ],
   "source": [
    "### Creates crosswalk for townships that do not match with our jurisdiction_fips.csv\n",
    "### First utilizes string matching, then 2018-precincts matching to account for townships not found in \n",
    "### jurisdiction-fips file. Takes in df then returns df with converted township names for precincts with missing\n",
    "### jurisdiction_fips information. \n",
    "\n",
    "def township_jurisdiction_crosswalk(df):\n",
    "    # finds rows with no jurisdiction fips after initial merge\n",
    "    to_crosswalk=df[df['jurisdiction_fips']==''][['county_fips','jurisdiction_name']]\n",
    "    to_crosswalk=to_crosswalk[to_crosswalk['jurisdiction_name']!='']\n",
    "\n",
    "    # performs merge again, but retains jurisdiction names of each respective file\n",
    "    fips = pd.read_csv('/Users/declanchin/Desktop/MEDSL/2020-precincts/help-files/jurisdiction-fips-codes.csv')\n",
    "    fips=fips[fips['state']=='Maine']\n",
    "    fips['county_fips']=fips['jurisdiction_fips'].astype(str).str[:5].astype(int)\n",
    "    crosswalk=to_crosswalk.merge(fips, on='county_fips',how='inner',suffixes=('_raw','_file'))\n",
    "    # fips['county_fips']=fips['jurisdiction_fips'].astype(str).str[:5].astype(int)\n",
    "    # crosswalk=to_crosswalk.merge(fips, on='county_fips',how='inner',suffixes=('_raw','_file'))\n",
    "\n",
    "    # removes slashes and whitespace from each jurisdiction name from each file\n",
    "    crosswalk['jurisdiction_first_raw']= [i[0] for i in crosswalk['jurisdiction_name_raw'].str.replace('/',' ').str.split(' ')]\n",
    "    crosswalk['jurisdiction_first_file']= [i[0] for i in crosswalk['jurisdiction_name_file'].str.replace('/',' ').str.split(' ')]\n",
    "    #loop to str match raw to file jurisdictions based on the first word of each \n",
    "    index_list=[]\n",
    "    for i in crosswalk['jurisdiction_name_raw'].unique():\n",
    "        sub = crosswalk[crosswalk['jurisdiction_name_raw']==i]\n",
    "        if sum(sub['jurisdiction_first_raw']==sub['jurisdiction_first_file'])>0:\n",
    "            index=list(sub[sub['jurisdiction_first_raw']==sub['jurisdiction_first_file']].index)\n",
    "            index_list = index_list+index\n",
    "\n",
    "    crosswalk_matched=crosswalk.iloc[index_list].drop_duplicates()\n",
    "    # removes false positive matches, then retains the two columns needed to crosswalk\n",
    "    crosswalk_matched=crosswalk_matched[~crosswalk_matched['jurisdiction_name_file'].isin(['FORT FAIRFIELD','EAST CENTRAL WASHINGTON UT'])]\n",
    "    crosswalk_matched=crosswalk_matched.drop(crosswalk_matched[(crosswalk_matched['jurisdiction_name_raw'] == 'RANGELEY/ADAMSTOWN TWP') & (crosswalk_matched['jurisdiction_name_file'] == 'RANGELEY PLANTATION')].index)\n",
    "    crosswalk_matched=crosswalk_matched.drop(crosswalk_matched[(crosswalk_matched['jurisdiction_name_raw'] == 'RANGELEY PLT') & (crosswalk_matched['jurisdiction_name_file'] == 'RANGELEY')].index).sort_values('jurisdiction_first_raw')\n",
    "    crosswalk_matched=crosswalk_matched[['jurisdiction_name_raw','jurisdiction_name_file']]\n",
    "\n",
    "    # this creates another crosswalk from the 2018 data to be used on any remaining blank jurisdiction fips\n",
    "    # after merging with the above crosswalk.\n",
    "    me_2018 = pd.read_csv('/Users/declanchin/Desktop/MEDSL/2020-precincts/precinct/ME/raw/2018-me-precinct.csv')\n",
    "    me_2018 = me_2018[~((me_2018['precinct']=='County Totals')|(me_2018['precinct']=='STATE UOCAVA'))]\n",
    "    j_p_mismatches=me_2018[(me_2018['jurisdiction']!=me_2018['precinct'])][['jurisdiction','precinct']]\n",
    "    j_p_mismatches_unique = j_p_mismatches.drop_duplicates()\n",
    "    j_p_mismatches_unique['jurisdiction']=j_p_mismatches_unique['jurisdiction'].str.upper()\n",
    "    j_p_mismatches_unique['precinct']=j_p_mismatches_unique['precinct'].str.upper()\n",
    "    j_p_mismatches_unique = j_p_mismatches_unique.rename(columns={'precinct':'jurisdiction_name_raw',\n",
    "                                                                  'jurisdiction':'jurisdiction_name_file'})\n",
    "\n",
    "    #this contains info from str matched precinct/jurisdiction combos (prioritized) and 2018 unique precinct/jurisdiction pairs\n",
    "    complete_crosswalk=pd.concat([crosswalk_matched, j_p_mismatches_unique]).drop_duplicates('jurisdiction_name_raw', keep='first')\n",
    "    complete_crosswalk=complete_crosswalk.rename(columns={'jurisdiction_name_raw':'jurisdiction_name'})\n",
    "    saint_fixes=pd.DataFrame([['SAINT ALBANS','ST. ALBANS'],\n",
    "                          ['SAINT AGATHA/SINCLAIR','ST. AGATHA'],\n",
    "                          ['SAINT FRANCIS','ST. FRANCIS'],\n",
    "                          ['SAINT JOHN PLT','ST. JOHN PLANTATION'],\n",
    "                          ['SAINT GEORGE','ST. GEORGE']],columns=['jurisdiction_name','jurisdiction_name_file'])\n",
    "    complete_crosswalk = pd.concat([saint_fixes,complete_crosswalk])\n",
    "    complete_crosswalk=complete_crosswalk.drop_duplicates(subset = 'jurisdiction_name', keep = 'first')\n",
    "\n",
    "    # left merger on original df using the complete crosswalk, but creates a new column to ensure no information is \n",
    "    # overided. Then only retain new jurisdiction names for rows with empty jurisdiction fips codes \n",
    "    df=df.merge(complete_crosswalk, how='left', on='jurisdiction_name')\n",
    "    df['jurisdiction_name'] = np.where(df['jurisdiction_fips']=='',df['jurisdiction_name_file'],df['jurisdiction_name'])\n",
    "\n",
    "    # now merges original fips file, and retains fips for blanks that were addressed by the two crosswalks.\n",
    "    # Reassigned blanks to unmatched jurisdictions to county names. and assign blank juri-fips to county-fips\n",
    "    fips_file = pd.read_csv('/Users/declanchin/Desktop/MEDSL/2020-precincts/help-files/jurisdiction-fips-codes.csv')\n",
    "    fips_file['state'] = fips_file['state'].str.upper()\n",
    "    fips_file['county_fips'] = fips_file['jurisdiction_fips'].apply(lambda fips: int(str(fips)[:5]))\n",
    "    df = df.merge(fips_file, on=['state', 'county_fips', 'jurisdiction_name'], how=\"left\")\n",
    "    df = df.rename(columns={'jurisdiction_fips_y':'jurisdiction_fips'})\n",
    "    df['jurisdiction_fips'] = df['jurisdiction_fips'].fillna('')\n",
    "    df['jurisdiction_name'] = np.where(df['jurisdiction_fips']=='', df['county_name'], df['jurisdiction_name'])\n",
    "    df['jurisdiction_fips'] = np.where(df['jurisdiction_fips']=='', df['county_fips'], df['jurisdiction_fips'])\n",
    "    df['jurisdiction_fips'] = df['jurisdiction_fips'].astype(int)\n",
    "    return df\n",
    "df=township_jurisdiction_crosswalk(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 346,
   "id": "elegant-paintball",
   "metadata": {},
   "outputs": [],
   "source": [
    "#updating readme\n",
    "df['jurisdiction_fips']=df['jurisdiction_fips'].astype(str)\n",
    "def readme_check(x):\n",
    "    if len(x) == 5: return 'TRUE'\n",
    "    else: return 'FALSE'\n",
    "df['readme_check'] = df.jurisdiction_fips.apply(readme_check) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 347,
   "id": "unknown-crawford",
   "metadata": {},
   "outputs": [],
   "source": [
    "# corrections\n",
    "\n",
    "\n",
    "# revert UOCAVA total to county floating\n",
    "df['precinct'] = np.where(df['candidate'].str.contains('UOCAVA'), 'COUNTY FLOATING', df['precinct'])\n",
    "df['candidate'] = df['candidate'].str.replace(' - UOCAVA TOTAL','')\n",
    "df['candidate'] = df['candidate'].replace('ROQUE DE LA FUENTE','ROQUE \"ROCKY\" DE LA FUENTE')\n",
    "# President and US senate to statewide district\n",
    "df['district'] = np.where((df['office']=='US PRESIDENT')|(df['office']=='US SENATE'),\n",
    "                         'STATEWIDE', df['district'])\n",
    "\n",
    "# fixing STATEWIDE UOCAVA for federal elections\n",
    "federal_dic = {'precinct': 'STATEWIDE UOCAVA', 'county_name': 'STATEWIDE UOCAVA', 'county_fips': '23000',\n",
    "              'jurisdiction_name': 'STATEWIDE UOCAVA','jurisdiction_fips': '23000'}\n",
    "fed = ['US PRESIDENT', 'US HOUSE', 'US SENATE']\n",
    "for office in fed:\n",
    "    for field in federal_dic.keys():\n",
    "        df[field] = np.where((df['office']==office)&(df['mode']=='UOCAVA'), federal_dic[field],df[field])\n",
    "\n",
    "# relocating dataverse to fix mult problem\n",
    "df['dataverse'] = df.office.apply(get_dataverse)\n",
    "\n",
    "# fixing US President districts\n",
    "district_map=df[df['office']=='US HOUSE'][['precinct','county_name','district']].drop_duplicates()\n",
    "district_map = district_map[district_map['precinct']!='STATEWIDE UOCAVA']\n",
    "district_map['office'] = 'US PRESIDENT'\n",
    "district_map.columns = ['precinct','county_name','district_new','office']\n",
    "district_map.loc[len(district_map.index)] = ['Milo/Ornveille Twp', 'PISCATAQUIS', '002','US PRESIDENT'] # piscataquis is dist 2\n",
    "df = df.merge(district_map, on = ['precinct','county_name','office'],\n",
    "             how = 'left')\n",
    "df['district'] = np.where(df['district_new'].notnull(), df['district_new'], df['district'])\n",
    "\n",
    "# replacing UOCAVA pres votes with the correct district groupings\n",
    "# Originally used county sheet, so now manually replacing with the right breakdowns from dist. sheet\n",
    "\n",
    "#get uocava pres df, and correct candidate names\n",
    "uocava_pres=df[((df['office']=='US PRESIDENT')&(df['precinct']=='STATEWIDE UOCAVA'))].drop(columns=['votes','district'])\n",
    "correct_names=list(uocava_pres['candidate'])\n",
    "#read sheets with uocava by district info\n",
    "pres_dist1 = pd.read_excel('/Users/declanchin/Desktop/MEDSL/2020-precincts/precinct/ME/officialresults/presandvice1120.xlsx')\n",
    "pres_dist2 = pd.read_excel('/Users/declanchin/Desktop/MEDSL/2020-precincts/precinct/ME/officialresults/presandvice1120.xlsx', sheet_name = 1)\n",
    "#get dist 1 uocava df\n",
    "dist1=pres_dist1[pres_dist1['MUNICIPALITY'] == 'UOCAVA Dist 1']\n",
    "dist1 = dist1[['Biden, Joseph R.','De La Fuente, Roque','Hawkins, Howard','Jorgensen, Jo','Trump, Donald J.','Others','Blank']].T.reset_index().rename(columns={'index':'candidate',131:'votes'})\n",
    "dist1['candidate'] = dist1['candidate'].replace(dict(zip(list(dist1['candidate']), correct_names)))\n",
    "dist1['votes'] = dist1['votes'].astype(int)\n",
    "dist1['district'] = '001'\n",
    "dist1=uocava_pres.merge(dist1, on ='candidate', how = 'left')\n",
    "#get dist 1 uocava df\n",
    "dist2=pres_dist2[pres_dist2['MUNICIPALITY'] == 'UOCAVA CG2 Total']\n",
    "dist2 = dist2[['Biden, Joseph R.','De La Fuente, Roque','Hawkins, Howard','Jorgensen, Jo','Trump, Donald J.','Others','Blank']].T.reset_index().rename(columns={'index':'candidate',423:'votes'})\n",
    "dist2['candidate'] = dist2['candidate'].replace(dict(zip(list(dist2['candidate']), correct_names)))\n",
    "dist2['votes'] = dist2['votes'].astype(int)\n",
    "dist2['district'] = '002'\n",
    "dist2=uocava_pres.merge(dist2, on ='candidate', how = 'left')\n",
    "#dropping uocava pres columns and replacing with ones with district breakdown\n",
    "df = df[~((df['office']=='US PRESIDENT')&(df['precinct']=='STATEWIDE UOCAVA'))]\n",
    "df = pd.concat([df,dist1,dist2]).reset_index(drop=True)\n",
    "\n",
    "#reassigning mode to total to match previous years\n",
    "df['mode'] = 'TOTAL'\n",
    "\n",
    "#upper and trim precinct\n",
    "df['precinct'] = df['precinct'].str.upper().str.strip()\n",
    "\n",
    "#making non-partisan local offices\n",
    "df['party_detailed'] = np.where(((df['party_detailed']=='')&(df['candidate']!='UNDERVOTES')&(df['candidate']!='WRITEIN')), \n",
    "                                'NONPARTISAN',df['party_detailed'])\n",
    "#reapply\n",
    "df['party_simplified'] = df.party_detailed.apply(get_party_simplified)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 354,
   "id": "blind-colombia",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[['precinct', 'office', 'party_detailed', 'party_simplified', 'mode',\n",
    "       'votes', 'county_name', 'county_fips', 'jurisdiction_name',\n",
    "       'jurisdiction_fips', 'candidate', 'district', 'magnitude', 'dataverse',\n",
    "       'year', 'stage', 'state', 'special', 'writein', 'state_po',\n",
    "       'state_fips', 'state_cen', 'state_ic', 'date', 'readme_check']]\n",
    "\n",
    "df.to_csv('2020-me-precinct-general.csv',index=False, quoting=csv.QUOTE_NONNUMERIC)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "democratic-madrid",
   "metadata": {},
   "outputs": [],
   "source": [
    "#knox budget committee non-partisan,https://www.mainelegislature.org/legis/statutes/30-A/title30-Asec751.html\n",
    "#aroostook finance committee non partisan, http://legislature.maine.gov/statutes/30-A/title30-Asec739.html\n",
    "# CC nonpartisan"
   ]
  }
 ],
 "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.9.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}