feb_21_merge_county_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Single DataFrame with all County Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import glob\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "def grab_county_file_paths():\n",
    "    paths = []\n",
    "    dir = \"countyCSV\"\n",
    "    for path in glob.glob(f\"{dir}/*\"):\n",
    "        paths.append(path)\n",
    "    return paths\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "county_file_paths = grab_county_file_paths()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_csvs(paths):\n",
    "    FILE_NAME_PARSE = re.compile(r\"(\\w{9})/(.*).csv\")\n",
    "    \n",
    "    valid_dfs = []\n",
    "    invalid_dfs = []\n",
    "    \n",
    "    for path in paths:\n",
    "        df = pd.read_csv(path)\n",
    "        columns = df.columns\n",
    "        \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 len(columns) == 2:\n",
    "            if \"Virginia\" in columns[0] and \"Virginia\" not in columns[1]:\n",
    "                match = FILE_NAME_PARSE.match(path)\n",
    "                county = match.group(2)\n",
    "                \n",
    "                county = county.replace(\" \", \"_\")\n",
    "                df = df.rename({columns[0]: \"code_va\", columns[1]: county}, axis=1)\n",
    "                df = df.set_index(\"code_va\")\n",
    "                valid_dfs.append(df)\n",
    "#                 print(f\"Path, {path}, has the following columns, {df.columns}\")\n",
    "            else: \n",
    "                invalid_dfs.append(path)\n",
    "                    \n",
    "        else:\n",
    "            invalid_dfs.append(path)\n",
    "            \n",
    "    return valid_dfs, invalid_dfs\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "valid_dfs, invalid_dfs = process_csvs(county_file_paths)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "120"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(valid_dfs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              falls_church\n",
      "code_va                   \n",
      "1-1                    1-2\n",
      "1-21                   1-2\n",
      "1-200 et seq.          1-2\n",
      "1-210                  1-2\n",
      "1-216                  1-2\n",
      "        Richmond\n",
      "code_va         \n",
      "1-210        1-2\n",
      "1-216        1-2\n",
      "1-217        1-8\n",
      "1-222        1-2\n",
      "1-223        1-2\n",
      "            prince_george_county\n",
      "code_va                         \n",
      "1-1 et seq.                  1-2\n",
      "1-13.9                       1-3\n",
      "1-13.32                      1-2\n",
      "1-17.1                       1-7\n",
      "2.2-614.1                  2-731\n"
     ]
    }
   ],
   "source": [
    "for df in valid_dfs[:3]:\n",
    "    print(df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Concat Implementation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "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>falls_church</th>\n",
       "      <th>Richmond</th>\n",
       "      <th>prince_george_county</th>\n",
       "      <th>quantico</th>\n",
       "      <th>fairfax</th>\n",
       "      <th>madison_county</th>\n",
       "      <th>strasburg_2</th>\n",
       "      <th>covington</th>\n",
       "      <th>hamilton_2</th>\n",
       "      <th>bluefield</th>\n",
       "      <th>...</th>\n",
       "      <th>south_hill</th>\n",
       "      <th>west_point</th>\n",
       "      <th>new_kent_county</th>\n",
       "      <th>halifax_county_2</th>\n",
       "      <th>greene_county</th>\n",
       "      <th>new_market</th>\n",
       "      <th>washington_county</th>\n",
       "      <th>manassas</th>\n",
       "      <th>colonial_beach</th>\n",
       "      <th>haymarket_2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>code_va</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1-21</th>\n",
       "      <td>1-2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1-200 et seq.</th>\n",
       "      <td>1-2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1-210</th>\n",
       "      <td>1-2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1-216</th>\n",
       "      <td>1-2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58.1-3993</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14-49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59.1-142 et seq.</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30-4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59.1-296</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62.1-44.2 et seq.</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ch. 50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62.1-44.34:8</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19-6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>56640 rows × 120 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  falls_church Richmond prince_george_county quantico fairfax  \\\n",
       "code_va                                                                         \n",
       "1-1                        1-2      NaN                  NaN      NaN     NaN   \n",
       "1-21                       1-2      NaN                  NaN      NaN     NaN   \n",
       "1-200 et seq.              1-2      NaN                  NaN      NaN     NaN   \n",
       "1-210                      1-2      NaN                  NaN      NaN     NaN   \n",
       "1-216                      1-2      NaN                  NaN      NaN     NaN   \n",
       "...                        ...      ...                  ...      ...     ...   \n",
       "58.1-3993                  NaN      NaN                  NaN      NaN     NaN   \n",
       "59.1-142 et seq.           NaN      NaN                  NaN      NaN     NaN   \n",
       "59.1-296                   NaN      NaN                  NaN      NaN     NaN   \n",
       "62.1-44.2 et seq.          NaN      NaN                  NaN      NaN     NaN   \n",
       "62.1-44.34:8               NaN      NaN                  NaN      NaN     NaN   \n",
       "\n",
       "                  madison_county strasburg_2 covington hamilton_2 bluefield  \\\n",
       "code_va                                                                       \n",
       "1-1                          NaN         NaN       NaN        NaN       NaN   \n",
       "1-21                         NaN         NaN       NaN        NaN       NaN   \n",
       "1-200 et seq.                NaN         NaN       NaN        NaN       NaN   \n",
       "1-210                        NaN         NaN       NaN        NaN       NaN   \n",
       "1-216                        NaN         NaN       NaN        NaN       NaN   \n",
       "...                          ...         ...       ...        ...       ...   \n",
       "58.1-3993                    NaN         NaN       NaN        NaN       NaN   \n",
       "59.1-142 et seq.             NaN         NaN       NaN        NaN       NaN   \n",
       "59.1-296                     NaN         NaN       NaN        NaN       NaN   \n",
       "62.1-44.2 et seq.            NaN         NaN       NaN        NaN       NaN   \n",
       "62.1-44.34:8                 NaN         NaN       NaN        NaN       NaN   \n",
       "\n",
       "                   ... south_hill west_point new_kent_county halifax_county_2  \\\n",
       "code_va            ...                                                          \n",
       "1-1                ...        NaN        NaN             NaN              NaN   \n",
       "1-21               ...        NaN        NaN             NaN              NaN   \n",
       "1-200 et seq.      ...        NaN        NaN             NaN              NaN   \n",
       "1-210              ...        NaN        NaN             NaN              NaN   \n",
       "1-216              ...        NaN        NaN             NaN              NaN   \n",
       "...                ...        ...        ...             ...              ...   \n",
       "58.1-3993          ...        NaN        NaN             NaN              NaN   \n",
       "59.1-142 et seq.   ...        NaN        NaN             NaN              NaN   \n",
       "59.1-296           ...        NaN        NaN             NaN              NaN   \n",
       "62.1-44.2 et seq.  ...        NaN        NaN             NaN              NaN   \n",
       "62.1-44.34:8       ...        NaN        NaN             NaN              NaN   \n",
       "\n",
       "                  greene_county new_market washington_county manassas  \\\n",
       "code_va                                                                 \n",
       "1-1                         NaN        NaN               NaN      NaN   \n",
       "1-21                        NaN        NaN               NaN      NaN   \n",
       "1-200 et seq.               NaN        NaN               NaN      NaN   \n",
       "1-210                       NaN        NaN               NaN      NaN   \n",
       "1-216                       NaN        NaN               NaN      NaN   \n",
       "...                         ...        ...               ...      ...   \n",
       "58.1-3993                   NaN        NaN               NaN      NaN   \n",
       "59.1-142 et seq.            NaN        NaN               NaN      NaN   \n",
       "59.1-296                    NaN        NaN               NaN      NaN   \n",
       "62.1-44.2 et seq.           NaN        NaN               NaN      NaN   \n",
       "62.1-44.34:8                NaN        NaN               NaN      NaN   \n",
       "\n",
       "                  colonial_beach haymarket_2  \n",
       "code_va                                       \n",
       "1-1                          NaN         NaN  \n",
       "1-21                         NaN         NaN  \n",
       "1-200 et seq.                NaN         NaN  \n",
       "1-210                        NaN         NaN  \n",
       "1-216                        NaN         NaN  \n",
       "...                          ...         ...  \n",
       "58.1-3993                    NaN       14-49  \n",
       "59.1-142 et seq.             NaN        30-4  \n",
       "59.1-296                     NaN        58-1  \n",
       "62.1-44.2 et seq.            NaN      Ch. 50  \n",
       "62.1-44.34:8                 NaN        19-6  \n",
       "\n",
       "[56640 rows x 120 columns]"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "big_df = pd.concat(valid_dfs, join=\"outer\")\n",
    "big_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge Implementation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "def merge_dfs(single_df, df):\n",
    "    return pd.merge(single_df, df, on=\"code_va\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>falls_church</th>\n",
       "      <th>Richmond</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1-210</td>\n",
       "      <td>1-2</td>\n",
       "      <td>1-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1-216</td>\n",
       "      <td>1-2</td>\n",
       "      <td>1-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1-217</td>\n",
       "      <td>1-5</td>\n",
       "      <td>1-8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1-223</td>\n",
       "      <td>1-2</td>\n",
       "      <td>1-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1-227</td>\n",
       "      <td>1-2</td>\n",
       "      <td>1-2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>62.1-44.15:24 et seq.</td>\n",
       "      <td>35-1, 35-2</td>\n",
       "      <td>14-149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>62.1-44.15:51 et seq.</td>\n",
       "      <td>35-1, 35-2</td>\n",
       "      <td>Ch. 14, Art. III</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183</th>\n",
       "      <td>62.1-44.15:52</td>\n",
       "      <td>35-14</td>\n",
       "      <td>14-149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>62.1-44.15:67 et seq.</td>\n",
       "      <td>Ch. 35</td>\n",
       "      <td>Ch. 14, Art. IV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>62.1-44.15:74</td>\n",
       "      <td>35-2</td>\n",
       "      <td>Ch. 14, Art. IV</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>186 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   code_va falls_church          Richmond\n",
       "0                    1-210          1-2               1-2\n",
       "1                    1-216          1-2               1-2\n",
       "2                    1-217          1-5               1-8\n",
       "3                    1-223          1-2               1-2\n",
       "4                    1-227          1-2               1-2\n",
       "..                     ...          ...               ...\n",
       "181  62.1-44.15:24 et seq.   35-1, 35-2            14-149\n",
       "182  62.1-44.15:51 et seq.   35-1, 35-2  Ch. 14, Art. III\n",
       "183          62.1-44.15:52        35-14            14-149\n",
       "184  62.1-44.15:67 et seq.       Ch. 35   Ch. 14, Art. IV\n",
       "185          62.1-44.15:74         35-2   Ch. 14, Art. IV\n",
       "\n",
       "[186 rows x 3 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "big_df = pd.merge(valid_dfs[0], valid_dfs[1], on=\"code_va\")\n",
    "big_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>falls_church</th>\n",
       "      <th>Richmond</th>\n",
       "      <th>prince_george_county</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3.2-6503</td>\n",
       "      <td>4-75</td>\n",
       "      <td>4-241</td>\n",
       "      <td>6-149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.2-6538</td>\n",
       "      <td>4-40, 4-41</td>\n",
       "      <td>4-243</td>\n",
       "      <td>6-146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9.1-101</td>\n",
       "      <td>28-202</td>\n",
       "      <td>3-73</td>\n",
       "      <td>6-3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.1-1400 et seq.</td>\n",
       "      <td>Ch. 34</td>\n",
       "      <td>Ch. 23 (note)</td>\n",
       "      <td>Ch. 66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>15.2-901</td>\n",
       "      <td>33-10</td>\n",
       "      <td>Ch. 11, Art. IV</td>\n",
       "      <td>54-27</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>57</th>\n",
       "      <td>58.1-3800 et seq.</td>\n",
       "      <td>Ch. 40</td>\n",
       "      <td>Ch. 26, Art. XI</td>\n",
       "      <td>Ch. 74, Art. IV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>58.1-3814</td>\n",
       "      <td>40-296—40-299</td>\n",
       "      <td>Ch. 26, Art. VII</td>\n",
       "      <td>74-153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>58.1-3916</td>\n",
       "      <td>40-283</td>\n",
       "      <td>26-331</td>\n",
       "      <td>14-33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>58.1-3986</td>\n",
       "      <td>40-459</td>\n",
       "      <td>26-870</td>\n",
       "      <td>14-34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>58.1-3993</td>\n",
       "      <td>40-459</td>\n",
       "      <td>26-870</td>\n",
       "      <td>14-34</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>62 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              code_va   falls_church          Richmond prince_george_county\n",
       "0            3.2-6503           4-75             4-241                6-149\n",
       "1            3.2-6538     4-40, 4-41             4-243                6-146\n",
       "2             9.1-101         28-202              3-73                  6-3\n",
       "3   10.1-1400 et seq.         Ch. 34     Ch. 23 (note)               Ch. 66\n",
       "4            15.2-901          33-10   Ch. 11, Art. IV                54-27\n",
       "..                ...            ...               ...                  ...\n",
       "57  58.1-3800 et seq.         Ch. 40   Ch. 26, Art. XI      Ch. 74, Art. IV\n",
       "58          58.1-3814  40-296—40-299  Ch. 26, Art. VII               74-153\n",
       "59          58.1-3916         40-283            26-331                14-33\n",
       "60          58.1-3986         40-459            26-870                14-34\n",
       "61          58.1-3993         40-459            26-870                14-34\n",
       "\n",
       "[62 rows x 4 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(big_df, valid_dfs[2], on=\"code_va\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "processed 0 dataframes...\n",
      "processed 1 dataframes...\n",
      "processed 2 dataframes...\n",
      "processed 3 dataframes...\n",
      "processed 4 dataframes...\n"
     ]
    },
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-43-bce6683a3867>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0mbig_df\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmerge\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalid_dfs\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalid_dfs\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mon\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"code_va\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mi\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdf\u001b[0m \u001b[0;32min\u001b[0m \u001b[0menumerate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalid_dfs\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m     \u001b[0mbig_df\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmerge\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mbig_df\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mhow\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"outer\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      4\u001b[0m     \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34mf\"processed {i} dataframes...\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "big_df = pd.merge(valid_dfs[0], valid_dfs[1], on=\"code_va\")\n",
    "for i, df in enumerate(valid_dfs[2:]): \n",
    "    big_df = pd.merge(big_df, df, how=\"outer\")\n",
    "    print(f\"processed {i} dataframes...\")\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "big_df"
   ]
  }
 ],
 "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
}