march_21_merge_county_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Single DataFrame with all County Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import glob\n",
    "import re\n",
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "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": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "county_file_paths = grab_county_file_paths()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "# county_file_paths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "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": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "valid_dfs, invalid_dfs = process_csvs(county_file_paths)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "84"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(valid_dfs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(invalid_dfs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "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": 90,
   "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": 91,
   "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": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merged data\n",
    "big_df = pd.concat(valid_dfs, join=\"outer\")\n",
    "big_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean Data\n",
    "### TODO - up next"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "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": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "big_df.groupby(\"code_va\").get_group(\"1-1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}