state_border_dataset_construction.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Note: This notebook will NOT run in the Project Road Trip environment (prt_env_yaml).\n",
    "# In order to run this notebook you will need to install bs4.\n",
    "# (But the sqlite database object that this notebook created is inlcuded in the repo!)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from bs4 import BeautifulSoup\n",
    "import requests as rq\n",
    "import pandas as pd\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "content = rq.get(\"http://users.econ.umn.edu/~holmes/data/BORDLIST.html\").content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "soup = BeautifulSoup(content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "table = soup.find('table')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean = []\n",
    "for row in table.find_all('tr'):\n",
    "    values = row.find_all('p')\n",
    "    clean.append([value.text for value in values])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# clean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(clean[1:], columns=clean[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>BORDINDX</th>\n",
       "      <th>ST1ST2</th>\n",
       "      <th>LENGTH</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>AL-FL</td>\n",
       "      <td>196.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>AL-GA</td>\n",
       "      <td>291.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>AL-MS</td>\n",
       "      <td>323.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>AL-TN</td>\n",
       "      <td>146.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>AR-LA</td>\n",
       "      <td>166.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  BORDINDX ST1ST2 LENGTH\n",
       "0        1  AL-FL  196.1\n",
       "1        2  AL-GA  291.3\n",
       "2        3  AL-MS  323.9\n",
       "3        4  AL-TN  146.7\n",
       "4        5  AR-LA  166.5"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop('BORDINDX', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['LENGTH'] = df['LENGTH'].map(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>ST1ST2</th>\n",
       "      <th>LENGTH</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>AZ-CO</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>DC-MD</td>\n",
       "      <td>24.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>DC-VA</td>\n",
       "      <td>1.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>NM-UT</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ST1ST2  LENGTH\n",
       "11  AZ-CO     0.0\n",
       "26  DC-MD    24.6\n",
       "27  DC-VA     1.9\n",
       "95  NM-UT     0.0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df['ST1ST2'] == 'AZ-CO') | (df['ST1ST2'] == 'NM-UT') | (df['ST1ST2'] == 'DC-MD') | (df['ST1ST2'] == 'DC-VA')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop([11, 26, 27, 95])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Interstate'] = True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def no_interstates(border):\n",
    "    if border in {'AR-MS', 'CO-OK', 'GA-NC', 'ID-NV', 'ID-WY', 'IA-WI', 'KS-NE', 'KY-MO',\n",
    "                 'KY-VA', 'MI-WI', 'MO-NE', 'MT-SD', 'NE-SD', 'NV-OR', 'NM-OK', 'NY-VT'}:\n",
    "        return False\n",
    "    else:\n",
    "        return True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Interstate'] = df['ST1ST2'].map(no_interstates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['TimeZone'] = [\n",
    "    'Central', 'Central-Eastern', 'Central', 'Central',\n",
    "    'Central', 'Central', 'Central', 'Central', 'Central', 'Central',\n",
    "    'Mountain-Pacific', 'Mountain', 'Mountain-Pacific', 'Mountain',\n",
    "    'Pacific', 'Pacific',\n",
    "    'Mountain & Central-Mountain', 'Mountain', 'Mountain', 'Central-Mountain', 'Mountain', 'Mountain',\n",
    "    'Eastern', 'Eastern', 'Eastern',\n",
    "    'Eastern', 'Eastern', 'Eastern',\n",
    "    'Eastern',\n",
    "    'Eastern', 'Eastern', 'Eastern',\n",
    "    'Central', 'Central', 'Central', 'Central', 'Central',\n",
    "    'Mountain & Mountain-Pacific', 'Mountain-Pacific', 'Mountain & Pacific & Mountain-Pacific', 'Mountain',\n",
    "        'Pacific', 'Mountain',\n",
    "    'Central', 'Central & Central-Eastern', 'Central', 'Central', 'Central',\n",
    "    'Central & Eastern', 'Eastern & Central-Eastern', 'Eastern',\n",
    "    'Central', 'Central & Central-Mountain', 'Central',\n",
    "    'Central', 'Eastern', 'Central & Eastern', 'Eastern', 'Eastern',\n",
    "    'Central', 'Central',\n",
    "    'Eastern', 'Eastern', 'Eastern', 'Eastern',\n",
    "    'Eastern', 'Eastern', 'Eastern',\n",
    "    'Eastern',\n",
    "    'Eastern', 'Central',\n",
    "    'Central', 'Central', 'Central',\n",
    "    'Central', 'Central', 'Central',\n",
    "    'Central',\n",
    "    'Mountain & Central-Mountain', 'Mountain', 'Mountain',\n",
    "    'Eastern', 'Eastern', 'Eastern',\n",
    "    'Central & Mountain',\n",
    "    'Central & Mountain', 'Mountain',\n",
    "    'Eastern',\n",
    "    'Eastern', 'Eastern',\n",
    "    'Central-Mountain', 'Mountain & Central-Mountain',\n",
    "    'Pacific', 'Mountain-Pacific',\n",
    "    'Eastern', 'Eastern',\n",
    "    'Eastern', 'Eastern',\n",
    "    'Central',\n",
    "    'Pacific',\n",
    "    'Eastern',\n",
    "    'Mountain',\n",
    "    'Eastern',\n",
    "    'Mountain',\n",
    "    'Eastern'\n",
    "                 ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>ST1ST2</th>\n",
       "      <th>LENGTH</th>\n",
       "      <th>Interstate</th>\n",
       "      <th>TimeZone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL-GA</td>\n",
       "      <td>291.3</td>\n",
       "      <td>True</td>\n",
       "      <td>Central-Eastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>NM-OK</td>\n",
       "      <td>34.5</td>\n",
       "      <td>False</td>\n",
       "      <td>Central-Mountain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>KS-MO</td>\n",
       "      <td>307.5</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>KY-WV</td>\n",
       "      <td>79.1</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>KY-OH</td>\n",
       "      <td>155.5</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>OH-PA</td>\n",
       "      <td>92.4</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>IL-WI</td>\n",
       "      <td>144.4</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>IA-MO</td>\n",
       "      <td>235.0</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>NV-UT</td>\n",
       "      <td>344.6</td>\n",
       "      <td>True</td>\n",
       "      <td>Mountain-Pacific</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>IN-OH</td>\n",
       "      <td>178.8</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>NM-TX</td>\n",
       "      <td>536.2</td>\n",
       "      <td>True</td>\n",
       "      <td>Mountain &amp; Central-Mountain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>KY-TN</td>\n",
       "      <td>338.5</td>\n",
       "      <td>True</td>\n",
       "      <td>Central &amp; Eastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL-MS</td>\n",
       "      <td>323.9</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>OH-WV</td>\n",
       "      <td>243.5</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>IN-KY</td>\n",
       "      <td>296.0</td>\n",
       "      <td>True</td>\n",
       "      <td>Central &amp; Eastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>OR-WA</td>\n",
       "      <td>385.8</td>\n",
       "      <td>True</td>\n",
       "      <td>Pacific</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>IA-MN</td>\n",
       "      <td>262.1</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>MA-RI</td>\n",
       "      <td>62.6</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>NJ-NY</td>\n",
       "      <td>103.4</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>IA-SD</td>\n",
       "      <td>85.6</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ST1ST2  LENGTH  Interstate                     TimeZone\n",
       "1   AL-GA   291.3        True              Central-Eastern\n",
       "90  NM-OK    34.5       False             Central-Mountain\n",
       "51  KS-MO   307.5        True                      Central\n",
       "58  KY-WV    79.1        True                      Eastern\n",
       "55  KY-OH   155.5        True                      Eastern\n",
       "96  OH-PA    92.4        True                      Eastern\n",
       "47  IL-WI   144.4        True                      Central\n",
       "33  IA-MO   235.0        True                      Central\n",
       "93  NV-UT   344.6        True             Mountain-Pacific\n",
       "50  IN-OH   178.8        True                      Eastern\n",
       "91  NM-TX   536.2        True  Mountain & Central-Mountain\n",
       "56  KY-TN   338.5        True            Central & Eastern\n",
       "2   AL-MS   323.9        True                      Central\n",
       "97  OH-WV   243.5        True                      Eastern\n",
       "48  IN-KY   296.0        True            Central & Eastern\n",
       "99  OR-WA   385.8        True                      Pacific\n",
       "32  IA-MN   262.1        True                      Central\n",
       "63  MA-RI    62.6        True                      Eastern\n",
       "88  NJ-NY   103.4        True                      Eastern\n",
       "35  IA-SD    85.6        True                      Central"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sample(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "length_series = df['LENGTH'].sort_values(ascending=False).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['LengthRank'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "mapper = dict(zip(length_series, length_series.index + 1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['LengthRank'] = df['LENGTH'].map(mapper)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>ST1ST2</th>\n",
       "      <th>LENGTH</th>\n",
       "      <th>Interstate</th>\n",
       "      <th>TimeZone</th>\n",
       "      <th>LengthRank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>CA-NV</td>\n",
       "      <td>608.2</td>\n",
       "      <td>True</td>\n",
       "      <td>Pacific</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>MA-RI</td>\n",
       "      <td>62.6</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL-GA</td>\n",
       "      <td>291.3</td>\n",
       "      <td>True</td>\n",
       "      <td>Central-Eastern</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>ID-WY</td>\n",
       "      <td>170.7</td>\n",
       "      <td>False</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>IA-NE</td>\n",
       "      <td>148.9</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AR-MO</td>\n",
       "      <td>325.3</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>OH-PA</td>\n",
       "      <td>92.4</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>IA-MN</td>\n",
       "      <td>262.1</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL-FL</td>\n",
       "      <td>196.1</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>MN-WI</td>\n",
       "      <td>291.1</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>LA-TX</td>\n",
       "      <td>222.7</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>MA-NY</td>\n",
       "      <td>49.4</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>NJ-NY</td>\n",
       "      <td>103.4</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>KS-MO</td>\n",
       "      <td>307.5</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>OR-WA</td>\n",
       "      <td>385.8</td>\n",
       "      <td>True</td>\n",
       "      <td>Pacific</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>CO-OK</td>\n",
       "      <td>52.9</td>\n",
       "      <td>False</td>\n",
       "      <td>Central-Mountain</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>AR-TX</td>\n",
       "      <td>65.0</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>KY-WV</td>\n",
       "      <td>79.1</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>DE-PA</td>\n",
       "      <td>22.6</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>NH-VT</td>\n",
       "      <td>180.0</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ST1ST2  LENGTH  Interstate          TimeZone  LengthRank\n",
       "14  CA-NV   608.2        True           Pacific           2\n",
       "63  MA-RI    62.6        True           Eastern          94\n",
       "1   AL-GA   291.3        True   Central-Eastern          27\n",
       "42  ID-WY   170.7       False          Mountain          59\n",
       "34  IA-NE   148.9        True           Central          67\n",
       "5   AR-MO   325.3        True           Central          19\n",
       "96  OH-PA    92.4        True           Eastern          81\n",
       "32  IA-MN   262.1        True           Central          32\n",
       "0   AL-FL   196.1        True           Central          50\n",
       "73  MN-WI   291.1        True           Central          28\n",
       "60  LA-TX   222.7        True           Central          39\n",
       "62  MA-NY    49.4        True           Eastern          99\n",
       "88  NJ-NY   103.4        True           Eastern          80\n",
       "51  KS-MO   307.5        True           Central          22\n",
       "99  OR-WA   385.8        True           Pacific           9\n",
       "19  CO-OK    52.9       False  Central-Mountain          98\n",
       "9   AR-TX    65.0        True           Central          93\n",
       "58  KY-WV    79.1        True           Eastern          88\n",
       "27  DE-PA    22.6        True           Eastern         104\n",
       "87  NH-VT   180.0        True           Eastern          54"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sample(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>ST1ST2</th>\n",
       "      <th>LENGTH</th>\n",
       "      <th>Interstate</th>\n",
       "      <th>TimeZone</th>\n",
       "      <th>LengthRank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>DE-NJ</td>\n",
       "      <td>1.4</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>OK-TX</td>\n",
       "      <td>715.4</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ST1ST2  LENGTH  Interstate TimeZone  LengthRank\n",
       "26  DE-NJ     1.4        True  Eastern         105\n",
       "98  OK-TX   715.4        True  Central           1"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df['LengthRank'] == 1) | (df['LengthRank'] == 105)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[['ST1ST2', 'LENGTH', 'LengthRank', 'Interstate', 'TimeZone']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ST1ST2</th>\n",
       "      <th>LENGTH</th>\n",
       "      <th>LengthRank</th>\n",
       "      <th>Interstate</th>\n",
       "      <th>TimeZone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL-FL</td>\n",
       "      <td>196.1</td>\n",
       "      <td>50</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL-GA</td>\n",
       "      <td>291.3</td>\n",
       "      <td>27</td>\n",
       "      <td>True</td>\n",
       "      <td>Central-Eastern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL-MS</td>\n",
       "      <td>323.9</td>\n",
       "      <td>20</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL-TN</td>\n",
       "      <td>146.7</td>\n",
       "      <td>68</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AR-LA</td>\n",
       "      <td>166.5</td>\n",
       "      <td>60</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AR-MO</td>\n",
       "      <td>325.3</td>\n",
       "      <td>19</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AR-MS</td>\n",
       "      <td>282.8</td>\n",
       "      <td>29</td>\n",
       "      <td>False</td>\n",
       "      <td>Central</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AR-OK</td>\n",
       "      <td>198.2</td>\n",
       "      <td>49</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>AR-TN</td>\n",
       "      <td>125.1</td>\n",
       "      <td>72</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>AR-TX</td>\n",
       "      <td>65.0</td>\n",
       "      <td>93</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ST1ST2  LENGTH  LengthRank  Interstate         TimeZone\n",
       "0  AL-FL   196.1          50        True          Central\n",
       "1  AL-GA   291.3          27        True  Central-Eastern\n",
       "2  AL-MS   323.9          20        True          Central\n",
       "3  AL-TN   146.7          68        True          Central\n",
       "4  AR-LA   166.5          60        True          Central\n",
       "5  AR-MO   325.3          19        True          Central\n",
       "6  AR-MS   282.8          29       False          Central\n",
       "7  AR-OK   198.2          49        True          Central\n",
       "8  AR-TN   125.1          72        True          Central\n",
       "9  AR-TX    65.0          93        True          Central"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "river_content = rq.get('https://en.wikipedia.org/wiki/List_of_river_borders_of_U.S._states').content\n",
    "river_soup = BeautifulSoup(river_content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "river_list = river_soup.find('div', id='mw-content-text').find_all('ul')[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Arthur Kill: New Jersey, New York (tidal strait)'"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "river_list.find_all('li')[0].text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "rivers = {}\n",
    "\n",
    "for bord in river_list.find_all('li'):\n",
    "    colon = bord.text.index(':')\n",
    "    rivers[bord.text[:colon]] = bord.text[colon+2:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Arthur Kill': 'New Jersey, New York (tidal strait)',\n",
       " 'Big Sandy River': 'Kentucky, West Virginia',\n",
       " 'Big Sioux River': 'South Dakota, Iowa',\n",
       " 'Blackwater River': 'Virginia, North Carolina',\n",
       " 'Bois de Sioux River': 'South Dakota, Minnesota, North Dakota',\n",
       " 'Brule River': 'Michigan, Wisconsin',\n",
       " 'Byram River': 'Connecticut, New York',\n",
       " 'Catawba River': 'North Carolina, South Carolina',\n",
       " 'Chattahoochee River': 'Alabama, Florida, Georgia',\n",
       " 'Chattooga River': ' Georgia, South Carolina',\n",
       " 'Colorado River': 'Arizona, Nevada, California, Baja California',\n",
       " 'Columbia River': 'Washington, Oregon',\n",
       " 'Connecticut River': 'New Hampshire, Vermont',\n",
       " 'Delaware River': 'New York, Pennsylvania, New Jersey, Delaware',\n",
       " 'Des Moines River': 'Iowa, Missouri',\n",
       " 'Detroit River': 'Michigan, Ontario',\n",
       " 'Great Miami River (mouth only)': 'Ohio, Indiana',\n",
       " 'Halls Stream': 'New Hampshire, Quebec',\n",
       " 'Hudson River (lower part only)': 'New Jersey, New York',\n",
       " 'Kill Van Kull': 'New Jersey, New York (tidal strait)',\n",
       " 'Menominee River': 'Michigan, Wisconsin',\n",
       " 'Mississippi River': 'Minnesota, Wisconsin, Iowa, Illinois, Missouri, Kentucky, Tennessee, Arkansas, Mississippi, and Louisiana',\n",
       " 'Missouri River': 'South Dakota, Nebraska, Iowa, Missouri, Kansas',\n",
       " 'Montreal River': 'Michigan (Upper Peninsula), Wisconsin',\n",
       " 'Niagara River': 'New York, Ontario',\n",
       " 'Ohio River': 'Illinois, Indiana, Ohio, Kentucky, West Virginia',\n",
       " 'Palmer River': 'Rhode Island, Massachusetts',\n",
       " 'Pawcatuck River': 'Connecticut, Rhode Island',\n",
       " 'Pearl River': 'Mississippi, Louisiana',\n",
       " 'Perdido River': 'Florida, Alabama',\n",
       " 'Pigeon River': 'Minnesota, Ontario',\n",
       " 'Pine River': 'Minnesota, Ontario',\n",
       " 'Piscataqua River': 'Maine, New Hampshire',\n",
       " 'Pocomoke River': 'Maryland, Virginia',\n",
       " 'Poteau River': 'Arkansas, Oklahoma',\n",
       " 'Potomac River': 'Maryland, Virginia, D.C., West Virginia',\n",
       " 'Poultney River': 'Vermont, New York',\n",
       " 'Rainy River': 'Minnesota, Ontario',\n",
       " 'Red River of the North': 'North Dakota, Minnesota',\n",
       " 'Red River of the South': 'Texas, Oklahoma, Arkansas',\n",
       " 'Rio Grande': 'New Mexico, Texas, Chihuahua, Coahuila, Nuevo León, Tamaulipas',\n",
       " 'Runnins River': 'Rhode Island, Massachusetts',\n",
       " 'Sabine River': 'Texas, Louisiana',\n",
       " 'St. Clair River': 'Michigan, Ontario',\n",
       " 'St. Croix River (Maine-New Brunswick)': 'Maine, New Brunswick',\n",
       " 'St. Croix River (Wisconsin-Minnesota)': 'Minnesota, Wisconsin',\n",
       " 'St. Francis River (Missouri-Arkansas)': 'Arkansas, Missouri',\n",
       " 'St. Francis River (Quebec-Maine)': 'Maine, Quebec',\n",
       " 'St. John River': 'Maine, New Brunswick',\n",
       " 'St. Lawrence River': 'New York, Ontario',\n",
       " 'St. Louis River': 'Minnesota, Wisconsin',\n",
       " 'St. Marys River (Florida-Georgia)': 'Florida, Georgia',\n",
       " 'St. Marys River (Michigan-Ontario)': 'Michigan, Ontario',\n",
       " 'Salmon Falls River': 'New Hampshire, Maine',\n",
       " 'Savannah River': 'South Carolina, Georgia',\n",
       " 'Snake River': 'Idaho, Washington, Oregon',\n",
       " 'Tennessee River': 'Kentucky, Tennessee, Mississippi, Alabama',\n",
       " 'Tug Fork River': 'Kentucky, West Virginia, Virginia',\n",
       " 'Tugaloo River': 'Georgia, South Carolina',\n",
       " 'Wabash River': 'Illinois, Indiana',\n",
       " 'Montreal  River': 'Michigan, Wisconsin'}"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rivers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(rivers.values()) == len(set(rivers.values()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "revers = {}\n",
    "revers['AL-FL'] = 'Perdido'\n",
    "revers['AL-GA'] = 'Chattahoochee'\n",
    "revers['AL-MS'] = 'Tennessee'\n",
    "revers['AR-MO'] = 'St. Francis'\n",
    "revers['AR-MS'] = 'Mississippi'\n",
    "revers['AR-OK'] = 'Poteau'\n",
    "revers['AR-TN'] = 'Mississippi'\n",
    "revers['AR-TX'] = 'Red (South)'\n",
    "revers['AZ-CA'] = 'Colorado'\n",
    "revers['AZ-NV'] = 'Colorado'\n",
    "revers['CT-NY'] = 'Byram'\n",
    "revers['CT-RI'] = 'Pawcatuck'\n",
    "revers['DE-NJ'] = 'Delaware'\n",
    "revers['FL-GA'] = 'St. Marys'\n",
    "revers['GA-SC'] = 'Savannah'\n",
    "revers['IA-MO'] = 'Des Moines'\n",
    "revers['IA-NE'] = 'Missouri'\n",
    "revers['IA-SD'] = 'Big Sioux'\n",
    "revers['IA-WI'] = 'Mississippi'\n",
    "revers['ID-OR'] = 'Snake'\n",
    "revers['ID-WA'] = 'Snake'\n",
    "revers['IL-IA'] = 'Mississippi'\n",
    "revers['IL-IN'] = 'Wabash'\n",
    "revers['IL-KY'] = 'Ohio'\n",
    "revers['IL-MO'] = 'Mississippi'\n",
    "revers['IN-KY'] = 'Ohio'\n",
    "revers['KS-MO'] = 'Missouri'\n",
    "revers['KY-MO'] = 'Mississippi'\n",
    "revers['KY-WV'] = 'Big Sandy'\n",
    "revers['LA-MS'] = 'Mississippi'\n",
    "revers['LA-TX'] = 'Sabine'\n",
    "revers['MA-RI'] = 'Palmer'\n",
    "revers['MD-VA'] = 'Potomac'\n",
    "revers['MD-WV'] = 'Potomac'\n",
    "revers['ME-NH'] = 'Piscataqua'\n",
    "revers['MI-WI'] = 'Menominee'\n",
    "revers['MN-ND'] = 'Red (North)'\n",
    "revers['MN-SD'] = 'Bois de Sioux'\n",
    "revers['MN-WI'] = 'Mississippi'\n",
    "revers['MO-NE'] = 'Missouri'\n",
    "revers['MO-TN'] = 'Mississippi'\n",
    "revers['NC-SC'] = 'Catawba'\n",
    "revers['NC-VA'] = 'Blackwater'\n",
    "revers['NE-SD'] = 'Missouri'\n",
    "revers['NH-VT'] = 'Connecticut'\n",
    "revers['NJ-NY'] = 'Hudson'\n",
    "revers['NJ-PA'] = 'Delaware'\n",
    "revers['NY-PA'] = 'Delaware'\n",
    "revers['NY-VT'] = 'Poultney'\n",
    "revers['OH-KY'] = 'Ohio'\n",
    "revers['OH-WV'] = 'Ohio'\n",
    "revers['OK-TX'] = 'Red (South)'\n",
    "revers['OR-WA'] = 'Columbia'\n",
    "revers['VA-WV'] = 'Potomac'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "def dict_mapper(bord):\n",
    "    if bord in revers.keys():\n",
    "        return revers[bord]\n",
    "    else:\n",
    "        return 'N/A'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['River'] = df['ST1ST2'].map(dict_mapper)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>ST1ST2</th>\n",
       "      <th>LENGTH</th>\n",
       "      <th>LengthRank</th>\n",
       "      <th>Interstate</th>\n",
       "      <th>TimeZone</th>\n",
       "      <th>River</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL-GA</td>\n",
       "      <td>291.3</td>\n",
       "      <td>27</td>\n",
       "      <td>True</td>\n",
       "      <td>Central-Eastern</td>\n",
       "      <td>Chattahoochee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>AZ-CA</td>\n",
       "      <td>198.4</td>\n",
       "      <td>48</td>\n",
       "      <td>True</td>\n",
       "      <td>Mountain-Pacific</td>\n",
       "      <td>Colorado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>KS-MO</td>\n",
       "      <td>307.5</td>\n",
       "      <td>22</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "      <td>Missouri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>OR-WA</td>\n",
       "      <td>385.8</td>\n",
       "      <td>9</td>\n",
       "      <td>True</td>\n",
       "      <td>Pacific</td>\n",
       "      <td>Columbia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>ID-MT</td>\n",
       "      <td>569.8</td>\n",
       "      <td>3</td>\n",
       "      <td>True</td>\n",
       "      <td>Mountain &amp; Mountain-Pacific</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>NE-WY</td>\n",
       "      <td>138.0</td>\n",
       "      <td>70</td>\n",
       "      <td>True</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AR-MS</td>\n",
       "      <td>282.8</td>\n",
       "      <td>29</td>\n",
       "      <td>False</td>\n",
       "      <td>Central</td>\n",
       "      <td>Mississippi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>ID-OR</td>\n",
       "      <td>305.8</td>\n",
       "      <td>24</td>\n",
       "      <td>True</td>\n",
       "      <td>Mountain &amp; Pacific &amp; Mountain-Pacific</td>\n",
       "      <td>Snake</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>IA-SD</td>\n",
       "      <td>85.6</td>\n",
       "      <td>85</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "      <td>Big Sioux</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>IN-MI</td>\n",
       "      <td>108.4</td>\n",
       "      <td>79</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern &amp; Central-Eastern</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>KY-WV</td>\n",
       "      <td>79.1</td>\n",
       "      <td>88</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Big Sandy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>MO-NE</td>\n",
       "      <td>53.6</td>\n",
       "      <td>97</td>\n",
       "      <td>False</td>\n",
       "      <td>Central</td>\n",
       "      <td>Missouri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>IL-KY</td>\n",
       "      <td>122.4</td>\n",
       "      <td>73</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "      <td>Ohio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>NJ-PA</td>\n",
       "      <td>150.9</td>\n",
       "      <td>66</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Delaware</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>NY-VT</td>\n",
       "      <td>80.3</td>\n",
       "      <td>87</td>\n",
       "      <td>False</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Poultney</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>MO-TN</td>\n",
       "      <td>60.9</td>\n",
       "      <td>95</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "      <td>Mississippi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>MA-NY</td>\n",
       "      <td>49.4</td>\n",
       "      <td>99</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>MA-NH</td>\n",
       "      <td>86.7</td>\n",
       "      <td>83</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>MO-OK</td>\n",
       "      <td>34.4</td>\n",
       "      <td>103</td>\n",
       "      <td>True</td>\n",
       "      <td>Central</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>NJ-NY</td>\n",
       "      <td>103.4</td>\n",
       "      <td>80</td>\n",
       "      <td>True</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Hudson</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ST1ST2  LENGTH  LengthRank  Interstate  \\\n",
       "1   AL-GA   291.3          27        True   \n",
       "10  AZ-CA   198.4          48        True   \n",
       "51  KS-MO   307.5          22        True   \n",
       "99  OR-WA   385.8           9        True   \n",
       "37  ID-MT   569.8           3        True   \n",
       "86  NE-WY   138.0          70        True   \n",
       "6   AR-MS   282.8          29       False   \n",
       "39  ID-OR   305.8          24        True   \n",
       "35  IA-SD    85.6          85        True   \n",
       "49  IN-MI   108.4          79        True   \n",
       "58  KY-WV    79.1          88        True   \n",
       "74  MO-NE    53.6          97       False   \n",
       "45  IL-KY   122.4          73        True   \n",
       "89  NJ-PA   150.9          66        True   \n",
       "95  NY-VT    80.3          87       False   \n",
       "76  MO-TN    60.9          95        True   \n",
       "62  MA-NY    49.4          99        True   \n",
       "61  MA-NH    86.7          83        True   \n",
       "75  MO-OK    34.4         103        True   \n",
       "88  NJ-NY   103.4          80        True   \n",
       "\n",
       "                                 TimeZone          River  \n",
       "1                         Central-Eastern  Chattahoochee  \n",
       "10                       Mountain-Pacific       Colorado  \n",
       "51                                Central       Missouri  \n",
       "99                                Pacific       Columbia  \n",
       "37            Mountain & Mountain-Pacific            N/A  \n",
       "86                               Mountain            N/A  \n",
       "6                                 Central    Mississippi  \n",
       "39  Mountain & Pacific & Mountain-Pacific          Snake  \n",
       "35                                Central      Big Sioux  \n",
       "49              Eastern & Central-Eastern            N/A  \n",
       "58                                Eastern      Big Sandy  \n",
       "74                                Central       Missouri  \n",
       "45                                Central           Ohio  \n",
       "89                                Eastern       Delaware  \n",
       "95                                Eastern       Poultney  \n",
       "76                                Central    Mississippi  \n",
       "62                                Eastern            N/A  \n",
       "61                                Eastern            N/A  \n",
       "75                                Central            N/A  \n",
       "88                                Eastern         Hudson  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sample(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect('borders.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.to_sql('borders', conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>index</th>\n",
       "      <th>ST1ST2</th>\n",
       "      <th>LENGTH</th>\n",
       "      <th>LengthRank</th>\n",
       "      <th>Interstate</th>\n",
       "      <th>TimeZone</th>\n",
       "      <th>River</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>AL-FL</td>\n",
       "      <td>196.1</td>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "      <td>Central</td>\n",
       "      <td>Perdido</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>AL-GA</td>\n",
       "      <td>291.3</td>\n",
       "      <td>27</td>\n",
       "      <td>1</td>\n",
       "      <td>Central-Eastern</td>\n",
       "      <td>Chattahoochee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>AL-MS</td>\n",
       "      <td>323.9</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>Central</td>\n",
       "      <td>Tennessee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>AL-TN</td>\n",
       "      <td>146.7</td>\n",
       "      <td>68</td>\n",
       "      <td>1</td>\n",
       "      <td>Central</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>AR-LA</td>\n",
       "      <td>166.5</td>\n",
       "      <td>60</td>\n",
       "      <td>1</td>\n",
       "      <td>Central</td>\n",
       "      <td>N/A</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>100</td>\n",
       "      <td>PA-WV</td>\n",
       "      <td>118.6</td>\n",
       "      <td>77</td>\n",
       "      <td>1</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>101</td>\n",
       "      <td>SD-WY</td>\n",
       "      <td>137.7</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>102</td>\n",
       "      <td>TN-VA</td>\n",
       "      <td>110.7</td>\n",
       "      <td>78</td>\n",
       "      <td>1</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>103</td>\n",
       "      <td>UT-WY</td>\n",
       "      <td>173.1</td>\n",
       "      <td>58</td>\n",
       "      <td>1</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>104</td>\n",
       "      <td>VA-WV</td>\n",
       "      <td>381.2</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Potomac</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>105 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     index ST1ST2  LENGTH  LengthRank  Interstate         TimeZone  \\\n",
       "0        0  AL-FL   196.1          50           1          Central   \n",
       "1        1  AL-GA   291.3          27           1  Central-Eastern   \n",
       "2        2  AL-MS   323.9          20           1          Central   \n",
       "3        3  AL-TN   146.7          68           1          Central   \n",
       "4        4  AR-LA   166.5          60           1          Central   \n",
       "..     ...    ...     ...         ...         ...              ...   \n",
       "100    100  PA-WV   118.6          77           1          Eastern   \n",
       "101    101  SD-WY   137.7          71           1         Mountain   \n",
       "102    102  TN-VA   110.7          78           1          Eastern   \n",
       "103    103  UT-WY   173.1          58           1         Mountain   \n",
       "104    104  VA-WV   381.2          10           1          Eastern   \n",
       "\n",
       "             River  \n",
       "0          Perdido  \n",
       "1    Chattahoochee  \n",
       "2        Tennessee  \n",
       "3              N/A  \n",
       "4              N/A  \n",
       "..             ...  \n",
       "100            N/A  \n",
       "101            N/A  \n",
       "102            N/A  \n",
       "103            N/A  \n",
       "104        Potomac  \n",
       "\n",
       "[105 rows x 7 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"\"\"\n",
    "    SELECT *\n",
    "    FROM borders\n",
    "    \"\"\"\n",
    ", conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>index</th>\n",
       "      <th>ST1ST2</th>\n",
       "      <th>LENGTH</th>\n",
       "      <th>LengthRank</th>\n",
       "      <th>Interstate</th>\n",
       "      <th>TimeZone</th>\n",
       "      <th>River</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>98</td>\n",
       "      <td>OK-TX</td>\n",
       "      <td>715.4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Central</td>\n",
       "      <td>Red (South)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14</td>\n",
       "      <td>CA-NV</td>\n",
       "      <td>608.2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Pacific</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>37</td>\n",
       "      <td>ID-MT</td>\n",
       "      <td>569.8</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Mountain &amp; Mountain-Pacific</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>91</td>\n",
       "      <td>NM-TX</td>\n",
       "      <td>536.2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>Mountain &amp; Central-Mountain</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>59</td>\n",
       "      <td>LA-MS</td>\n",
       "      <td>456.6</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>Central</td>\n",
       "      <td>Mississippi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>53</td>\n",
       "      <td>KS-OK</td>\n",
       "      <td>409.1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>Central</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>85</td>\n",
       "      <td>NE-SD</td>\n",
       "      <td>399.4</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>Central &amp; Mountain</td>\n",
       "      <td>Missouri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>11</td>\n",
       "      <td>AZ-NM</td>\n",
       "      <td>391.0</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>99</td>\n",
       "      <td>OR-WA</td>\n",
       "      <td>385.8</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>Pacific</td>\n",
       "      <td>Columbia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>104</td>\n",
       "      <td>VA-WV</td>\n",
       "      <td>381.2</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Potomac</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>80</td>\n",
       "      <td>MT-WY</td>\n",
       "      <td>377.7</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>84</td>\n",
       "      <td>ND-SD</td>\n",
       "      <td>359.0</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>Central &amp; Mountain</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>52</td>\n",
       "      <td>KS-NE</td>\n",
       "      <td>356.5</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>Central &amp; Central-Mountain</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>93</td>\n",
       "      <td>NV-UT</td>\n",
       "      <td>344.6</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>Mountain-Pacific</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>56</td>\n",
       "      <td>KY-TN</td>\n",
       "      <td>338.5</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>Central &amp; Eastern</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>18</td>\n",
       "      <td>CO-NM</td>\n",
       "      <td>333.0</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>46</td>\n",
       "      <td>IL-MO</td>\n",
       "      <td>331.6</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>Central</td>\n",
       "      <td>Mississippi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>83</td>\n",
       "      <td>NC-VA</td>\n",
       "      <td>327.7</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Blackwater</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>5</td>\n",
       "      <td>AR-MO</td>\n",
       "      <td>325.3</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>Central</td>\n",
       "      <td>St. Francis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2</td>\n",
       "      <td>AL-MS</td>\n",
       "      <td>323.9</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>Central</td>\n",
       "      <td>Tennessee</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    index ST1ST2  LENGTH  LengthRank  Interstate                     TimeZone  \\\n",
       "0      98  OK-TX   715.4           1           1                      Central   \n",
       "1      14  CA-NV   608.2           2           1                      Pacific   \n",
       "2      37  ID-MT   569.8           3           1  Mountain & Mountain-Pacific   \n",
       "3      91  NM-TX   536.2           4           1  Mountain & Central-Mountain   \n",
       "4      59  LA-MS   456.6           5           1                      Central   \n",
       "5      53  KS-OK   409.1           6           1                      Central   \n",
       "6      85  NE-SD   399.4           7           0           Central & Mountain   \n",
       "7      11  AZ-NM   391.0           8           1                     Mountain   \n",
       "8      99  OR-WA   385.8           9           1                      Pacific   \n",
       "9     104  VA-WV   381.2          10           1                      Eastern   \n",
       "10     80  MT-WY   377.7          11           1                     Mountain   \n",
       "11     84  ND-SD   359.0          12           1           Central & Mountain   \n",
       "12     52  KS-NE   356.5          13           0   Central & Central-Mountain   \n",
       "13     93  NV-UT   344.6          14           1             Mountain-Pacific   \n",
       "14     56  KY-TN   338.5          15           1            Central & Eastern   \n",
       "15     18  CO-NM   333.0          16           1                     Mountain   \n",
       "16     46  IL-MO   331.6          17           1                      Central   \n",
       "17     83  NC-VA   327.7          18           1                      Eastern   \n",
       "18      5  AR-MO   325.3          19           1                      Central   \n",
       "19      2  AL-MS   323.9          20           1                      Central   \n",
       "\n",
       "          River  \n",
       "0   Red (South)  \n",
       "1           N/A  \n",
       "2           N/A  \n",
       "3           N/A  \n",
       "4   Mississippi  \n",
       "5           N/A  \n",
       "6      Missouri  \n",
       "7           N/A  \n",
       "8      Columbia  \n",
       "9       Potomac  \n",
       "10          N/A  \n",
       "11          N/A  \n",
       "12          N/A  \n",
       "13          N/A  \n",
       "14          N/A  \n",
       "15          N/A  \n",
       "16  Mississippi  \n",
       "17   Blackwater  \n",
       "18  St. Francis  \n",
       "19    Tennessee  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"\"\"\n",
    "    SELECT *\n",
    "    FROM borders\n",
    "    ORDER BY LengthRank\n",
    "    LIMIT 20\n",
    "    \"\"\", conn\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>index</th>\n",
       "      <th>ST1ST2</th>\n",
       "      <th>LENGTH</th>\n",
       "      <th>LengthRank</th>\n",
       "      <th>Interstate</th>\n",
       "      <th>TimeZone</th>\n",
       "      <th>River</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>37</td>\n",
       "      <td>ID-MT</td>\n",
       "      <td>569.8</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Mountain &amp; Mountain-Pacific</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>39</td>\n",
       "      <td>ID-OR</td>\n",
       "      <td>305.8</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>Mountain &amp; Pacific &amp; Mountain-Pacific</td>\n",
       "      <td>Snake</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>41</td>\n",
       "      <td>ID-WA</td>\n",
       "      <td>210.3</td>\n",
       "      <td>44</td>\n",
       "      <td>1</td>\n",
       "      <td>Pacific</td>\n",
       "      <td>Snake</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>42</td>\n",
       "      <td>ID-WY</td>\n",
       "      <td>170.7</td>\n",
       "      <td>59</td>\n",
       "      <td>0</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>40</td>\n",
       "      <td>ID-UT</td>\n",
       "      <td>153.6</td>\n",
       "      <td>63</td>\n",
       "      <td>1</td>\n",
       "      <td>Mountain</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>38</td>\n",
       "      <td>ID-NV</td>\n",
       "      <td>153.0</td>\n",
       "      <td>64</td>\n",
       "      <td>0</td>\n",
       "      <td>Mountain-Pacific</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index ST1ST2  LENGTH  LengthRank  Interstate  \\\n",
       "0     37  ID-MT   569.8           3           1   \n",
       "1     39  ID-OR   305.8          24           1   \n",
       "2     41  ID-WA   210.3          44           1   \n",
       "3     42  ID-WY   170.7          59           0   \n",
       "4     40  ID-UT   153.6          63           1   \n",
       "5     38  ID-NV   153.0          64           0   \n",
       "\n",
       "                                TimeZone  River  \n",
       "0            Mountain & Mountain-Pacific    N/A  \n",
       "1  Mountain & Pacific & Mountain-Pacific  Snake  \n",
       "2                                Pacific  Snake  \n",
       "3                               Mountain    N/A  \n",
       "4                               Mountain    N/A  \n",
       "5                       Mountain-Pacific    N/A  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"\"\"\n",
    "    SELECT *\n",
    "    FROM borders\n",
    "    WHERE ST1ST2 LIKE '%ID%'\n",
    "    ORDER BY LengthRank\n",
    "    \"\"\", conn\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>index</th>\n",
       "      <th>ST1ST2</th>\n",
       "      <th>LENGTH</th>\n",
       "      <th>LengthRank</th>\n",
       "      <th>Interstate</th>\n",
       "      <th>TimeZone</th>\n",
       "      <th>River</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>97</td>\n",
       "      <td>OH-WV</td>\n",
       "      <td>243.5</td>\n",
       "      <td>37</td>\n",
       "      <td>1</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Ohio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50</td>\n",
       "      <td>IN-OH</td>\n",
       "      <td>178.8</td>\n",
       "      <td>55</td>\n",
       "      <td>1</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>55</td>\n",
       "      <td>KY-OH</td>\n",
       "      <td>155.5</td>\n",
       "      <td>62</td>\n",
       "      <td>1</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>96</td>\n",
       "      <td>OH-PA</td>\n",
       "      <td>92.4</td>\n",
       "      <td>81</td>\n",
       "      <td>1</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>69</td>\n",
       "      <td>MI-OH</td>\n",
       "      <td>69.7</td>\n",
       "      <td>90</td>\n",
       "      <td>1</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>N/A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index ST1ST2  LENGTH  LengthRank  Interstate TimeZone River\n",
       "0     97  OH-WV   243.5          37           1  Eastern  Ohio\n",
       "1     50  IN-OH   178.8          55           1  Eastern   N/A\n",
       "2     55  KY-OH   155.5          62           1  Eastern   N/A\n",
       "3     96  OH-PA    92.4          81           1  Eastern   N/A\n",
       "4     69  MI-OH    69.7          90           1  Eastern   N/A"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\n",
    "    \"\"\"\n",
    "    SELECT *\n",
    "    FROM borders\n",
    "    WHERE ST1ST2 LIKE '%OH%'\n",
    "    ORDER BY LengthRank\n",
    "    \"\"\", conn\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "learn-env",
   "language": "python",
   "name": "learn-env"
  },
  "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}