March27.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "a18bffe3",
   "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>fips</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Accomack</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Albemarle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>Alleghany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>Amelia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>Amherst</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>800</td>\n",
       "      <td>Suffolk City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>810</td>\n",
       "      <td>Virginia Beach City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>820</td>\n",
       "      <td>Waynesboro City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>830</td>\n",
       "      <td>Williamsburg City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>840</td>\n",
       "      <td>Winchester City</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>128 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     fips                 name\n",
       "0       1             Accomack\n",
       "1       3            Albemarle\n",
       "2       5            Alleghany\n",
       "3       7               Amelia\n",
       "4       9              Amherst\n",
       "..    ...                  ...\n",
       "123   800         Suffolk City\n",
       "124   810  Virginia Beach City\n",
       "125   820      Waynesboro City\n",
       "126   830    Williamsburg City\n",
       "127   840      Winchester City\n",
       "\n",
       "[128 rows x 2 columns]"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd \n",
    "\n",
    "fips = pd.read_csv('fips.csv', header=None).rename(columns={0:\"fips\", 1:\"name\"})\n",
    "fips"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "4a5f0511",
   "metadata": {},
   "outputs": [],
   "source": [
    "fips['fips'] = fips['fips'].apply(lambda x: '{0:0>4}'.format(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "e246a519",
   "metadata": {},
   "outputs": [],
   "source": [
    "fips['name'] = fips['name'].str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "883464e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# update the name of spotsylvania \n",
    "fips[fips['name']=='spotsylvania'] = 'spotsylvania county'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "f43793e1",
   "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>fips</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0001</td>\n",
       "      <td>accomack</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0003</td>\n",
       "      <td>albemarle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0005</td>\n",
       "      <td>alleghany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0007</td>\n",
       "      <td>amelia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0009</td>\n",
       "      <td>amherst</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>0800</td>\n",
       "      <td>suffolk city</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>0810</td>\n",
       "      <td>virginia beach city</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>0820</td>\n",
       "      <td>waynesboro city</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>0830</td>\n",
       "      <td>williamsburg city</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>0840</td>\n",
       "      <td>winchester city</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>128 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     fips                 name\n",
       "0    0001             accomack\n",
       "1    0003            albemarle\n",
       "2    0005            alleghany\n",
       "3    0007               amelia\n",
       "4    0009              amherst\n",
       "..    ...                  ...\n",
       "123  0800         suffolk city\n",
       "124  0810  virginia beach city\n",
       "125  0820      waynesboro city\n",
       "126  0830    williamsburg city\n",
       "127  0840      winchester city\n",
       "\n",
       "[128 rows x 2 columns]"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# fips['name'] = fips['name'].str.replace(\" city\",\"\")\n",
    "fips"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8e027d7",
   "metadata": {},
   "source": [
    "## Now bring in compDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "id": "9eb4e21e",
   "metadata": {},
   "outputs": [],
   "source": [
    "compDF = pd.read_csv('compDF.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "94fc5a3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "compDF['name'] = compDF['locality'].str.replace(\"_\",\" \")\n",
    "#compDF['name'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "id": "70ca4f9a",
   "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>code_va</th>\n",
       "      <th>local_code</th>\n",
       "      <th>locality</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1-3.9</td>\n",
       "      <td>1-3</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "      <td>spotsylvania county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1-13.3 et seq.</td>\n",
       "      <td>1-2</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "      <td>spotsylvania county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.1-116.1</td>\n",
       "      <td>16-147</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "      <td>spotsylvania county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2.1-116.1 et seq.</td>\n",
       "      <td>16-147</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "      <td>spotsylvania county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2.1-340 et seq.</td>\n",
       "      <td>2-107(b)</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "      <td>spotsylvania county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37344</th>\n",
       "      <td>59.1-142, 59.1-143  10-7</td>\n",
       "      <td>59.1-142, 59.1-143  10-7</td>\n",
       "      <td>colonial_beach</td>\n",
       "      <td>colonial beach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37345</th>\n",
       "      <td>59.1-144</td>\n",
       "      <td>10-7</td>\n",
       "      <td>colonial_beach</td>\n",
       "      <td>colonial beach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37346</th>\n",
       "      <td>NaN</td>\n",
       "      <td>10-8</td>\n",
       "      <td>colonial_beach</td>\n",
       "      <td>colonial beach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37347</th>\n",
       "      <td>59.1-145—59.1-148  10-7</td>\n",
       "      <td>59.1-145—59.1-148  10-7</td>\n",
       "      <td>colonial_beach</td>\n",
       "      <td>colonial beach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37348</th>\n",
       "      <td>62.1-176</td>\n",
       "      <td>5-3</td>\n",
       "      <td>colonial_beach</td>\n",
       "      <td>colonial beach</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>37349 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        code_va                local_code  \\\n",
       "0                         1-3.9                       1-3   \n",
       "1                1-13.3 et seq.                       1-2   \n",
       "2                     2.1-116.1                    16-147   \n",
       "3             2.1-116.1 et seq.                    16-147   \n",
       "4               2.1-340 et seq.                  2-107(b)   \n",
       "...                         ...                       ...   \n",
       "37344  59.1-142, 59.1-143  10-7  59.1-142, 59.1-143  10-7   \n",
       "37345                  59.1-144                      10-7   \n",
       "37346                       NaN                      10-8   \n",
       "37347   59.1-145—59.1-148  10-7   59.1-145—59.1-148  10-7   \n",
       "37348                  62.1-176                       5-3   \n",
       "\n",
       "                  locality                 name  \n",
       "0      spotsylvania_county  spotsylvania county  \n",
       "1      spotsylvania_county  spotsylvania county  \n",
       "2      spotsylvania_county  spotsylvania county  \n",
       "3      spotsylvania_county  spotsylvania county  \n",
       "4      spotsylvania_county  spotsylvania county  \n",
       "...                    ...                  ...  \n",
       "37344       colonial_beach       colonial beach  \n",
       "37345       colonial_beach       colonial beach  \n",
       "37346       colonial_beach       colonial beach  \n",
       "37347       colonial_beach       colonial beach  \n",
       "37348       colonial_beach       colonial beach  \n",
       "\n",
       "[37349 rows x 4 columns]"
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#compDF['name'].str.replace(\"county\",\"city\")\n",
    "compDF"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c3bfeba",
   "metadata": {},
   "source": [
    "## Compare compDF and fips dataframescompDF['name'].unique()\n",
    "https://www.tax.virginia.gov/sites/default/files/taxforms/motor-vehicle-rental-tax/any/locality-dmv-codes-any.pdf\n",
    "\n",
    "Also manually adding all of the correct fips codes to the corresponding county/town in our compDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "e4d2e928",
   "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>name</th>\n",
       "      <th>fips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>spotsylvania county</td>\n",
       "      <td>0177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>falls church</td>\n",
       "      <td>0610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>richmond</td>\n",
       "      <td>0159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>goochland county</td>\n",
       "      <td>0075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>virginia beach</td>\n",
       "      <td>0810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>tazewell county</td>\n",
       "      <td>0185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>greene county</td>\n",
       "      <td>0079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>new market</td>\n",
       "      <td>0420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>washington county</td>\n",
       "      <td>0191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>colonial beach</td>\n",
       "      <td>0345</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   name  fips\n",
       "0   spotsylvania county  0177\n",
       "1          falls church  0610\n",
       "2              richmond  0159\n",
       "3      goochland county  0075\n",
       "4        virginia beach  0810\n",
       "..                  ...   ...\n",
       "79      tazewell county  0185\n",
       "80        greene county  0079\n",
       "81           new market  0420\n",
       "82    washington county  0191\n",
       "83       colonial beach  0345\n",
       "\n",
       "[84 rows x 2 columns]"
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fipsComp = pd.DataFrame(compDF['name'].unique())\n",
    "fipsComp['fips'] = pd.DataFrame(['0177','0610','0159','0075','0810','0149','0153','0600','0113','0580','0314','0464','0384','0474',\n",
    " '0487','0147','0396','0327','0183','0349','0312','0400','0145','0025','0185','0359','0485','0109',\n",
    " '0155','0137','0473','0063','0005','0595','0351','0001','0009','0740','0175','0310','0087','0045',\n",
    " '0444','0405','0125','0492','0097','0448','0820','0304','0101','0057','0407','0540','0193','0493','0303',\n",
    " '0460','0153','0019','0700','0173','0041','0332','0472','0382','0049','0083','0454','0171','0111','0036',\n",
    " '0530','0352','0381','0336','0455','0483','0127','0185','0079','0420','0191','0345'\n",
    "])\n",
    "fipsComp = fipsComp.rename(columns={0:'name'})\n",
    "fipsComp"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "289df408",
   "metadata": {},
   "source": [
    "## Now merge compDF and fips dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "bf1a6281",
   "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>code_va</th>\n",
       "      <th>local_code</th>\n",
       "      <th>locality</th>\n",
       "      <th>name</th>\n",
       "      <th>fips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1-3.9</td>\n",
       "      <td>1-3</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "      <td>spotsylvania county</td>\n",
       "      <td>0177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1-13.3 et seq.</td>\n",
       "      <td>1-2</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "      <td>spotsylvania county</td>\n",
       "      <td>0177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.1-116.1</td>\n",
       "      <td>16-147</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "      <td>spotsylvania county</td>\n",
       "      <td>0177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2.1-116.1 et seq.</td>\n",
       "      <td>16-147</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "      <td>spotsylvania county</td>\n",
       "      <td>0177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2.1-340 et seq.</td>\n",
       "      <td>2-107(b)</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "      <td>spotsylvania county</td>\n",
       "      <td>0177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37344</th>\n",
       "      <td>59.1-142, 59.1-143  10-7</td>\n",
       "      <td>59.1-142, 59.1-143  10-7</td>\n",
       "      <td>colonial_beach</td>\n",
       "      <td>colonial beach</td>\n",
       "      <td>0345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37345</th>\n",
       "      <td>59.1-144</td>\n",
       "      <td>10-7</td>\n",
       "      <td>colonial_beach</td>\n",
       "      <td>colonial beach</td>\n",
       "      <td>0345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37346</th>\n",
       "      <td>NaN</td>\n",
       "      <td>10-8</td>\n",
       "      <td>colonial_beach</td>\n",
       "      <td>colonial beach</td>\n",
       "      <td>0345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37347</th>\n",
       "      <td>59.1-145—59.1-148  10-7</td>\n",
       "      <td>59.1-145—59.1-148  10-7</td>\n",
       "      <td>colonial_beach</td>\n",
       "      <td>colonial beach</td>\n",
       "      <td>0345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37348</th>\n",
       "      <td>62.1-176</td>\n",
       "      <td>5-3</td>\n",
       "      <td>colonial_beach</td>\n",
       "      <td>colonial beach</td>\n",
       "      <td>0345</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>37349 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        code_va                local_code  \\\n",
       "0                         1-3.9                       1-3   \n",
       "1                1-13.3 et seq.                       1-2   \n",
       "2                     2.1-116.1                    16-147   \n",
       "3             2.1-116.1 et seq.                    16-147   \n",
       "4               2.1-340 et seq.                  2-107(b)   \n",
       "...                         ...                       ...   \n",
       "37344  59.1-142, 59.1-143  10-7  59.1-142, 59.1-143  10-7   \n",
       "37345                  59.1-144                      10-7   \n",
       "37346                       NaN                      10-8   \n",
       "37347   59.1-145—59.1-148  10-7   59.1-145—59.1-148  10-7   \n",
       "37348                  62.1-176                       5-3   \n",
       "\n",
       "                  locality                 name  fips  \n",
       "0      spotsylvania_county  spotsylvania county  0177  \n",
       "1      spotsylvania_county  spotsylvania county  0177  \n",
       "2      spotsylvania_county  spotsylvania county  0177  \n",
       "3      spotsylvania_county  spotsylvania county  0177  \n",
       "4      spotsylvania_county  spotsylvania county  0177  \n",
       "...                    ...                  ...   ...  \n",
       "37344       colonial_beach       colonial beach  0345  \n",
       "37345       colonial_beach       colonial beach  0345  \n",
       "37346       colonial_beach       colonial beach  0345  \n",
       "37347       colonial_beach       colonial beach  0345  \n",
       "37348       colonial_beach       colonial beach  0345  \n",
       "\n",
       "[37349 rows x 5 columns]"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "compDF_merge = compDF.merge(fipsComp)\n",
    "compDF_merge"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25b7bfda",
   "metadata": {},
   "source": [
    "## Expungement Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "2a4306e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "expunge = pd.read_csv(\"jonData.csv\").dropna(subset=['fips'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "id": "c50e76b0",
   "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>CodeSection</th>\n",
       "      <th>fips</th>\n",
       "      <th>total</th>\n",
       "      <th>black_percent</th>\n",
       "      <th>hispanic_percent</th>\n",
       "      <th>Black (Non-Hispanic)</th>\n",
       "      <th>White (Non-Hispanic)</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>American Indian or Alaskan Native</th>\n",
       "      <th>Asian or Pacific Islander</th>\n",
       "      <th>MISSING</th>\n",
       "      <th>Other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ZONING: 1001(A)</td>\n",
       "      <td>0810</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ZONING CHAP 25</td>\n",
       "      <td>0711</td>\n",
       "      <td>2</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ZONING 33-3</td>\n",
       "      <td>0730</td>\n",
       "      <td>2</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ZONING 212(C)</td>\n",
       "      <td>0810</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ZON. ORID. 1960</td>\n",
       "      <td>0650</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22693</th>\n",
       "      <td>0104-1-2</td>\n",
       "      <td>0059</td>\n",
       "      <td>92</td>\n",
       "      <td>2.17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>78</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22694</th>\n",
       "      <td>10-3-0103</td>\n",
       "      <td>0510</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22695</th>\n",
       "      <td>0103-3-11</td>\n",
       "      <td>0059</td>\n",
       "      <td>22</td>\n",
       "      <td>27.27</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22696</th>\n",
       "      <td>0103-1-11</td>\n",
       "      <td>0059</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22697</th>\n",
       "      <td>9-12-0100</td>\n",
       "      <td>0510</td>\n",
       "      <td>1</td>\n",
       "      <td>100.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>19096 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           CodeSection  fips  total  black_percent  hispanic_percent  \\\n",
       "0      ZONING: 1001(A)  0810      1           0.00               0.0   \n",
       "1       ZONING CHAP 25  0711      2           0.00               0.0   \n",
       "2          ZONING 33-3  0730      2           0.00               0.0   \n",
       "3        ZONING 212(C)  0810      1           0.00               0.0   \n",
       "4      ZON. ORID. 1960  0650      1           0.00               0.0   \n",
       "...                ...   ...    ...            ...               ...   \n",
       "22693         0104-1-2  0059     92           2.17               0.0   \n",
       "22694        10-3-0103  0510      1           0.00               0.0   \n",
       "22695        0103-3-11  0059     22          27.27               0.0   \n",
       "22696        0103-1-11  0059      1           0.00               0.0   \n",
       "22697        9-12-0100  0510      1         100.00               0.0   \n",
       "\n",
       "       Black (Non-Hispanic)  White (Non-Hispanic)  Hispanic  \\\n",
       "0                         0                     1         0   \n",
       "1                         0                     2         0   \n",
       "2                         0                     0         0   \n",
       "3                         0                     1         0   \n",
       "4                         0                     1         0   \n",
       "...                     ...                   ...       ...   \n",
       "22693                     2                    78         0   \n",
       "22694                     0                     1         0   \n",
       "22695                     6                    13         0   \n",
       "22696                     0                     1         0   \n",
       "22697                     1                     0         0   \n",
       "\n",
       "       American Indian or Alaskan Native  Asian or Pacific Islander  MISSING  \\\n",
       "0                                      0                          0        0   \n",
       "1                                      0                          0        0   \n",
       "2                                      0                          0        0   \n",
       "3                                      0                          0        0   \n",
       "4                                      0                          0        0   \n",
       "...                                  ...                        ...      ...   \n",
       "22693                                  0                         10        0   \n",
       "22694                                  0                          0        0   \n",
       "22695                                  0                          3        0   \n",
       "22696                                  0                          0        0   \n",
       "22697                                  0                          0        0   \n",
       "\n",
       "       Other  \n",
       "0          0  \n",
       "1          0  \n",
       "2          2  \n",
       "3          0  \n",
       "4          0  \n",
       "...      ...  \n",
       "22693      2  \n",
       "22694      0  \n",
       "22695      0  \n",
       "22696      0  \n",
       "22697      0  \n",
       "\n",
       "[19096 rows x 12 columns]"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# first to int\n",
    "expunge['fips'] = expunge['fips'].astype(int)\n",
    "# then to str\n",
    "expunge['fips'] = expunge['fips'].astype(str)\n",
    "\n",
    "# then add the leading zeros\n",
    "expunge['fips'] = expunge['fips'].apply(lambda x: '{0:0>4}'.format(x))\n",
    "expunge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ab42d4cb",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "4af65fc3",
   "metadata": {},
   "source": [
    "# Now merge compDF (now called 'compDF_merge') and expunge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "id": "a708c311",
   "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>code_va</th>\n",
       "      <th>local_code</th>\n",
       "      <th>locality</th>\n",
       "      <th>name</th>\n",
       "      <th>fips</th>\n",
       "      <th>CodeSection</th>\n",
       "      <th>total</th>\n",
       "      <th>black_percent</th>\n",
       "      <th>hispanic_percent</th>\n",
       "      <th>Black (Non-Hispanic)</th>\n",
       "      <th>White (Non-Hispanic)</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>American Indian or Alaskan Native</th>\n",
       "      <th>Asian or Pacific Islander</th>\n",
       "      <th>MISSING</th>\n",
       "      <th>Other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1-3.9</td>\n",
       "      <td>1-3</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "      <td>spotsylvania county</td>\n",
       "      <td>0177</td>\n",
       "      <td>VAC15-390-20</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1-3.9</td>\n",
       "      <td>1-3</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "      <td>spotsylvania county</td>\n",
       "      <td>0177</td>\n",
       "      <td>G.46.2-874</td>\n",
       "      <td>15</td>\n",
       "      <td>53.33</td>\n",
       "      <td>6.67</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1-3.9</td>\n",
       "      <td>1-3</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "      <td>spotsylvania county</td>\n",
       "      <td>0177</td>\n",
       "      <td>F.46.2-874</td>\n",
       "      <td>6</td>\n",
       "      <td>33.33</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1-3.9</td>\n",
       "      <td>1-3</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "      <td>spotsylvania county</td>\n",
       "      <td>0177</td>\n",
       "      <td>AVAC45-420-30</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1-3.9</td>\n",
       "      <td>1-3</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "      <td>spotsylvania county</td>\n",
       "      <td>0177</td>\n",
       "      <td>AVAC15-390-20</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4831687</th>\n",
       "      <td>NaN</td>\n",
       "      <td>63-204</td>\n",
       "      <td>washington_county</td>\n",
       "      <td>washington county</td>\n",
       "      <td>0191</td>\n",
       "      <td>30.31</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4831688</th>\n",
       "      <td>NaN</td>\n",
       "      <td>63-204</td>\n",
       "      <td>washington_county</td>\n",
       "      <td>washington county</td>\n",
       "      <td>0191</td>\n",
       "      <td>30.111</td>\n",
       "      <td>6</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4831689</th>\n",
       "      <td>NaN</td>\n",
       "      <td>63-204</td>\n",
       "      <td>washington_county</td>\n",
       "      <td>washington county</td>\n",
       "      <td>0191</td>\n",
       "      <td>18.64</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4831690</th>\n",
       "      <td>NaN</td>\n",
       "      <td>63-204</td>\n",
       "      <td>washington_county</td>\n",
       "      <td>washington county</td>\n",
       "      <td>0191</td>\n",
       "      <td>10.32</td>\n",
       "      <td>7</td>\n",
       "      <td>14.29</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4831691</th>\n",
       "      <td>NaN</td>\n",
       "      <td>63-204</td>\n",
       "      <td>washington_county</td>\n",
       "      <td>washington county</td>\n",
       "      <td>0191</td>\n",
       "      <td>31:51:00</td>\n",
       "      <td>2</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4831692 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        code_va local_code             locality                 name  fips  \\\n",
       "0         1-3.9        1-3  spotsylvania_county  spotsylvania county  0177   \n",
       "1         1-3.9        1-3  spotsylvania_county  spotsylvania county  0177   \n",
       "2         1-3.9        1-3  spotsylvania_county  spotsylvania county  0177   \n",
       "3         1-3.9        1-3  spotsylvania_county  spotsylvania county  0177   \n",
       "4         1-3.9        1-3  spotsylvania_county  spotsylvania county  0177   \n",
       "...         ...        ...                  ...                  ...   ...   \n",
       "4831687     NaN     63-204    washington_county    washington county  0191   \n",
       "4831688     NaN     63-204    washington_county    washington county  0191   \n",
       "4831689     NaN     63-204    washington_county    washington county  0191   \n",
       "4831690     NaN     63-204    washington_county    washington county  0191   \n",
       "4831691     NaN     63-204    washington_county    washington county  0191   \n",
       "\n",
       "           CodeSection  total  black_percent  hispanic_percent  \\\n",
       "0         VAC15-390-20      1           0.00              0.00   \n",
       "1           G.46.2-874     15          53.33              6.67   \n",
       "2           F.46.2-874      6          33.33              0.00   \n",
       "3        AVAC45-420-30      1           0.00              0.00   \n",
       "4        AVAC15-390-20      1           0.00              0.00   \n",
       "...                ...    ...            ...               ...   \n",
       "4831687          30.31      1           0.00              0.00   \n",
       "4831688         30.111      6           0.00              0.00   \n",
       "4831689          18.64      1           0.00              0.00   \n",
       "4831690          10.32      7          14.29              0.00   \n",
       "4831691       31:51:00      2           0.00              0.00   \n",
       "\n",
       "         Black (Non-Hispanic)  White (Non-Hispanic)  Hispanic  \\\n",
       "0                           0                     1         0   \n",
       "1                           8                     5         1   \n",
       "2                           2                     3         0   \n",
       "3                           0                     1         0   \n",
       "4                           0                     1         0   \n",
       "...                       ...                   ...       ...   \n",
       "4831687                     0                     1         0   \n",
       "4831688                     0                     6         0   \n",
       "4831689                     0                     1         0   \n",
       "4831690                     1                     6         0   \n",
       "4831691                     0                     2         0   \n",
       "\n",
       "         American Indian or Alaskan Native  Asian or Pacific Islander  \\\n",
       "0                                        0                          0   \n",
       "1                                        0                          1   \n",
       "2                                        0                          0   \n",
       "3                                        0                          0   \n",
       "4                                        0                          0   \n",
       "...                                    ...                        ...   \n",
       "4831687                                  0                          0   \n",
       "4831688                                  0                          0   \n",
       "4831689                                  0                          0   \n",
       "4831690                                  0                          0   \n",
       "4831691                                  0                          0   \n",
       "\n",
       "         MISSING  Other  \n",
       "0              0      0  \n",
       "1              0      0  \n",
       "2              0      1  \n",
       "3              0      0  \n",
       "4              0      0  \n",
       "...          ...    ...  \n",
       "4831687        0      0  \n",
       "4831688        0      0  \n",
       "4831689        0      0  \n",
       "4831690        0      0  \n",
       "4831691        0      0  \n",
       "\n",
       "[4831692 rows x 16 columns]"
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final = compDF_merge.merge(expunge, how='inner', on='fips')\n",
    "final"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29cdd57f",
   "metadata": {},
   "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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}