april_6.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Single DataFrame with all County Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import glob\n",
    "import re\n",
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [],
   "source": [
    "def grab_county_file_paths():\n",
    "    p = Path(\"countyCSV_March7\")\n",
    "    paths = list(p.glob(\"*.csv\"))\n",
    "    return paths\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [],
   "source": [
    "county_file_paths = grab_county_file_paths()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [],
   "source": [
    "# county_file_paths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_csvs(paths):\n",
    "    valid_dfs = []\n",
    "    invalid_dfs = []\n",
    "    \n",
    "    for path in paths:\n",
    "        df = pd.read_csv(path)\n",
    "        \n",
    "        county = path.stem\n",
    "        \n",
    "        columns = df.columns\n",
    "        if \"0\" in columns or \"1\" in columns:\n",
    "            header = df.iloc[0]\n",
    "            df = df.rename(header, axis=\"columns\")\n",
    "            df = df.iloc[1:]\n",
    "        columns = df.columns\n",
    "        \n",
    "        if \"Virginia\" in columns[0] or \"Va\" in columns[0] and \"Virginia\" not in columns[1]:\n",
    "            df = df.rename({columns[0]: \"code_va\", columns[1]: \"local_code\"}, axis=1)\n",
    "            df[\"locality\"] = county\n",
    "            df = df.set_index(\"code_va\")\n",
    "            valid_dfs.append(df)\n",
    "        else: \n",
    "            invalid_dfs.append(path)\n",
    "\n",
    "    return valid_dfs, invalid_dfs\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [],
   "source": [
    "valid_dfs, invalid_dfs = process_csvs(county_file_paths)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "84"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(valid_dfs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(invalid_dfs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  Code of  Virgina  Section  salem\n",
      "0              1-10 et seq.  Ch. 1\n",
      "1                    1-13.3    1-2\n",
      "2                  1-13.3:1    1-2\n",
      "3            1-13.6, 1-13.7    1-2\n",
      "4                    1-13.9    1-6\n"
     ]
    }
   ],
   "source": [
    "for p in invalid_dfs:\n",
    "    df = pd.read_csv(p)\n",
    "    print(df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                  local_code             locality\n",
      "code_va                                          \n",
      "1-3.9                    1-3  spotsylvania_county\n",
      "1-13.3 et seq.           1-2  spotsylvania_county\n",
      "2.1-116.1             16-147  spotsylvania_county\n",
      "2.1-116.1 et seq.     16-147  spotsylvania_county\n",
      "2.1-340 et seq.     2-107(b)  spotsylvania_county\n",
      "              local_code      locality\n",
      "code_va                               \n",
      "1-1                  1-2  falls_church\n",
      "1-21                 1-2  falls_church\n",
      "1-200 et seq.        1-2  falls_church\n",
      "1-210                1-2  falls_church\n",
      "1-216                1-2  falls_church\n",
      "        local_code  locality\n",
      "code_va                     \n",
      "1-210          1-2  richmond\n",
      "1-216          1-2  richmond\n",
      "1-217          1-8  richmond\n",
      "1-222          1-2  richmond\n",
      "1-223          1-2  richmond\n"
     ]
    }
   ],
   "source": [
    "for df in valid_dfs[:3]:\n",
    "    print(df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Concat Implementation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "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>local_code</th>\n",
       "      <th>locality</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>code_va</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1-3.9</th>\n",
       "      <td>1-3</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1-13.3 et seq.</th>\n",
       "      <td>1-2</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2.1-116.1</th>\n",
       "      <td>16-147</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2.1-116.1 et seq.</th>\n",
       "      <td>16-147</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2.1-340 et seq.</th>\n",
       "      <td>2-107(b)</td>\n",
       "      <td>spotsylvania_county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59.1-142, 59.1-143  10-7</th>\n",
       "      <td>59.1-142, 59.1-143  10-7</td>\n",
       "      <td>colonial_beach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59.1-144</th>\n",
       "      <td>10-7</td>\n",
       "      <td>colonial_beach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NaN</th>\n",
       "      <td>10-8</td>\n",
       "      <td>colonial_beach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59.1-145—59.1-148  10-7</th>\n",
       "      <td>59.1-145—59.1-148  10-7</td>\n",
       "      <td>colonial_beach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62.1-176</th>\n",
       "      <td>5-3</td>\n",
       "      <td>colonial_beach</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>37349 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        local_code             locality\n",
       "code_va                                                                \n",
       "1-3.9                                          1-3  spotsylvania_county\n",
       "1-13.3 et seq.                                 1-2  spotsylvania_county\n",
       "2.1-116.1                                   16-147  spotsylvania_county\n",
       "2.1-116.1 et seq.                           16-147  spotsylvania_county\n",
       "2.1-340 et seq.                           2-107(b)  spotsylvania_county\n",
       "...                                            ...                  ...\n",
       "59.1-142, 59.1-143  10-7  59.1-142, 59.1-143  10-7       colonial_beach\n",
       "59.1-144                                      10-7       colonial_beach\n",
       "NaN                                           10-8       colonial_beach\n",
       "59.1-145—59.1-148  10-7    59.1-145—59.1-148  10-7       colonial_beach\n",
       "62.1-176                                       5-3       colonial_beach\n",
       "\n",
       "[37349 rows x 2 columns]"
      ]
     },
     "execution_count": 233,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merged data\n",
    "big_df = pd.concat(valid_dfs, join=\"outer\")\n",
    "big_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "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>local_code</th>\n",
       "      <th>locality</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>code_va</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1-1</th>\n",
       "      <td>1-2</td>\n",
       "      <td>falls_church</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1-1</th>\n",
       "      <td>1-2</td>\n",
       "      <td>pulaski</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1-1</th>\n",
       "      <td>1-2</td>\n",
       "      <td>clifton_forge</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1-1</th>\n",
       "      <td>1-2</td>\n",
       "      <td>christiansburg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        local_code        locality\n",
       "code_va                           \n",
       "1-1            1-2    falls_church\n",
       "1-1            1-2         pulaski\n",
       "1-1            1-2   clifton_forge\n",
       "1-1            1-2  christiansburg"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "big_df.groupby(\"code_va\").get_group(\"1-1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37345</th>\n",
       "      <td>59.1-144</td>\n",
       "      <td>10-7</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",
       "    </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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37348</th>\n",
       "      <td>62.1-176</td>\n",
       "      <td>5-3</td>\n",
       "      <td>colonial_beach</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>37349 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        code_va                local_code             locality\n",
       "0                         1-3.9                       1-3  spotsylvania_county\n",
       "1                1-13.3 et seq.                       1-2  spotsylvania_county\n",
       "2                     2.1-116.1                    16-147  spotsylvania_county\n",
       "3             2.1-116.1 et seq.                    16-147  spotsylvania_county\n",
       "4               2.1-340 et seq.                  2-107(b)  spotsylvania_county\n",
       "...                         ...                       ...                  ...\n",
       "37344  59.1-142, 59.1-143  10-7  59.1-142, 59.1-143  10-7       colonial_beach\n",
       "37345                  59.1-144                      10-7       colonial_beach\n",
       "37346                       NaN                      10-8       colonial_beach\n",
       "37347   59.1-145—59.1-148  10-7   59.1-145—59.1-148  10-7       colonial_beach\n",
       "37348                  62.1-176                       5-3       colonial_beach\n",
       "\n",
       "[37349 rows x 3 columns]"
      ]
     },
     "execution_count": 234,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "big_df = big_df.reset_index()\n",
    "big_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Fips"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "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>circuit</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Accomack Circuit Court</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Albemarle Circuit Court</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>Alleghany Circuit Court</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>Amelia Circuit Court</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>Amherst Circuit Court</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>800</td>\n",
       "      <td>Suffolk Circuit Court</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>810</td>\n",
       "      <td>Virginia Beach Circuit Court</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>820</td>\n",
       "      <td>Waynesboro Circuit Court</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>830</td>\n",
       "      <td>Williamsburg/James City County Circuit Court</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>840</td>\n",
       "      <td>Winchester Circuit Court</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>119 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     circuit                                          name\n",
       "0          1                        Accomack Circuit Court\n",
       "1          3                       Albemarle Circuit Court\n",
       "2          5                       Alleghany Circuit Court\n",
       "3          7                          Amelia Circuit Court\n",
       "4          9                         Amherst Circuit Court\n",
       "..       ...                                           ...\n",
       "114      800                         Suffolk Circuit Court\n",
       "115      810                  Virginia Beach Circuit Court\n",
       "116      820                      Waynesboro Circuit Court\n",
       "117      830  Williamsburg/James City County Circuit Court\n",
       "118      840                      Winchester Circuit Court\n",
       "\n",
       "[119 rows x 2 columns]"
      ]
     },
     "execution_count": 235,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "circuit_courts_data = pd.read_csv('circuit_courts.csv').rename(columns={\"fips\":\"circuit\"})\n",
    "circuit_courts_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "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>circuit</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>114</th>\n",
       "      <td>800</td>\n",
       "      <td>suffolk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>810</td>\n",
       "      <td>virginia_beach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>820</td>\n",
       "      <td>waynesboro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>830</td>\n",
       "      <td>williamsburg/james_city_county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>840</td>\n",
       "      <td>winchester</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>119 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     circuit                            name\n",
       "0          1                        accomack\n",
       "1          3                       albemarle\n",
       "2          5                       alleghany\n",
       "3          7                          amelia\n",
       "4          9                         amherst\n",
       "..       ...                             ...\n",
       "114      800                         suffolk\n",
       "115      810                  virginia_beach\n",
       "116      820                      waynesboro\n",
       "117      830  williamsburg/james_city_county\n",
       "118      840                      winchester\n",
       "\n",
       "[119 rows x 2 columns]"
      ]
     },
     "execution_count": 236,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# clean name\n",
    "# strip circuit court from name, make lower case, convert spaces to underscores\n",
    "names = circuit_courts_data[\"name\"]\n",
    "for name in names:\n",
    "    circuit_courts_data = circuit_courts_data.replace(name, name.replace(\" Circuit Court\", \"\").lower().replace(\" \", \"_\"))\n",
    "circuit_courts_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "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>district</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Accomack General District Court</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Albemarle General District Court</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>Alleghany General District Court</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>Amelia General District Court</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>Amherst General District Court</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>800</td>\n",
       "      <td>Suffolk General District Court</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>810</td>\n",
       "      <td>Virginia Beach General District Court</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>820</td>\n",
       "      <td>Waynesboro General District Court</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>830</td>\n",
       "      <td>Williamsburg/James City County General Distric...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>840</td>\n",
       "      <td>Winchester General District Court</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>133 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     district                                               name\n",
       "0           1                    Accomack General District Court\n",
       "1           3                   Albemarle General District Court\n",
       "2           5                   Alleghany General District Court\n",
       "3           7                      Amelia General District Court\n",
       "4           9                     Amherst General District Court\n",
       "..        ...                                                ...\n",
       "128       800                     Suffolk General District Court\n",
       "129       810              Virginia Beach General District Court\n",
       "130       820                  Waynesboro General District Court\n",
       "131       830  Williamsburg/James City County General Distric...\n",
       "132       840                  Winchester General District Court\n",
       "\n",
       "[133 rows x 2 columns]"
      ]
     },
     "execution_count": 237,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "district_courts_data = pd.read_csv('district_courts.csv').rename(columns={\"fips\":\"district\"})\n",
    "district_courts_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "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>district</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>128</th>\n",
       "      <td>800</td>\n",
       "      <td>suffolk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>810</td>\n",
       "      <td>virginia_beach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>820</td>\n",
       "      <td>waynesboro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>830</td>\n",
       "      <td>williamsburg/james_city_county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>840</td>\n",
       "      <td>winchester</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>133 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     district                            name\n",
       "0           1                        accomack\n",
       "1           3                       albemarle\n",
       "2           5                       alleghany\n",
       "3           7                          amelia\n",
       "4           9                         amherst\n",
       "..        ...                             ...\n",
       "128       800                         suffolk\n",
       "129       810                  virginia_beach\n",
       "130       820                      waynesboro\n",
       "131       830  williamsburg/james_city_county\n",
       "132       840                      winchester\n",
       "\n",
       "[133 rows x 2 columns]"
      ]
     },
     "execution_count": 238,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# clean name\n",
    "# strip district court from name, make lower case, convert spaces to underscores\n",
    "names = district_courts_data[\"name\"]\n",
    "for name in names:\n",
    "    district_courts_data = district_courts_data.replace(name, name.replace(\" General District Court\", \"\").lower().replace(\" \", \"_\"))\n",
    "district_courts_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "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>circuit</th>\n",
       "      <th>name</th>\n",
       "      <th>district</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>accomack</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.0</td>\n",
       "      <td>albemarle</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5.0</td>\n",
       "      <td>alleghany</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7.0</td>\n",
       "      <td>amelia</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9.0</td>\n",
       "      <td>amherst</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>NaN</td>\n",
       "      <td>norfolk-civil</td>\n",
       "      <td>713.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>NaN</td>\n",
       "      <td>richmond-marsh_criminal/traffic_at_manchester</td>\n",
       "      <td>761.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>NaN</td>\n",
       "      <td>richmond-john_marshall_criminal/traffic</td>\n",
       "      <td>762.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>NaN</td>\n",
       "      <td>richmond-civil</td>\n",
       "      <td>763.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>NaN</td>\n",
       "      <td>richmond_manchester</td>\n",
       "      <td>764.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>143 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     circuit                                           name  district\n",
       "0        1.0                                       accomack       1.0\n",
       "1        3.0                                      albemarle       3.0\n",
       "2        5.0                                      alleghany       5.0\n",
       "3        7.0                                         amelia       7.0\n",
       "4        9.0                                        amherst       9.0\n",
       "..       ...                                            ...       ...\n",
       "138      NaN                                  norfolk-civil     713.0\n",
       "139      NaN  richmond-marsh_criminal/traffic_at_manchester     761.0\n",
       "140      NaN        richmond-john_marshall_criminal/traffic     762.0\n",
       "141      NaN                                 richmond-civil     763.0\n",
       "142      NaN                            richmond_manchester     764.0\n",
       "\n",
       "[143 rows x 3 columns]"
      ]
     },
     "execution_count": 239,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge district and circuit data \n",
    "fips = pd.merge(circuit_courts_data, district_courts_data, on=\"name\", how=\"outer\")\n",
    "fips"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "counter: 133 list: 133\n"
     ]
    }
   ],
   "source": [
    "# check to make sure all fips are present in result after merge\n",
    "district_fips = list(district_courts_data[\"district\"])\n",
    "circuit_fips = list(circuit_courts_data[\"circuit\"])\n",
    "\n",
    "counter = 0\n",
    "for fip in district_fips:\n",
    "    if fip in list(fips[\"district\"]):\n",
    "        counter+=1\n",
    "assert counter == len(district_fips)\n",
    "print(f\"counter: {counter} list: {len(district_fips)}\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "counter: 119 list: 119\n"
     ]
    }
   ],
   "source": [
    "counter = 0\n",
    "for fip in circuit_fips:\n",
    "    if fip in list(fips[\"circuit\"]):\n",
    "        counter+=1\n",
    "assert counter == len(circuit_fips)\n",
    "print(f\"counter: {counter} list: {len(circuit_fips)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "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>circuit</th>\n",
       "      <th>locality</th>\n",
       "      <th>district</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>accomack</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.0</td>\n",
       "      <td>albemarle</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5.0</td>\n",
       "      <td>alleghany</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7.0</td>\n",
       "      <td>amelia</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9.0</td>\n",
       "      <td>amherst</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>NaN</td>\n",
       "      <td>norfolk-civil</td>\n",
       "      <td>713.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>NaN</td>\n",
       "      <td>richmond-marsh_criminal/traffic_at_manchester</td>\n",
       "      <td>761.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>NaN</td>\n",
       "      <td>richmond-john_marshall_criminal/traffic</td>\n",
       "      <td>762.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>NaN</td>\n",
       "      <td>richmond-civil</td>\n",
       "      <td>763.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>NaN</td>\n",
       "      <td>richmond_manchester</td>\n",
       "      <td>764.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>143 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     circuit                                       locality  district\n",
       "0        1.0                                       accomack       1.0\n",
       "1        3.0                                      albemarle       3.0\n",
       "2        5.0                                      alleghany       5.0\n",
       "3        7.0                                         amelia       7.0\n",
       "4        9.0                                        amherst       9.0\n",
       "..       ...                                            ...       ...\n",
       "138      NaN                                  norfolk-civil     713.0\n",
       "139      NaN  richmond-marsh_criminal/traffic_at_manchester     761.0\n",
       "140      NaN        richmond-john_marshall_criminal/traffic     762.0\n",
       "141      NaN                                 richmond-civil     763.0\n",
       "142      NaN                            richmond_manchester     764.0\n",
       "\n",
       "[143 rows x 3 columns]"
      ]
     },
     "execution_count": 242,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# rename name column \n",
    "fips = fips.rename(columns = {\"name\": \"locality\"})\n",
    "fips"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The county df includes the word \"county\" in the locality name while the fips df does not. The next block updates the county df to remove county declaration and to only include the word \"city\" if the locality is a city. Maintaining the city declaration is important for localities like Fairfax that have the same name for both the county and city. This update will result in a cleaner merge becuase we are now seeing duplication due to the different naming conventions. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 247,
   "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",
       "    </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</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</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.1-116.1</td>\n",
       "      <td>16-147</td>\n",
       "      <td>spotsylvania</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</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</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37345</th>\n",
       "      <td>59.1-144</td>\n",
       "      <td>10-7</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",
       "    </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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37348</th>\n",
       "      <td>62.1-176</td>\n",
       "      <td>5-3</td>\n",
       "      <td>colonial_beach</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>37349 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        code_va                local_code        locality\n",
       "0                         1-3.9                       1-3    spotsylvania\n",
       "1                1-13.3 et seq.                       1-2    spotsylvania\n",
       "2                     2.1-116.1                    16-147    spotsylvania\n",
       "3             2.1-116.1 et seq.                    16-147    spotsylvania\n",
       "4               2.1-340 et seq.                  2-107(b)    spotsylvania\n",
       "...                         ...                       ...             ...\n",
       "37344  59.1-142, 59.1-143  10-7  59.1-142, 59.1-143  10-7  colonial_beach\n",
       "37345                  59.1-144                      10-7  colonial_beach\n",
       "37346                       NaN                      10-8  colonial_beach\n",
       "37347   59.1-145—59.1-148  10-7   59.1-145—59.1-148  10-7  colonial_beach\n",
       "37348                  62.1-176                       5-3  colonial_beach\n",
       "\n",
       "[37349 rows x 3 columns]"
      ]
     },
     "execution_count": 247,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# update county df\n",
    "for locality in localities: \n",
    "    if \"/\" not in locality: \n",
    "        big_df = big_df.replace(locality, locality.replace(\"_county\", \"\"))\n",
    "big_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 248,
   "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>circuit</th>\n",
       "      <th>district</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</td>\n",
       "      <td>177.0</td>\n",
       "      <td>177.0</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</td>\n",
       "      <td>177.0</td>\n",
       "      <td>177.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.1-116.1</td>\n",
       "      <td>16-147</td>\n",
       "      <td>spotsylvania</td>\n",
       "      <td>177.0</td>\n",
       "      <td>177.0</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</td>\n",
       "      <td>177.0</td>\n",
       "      <td>177.0</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</td>\n",
       "      <td>177.0</td>\n",
       "      <td>177.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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37442</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>norfolk-civil</td>\n",
       "      <td>NaN</td>\n",
       "      <td>713.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37443</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>richmond-marsh_criminal/traffic_at_manchester</td>\n",
       "      <td>NaN</td>\n",
       "      <td>761.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37444</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>richmond-john_marshall_criminal/traffic</td>\n",
       "      <td>NaN</td>\n",
       "      <td>762.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37445</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>richmond-civil</td>\n",
       "      <td>NaN</td>\n",
       "      <td>763.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37446</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>richmond_manchester</td>\n",
       "      <td>NaN</td>\n",
       "      <td>764.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>37447 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",
       "37442                NaN        NaN   \n",
       "37443                NaN        NaN   \n",
       "37444                NaN        NaN   \n",
       "37445                NaN        NaN   \n",
       "37446                NaN        NaN   \n",
       "\n",
       "                                            locality  circuit  district  \n",
       "0                                       spotsylvania    177.0     177.0  \n",
       "1                                       spotsylvania    177.0     177.0  \n",
       "2                                       spotsylvania    177.0     177.0  \n",
       "3                                       spotsylvania    177.0     177.0  \n",
       "4                                       spotsylvania    177.0     177.0  \n",
       "...                                              ...      ...       ...  \n",
       "37442                                  norfolk-civil      NaN     713.0  \n",
       "37443  richmond-marsh_criminal/traffic_at_manchester      NaN     761.0  \n",
       "37444        richmond-john_marshall_criminal/traffic      NaN     762.0  \n",
       "37445                                 richmond-civil      NaN     763.0  \n",
       "37446                            richmond_manchester      NaN     764.0  \n",
       "\n",
       "[37447 rows x 5 columns]"
      ]
     },
     "execution_count": 248,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge fips df with county df\n",
    "result = pd.merge(big_df, fips, how=\"outer\")\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "metadata": {},
   "outputs": [],
   "source": [
    "localities = list(result[\"locality\"].unique())\n",
    "localities.sort()\n",
    "# localities"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge county and expungement data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 250,
   "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>810.0</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>711.0</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>730.0</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>810.0</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>650.0</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>59.0</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>510.0</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>59.0</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>59.0</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>510.0</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>22698 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           CodeSection   fips  total  black_percent  hispanic_percent  \\\n",
       "0      ZONING: 1001(A)  810.0      1           0.00               0.0   \n",
       "1       ZONING CHAP 25  711.0      2           0.00               0.0   \n",
       "2          ZONING 33-3  730.0      2           0.00               0.0   \n",
       "3        ZONING 212(C)  810.0      1           0.00               0.0   \n",
       "4      ZON. ORID. 1960  650.0      1           0.00               0.0   \n",
       "...                ...    ...    ...            ...               ...   \n",
       "22693         0104-1-2   59.0     92           2.17               0.0   \n",
       "22694        10-3-0103  510.0      1           0.00               0.0   \n",
       "22695        0103-3-11   59.0     22          27.27               0.0   \n",
       "22696        0103-1-11   59.0      1           0.00               0.0   \n",
       "22697        9-12-0100  510.0      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",
       "[22698 rows x 12 columns]"
      ]
     },
     "execution_count": 250,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load expungement data \n",
    "expunge = pd.read_csv(\"jonData.csv\")\n",
    "expunge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "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",
       "      <th>code_va</th>\n",
       "      <th>local_code</th>\n",
       "      <th>locality</th>\n",
       "      <th>circuit</th>\n",
       "      <th>district</th>\n",
       "      <th>matched</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>86-4</td>\n",
       "      <td>109.0</td>\n",
       "      <td>17</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15.2-2280</td>\n",
       "      <td>86-4</td>\n",
       "      <td>louisa</td>\n",
       "      <td>109.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>86-4</td>\n",
       "      <td>109.0</td>\n",
       "      <td>17</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15.2-2281</td>\n",
       "      <td>86-4</td>\n",
       "      <td>louisa</td>\n",
       "      <td>109.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>86-13</td>\n",
       "      <td>109.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.2-5130(A)</td>\n",
       "      <td>86-13</td>\n",
       "      <td>louisa</td>\n",
       "      <td>109.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>86-13</td>\n",
       "      <td>109.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4.1-207(5)</td>\n",
       "      <td>86-13</td>\n",
       "      <td>louisa</td>\n",
       "      <td>109.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>86-13</td>\n",
       "      <td>109.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4.1-208(2)</td>\n",
       "      <td>86-13</td>\n",
       "      <td>louisa</td>\n",
       "      <td>109.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>both</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558</th>\n",
       "      <td>3-28</td>\n",
       "      <td>185.0</td>\n",
       "      <td>45</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.2-6524</td>\n",
       "      <td>3-28</td>\n",
       "      <td>tazewell</td>\n",
       "      <td>185.0</td>\n",
       "      <td>185.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>559</th>\n",
       "      <td>3-28</td>\n",
       "      <td>185.0</td>\n",
       "      <td>45</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.2-6538</td>\n",
       "      <td>3-28</td>\n",
       "      <td>tazewell</td>\n",
       "      <td>185.0</td>\n",
       "      <td>185.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>560</th>\n",
       "      <td>3-26</td>\n",
       "      <td>125.0</td>\n",
       "      <td>182</td>\n",
       "      <td>13.74</td>\n",
       "      <td>0.55</td>\n",
       "      <td>25</td>\n",
       "      <td>139</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>3.1-796.85</td>\n",
       "      <td>3-26</td>\n",
       "      <td>nelson</td>\n",
       "      <td>125.0</td>\n",
       "      <td>125.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>561</th>\n",
       "      <td>1-15</td>\n",
       "      <td>191.0</td>\n",
       "      <td>10</td>\n",
       "      <td>10.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15.2-1429</td>\n",
       "      <td>1-15</td>\n",
       "      <td>washington</td>\n",
       "      <td>191.0</td>\n",
       "      <td>191.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>562</th>\n",
       "      <td>1-11</td>\n",
       "      <td>111.0</td>\n",
       "      <td>1</td>\n",
       "      <td>100.00</td>\n",
       "      <td>0.00</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",
       "      <td>NaN</td>\n",
       "      <td>1-11</td>\n",
       "      <td>lunenburg</td>\n",
       "      <td>111.0</td>\n",
       "      <td>111.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>563 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    CodeSection   fips  total  black_percent  hispanic_percent  \\\n",
       "0          86-4  109.0     17           0.00              0.00   \n",
       "1          86-4  109.0     17           0.00              0.00   \n",
       "2         86-13  109.0      3           0.00              0.00   \n",
       "3         86-13  109.0      3           0.00              0.00   \n",
       "4         86-13  109.0      3           0.00              0.00   \n",
       "..          ...    ...    ...            ...               ...   \n",
       "558        3-28  185.0     45           0.00              0.00   \n",
       "559        3-28  185.0     45           0.00              0.00   \n",
       "560        3-26  125.0    182          13.74              0.55   \n",
       "561        1-15  191.0     10          10.00              0.00   \n",
       "562        1-11  111.0      1         100.00              0.00   \n",
       "\n",
       "     Black (Non-Hispanic)  White (Non-Hispanic)  Hispanic  \\\n",
       "0                       0                    17         0   \n",
       "1                       0                    17         0   \n",
       "2                       0                     3         0   \n",
       "3                       0                     3         0   \n",
       "4                       0                     3         0   \n",
       "..                    ...                   ...       ...   \n",
       "558                     0                    45         0   \n",
       "559                     0                    45         0   \n",
       "560                    25                   139         1   \n",
       "561                     1                     9         0   \n",
       "562                     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",
       "558                                  0                          0        0   \n",
       "559                                  0                          0        0   \n",
       "560                                  0                          0       17   \n",
       "561                                  0                          0        0   \n",
       "562                                  0                          0        0   \n",
       "\n",
       "     Other      code_va local_code    locality  circuit  district matched  \n",
       "0        0    15.2-2280       86-4      louisa    109.0     109.0    both  \n",
       "1        0    15.2-2281       86-4      louisa    109.0     109.0    both  \n",
       "2        0  3.2-5130(A)      86-13      louisa    109.0     109.0    both  \n",
       "3        0   4.1-207(5)      86-13      louisa    109.0     109.0    both  \n",
       "4        0   4.1-208(2)      86-13      louisa    109.0     109.0    both  \n",
       "..     ...          ...        ...         ...      ...       ...     ...  \n",
       "558      0     3.2-6524       3-28    tazewell    185.0     185.0    both  \n",
       "559      0     3.2-6538       3-28    tazewell    185.0     185.0    both  \n",
       "560      0   3.1-796.85       3-26      nelson    125.0     125.0    both  \n",
       "561      0    15.2-1429       1-15  washington    191.0     191.0    both  \n",
       "562      0          NaN       1-11   lunenburg    111.0     111.0    both  \n",
       "\n",
       "[563 rows x 18 columns]"
      ]
     },
     "execution_count": 254,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge data\n",
    "merged_data = pd.merge(expunge, result.drop_duplicates(), left_on=[\"CodeSection\", \"fips\"], right_on=[\"local_code\", \"circuit\"], indicator=\"matched\")\n",
    "merged_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "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>local_code</th>\n",
       "      <th>locality</th>\n",
       "      <th>circuit</th>\n",
       "      <th>district</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>37424</th>\n",
       "      <td>NaN</td>\n",
       "      <td>winchester</td>\n",
       "      <td>840.0</td>\n",
       "      <td>840.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      local_code    locality  circuit  district\n",
       "37424        NaN  winchester    840.0     840.0"
      ]
     },
     "execution_count": 232,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result[result[\"locality\"] == \"winchester\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "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>circuit</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>2063</th>\n",
       "      <td>86-13</td>\n",
       "      <td>109.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</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",
       "</div>"
      ],
      "text/plain": [
       "     CodeSection  circuit  total  black_percent  hispanic_percent  \\\n",
       "2063       86-13    109.0      3            0.0               0.0   \n",
       "\n",
       "      Black (Non-Hispanic)  White (Non-Hispanic)  Hispanic  \\\n",
       "2063                     0                     3         0   \n",
       "\n",
       "      American Indian or Alaskan Native  Asian or Pacific Islander  MISSING  \\\n",
       "2063                                  0                          0        0   \n",
       "\n",
       "      Other  \n",
       "2063      0  "
      ]
     },
     "execution_count": 216,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "expunge[(expunge[\"CodeSection\"] == \"86-13\") & (expunge[\"circuit\"] == 109)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 218,
   "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>local_code</th>\n",
       "      <th>locality</th>\n",
       "      <th>circuit</th>\n",
       "      <th>district</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12532</th>\n",
       "      <td>86-13</td>\n",
       "      <td>louisa</td>\n",
       "      <td>109.0</td>\n",
       "      <td>109.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      local_code locality  circuit  district\n",
       "12532      86-13   louisa    109.0     109.0"
      ]
     },
     "execution_count": 218,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result[(result[\"local_code\"] == \"86-13\") & (result[\"circuit\"] == 109)].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.024803947484359854"
      ]
     },
     "execution_count": 255,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "563/22698"
   ]
  }
 ],
 "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}