ri2020.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "import math\n",
    "from string import digits\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "file = '/Users/declanchin/Desktop/MEDSL/2020-precincts/precinct/RI/raw/RI_precincts.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(columns='Unnamed: 0', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "# uppercasing all columns\n",
    "df = df.applymap(lambda x:x.upper() if type(x) == str else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "# turning boolean values into strings\n",
    "def fix_boolean(x):\n",
    "    if x == True:\n",
    "        return 'TRUE'\n",
    "    else:\n",
    "        return 'FALSE'\n",
    "df['special'] = df['special'].apply(fix_boolean)\n",
    "df['writein'] = df['writein'].apply(fix_boolean)\n",
    "df['readme_check'] = 'FALSE'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "# making certain columns strings\n",
    "df['state_fips'] = df['state_fips'].astype(str)\n",
    "df['state_cen'] = df['state_cen'].astype(str)\n",
    "df['state_ic'] = df['state_ic'].astype(str)\n",
    "df['county_fips'] = df['county_fips'].apply(lambda x:str(int(x)) if math.isnan(x) == False else '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "def cleanCandidate(x):\n",
    "    if x == 'ROQUE ROCKY DE LA FUENTE':\n",
    "        return 'ROQUE \"ROCKY\" DE LA FUENTE'\n",
    "    if x == 'WILLIAM W O BRIEN':\n",
    "        return \"WILLIAM W O'BRIEN\"\n",
    "    if x=='WILLIAM R O CONNELL':\n",
    "        return \"WILLIAM R O'CONNELL\"\n",
    "    if x=='WILLIAM M O DELL':\n",
    "        return \"WILLIAM M O'DELL\"\n",
    "    if x=='MARCIA P RANGLIN VASSELL':\n",
    "        return 'MARCIA P RANGLIN-VASSELL'\n",
    "    if x=='BARBARA ANN FENTON FUNG':\n",
    "        return 'BARBARA ANN FENTON-FUNG'\n",
    "    if x=='CAMILLE F VELLA WILKINSON':\n",
    "        return 'CAMILLE F VELLA-WILKINSON'\n",
    "    if x=='RICHARD P D ADDARIO':\n",
    "        return \"RICHARD P D'ADDARIO\"\n",
    "    if x=='TERRI DENISE CORTVRIEND':\n",
    "        return 'TERRI-DENISE CORTVRIEND'\n",
    "    if x=='MARGARET M O NEILL':\n",
    "        return \"MARGARET M O'NEILL\"\n",
    "    if x=='CLAIRE VALLIERES O HARA':\n",
    "        return \"CLAIRE VALLIERES O'HARA\"\n",
    "    if x=='JOHN F D AMICO':\n",
    "        return \"JOHN F D'AMICO\"\n",
    "    if x=='SARA K TINDALL WOODMAN':\n",
    "        return 'SARA K TINDALL-WOODMAN'\n",
    "    if x=='AUDREY J MACLEOD PFEIFFER':\n",
    "        return 'AUDREY J MACLEOD-PFEIFFER'\n",
    "    if x=='LISA BALDELLI HUNT':\n",
    "        return 'LISA BALDELLI-HUNT'\n",
    "    if x=='ELIZABETH J COOK MARTIN':\n",
    "        return 'ELIZABETH J COOK-MARTIN'\n",
    "    if x=='JEANNE MARIE NAPOLITANO':\n",
    "        return 'JEANNE-MARIE NAPOLITANO'\n",
    "    if x == 'WRITE IN':\n",
    "        return 'WRITEIN'\n",
    "    if x == 'APPROVE':\n",
    "        return 'YES'\n",
    "    if x == 'REJECT':\n",
    "        return 'NO'\n",
    "    else:\n",
    "        return x\n",
    "df['candidate'] = df['candidate'].apply(cleanCandidate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['precinct'] = df['precinct'].str.rstrip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge with jurisdiction fips csv\n",
    "jurisdiction_fips = pd.read_csv('../../help-files/jurisdiction-fips-codes.csv')\n",
    "jurisdiction_fips = jurisdiction_fips[jurisdiction_fips['state']=='Rhode Island'].drop(columns='state')\n",
    "jurisdiction_fips['county_fips'] = jurisdiction_fips['jurisdiction_fips'].apply(lambda fips: str(fips)[:5])\n",
    "df = df.merge(jurisdiction_fips, on=['jurisdiction_name', 'county_fips'], how='left')\n",
    "df['jurisdiction_fips'] = df['jurisdiction_fips'].fillna('')\n",
    "df['jurisdiction_fips'] = df['jurisdiction_fips'].apply(lambda x:str(int(x)) if x != '' else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "def providenceLimited(x, y):\n",
    "    if x == 'PROVIDENCE LIMITED':\n",
    "        return '44007'\n",
    "    return y\n",
    "df['jurisdiction_fips'] = df.apply(lambda x: providenceLimited(x['jurisdiction_name'], x['jurisdiction_fips']), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "def cleanDistrict(x):\n",
    "    if 'DISTRICT' in str(x):\n",
    "        dist_num = ''.join([str(n) for n in list(x) if n.isdigit()])\n",
    "        return str(dist_num).zfill(3)\n",
    "    else:\n",
    "        return x\n",
    "df['district'] = df['district'].apply(cleanDistrict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "def cleanParty(x, y):\n",
    "    x = x.strip()\n",
    "    if 'WRITEIN' in y:\n",
    "        return ''\n",
    "    if 'DEM' in x:\n",
    "        return 'DEMOCRAT'\n",
    "    if 'REP' in x:\n",
    "        return 'REPUBLICAN'\n",
    "    if 'S&L' in x:\n",
    "        return 'PARTY FOR SOCIALISM AND LIBERATION'\n",
    "    if 'NON' in x:\n",
    "        return 'NONPARTISAN'\n",
    "    if 'ALL' in x:\n",
    "        return 'ALLIANCE'\n",
    "    if 'AS' in x:\n",
    "        return 'AMERICAN SOLIDARITY'\n",
    "    if 'IND' in x:\n",
    "        return 'INDEPENDENT'\n",
    "    if 'LIB' in x:\n",
    "        return 'LIBERTARIAN'\n",
    "    if 'GRN' in x:\n",
    "        return 'GREEN'\n",
    "    else:\n",
    "        return x\n",
    "df['party_detailed'] = df.apply(lambda x: cleanParty(x['party_detailed'], x['candidate']), axis=1)\n",
    "df['party_simplified'] = df['party_detailed'].replace({'PARTY FOR SOCIALISM AND LIBERATION': 'OTHER', 'ALLIANCE': 'OTHER', 'GREEN': 'OTHER', 'AMERICAN SOLIDARITY': 'OTHER', 'INDEPENDENT' : 'OTHER', 'GG' : 'OTHER'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "def cleanOffice(x):\n",
    "#     x = x.lstrip(digits)\n",
    "    x = x.lstrip('. ')\n",
    "    if 'SENATOR IN CONGRESS' in x:\n",
    "        return 'US SENATE'\n",
    "    if 'PRESIDENT' in x:\n",
    "        return 'US PRESIDENT'\n",
    "    if 'REPRESENTATIVE IN CONGRESS' in x:\n",
    "        return 'US HOUSE'\n",
    "    if 'REPRESENTATIVE IN GENERAL ASSEMBLY' in x:\n",
    "        return 'STATE HOUSE'\n",
    "    if 'SENATOR IN GENERAL ASSEMBLY' in x:\n",
    "        return 'STATE SENATE'\n",
    "    x = x.strip()\n",
    "    x = x.replace('  ', ' ')\n",
    "    if x=='TOWN COUNCIL  TOWN OF CUMBERLAND':\n",
    "        return 'TOWN COUNCIL TOWN OF CUMBERLAND'\n",
    "    return x\n",
    "df['office'] = df['office'].apply(cleanOffice)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "def cleanDataverse(x, y):\n",
    "    if any(s in x for s in ['STATE HOUSE', 'STATE SENATE']):\n",
    "        return 'STATE'\n",
    "    else:\n",
    "        return y\n",
    "df['dataverse'] = df.apply(lambda x: cleanDataverse(x['office'], x['dataverse']), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fixMagnitude(x):\n",
    "    if any(s in x for s in['TOWN COUNCIL TOWN OF BARRINGTON', 'SCHOOL COMMITTEE TOWN OF BARRINGTON', \n",
    "                           'NON-PARTISAN REGIONAL SCHOOL COMMITTEE TOWN OF BRISTOL', 'TOWN COUNCIL TOWN OF BURRILLVILLE',\n",
    "                           'NON-PARTISAN SCHOOL COMMITTEE TOWN OF BURRILLVILLE', 'COUNCIL - CITY WIDE CITY OF CRANSTON',\n",
    "                           'SCHOOL COMMITTEE TOWN OF EAST GREENWICH', 'NON-PARTISAN SCHOOL COMMITTEE TOWN OF GLOCESTER',\n",
    "                           'SCHOOL COMMITTEE TOWN OF JAMESTOWN', 'TOWN COUNCIL TOWN OF NEW SHOREHAM', 'NON-PARTISAN SCHOOL COMMITTEE TOWN OF NEW SHOREHAM',\n",
    "                           'SCHOOL COMMITTEE TOWN OF NORTH KINGSTOWN', 'NON-PARTISAN SCHOOL COMMITTEE NORTH SMITHFIELD',\n",
    "                           'COUNCIL AT - LARGE CITY OF PAWTUCKET', 'SCHOOL COMMITTEE TOWN OF PORTSMOUTH',\n",
    "                           'NON-PARTISAN SCHOOL COMMITTEE TOWN OF TIVERTON']):\n",
    "        return 3\n",
    "    if any(s in x for s in['TOWN COUNCIL TOWN OF BRISTOL', 'TOWN COUNCIL TOWN OF CHARLESTOWN', \n",
    "                           'TOWN COUNCIL TOWN OF EAST GREENWICH', 'TOWN COUNCIL TOWN OF EXETER',\n",
    "                           'TOWN COUNCIL TOWN OF FOSTER', 'TOWN COUNCIL TOWN OF GLOCESTER', 'TOWN COUNCIL TOWN OF HOPKINTON',\n",
    "                           'TOWN COUNCIL TOWN OF JAMESTOWN', 'TOWN COUNCIL TOWN OF LITTLE COMPTON', 'NON-PARTISAN TOWN COUNCIL TOWN OF NARRAGANSETT',\n",
    "                           'NON-PARTISAN SCHOOL COMMITTEE TOWN OF NARRAGANSETT', 'TOWN COUNCIL TOWN OF NORTH KINGSTOWN',\n",
    "                           'NON-PARTISAN TOWN COUNCIL TOWN OF NORTH SMITHFIELD', 'TOWN COUNCIL TOWN OF RICHMOND',\n",
    "                           'TOWN COUNCIL TOWN OF SMITHFIELD', 'TOWN COUNCIL TOWN OF SOUTH KINGSTOWN', 'NON-PARTISAN BUDGET COMMITTEE TOWN OF TIVERTON',\n",
    "                           'SCHOOL COMMITTEE TOWN OF WESTERLY', 'TOWN COUNCIL TOWN OF WEST GREENWICH', 'NON-PARTISAN SCHOOL COMMITTEE CITY OF WOONSOCKET']):\n",
    "        return 5\n",
    "    if any(s in x for s in['NON-PARTISAN CITY COUNCIL - AT LARGE', 'TOWN COUNCIL TOWN OF CUMBERLAND', 'NON-PARTISAN SCHOOL COMMITTEE',\n",
    "                           'NON-PARTISAN FIRE COMMITTEE TOWN OF CUMBERLAND', 'SCHOOL COMMITTEE TOWN OF EXETER', \n",
    "                           'NON-PARTISAN SCHOOL COMMITTEE- TOWN OF LINCOLN', 'SCHOOL COMMITTEE TOWN OF LITTLE COMPTON',\n",
    "                           'NON-PARTISAN SCHOOL COMMITTEE TOWN OF MIDDLETOWN', 'NON-PARTISAN BLOCK ISLAND LAND TRUST TRUSTEE', 'NON-PARTISAN BLOCK ISLAND HOUSING BOARD',\n",
    "                           'CHARIHO SCHOOL COMMITTEE TOWN OF RICHMOND', 'SCHOOL COMMITTEE TOWN OF SCITUATE',\n",
    "                           'SCHOOL COMMITTEE TOWN OF SMITHFIELD', 'TOWN COUNCIL TOWN OF WARREN', 'NON-PARTISAN BRISTOL/WARREN REGIONAL SCHOOL COMMITTEE',\n",
    "                           'NON-PARTISAN SCHOOL COMMITTEE CITY OF WARWICK']):\n",
    "        return 2\n",
    "    if any(s in x for s in['NON-PARTISAN PLANNING COMMISSION TOWN OF CHARLESTOWN', 'NON-PARTISAN COUNCIL- CITY OF NEWPORT',\n",
    "                           'SCHOOL COMMITTEE TOWN OF SOUTH KINGSTOWN']):\n",
    "        return 4\n",
    "    if any(s in x for s in['NON-PARTISAN TOWN COUNCIL TOWN OF MIDDLETOWN', 'NON-PARTISAN SCHOOL COMMITTEE CITY OF NEWPORT',\n",
    "                           'SCHOOL COMMITTEE CITY OF PAWTUCKET', 'TOWN COUNCIL TOWN OF PORTSMOUTH', 'TOWN COUNCIL TOWN OF SCITUATE',\n",
    "                           'NON-PARTISAN TOWN COUNCIL TOWN OF TIVERTON', 'TOWN COUNCIL TOWN OF WESTERLY', \n",
    "                           'NON-PARTISAN CITY COUNCIL CITY OF WOONSOCKET']):\n",
    "        return 7\n",
    "    if any(s in x for s in['NON-PARTISAN CHARTER REVIEW COMMISSION TOWN OF WARREN']):\n",
    "        return 9\n",
    "    return 1\n",
    "df['magnitude'] = df['office'].apply(fixMagnitude)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getDistrict(x, y):\n",
    "    if any(s in x for s in ['AT - LARGE', '- AT LARGE']):\n",
    "        return 'AT-LARGE'\n",
    "    else:\n",
    "        return y\n",
    "df['district'] = df.apply(lambda x: getDistrict(x['office'], x['district']), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "def standardize_office(x):\n",
    "    if ' AT - LARGE ' in x:\n",
    "        x = x.replace(' AT - LARGE ', ' ')\n",
    "    elif ' - AT LARGE' in x:\n",
    "        x = x.replace(' - AT LARGE', '')\n",
    "    if 'NON-PARTISAN ' in x:\n",
    "        x = x.replace('NON-PARTISAN ', '')\n",
    "    if x=='SCHOOL COMMITTE EAST PROVIDENCE':\n",
    "        return 'SCHOOL COMMITTEE - EAST PROVIDENCE'\n",
    "    if '- CITY OF ' in x:\n",
    "        x = x.replace('- CITY OF ', ' - ')\n",
    "        return x\n",
    "    if '- TOWN OF ' in x:\n",
    "        x = x.replace('- TOWN OF ', ' - ')\n",
    "        return x\n",
    "    if ' TOWN OF ' in x:\n",
    "        x = x.replace(' TOWN OF ', ' - ')\n",
    "        return x\n",
    "    elif 'TOWN COUNCIL ' in x:\n",
    "        x = 'TOWN COUNCIL - ' + x.split('TOWN COUNCIL ')[1]\n",
    "        return x\n",
    "    if ' CITY OF ' in x:\n",
    "        x = x.replace(' CITY OF ', ' - ')\n",
    "        return x\n",
    "    if 'COUNCIL ' in x:\n",
    "        x = x.replace('COUNCIL ', 'COUNCIL - ')\n",
    "        return x\n",
    "    if 'FIRE COMMITTEE ' in x:\n",
    "        x = x.replace('FIRE COMMITTEE ', 'FIRE COMMITTEE - ')\n",
    "        return x\n",
    "    if 'SCHOOL COMMITTEE ' in x:\n",
    "        if '-' not in x:\n",
    "            x = x.replace('SCHOOL COMMITTEE ', 'SCHOOL COMMITTEE - ')\n",
    "            return x\n",
    "        else:\n",
    "            return x\n",
    "    if x=='SCHOOL COMMITTEE':\n",
    "        return 'SCHOOL COMMITTEE - CUMBERLAND'\n",
    "    if x=='CITY COUNCIL':\n",
    "        return 'CITY COUNCIL - CENTRAL FALLS'\n",
    "    return x\n",
    "    \n",
    "df['office'] = df['office'].apply(standardize_office)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "column_names = ['precinct', 'office', 'party_detailed', 'party_simplified', 'mode', 'votes', \n",
    "                'county_name', 'county_fips', 'jurisdiction_name', 'jurisdiction_fips', 'candidate', \n",
    "                'district', 'dataverse', 'year', 'stage', 'state', 'special', 'writein', 'state_po', \n",
    "                'state_fips', 'state_cen', 'state_ic', 'date', 'readme_check', 'magnitude']\n",
    "df = df.reindex(columns=column_names)\n",
    "df.fillna('', inplace=True)\n",
    "\n",
    "# # some instances of near duplicate writein scattered rows in raw data. need to agg these votes within precinct and replace to prevent dups\n",
    "multi_federal_precinct_agg = df[df.drop(columns = 'votes').duplicated(keep = False)].groupby(list(df.drop(columns = 'votes').columns)).agg(sum).reset_index()\n",
    "# drop near duplicates\n",
    "df = df[~(df.drop(columns = 'votes').duplicated(keep = False))].copy()\n",
    "df = pd.concat([df,multi_federal_precinct_agg])\n",
    "\n",
    "df.to_csv('2020-ri-precinct-general.csv', index=False, quoting=csv.QUOTE_NONNUMERIC)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- not sure about \"GG\" party, tried to find info online but couldn't get anything on the town council position.\n",
    "- is year supposed to be coded as a string or number?\n",
    "- some missing county and jurisdiction info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "testdf = df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>office</th>\n",
       "      <th>party_detailed</th>\n",
       "      <th>party_simplified</th>\n",
       "      <th>mode</th>\n",
       "      <th>votes</th>\n",
       "      <th>county_name</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>jurisdiction_name</th>\n",
       "      <th>jurisdiction_fips</th>\n",
       "      <th>...</th>\n",
       "      <th>state</th>\n",
       "      <th>special</th>\n",
       "      <th>writein</th>\n",
       "      <th>state_po</th>\n",
       "      <th>state_fips</th>\n",
       "      <th>state_cen</th>\n",
       "      <th>state_ic</th>\n",
       "      <th>date</th>\n",
       "      <th>readme_check</th>\n",
       "      <th>magnitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [precinct, office, party_detailed, party_simplified, mode, votes, county_name, county_fips, jurisdiction_name, jurisdiction_fips, candidate, district, dataverse, year, stage, state, special, writein, state_po, state_fips, state_cen, state_ic, date, readme_check, magnitude]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 25 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "testdf[testdf['jurisdiction_fips']=='nan']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>office</th>\n",
       "      <th>party_detailed</th>\n",
       "      <th>party_simplified</th>\n",
       "      <th>mode</th>\n",
       "      <th>votes</th>\n",
       "      <th>county_name</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>jurisdiction_name</th>\n",
       "      <th>jurisdiction_fips</th>\n",
       "      <th>...</th>\n",
       "      <th>state</th>\n",
       "      <th>special</th>\n",
       "      <th>writein</th>\n",
       "      <th>state_po</th>\n",
       "      <th>state_fips</th>\n",
       "      <th>state_cen</th>\n",
       "      <th>state_ic</th>\n",
       "      <th>date</th>\n",
       "      <th>readme_check</th>\n",
       "      <th>magnitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BARRINGTON 0101</td>\n",
       "      <td>US PRESIDENT</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>ELECTION DAY</td>\n",
       "      <td>229</td>\n",
       "      <td>BRISTOL</td>\n",
       "      <td>44001.0</td>\n",
       "      <td>BARRINGTON</td>\n",
       "      <td>4.400105e+09</td>\n",
       "      <td>...</td>\n",
       "      <td>RHODE ISLAND</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>RI</td>\n",
       "      <td>44</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BARRINGTON 0104</td>\n",
       "      <td>COUNCIL - NUMBER, SELECTION, TERM - BARRINGTON</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>ELECTION DAY</td>\n",
       "      <td>120</td>\n",
       "      <td>BRISTOL</td>\n",
       "      <td>44001.0</td>\n",
       "      <td>BARRINGTON</td>\n",
       "      <td>4.400105e+09</td>\n",
       "      <td>...</td>\n",
       "      <td>RHODE ISLAND</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>RI</td>\n",
       "      <td>44</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BARRINGTON 0105</td>\n",
       "      <td>TOWN COUNCIL - BARRINGTON</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>DEMOCRAT</td>\n",
       "      <td>ELECTION DAY</td>\n",
       "      <td>129</td>\n",
       "      <td>BRISTOL</td>\n",
       "      <td>44001.0</td>\n",
       "      <td>BARRINGTON</td>\n",
       "      <td>4.400105e+09</td>\n",
       "      <td>...</td>\n",
       "      <td>RHODE ISLAND</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>RI</td>\n",
       "      <td>44</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>False</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BARRINGTON LIMITED</td>\n",
       "      <td>US HOUSE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ELECTION DAY</td>\n",
       "      <td>0</td>\n",
       "      <td>BRISTOL</td>\n",
       "      <td>44001.0</td>\n",
       "      <td>BARRINGTON</td>\n",
       "      <td>4.400105e+09</td>\n",
       "      <td>...</td>\n",
       "      <td>RHODE ISLAND</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>RI</td>\n",
       "      <td>44</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BARRINGTON 0105</td>\n",
       "      <td>US SENATE</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>REPUBLICAN</td>\n",
       "      <td>ELECTION DAY</td>\n",
       "      <td>187</td>\n",
       "      <td>BRISTOL</td>\n",
       "      <td>44001.0</td>\n",
       "      <td>BARRINGTON</td>\n",
       "      <td>4.400105e+09</td>\n",
       "      <td>...</td>\n",
       "      <td>RHODE ISLAND</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>RI</td>\n",
       "      <td>44</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49714</th>\n",
       "      <td>WOONSOCKET 3902</td>\n",
       "      <td>SCHOOL COMMITTEE - WOONSOCKET</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>EARLY VOTING</td>\n",
       "      <td>53</td>\n",
       "      <td>PROVIDENCE</td>\n",
       "      <td>44007.0</td>\n",
       "      <td>WOONSOCKET</td>\n",
       "      <td>4.400781e+09</td>\n",
       "      <td>...</td>\n",
       "      <td>RHODE ISLAND</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>RI</td>\n",
       "      <td>44</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49715</th>\n",
       "      <td>WOONSOCKET 3903</td>\n",
       "      <td>SCHOOL COMMITTEE - WOONSOCKET</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>EARLY VOTING</td>\n",
       "      <td>49</td>\n",
       "      <td>PROVIDENCE</td>\n",
       "      <td>44007.0</td>\n",
       "      <td>WOONSOCKET</td>\n",
       "      <td>4.400781e+09</td>\n",
       "      <td>...</td>\n",
       "      <td>RHODE ISLAND</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>RI</td>\n",
       "      <td>44</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49716</th>\n",
       "      <td>WOONSOCKET 3904</td>\n",
       "      <td>SCHOOL COMMITTEE - WOONSOCKET</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>EARLY VOTING</td>\n",
       "      <td>108</td>\n",
       "      <td>PROVIDENCE</td>\n",
       "      <td>44007.0</td>\n",
       "      <td>WOONSOCKET</td>\n",
       "      <td>4.400781e+09</td>\n",
       "      <td>...</td>\n",
       "      <td>RHODE ISLAND</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>RI</td>\n",
       "      <td>44</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49717</th>\n",
       "      <td>WOONSOCKET 3905</td>\n",
       "      <td>SCHOOL COMMITTEE - WOONSOCKET</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>EARLY VOTING</td>\n",
       "      <td>83</td>\n",
       "      <td>PROVIDENCE</td>\n",
       "      <td>44007.0</td>\n",
       "      <td>WOONSOCKET</td>\n",
       "      <td>4.400781e+09</td>\n",
       "      <td>...</td>\n",
       "      <td>RHODE ISLAND</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>RI</td>\n",
       "      <td>44</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49718</th>\n",
       "      <td>WOONSOCKET 3906</td>\n",
       "      <td>SCHOOL COMMITTEE - WOONSOCKET</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>NONPARTISAN</td>\n",
       "      <td>EARLY VOTING</td>\n",
       "      <td>90</td>\n",
       "      <td>PROVIDENCE</td>\n",
       "      <td>44007.0</td>\n",
       "      <td>WOONSOCKET</td>\n",
       "      <td>4.400781e+09</td>\n",
       "      <td>...</td>\n",
       "      <td>RHODE ISLAND</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>RI</td>\n",
       "      <td>44</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-11-03</td>\n",
       "      <td>False</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49719 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 precinct                                          office  \\\n",
       "0         BARRINGTON 0101                                    US PRESIDENT   \n",
       "1         BARRINGTON 0104  COUNCIL - NUMBER, SELECTION, TERM - BARRINGTON   \n",
       "2         BARRINGTON 0105                       TOWN COUNCIL - BARRINGTON   \n",
       "3      BARRINGTON LIMITED                                        US HOUSE   \n",
       "4         BARRINGTON 0105                                       US SENATE   \n",
       "...                   ...                                             ...   \n",
       "49714     WOONSOCKET 3902                   SCHOOL COMMITTEE - WOONSOCKET   \n",
       "49715     WOONSOCKET 3903                   SCHOOL COMMITTEE - WOONSOCKET   \n",
       "49716     WOONSOCKET 3904                   SCHOOL COMMITTEE - WOONSOCKET   \n",
       "49717     WOONSOCKET 3905                   SCHOOL COMMITTEE - WOONSOCKET   \n",
       "49718     WOONSOCKET 3906                   SCHOOL COMMITTEE - WOONSOCKET   \n",
       "\n",
       "      party_detailed party_simplified          mode  votes county_name  \\\n",
       "0         REPUBLICAN       REPUBLICAN  ELECTION DAY    229     BRISTOL   \n",
       "1        NONPARTISAN      NONPARTISAN  ELECTION DAY    120     BRISTOL   \n",
       "2           DEMOCRAT         DEMOCRAT  ELECTION DAY    129     BRISTOL   \n",
       "3                NaN              NaN  ELECTION DAY      0     BRISTOL   \n",
       "4         REPUBLICAN       REPUBLICAN  ELECTION DAY    187     BRISTOL   \n",
       "...              ...              ...           ...    ...         ...   \n",
       "49714    NONPARTISAN      NONPARTISAN  EARLY VOTING     53  PROVIDENCE   \n",
       "49715    NONPARTISAN      NONPARTISAN  EARLY VOTING     49  PROVIDENCE   \n",
       "49716    NONPARTISAN      NONPARTISAN  EARLY VOTING    108  PROVIDENCE   \n",
       "49717    NONPARTISAN      NONPARTISAN  EARLY VOTING     83  PROVIDENCE   \n",
       "49718    NONPARTISAN      NONPARTISAN  EARLY VOTING     90  PROVIDENCE   \n",
       "\n",
       "       county_fips jurisdiction_name  jurisdiction_fips  ...         state  \\\n",
       "0          44001.0        BARRINGTON       4.400105e+09  ...  RHODE ISLAND   \n",
       "1          44001.0        BARRINGTON       4.400105e+09  ...  RHODE ISLAND   \n",
       "2          44001.0        BARRINGTON       4.400105e+09  ...  RHODE ISLAND   \n",
       "3          44001.0        BARRINGTON       4.400105e+09  ...  RHODE ISLAND   \n",
       "4          44001.0        BARRINGTON       4.400105e+09  ...  RHODE ISLAND   \n",
       "...            ...               ...                ...  ...           ...   \n",
       "49714      44007.0        WOONSOCKET       4.400781e+09  ...  RHODE ISLAND   \n",
       "49715      44007.0        WOONSOCKET       4.400781e+09  ...  RHODE ISLAND   \n",
       "49716      44007.0        WOONSOCKET       4.400781e+09  ...  RHODE ISLAND   \n",
       "49717      44007.0        WOONSOCKET       4.400781e+09  ...  RHODE ISLAND   \n",
       "49718      44007.0        WOONSOCKET       4.400781e+09  ...  RHODE ISLAND   \n",
       "\n",
       "      special writein  state_po state_fips state_cen  state_ic        date  \\\n",
       "0       False   False        RI         44        15         5  2020-11-03   \n",
       "1       False   False        RI         44        15         5  2020-11-03   \n",
       "2       False   False        RI         44        15         5  2020-11-03   \n",
       "3       False    True        RI         44        15         5  2020-11-03   \n",
       "4       False   False        RI         44        15         5  2020-11-03   \n",
       "...       ...     ...       ...        ...       ...       ...         ...   \n",
       "49714   False   False        RI         44        15         5  2020-11-03   \n",
       "49715   False   False        RI         44        15         5  2020-11-03   \n",
       "49716   False   False        RI         44        15         5  2020-11-03   \n",
       "49717   False   False        RI         44        15         5  2020-11-03   \n",
       "49718   False   False        RI         44        15         5  2020-11-03   \n",
       "\n",
       "      readme_check  magnitude  \n",
       "0            False          1  \n",
       "1            False          1  \n",
       "2            False          3  \n",
       "3            False          1  \n",
       "4            False          1  \n",
       "...            ...        ...  \n",
       "49714        False          5  \n",
       "49715        False          5  \n",
       "49716        False          5  \n",
       "49717        False          5  \n",
       "49718        False          5  \n",
       "\n",
       "[49719 rows x 25 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test = pd.read_csv('/Users/sophiazheng/Documents/GitHub/2020-precincts/precinct/RI/2020-ri-precinct-general.csv')\n",
    "test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>precinct</th>\n",
       "      <th>office</th>\n",
       "      <th>party_detailed</th>\n",
       "      <th>party_simplified</th>\n",
       "      <th>mode</th>\n",
       "      <th>votes</th>\n",
       "      <th>county_name</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>jurisdiction_name</th>\n",
       "      <th>jurisdiction_fips</th>\n",
       "      <th>...</th>\n",
       "      <th>state</th>\n",
       "      <th>special</th>\n",
       "      <th>writein</th>\n",
       "      <th>state_po</th>\n",
       "      <th>state_fips</th>\n",
       "      <th>state_cen</th>\n",
       "      <th>state_ic</th>\n",
       "      <th>date</th>\n",
       "      <th>readme_check</th>\n",
       "      <th>magnitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [precinct, office, party_detailed, party_simplified, mode, votes, county_name, county_fips, jurisdiction_name, jurisdiction_fips, candidate, district, dataverse, year, stage, state, special, writein, state_po, state_fips, state_cen, state_ic, date, readme_check, magnitude]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 25 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test[test['jurisdiction_fips']=='nan']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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": 4
}