{
 "cells": [
  {
   "cell_type": "raw",
   "metadata": {
    "vscode": {
     "languageId": "raw"
    }
   },
   "source": [
    "---\n",
    "title: \"Data Cleaning with Python\"\n",
    "author: \"Jeff Jacobs\"\n",
    "institute: \"<a href='mailto:jj1088@georgetown.edu' target='_blank'>`jj1088@georgetown.edu`</a>\"\n",
    "date: 2024-09-26\n",
    "bibliography: \"../../_DSAN5000.bib\"\n",
    "sidebar: mainnav\n",
    "categories:\n",
    "  - \"Extra Writeups\"\n",
    "execute:\n",
    "  enabled: true\n",
    "notebook-view:\n",
    "  - notebook: clean_data.ipynb\n",
    "    title: \"Data Cleaning with Python\"\n",
    "    url: https://colab.research.google.com/drive/18cyT9vc_qJRJWgegjmAH2tmtYpsq0Npe\n",
    "format:\n",
    "  html:\n",
    "    toc: true\n",
    "    df-print: kable\n",
    "    link-external-newwindow: true\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "::: {.callout-note title=\"Links\"}\n",
    "\n",
    "* This is the Jupyter notebook I created during the [**Video Walkthrough**](../../extra-videos/recording-w05-data-cleaning.html){target=\"_blank\"}\n",
    "* [**Run on Colab**](https://colab.research.google.com/drive/18cyT9vc_qJRJWgegjmAH2tmtYpsq0Npe){target=\"_blank\"} to pause the video and edit interactively!\n",
    "\n",
    ":::\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## (1) Overview\n",
    "\n",
    "A series of longitudinal studies in the *Journal of the American Medical Association (JAMA)*, most recently @reuben_association_2019 [direct link](https://www.ncbi.nlm.nih.gov/pmc/articles/PMC6450277/){target='_blank'}, have found consistent and robust associations between childhood exposure to **lead** and adult psychopathology:\n",
    "\n",
    "> In this multidecade, longitudinal study of lead-exposed children, higher childhood blood lead level was associated with greater psychopathology across the life course and difficult adult personality traits. Childhood lead exposure may have long-term consequences for adult mental health and personality. [@reuben_association_2019]\n",
    "\n",
    "Though we won't explore that association directly here, I wanted to gather two datasets that would be relevant for studying it, to demonstrate some basic data-cleaning steps using Python libraries like `pandas` and `openpyxl`.\n",
    "\n",
    "If you're interested in applying your DSAN 5000 skills to the healthcare sphere, definitely consider studying this issue! I have a close friend, for example, who teaches at a school in East Oakland which is [currently facing a lead contamination crisis](https://www.cbsnews.com/sanfrancisco/news/elevated-lead-levels-in-drinking-water-at-oakland-schools-sparks-outrage/){target='_blank'}. As a third dataset on top of the two we clean here, for example, you could try finding data on income by neighborhood---there is a reason why lead contamination is continually \"discovered\" at schools in neighborhoods like East Oakland and [West Baltimore](https://www.wbaltv.com/article/maryland-schools-arent-fixing-lead-in-water/43876244){target='_blank'}, and not at schools in neighborhoods like Georgetown."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## (2) Navigating The `.csv` Seas with `glob`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, since the `.csv` filenames are super long, let's just use one of my favorite Python libraries, `glob`, to automatically get a list of all the `.csv` files within the same folder as this notebook, so we don't even have to manually type the name(s) of the data files in our code!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['data/SHIP_Emergency_Department_Visits_Related_To_Mental_Health_Conditions_2008-2017.csv',\n",
       " 'data/fakedata.dat',\n",
       " 'data/MD_CountyLevelSummary_2017.xlsx']"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import glob\n",
    "glob.glob(\"data/*\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 data/SHIP_Emergency_Department_Visits_Related_To_Mental_Health_Conditions_2008-2017.csv\n",
      "1 data/MD_CountyLevelSummary_2017.xlsx\n"
     ]
    }
   ],
   "source": [
    "csv_fpaths = glob.glob(\"data/*.csv\")\n",
    "xlsx_fpaths = glob.glob(\"data/*.xlsx\")\n",
    "data_fpaths = csv_fpaths + xlsx_fpaths\n",
    "# for fpath in all_data_fpaths:\n",
    "#     print(fpath)\n",
    "for fpath_index, fpath in enumerate(data_fpaths):\n",
    "    print(fpath_index, fpath)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'data/SHIP_Emergency_Department_Visits_Related_To_Mental_Health_Conditions_2008-2017.csv'"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_fpaths[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## (3) All Aboard the SHIP"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SHIP stands (weirdly) for State Health Insurance Assistance Program, a program in the state of Maryland which subsidizes health insurance payments for low-income healthcare recipients. You can find the full info and codebook for the SHIP data [here](https://catalog.data.gov/dataset/ship-emergency-department-visits-related-to-mental-health-conditions-2008-2017){target='_blank'}\n",
    "\n",
    "Here we also see a third way to use `glob()`: somewhat like **tab completion** in the Linux shell, we can type the beginning of the filename followed by the wildcard character `*` to obtain the full filename we need. Note that `glob.glob()` always returns a **`list`**, so we need to add `[0]` at the end to access the first result (even if there is only one result in total!)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Jurisdiction</th>\n",
       "      <th>Value</th>\n",
       "      <th>Race/ ethnicity</th>\n",
       "      <th>Year</th>\n",
       "      <th>Measure</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>State</td>\n",
       "      <td>4291.5</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Allegany</td>\n",
       "      <td>3309.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Anne Arundel</td>\n",
       "      <td>5734.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Baltimore City</td>\n",
       "      <td>10093.5</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Baltimore County</td>\n",
       "      <td>4210.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Jurisdiction    Value                      Race/ ethnicity  Year  \\\n",
       "0             State   4291.5  All races/ ethnicities (aggregated)  2017   \n",
       "1          Allegany   3309.6  All races/ ethnicities (aggregated)  2017   \n",
       "2      Anne Arundel   5734.1  All races/ ethnicities (aggregated)  2017   \n",
       "3    Baltimore City  10093.5  All races/ ethnicities (aggregated)  2017   \n",
       "4  Baltimore County   4210.1  All races/ ethnicities (aggregated)  2017   \n",
       "\n",
       "                   Measure  \n",
       "0  Mental Health ED visits  \n",
       "1  Mental Health ED visits  \n",
       "2  Mental Health ED visits  \n",
       "3  Mental Health ED visits  \n",
       "4  Mental Health ED visits  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ship_fpath = glob.glob(\"data/SHIP*.csv\")[0]\n",
    "ship_df = pd.read_csv(ship_fpath)\n",
    "ship_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## (4) Lead Levels Data from the CDC\n",
    "\n",
    "Especially given "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: openpyxl in /Users/jpj/.pyenv/versions/3.11.5/lib/python3.11/site-packages (3.1.2)\r\n",
      "Requirement already satisfied: et-xmlfile in /Users/jpj/.pyenv/versions/3.11.5/lib/python3.11/site-packages (from openpyxl) (1.1.0)\r\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "%pip install openpyxl"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And data on childhood lead levels can be found from the [CDC website's data portal](https://www.cdc.gov/lead-prevention/php/data/state-surveillance-data.html){target='_blank'}. The most recent sample was conducted in 2021, the results of which are contained in the Excel-format dataset we load here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "xlsx_fpath = glob.glob(\"data/*.xlsx\")[0]\n",
    "places_df = pd.read_excel(xlsx_fpath, skiprows=3)\n",
    "places_df.rename(columns={'Unnamed: 0': 'fips', 'Unnamed: 1': 'county'}, inplace=True)\n",
    "places_df = places_df.rename(columns={'Unnamed: 0': 'fips', 'Unnamed: 1': 'county'})\n",
    "rename_map = {\n",
    "    'Unnamed: 0': 'fips',\n",
    "    'Unnamed: 1': 'county',\n",
    "    #'Unnamed: 2': 'child_pop',\n",
    "    'Percent': 'pct_lead'\n",
    "}\n",
    "places_df.rename(columns=rename_map, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols_to_keep = list(rename_map.values())\n",
    "places_df = places_df[cols_to_keep]"
   ]
  },
  {
   "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>fips</th>\n",
       "      <th>county</th>\n",
       "      <th>pct_lead</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>001</td>\n",
       "      <td>Allegany County</td>\n",
       "      <td>0.020888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>003</td>\n",
       "      <td>Anne Arundel County</td>\n",
       "      <td>0.003220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>005</td>\n",
       "      <td>Baltimore County</td>\n",
       "      <td>0.009980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>009</td>\n",
       "      <td>Calvert County</td>\n",
       "      <td>0.006550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>011</td>\n",
       "      <td>Caroline County</td>\n",
       "      <td>0.019973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>013</td>\n",
       "      <td>Carroll County</td>\n",
       "      <td>0.006377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>015</td>\n",
       "      <td>Cecil County</td>\n",
       "      <td>0.006893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>017</td>\n",
       "      <td>Charles County</td>\n",
       "      <td>0.002288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>019</td>\n",
       "      <td>Dorchester County</td>\n",
       "      <td>0.027439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>021</td>\n",
       "      <td>Frederick County</td>\n",
       "      <td>0.003054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>023</td>\n",
       "      <td>Garrett County</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>025</td>\n",
       "      <td>Harford County</td>\n",
       "      <td>0.003109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>027</td>\n",
       "      <td>Howard County</td>\n",
       "      <td>0.008291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>029</td>\n",
       "      <td>Kent County</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>031</td>\n",
       "      <td>Montgomery County</td>\n",
       "      <td>0.003826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>033</td>\n",
       "      <td>Prince George's County</td>\n",
       "      <td>0.012376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>035</td>\n",
       "      <td>Queen Anne's County</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>037</td>\n",
       "      <td>St. Mary's County</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>039</td>\n",
       "      <td>Somerset County</td>\n",
       "      <td>0.015837</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>041</td>\n",
       "      <td>Talbot County</td>\n",
       "      <td>0.012442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>043</td>\n",
       "      <td>Washington County</td>\n",
       "      <td>0.008881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>045</td>\n",
       "      <td>Wicomico County</td>\n",
       "      <td>0.013187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>047</td>\n",
       "      <td>Worcester County</td>\n",
       "      <td>0.012959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>510</td>\n",
       "      <td>Baltimore (city) County</td>\n",
       "      <td>0.041661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Notes: 'N/A' indicates data are supressed when...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Population estimates calculated as population ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Data received and processed by CDC as of April...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 fips  \\\n",
       "0                                                 001   \n",
       "1                                                 003   \n",
       "2                                                 005   \n",
       "3                                                 009   \n",
       "4                                                 011   \n",
       "5                                                 013   \n",
       "6                                                 015   \n",
       "7                                                 017   \n",
       "8                                                 019   \n",
       "9                                                 021   \n",
       "10                                                023   \n",
       "11                                                025   \n",
       "12                                                027   \n",
       "13                                                029   \n",
       "14                                                031   \n",
       "15                                                033   \n",
       "16                                                035   \n",
       "17                                                037   \n",
       "18                                                039   \n",
       "19                                                041   \n",
       "20                                                043   \n",
       "21                                                045   \n",
       "22                                                047   \n",
       "23                                                510   \n",
       "24                                                NaN   \n",
       "25                                                NaN   \n",
       "26  Notes: 'N/A' indicates data are supressed when...   \n",
       "27  Population estimates calculated as population ...   \n",
       "28  Data received and processed by CDC as of April...   \n",
       "\n",
       "                     county  pct_lead  \n",
       "0           Allegany County  0.020888  \n",
       "1       Anne Arundel County  0.003220  \n",
       "2          Baltimore County  0.009980  \n",
       "3            Calvert County  0.006550  \n",
       "4           Caroline County  0.019973  \n",
       "5            Carroll County  0.006377  \n",
       "6              Cecil County  0.006893  \n",
       "7            Charles County  0.002288  \n",
       "8         Dorchester County  0.027439  \n",
       "9          Frederick County  0.003054  \n",
       "10           Garrett County       NaN  \n",
       "11           Harford County  0.003109  \n",
       "12            Howard County  0.008291  \n",
       "13              Kent County       NaN  \n",
       "14        Montgomery County  0.003826  \n",
       "15   Prince George's County  0.012376  \n",
       "16      Queen Anne's County       NaN  \n",
       "17        St. Mary's County       NaN  \n",
       "18          Somerset County  0.015837  \n",
       "19            Talbot County  0.012442  \n",
       "20        Washington County  0.008881  \n",
       "21          Wicomico County  0.013187  \n",
       "22         Worcester County  0.012959  \n",
       "23  Baltimore (city) County  0.041661  \n",
       "24                  Unknown       NaN  \n",
       "25                      NaN       NaN  \n",
       "26                      NaN       NaN  \n",
       "27                      NaN       NaN  \n",
       "28                      NaN       NaN  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "places_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, we note that only the rows from index 0 to index 23 contain the data we want (the rest contain footnotes added to the cells below the main dataset). So, we use the `.iloc` accessor from Pandas to \"slice\" just these first 24 rows, keeping in mind that Python's **slice operator `:`** is **inclusive-exclusive**, meaning that:\n",
    "\n",
    "* The number before the `:` will be the first index included, but\n",
    "* The number after the `:` will be **one greater than** the last index included"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "places_df = places_df.iloc[0:24].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fips</th>\n",
       "      <th>county</th>\n",
       "      <th>pct_lead</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>001</td>\n",
       "      <td>Allegany County</td>\n",
       "      <td>0.020888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>003</td>\n",
       "      <td>Anne Arundel County</td>\n",
       "      <td>0.003220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>005</td>\n",
       "      <td>Baltimore County</td>\n",
       "      <td>0.009980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>009</td>\n",
       "      <td>Calvert County</td>\n",
       "      <td>0.006550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>011</td>\n",
       "      <td>Caroline County</td>\n",
       "      <td>0.019973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>013</td>\n",
       "      <td>Carroll County</td>\n",
       "      <td>0.006377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>015</td>\n",
       "      <td>Cecil County</td>\n",
       "      <td>0.006893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>017</td>\n",
       "      <td>Charles County</td>\n",
       "      <td>0.002288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>019</td>\n",
       "      <td>Dorchester County</td>\n",
       "      <td>0.027439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>021</td>\n",
       "      <td>Frederick County</td>\n",
       "      <td>0.003054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>023</td>\n",
       "      <td>Garrett County</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>025</td>\n",
       "      <td>Harford County</td>\n",
       "      <td>0.003109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>027</td>\n",
       "      <td>Howard County</td>\n",
       "      <td>0.008291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>029</td>\n",
       "      <td>Kent County</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>031</td>\n",
       "      <td>Montgomery County</td>\n",
       "      <td>0.003826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>033</td>\n",
       "      <td>Prince George's County</td>\n",
       "      <td>0.012376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>035</td>\n",
       "      <td>Queen Anne's County</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>037</td>\n",
       "      <td>St. Mary's County</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>039</td>\n",
       "      <td>Somerset County</td>\n",
       "      <td>0.015837</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>041</td>\n",
       "      <td>Talbot County</td>\n",
       "      <td>0.012442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>043</td>\n",
       "      <td>Washington County</td>\n",
       "      <td>0.008881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>045</td>\n",
       "      <td>Wicomico County</td>\n",
       "      <td>0.013187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>047</td>\n",
       "      <td>Worcester County</td>\n",
       "      <td>0.012959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>510</td>\n",
       "      <td>Baltimore (city) County</td>\n",
       "      <td>0.041661</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   fips                   county  pct_lead\n",
       "0   001          Allegany County  0.020888\n",
       "1   003      Anne Arundel County  0.003220\n",
       "2   005         Baltimore County  0.009980\n",
       "3   009           Calvert County  0.006550\n",
       "4   011          Caroline County  0.019973\n",
       "5   013           Carroll County  0.006377\n",
       "6   015             Cecil County  0.006893\n",
       "7   017           Charles County  0.002288\n",
       "8   019        Dorchester County  0.027439\n",
       "9   021         Frederick County  0.003054\n",
       "10  023           Garrett County       NaN\n",
       "11  025           Harford County  0.003109\n",
       "12  027            Howard County  0.008291\n",
       "13  029              Kent County       NaN\n",
       "14  031        Montgomery County  0.003826\n",
       "15  033   Prince George's County  0.012376\n",
       "16  035      Queen Anne's County       NaN\n",
       "17  037        St. Mary's County       NaN\n",
       "18  039          Somerset County  0.015837\n",
       "19  041            Talbot County  0.012442\n",
       "20  043        Washington County  0.008881\n",
       "21  045          Wicomico County  0.013187\n",
       "22  047         Worcester County  0.012959\n",
       "23  510  Baltimore (city) County  0.041661"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "places_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "places_df['county'] = places_df['county'].str.replace(\" County\",\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fips</th>\n",
       "      <th>county</th>\n",
       "      <th>pct_lead</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>001</td>\n",
       "      <td>Allegany</td>\n",
       "      <td>0.020888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>003</td>\n",
       "      <td>Anne Arundel</td>\n",
       "      <td>0.003220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>005</td>\n",
       "      <td>Baltimore</td>\n",
       "      <td>0.009980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>009</td>\n",
       "      <td>Calvert</td>\n",
       "      <td>0.006550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>011</td>\n",
       "      <td>Caroline</td>\n",
       "      <td>0.019973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>013</td>\n",
       "      <td>Carroll</td>\n",
       "      <td>0.006377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>015</td>\n",
       "      <td>Cecil</td>\n",
       "      <td>0.006893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>017</td>\n",
       "      <td>Charles</td>\n",
       "      <td>0.002288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>019</td>\n",
       "      <td>Dorchester</td>\n",
       "      <td>0.027439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>021</td>\n",
       "      <td>Frederick</td>\n",
       "      <td>0.003054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>023</td>\n",
       "      <td>Garrett</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>025</td>\n",
       "      <td>Harford</td>\n",
       "      <td>0.003109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>027</td>\n",
       "      <td>Howard</td>\n",
       "      <td>0.008291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>029</td>\n",
       "      <td>Kent</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>031</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>0.003826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>033</td>\n",
       "      <td>Prince George's</td>\n",
       "      <td>0.012376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>035</td>\n",
       "      <td>Queen Anne's</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>037</td>\n",
       "      <td>St. Mary's</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>039</td>\n",
       "      <td>Somerset</td>\n",
       "      <td>0.015837</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>041</td>\n",
       "      <td>Talbot</td>\n",
       "      <td>0.012442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>043</td>\n",
       "      <td>Washington</td>\n",
       "      <td>0.008881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>045</td>\n",
       "      <td>Wicomico</td>\n",
       "      <td>0.013187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>047</td>\n",
       "      <td>Worcester</td>\n",
       "      <td>0.012959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>510</td>\n",
       "      <td>Baltimore (city)</td>\n",
       "      <td>0.041661</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   fips            county  pct_lead\n",
       "0   001          Allegany  0.020888\n",
       "1   003      Anne Arundel  0.003220\n",
       "2   005         Baltimore  0.009980\n",
       "3   009           Calvert  0.006550\n",
       "4   011          Caroline  0.019973\n",
       "5   013           Carroll  0.006377\n",
       "6   015             Cecil  0.006893\n",
       "7   017           Charles  0.002288\n",
       "8   019        Dorchester  0.027439\n",
       "9   021         Frederick  0.003054\n",
       "10  023           Garrett       NaN\n",
       "11  025           Harford  0.003109\n",
       "12  027            Howard  0.008291\n",
       "13  029              Kent       NaN\n",
       "14  031        Montgomery  0.003826\n",
       "15  033   Prince George's  0.012376\n",
       "16  035      Queen Anne's       NaN\n",
       "17  037        St. Mary's       NaN\n",
       "18  039          Somerset  0.015837\n",
       "19  041            Talbot  0.012442\n",
       "20  043        Washington  0.008881\n",
       "21  045          Wicomico  0.013187\n",
       "22  047         Worcester  0.012959\n",
       "23  510  Baltimore (city)  0.041661"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "places_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "places_df['county'] = places_df['county'].str.replace('(city)', 'City', regex=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fips</th>\n",
       "      <th>county</th>\n",
       "      <th>pct_lead</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>001</td>\n",
       "      <td>Allegany</td>\n",
       "      <td>0.020888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>003</td>\n",
       "      <td>Anne Arundel</td>\n",
       "      <td>0.003220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>005</td>\n",
       "      <td>Baltimore</td>\n",
       "      <td>0.009980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>009</td>\n",
       "      <td>Calvert</td>\n",
       "      <td>0.006550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>011</td>\n",
       "      <td>Caroline</td>\n",
       "      <td>0.019973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>013</td>\n",
       "      <td>Carroll</td>\n",
       "      <td>0.006377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>015</td>\n",
       "      <td>Cecil</td>\n",
       "      <td>0.006893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>017</td>\n",
       "      <td>Charles</td>\n",
       "      <td>0.002288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>019</td>\n",
       "      <td>Dorchester</td>\n",
       "      <td>0.027439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>021</td>\n",
       "      <td>Frederick</td>\n",
       "      <td>0.003054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>023</td>\n",
       "      <td>Garrett</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>025</td>\n",
       "      <td>Harford</td>\n",
       "      <td>0.003109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>027</td>\n",
       "      <td>Howard</td>\n",
       "      <td>0.008291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>029</td>\n",
       "      <td>Kent</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>031</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>0.003826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>033</td>\n",
       "      <td>Prince George's</td>\n",
       "      <td>0.012376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>035</td>\n",
       "      <td>Queen Anne's</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>037</td>\n",
       "      <td>St. Mary's</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>039</td>\n",
       "      <td>Somerset</td>\n",
       "      <td>0.015837</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>041</td>\n",
       "      <td>Talbot</td>\n",
       "      <td>0.012442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>043</td>\n",
       "      <td>Washington</td>\n",
       "      <td>0.008881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>045</td>\n",
       "      <td>Wicomico</td>\n",
       "      <td>0.013187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>047</td>\n",
       "      <td>Worcester</td>\n",
       "      <td>0.012959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>510</td>\n",
       "      <td>Baltimore City</td>\n",
       "      <td>0.041661</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   fips           county  pct_lead\n",
       "0   001         Allegany  0.020888\n",
       "1   003     Anne Arundel  0.003220\n",
       "2   005        Baltimore  0.009980\n",
       "3   009          Calvert  0.006550\n",
       "4   011         Caroline  0.019973\n",
       "5   013          Carroll  0.006377\n",
       "6   015            Cecil  0.006893\n",
       "7   017          Charles  0.002288\n",
       "8   019       Dorchester  0.027439\n",
       "9   021        Frederick  0.003054\n",
       "10  023          Garrett       NaN\n",
       "11  025          Harford  0.003109\n",
       "12  027           Howard  0.008291\n",
       "13  029             Kent       NaN\n",
       "14  031       Montgomery  0.003826\n",
       "15  033  Prince George's  0.012376\n",
       "16  035     Queen Anne's       NaN\n",
       "17  037       St. Mary's       NaN\n",
       "18  039         Somerset  0.015837\n",
       "19  041           Talbot  0.012442\n",
       "20  043       Washington  0.008881\n",
       "21  045         Wicomico  0.013187\n",
       "22  047        Worcester  0.012959\n",
       "23  510   Baltimore City  0.041661"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "places_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        True\n",
       "1        True\n",
       "2        True\n",
       "3        True\n",
       "4        True\n",
       "        ...  \n",
       "1245    False\n",
       "1246    False\n",
       "1247    False\n",
       "1248    False\n",
       "1249    False\n",
       "Name: Year, Length: 1250, dtype: bool"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ship_df['Year'] == 2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "ship_df = ship_df.loc[ship_df['Year'] == 2017]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "ship_df = ship_df[ship_df['Jurisdiction'] != \"State\"]"
   ]
  },
  {
   "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>Jurisdiction</th>\n",
       "      <th>Value</th>\n",
       "      <th>Race/ ethnicity</th>\n",
       "      <th>Year</th>\n",
       "      <th>Measure</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Allegany</td>\n",
       "      <td>3309.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Anne Arundel</td>\n",
       "      <td>5734.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Baltimore City</td>\n",
       "      <td>10093.5</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Baltimore County</td>\n",
       "      <td>4210.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Calvert</td>\n",
       "      <td>2999.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Caroline</td>\n",
       "      <td>7556.2</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Carroll</td>\n",
       "      <td>4216.0</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Cecil</td>\n",
       "      <td>9584.2</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Charles</td>\n",
       "      <td>2817.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Dorchester</td>\n",
       "      <td>11251.8</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Frederick</td>\n",
       "      <td>3064.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Garrett</td>\n",
       "      <td>7967.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Harford</td>\n",
       "      <td>3020.2</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Howard</td>\n",
       "      <td>3082.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Kent</td>\n",
       "      <td>13662.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Montgomery</td>\n",
       "      <td>2312.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Prince George's</td>\n",
       "      <td>1955.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Queen Anne's</td>\n",
       "      <td>6119.5</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Saint Mary's</td>\n",
       "      <td>6173.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Somerset</td>\n",
       "      <td>2696.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Talbot</td>\n",
       "      <td>7661.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Washington</td>\n",
       "      <td>5410.8</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Wicomico</td>\n",
       "      <td>2897.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Worcester</td>\n",
       "      <td>3502.8</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Jurisdiction    Value                      Race/ ethnicity  Year  \\\n",
       "1           Allegany   3309.6  All races/ ethnicities (aggregated)  2017   \n",
       "2       Anne Arundel   5734.1  All races/ ethnicities (aggregated)  2017   \n",
       "3     Baltimore City  10093.5  All races/ ethnicities (aggregated)  2017   \n",
       "4   Baltimore County   4210.1  All races/ ethnicities (aggregated)  2017   \n",
       "5            Calvert   2999.1  All races/ ethnicities (aggregated)  2017   \n",
       "6           Caroline   7556.2  All races/ ethnicities (aggregated)  2017   \n",
       "7            Carroll   4216.0  All races/ ethnicities (aggregated)  2017   \n",
       "8              Cecil   9584.2  All races/ ethnicities (aggregated)  2017   \n",
       "9            Charles   2817.6  All races/ ethnicities (aggregated)  2017   \n",
       "10        Dorchester  11251.8  All races/ ethnicities (aggregated)  2017   \n",
       "11         Frederick   3064.1  All races/ ethnicities (aggregated)  2017   \n",
       "12           Garrett   7967.6  All races/ ethnicities (aggregated)  2017   \n",
       "13           Harford   3020.2  All races/ ethnicities (aggregated)  2017   \n",
       "14            Howard   3082.1  All races/ ethnicities (aggregated)  2017   \n",
       "15              Kent  13662.1  All races/ ethnicities (aggregated)  2017   \n",
       "16        Montgomery   2312.1  All races/ ethnicities (aggregated)  2017   \n",
       "17   Prince George's   1955.6  All races/ ethnicities (aggregated)  2017   \n",
       "18      Queen Anne's   6119.5  All races/ ethnicities (aggregated)  2017   \n",
       "19      Saint Mary's   6173.1  All races/ ethnicities (aggregated)  2017   \n",
       "20          Somerset   2696.1  All races/ ethnicities (aggregated)  2017   \n",
       "21            Talbot   7661.6  All races/ ethnicities (aggregated)  2017   \n",
       "22        Washington   5410.8  All races/ ethnicities (aggregated)  2017   \n",
       "23          Wicomico   2897.6  All races/ ethnicities (aggregated)  2017   \n",
       "24         Worcester   3502.8  All races/ ethnicities (aggregated)  2017   \n",
       "\n",
       "                    Measure  \n",
       "1   Mental Health ED visits  \n",
       "2   Mental Health ED visits  \n",
       "3   Mental Health ED visits  \n",
       "4   Mental Health ED visits  \n",
       "5   Mental Health ED visits  \n",
       "6   Mental Health ED visits  \n",
       "7   Mental Health ED visits  \n",
       "8   Mental Health ED visits  \n",
       "9   Mental Health ED visits  \n",
       "10  Mental Health ED visits  \n",
       "11  Mental Health ED visits  \n",
       "12  Mental Health ED visits  \n",
       "13  Mental Health ED visits  \n",
       "14  Mental Health ED visits  \n",
       "15  Mental Health ED visits  \n",
       "16  Mental Health ED visits  \n",
       "17  Mental Health ED visits  \n",
       "18  Mental Health ED visits  \n",
       "19  Mental Health ED visits  \n",
       "20  Mental Health ED visits  \n",
       "21  Mental Health ED visits  \n",
       "22  Mental Health ED visits  \n",
       "23  Mental Health ED visits  \n",
       "24  Mental Health ED visits  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ship_df = ship_df[ship_df['Race/ ethnicity'] == \"All races/ ethnicities (aggregated)\"]\n",
    "ship_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>Jurisdiction</th>\n",
       "      <th>Value</th>\n",
       "      <th>Race/ ethnicity</th>\n",
       "      <th>Year</th>\n",
       "      <th>Measure</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Allegany</td>\n",
       "      <td>3309.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Anne Arundel</td>\n",
       "      <td>5734.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Baltimore City</td>\n",
       "      <td>10093.5</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Baltimore County</td>\n",
       "      <td>4210.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Calvert</td>\n",
       "      <td>2999.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Caroline</td>\n",
       "      <td>7556.2</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Carroll</td>\n",
       "      <td>4216.0</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Cecil</td>\n",
       "      <td>9584.2</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Charles</td>\n",
       "      <td>2817.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Dorchester</td>\n",
       "      <td>11251.8</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Frederick</td>\n",
       "      <td>3064.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Garrett</td>\n",
       "      <td>7967.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Harford</td>\n",
       "      <td>3020.2</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Howard</td>\n",
       "      <td>3082.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Kent</td>\n",
       "      <td>13662.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Montgomery</td>\n",
       "      <td>2312.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Prince George's</td>\n",
       "      <td>1955.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Queen Anne's</td>\n",
       "      <td>6119.5</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Saint Mary's</td>\n",
       "      <td>6173.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Somerset</td>\n",
       "      <td>2696.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Talbot</td>\n",
       "      <td>7661.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Washington</td>\n",
       "      <td>5410.8</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Wicomico</td>\n",
       "      <td>2897.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Worcester</td>\n",
       "      <td>3502.8</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Jurisdiction    Value                      Race/ ethnicity  Year  \\\n",
       "1           Allegany   3309.6  All races/ ethnicities (aggregated)  2017   \n",
       "2       Anne Arundel   5734.1  All races/ ethnicities (aggregated)  2017   \n",
       "3     Baltimore City  10093.5  All races/ ethnicities (aggregated)  2017   \n",
       "4   Baltimore County   4210.1  All races/ ethnicities (aggregated)  2017   \n",
       "5            Calvert   2999.1  All races/ ethnicities (aggregated)  2017   \n",
       "6           Caroline   7556.2  All races/ ethnicities (aggregated)  2017   \n",
       "7            Carroll   4216.0  All races/ ethnicities (aggregated)  2017   \n",
       "8              Cecil   9584.2  All races/ ethnicities (aggregated)  2017   \n",
       "9            Charles   2817.6  All races/ ethnicities (aggregated)  2017   \n",
       "10        Dorchester  11251.8  All races/ ethnicities (aggregated)  2017   \n",
       "11         Frederick   3064.1  All races/ ethnicities (aggregated)  2017   \n",
       "12           Garrett   7967.6  All races/ ethnicities (aggregated)  2017   \n",
       "13           Harford   3020.2  All races/ ethnicities (aggregated)  2017   \n",
       "14            Howard   3082.1  All races/ ethnicities (aggregated)  2017   \n",
       "15              Kent  13662.1  All races/ ethnicities (aggregated)  2017   \n",
       "16        Montgomery   2312.1  All races/ ethnicities (aggregated)  2017   \n",
       "17   Prince George's   1955.6  All races/ ethnicities (aggregated)  2017   \n",
       "18      Queen Anne's   6119.5  All races/ ethnicities (aggregated)  2017   \n",
       "19      Saint Mary's   6173.1  All races/ ethnicities (aggregated)  2017   \n",
       "20          Somerset   2696.1  All races/ ethnicities (aggregated)  2017   \n",
       "21            Talbot   7661.6  All races/ ethnicities (aggregated)  2017   \n",
       "22        Washington   5410.8  All races/ ethnicities (aggregated)  2017   \n",
       "23          Wicomico   2897.6  All races/ ethnicities (aggregated)  2017   \n",
       "24         Worcester   3502.8  All races/ ethnicities (aggregated)  2017   \n",
       "\n",
       "                    Measure  \n",
       "1   Mental Health ED visits  \n",
       "2   Mental Health ED visits  \n",
       "3   Mental Health ED visits  \n",
       "4   Mental Health ED visits  \n",
       "5   Mental Health ED visits  \n",
       "6   Mental Health ED visits  \n",
       "7   Mental Health ED visits  \n",
       "8   Mental Health ED visits  \n",
       "9   Mental Health ED visits  \n",
       "10  Mental Health ED visits  \n",
       "11  Mental Health ED visits  \n",
       "12  Mental Health ED visits  \n",
       "13  Mental Health ED visits  \n",
       "14  Mental Health ED visits  \n",
       "15  Mental Health ED visits  \n",
       "16  Mental Health ED visits  \n",
       "17  Mental Health ED visits  \n",
       "18  Mental Health ED visits  \n",
       "19  Mental Health ED visits  \n",
       "20  Mental Health ED visits  \n",
       "21  Mental Health ED visits  \n",
       "22  Mental Health ED visits  \n",
       "23  Mental Health ED visits  \n",
       "24  Mental Health ED visits  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ship_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "ship_df['Jurisdiction'] = ship_df['Jurisdiction'].str.replace(\"Baltimore County\", \"Baltimore\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>Jurisdiction</th>\n",
       "      <th>Value</th>\n",
       "      <th>Race/ ethnicity</th>\n",
       "      <th>Year</th>\n",
       "      <th>Measure</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Allegany</td>\n",
       "      <td>3309.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Anne Arundel</td>\n",
       "      <td>5734.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Baltimore City</td>\n",
       "      <td>10093.5</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Baltimore</td>\n",
       "      <td>4210.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Calvert</td>\n",
       "      <td>2999.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Caroline</td>\n",
       "      <td>7556.2</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Carroll</td>\n",
       "      <td>4216.0</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Cecil</td>\n",
       "      <td>9584.2</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Charles</td>\n",
       "      <td>2817.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Dorchester</td>\n",
       "      <td>11251.8</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Frederick</td>\n",
       "      <td>3064.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Garrett</td>\n",
       "      <td>7967.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Harford</td>\n",
       "      <td>3020.2</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Howard</td>\n",
       "      <td>3082.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Kent</td>\n",
       "      <td>13662.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Montgomery</td>\n",
       "      <td>2312.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Prince George's</td>\n",
       "      <td>1955.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Queen Anne's</td>\n",
       "      <td>6119.5</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Saint Mary's</td>\n",
       "      <td>6173.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Somerset</td>\n",
       "      <td>2696.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Talbot</td>\n",
       "      <td>7661.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Washington</td>\n",
       "      <td>5410.8</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Wicomico</td>\n",
       "      <td>2897.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Worcester</td>\n",
       "      <td>3502.8</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Jurisdiction    Value                      Race/ ethnicity  Year  \\\n",
       "1          Allegany   3309.6  All races/ ethnicities (aggregated)  2017   \n",
       "2      Anne Arundel   5734.1  All races/ ethnicities (aggregated)  2017   \n",
       "3    Baltimore City  10093.5  All races/ ethnicities (aggregated)  2017   \n",
       "4         Baltimore   4210.1  All races/ ethnicities (aggregated)  2017   \n",
       "5           Calvert   2999.1  All races/ ethnicities (aggregated)  2017   \n",
       "6          Caroline   7556.2  All races/ ethnicities (aggregated)  2017   \n",
       "7           Carroll   4216.0  All races/ ethnicities (aggregated)  2017   \n",
       "8             Cecil   9584.2  All races/ ethnicities (aggregated)  2017   \n",
       "9           Charles   2817.6  All races/ ethnicities (aggregated)  2017   \n",
       "10       Dorchester  11251.8  All races/ ethnicities (aggregated)  2017   \n",
       "11        Frederick   3064.1  All races/ ethnicities (aggregated)  2017   \n",
       "12          Garrett   7967.6  All races/ ethnicities (aggregated)  2017   \n",
       "13          Harford   3020.2  All races/ ethnicities (aggregated)  2017   \n",
       "14           Howard   3082.1  All races/ ethnicities (aggregated)  2017   \n",
       "15             Kent  13662.1  All races/ ethnicities (aggregated)  2017   \n",
       "16       Montgomery   2312.1  All races/ ethnicities (aggregated)  2017   \n",
       "17  Prince George's   1955.6  All races/ ethnicities (aggregated)  2017   \n",
       "18     Queen Anne's   6119.5  All races/ ethnicities (aggregated)  2017   \n",
       "19     Saint Mary's   6173.1  All races/ ethnicities (aggregated)  2017   \n",
       "20         Somerset   2696.1  All races/ ethnicities (aggregated)  2017   \n",
       "21           Talbot   7661.6  All races/ ethnicities (aggregated)  2017   \n",
       "22       Washington   5410.8  All races/ ethnicities (aggregated)  2017   \n",
       "23         Wicomico   2897.6  All races/ ethnicities (aggregated)  2017   \n",
       "24        Worcester   3502.8  All races/ ethnicities (aggregated)  2017   \n",
       "\n",
       "                    Measure  \n",
       "1   Mental Health ED visits  \n",
       "2   Mental Health ED visits  \n",
       "3   Mental Health ED visits  \n",
       "4   Mental Health ED visits  \n",
       "5   Mental Health ED visits  \n",
       "6   Mental Health ED visits  \n",
       "7   Mental Health ED visits  \n",
       "8   Mental Health ED visits  \n",
       "9   Mental Health ED visits  \n",
       "10  Mental Health ED visits  \n",
       "11  Mental Health ED visits  \n",
       "12  Mental Health ED visits  \n",
       "13  Mental Health ED visits  \n",
       "14  Mental Health ED visits  \n",
       "15  Mental Health ED visits  \n",
       "16  Mental Health ED visits  \n",
       "17  Mental Health ED visits  \n",
       "18  Mental Health ED visits  \n",
       "19  Mental Health ED visits  \n",
       "20  Mental Health ED visits  \n",
       "21  Mental Health ED visits  \n",
       "22  Mental Health ED visits  \n",
       "23  Mental Health ED visits  \n",
       "24  Mental Health ED visits  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ship_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "ship_df['Jurisdiction'] = ship_df['Jurisdiction'].str.replace(\"Saint\", \"St.\")"
   ]
  },
  {
   "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>Jurisdiction</th>\n",
       "      <th>Value</th>\n",
       "      <th>Race/ ethnicity</th>\n",
       "      <th>Year</th>\n",
       "      <th>Measure</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Allegany</td>\n",
       "      <td>3309.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Anne Arundel</td>\n",
       "      <td>5734.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Baltimore City</td>\n",
       "      <td>10093.5</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Baltimore</td>\n",
       "      <td>4210.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Calvert</td>\n",
       "      <td>2999.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Caroline</td>\n",
       "      <td>7556.2</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Carroll</td>\n",
       "      <td>4216.0</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Cecil</td>\n",
       "      <td>9584.2</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Charles</td>\n",
       "      <td>2817.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Dorchester</td>\n",
       "      <td>11251.8</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Frederick</td>\n",
       "      <td>3064.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Garrett</td>\n",
       "      <td>7967.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Harford</td>\n",
       "      <td>3020.2</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Howard</td>\n",
       "      <td>3082.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Kent</td>\n",
       "      <td>13662.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Montgomery</td>\n",
       "      <td>2312.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Prince George's</td>\n",
       "      <td>1955.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Queen Anne's</td>\n",
       "      <td>6119.5</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>St. Mary's</td>\n",
       "      <td>6173.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Somerset</td>\n",
       "      <td>2696.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Talbot</td>\n",
       "      <td>7661.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Washington</td>\n",
       "      <td>5410.8</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Wicomico</td>\n",
       "      <td>2897.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Worcester</td>\n",
       "      <td>3502.8</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Jurisdiction    Value                      Race/ ethnicity  Year  \\\n",
       "1          Allegany   3309.6  All races/ ethnicities (aggregated)  2017   \n",
       "2      Anne Arundel   5734.1  All races/ ethnicities (aggregated)  2017   \n",
       "3    Baltimore City  10093.5  All races/ ethnicities (aggregated)  2017   \n",
       "4         Baltimore   4210.1  All races/ ethnicities (aggregated)  2017   \n",
       "5           Calvert   2999.1  All races/ ethnicities (aggregated)  2017   \n",
       "6          Caroline   7556.2  All races/ ethnicities (aggregated)  2017   \n",
       "7           Carroll   4216.0  All races/ ethnicities (aggregated)  2017   \n",
       "8             Cecil   9584.2  All races/ ethnicities (aggregated)  2017   \n",
       "9           Charles   2817.6  All races/ ethnicities (aggregated)  2017   \n",
       "10       Dorchester  11251.8  All races/ ethnicities (aggregated)  2017   \n",
       "11        Frederick   3064.1  All races/ ethnicities (aggregated)  2017   \n",
       "12          Garrett   7967.6  All races/ ethnicities (aggregated)  2017   \n",
       "13          Harford   3020.2  All races/ ethnicities (aggregated)  2017   \n",
       "14           Howard   3082.1  All races/ ethnicities (aggregated)  2017   \n",
       "15             Kent  13662.1  All races/ ethnicities (aggregated)  2017   \n",
       "16       Montgomery   2312.1  All races/ ethnicities (aggregated)  2017   \n",
       "17  Prince George's   1955.6  All races/ ethnicities (aggregated)  2017   \n",
       "18     Queen Anne's   6119.5  All races/ ethnicities (aggregated)  2017   \n",
       "19       St. Mary's   6173.1  All races/ ethnicities (aggregated)  2017   \n",
       "20         Somerset   2696.1  All races/ ethnicities (aggregated)  2017   \n",
       "21           Talbot   7661.6  All races/ ethnicities (aggregated)  2017   \n",
       "22       Washington   5410.8  All races/ ethnicities (aggregated)  2017   \n",
       "23         Wicomico   2897.6  All races/ ethnicities (aggregated)  2017   \n",
       "24        Worcester   3502.8  All races/ ethnicities (aggregated)  2017   \n",
       "\n",
       "                    Measure  \n",
       "1   Mental Health ED visits  \n",
       "2   Mental Health ED visits  \n",
       "3   Mental Health ED visits  \n",
       "4   Mental Health ED visits  \n",
       "5   Mental Health ED visits  \n",
       "6   Mental Health ED visits  \n",
       "7   Mental Health ED visits  \n",
       "8   Mental Health ED visits  \n",
       "9   Mental Health ED visits  \n",
       "10  Mental Health ED visits  \n",
       "11  Mental Health ED visits  \n",
       "12  Mental Health ED visits  \n",
       "13  Mental Health ED visits  \n",
       "14  Mental Health ED visits  \n",
       "15  Mental Health ED visits  \n",
       "16  Mental Health ED visits  \n",
       "17  Mental Health ED visits  \n",
       "18  Mental Health ED visits  \n",
       "19  Mental Health ED visits  \n",
       "20  Mental Health ED visits  \n",
       "21  Mental Health ED visits  \n",
       "22  Mental Health ED visits  \n",
       "23  Mental Health ED visits  \n",
       "24  Mental Health ED visits  "
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ship_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df = places_df.merge(ship_df, left_on='county', right_on='Jurisdiction', how='left', indicator=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fips</th>\n",
       "      <th>county</th>\n",
       "      <th>pct_lead</th>\n",
       "      <th>Jurisdiction</th>\n",
       "      <th>Value</th>\n",
       "      <th>Race/ ethnicity</th>\n",
       "      <th>Year</th>\n",
       "      <th>Measure</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>001</td>\n",
       "      <td>Allegany</td>\n",
       "      <td>0.020888</td>\n",
       "      <td>Allegany</td>\n",
       "      <td>3309.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>003</td>\n",
       "      <td>Anne Arundel</td>\n",
       "      <td>0.003220</td>\n",
       "      <td>Anne Arundel</td>\n",
       "      <td>5734.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>005</td>\n",
       "      <td>Baltimore</td>\n",
       "      <td>0.009980</td>\n",
       "      <td>Baltimore</td>\n",
       "      <td>4210.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>009</td>\n",
       "      <td>Calvert</td>\n",
       "      <td>0.006550</td>\n",
       "      <td>Calvert</td>\n",
       "      <td>2999.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>011</td>\n",
       "      <td>Caroline</td>\n",
       "      <td>0.019973</td>\n",
       "      <td>Caroline</td>\n",
       "      <td>7556.2</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>013</td>\n",
       "      <td>Carroll</td>\n",
       "      <td>0.006377</td>\n",
       "      <td>Carroll</td>\n",
       "      <td>4216.0</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>015</td>\n",
       "      <td>Cecil</td>\n",
       "      <td>0.006893</td>\n",
       "      <td>Cecil</td>\n",
       "      <td>9584.2</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>017</td>\n",
       "      <td>Charles</td>\n",
       "      <td>0.002288</td>\n",
       "      <td>Charles</td>\n",
       "      <td>2817.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>019</td>\n",
       "      <td>Dorchester</td>\n",
       "      <td>0.027439</td>\n",
       "      <td>Dorchester</td>\n",
       "      <td>11251.8</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>021</td>\n",
       "      <td>Frederick</td>\n",
       "      <td>0.003054</td>\n",
       "      <td>Frederick</td>\n",
       "      <td>3064.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>023</td>\n",
       "      <td>Garrett</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Garrett</td>\n",
       "      <td>7967.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>025</td>\n",
       "      <td>Harford</td>\n",
       "      <td>0.003109</td>\n",
       "      <td>Harford</td>\n",
       "      <td>3020.2</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>027</td>\n",
       "      <td>Howard</td>\n",
       "      <td>0.008291</td>\n",
       "      <td>Howard</td>\n",
       "      <td>3082.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>029</td>\n",
       "      <td>Kent</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Kent</td>\n",
       "      <td>13662.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>031</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>0.003826</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>2312.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>033</td>\n",
       "      <td>Prince George's</td>\n",
       "      <td>0.012376</td>\n",
       "      <td>Prince George's</td>\n",
       "      <td>1955.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>035</td>\n",
       "      <td>Queen Anne's</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Queen Anne's</td>\n",
       "      <td>6119.5</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>037</td>\n",
       "      <td>St. Mary's</td>\n",
       "      <td>NaN</td>\n",
       "      <td>St. Mary's</td>\n",
       "      <td>6173.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>039</td>\n",
       "      <td>Somerset</td>\n",
       "      <td>0.015837</td>\n",
       "      <td>Somerset</td>\n",
       "      <td>2696.1</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>041</td>\n",
       "      <td>Talbot</td>\n",
       "      <td>0.012442</td>\n",
       "      <td>Talbot</td>\n",
       "      <td>7661.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>043</td>\n",
       "      <td>Washington</td>\n",
       "      <td>0.008881</td>\n",
       "      <td>Washington</td>\n",
       "      <td>5410.8</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>045</td>\n",
       "      <td>Wicomico</td>\n",
       "      <td>0.013187</td>\n",
       "      <td>Wicomico</td>\n",
       "      <td>2897.6</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>047</td>\n",
       "      <td>Worcester</td>\n",
       "      <td>0.012959</td>\n",
       "      <td>Worcester</td>\n",
       "      <td>3502.8</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>510</td>\n",
       "      <td>Baltimore City</td>\n",
       "      <td>0.041661</td>\n",
       "      <td>Baltimore City</td>\n",
       "      <td>10093.5</td>\n",
       "      <td>All races/ ethnicities (aggregated)</td>\n",
       "      <td>2017</td>\n",
       "      <td>Mental Health ED visits</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   fips           county  pct_lead     Jurisdiction    Value  \\\n",
       "0   001         Allegany  0.020888         Allegany   3309.6   \n",
       "1   003     Anne Arundel  0.003220     Anne Arundel   5734.1   \n",
       "2   005        Baltimore  0.009980        Baltimore   4210.1   \n",
       "3   009          Calvert  0.006550          Calvert   2999.1   \n",
       "4   011         Caroline  0.019973         Caroline   7556.2   \n",
       "5   013          Carroll  0.006377          Carroll   4216.0   \n",
       "6   015            Cecil  0.006893            Cecil   9584.2   \n",
       "7   017          Charles  0.002288          Charles   2817.6   \n",
       "8   019       Dorchester  0.027439       Dorchester  11251.8   \n",
       "9   021        Frederick  0.003054        Frederick   3064.1   \n",
       "10  023          Garrett       NaN          Garrett   7967.6   \n",
       "11  025          Harford  0.003109          Harford   3020.2   \n",
       "12  027           Howard  0.008291           Howard   3082.1   \n",
       "13  029             Kent       NaN             Kent  13662.1   \n",
       "14  031       Montgomery  0.003826       Montgomery   2312.1   \n",
       "15  033  Prince George's  0.012376  Prince George's   1955.6   \n",
       "16  035     Queen Anne's       NaN     Queen Anne's   6119.5   \n",
       "17  037       St. Mary's       NaN       St. Mary's   6173.1   \n",
       "18  039         Somerset  0.015837         Somerset   2696.1   \n",
       "19  041           Talbot  0.012442           Talbot   7661.6   \n",
       "20  043       Washington  0.008881       Washington   5410.8   \n",
       "21  045         Wicomico  0.013187         Wicomico   2897.6   \n",
       "22  047        Worcester  0.012959        Worcester   3502.8   \n",
       "23  510   Baltimore City  0.041661   Baltimore City  10093.5   \n",
       "\n",
       "                        Race/ ethnicity  Year                  Measure  \n",
       "0   All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "1   All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "2   All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "3   All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "4   All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "5   All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "6   All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "7   All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "8   All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "9   All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "10  All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "11  All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "12  All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "13  All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "14  All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "15  All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "16  All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "17  All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "18  All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "19  All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "20  All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "21  All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "22  All races/ ethnicities (aggregated)  2017  Mental Health ED visits  \n",
       "23  All races/ ethnicities (aggregated)  2017  Mental Health ED visits  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df"
   ]
  },
  {
   "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>pct_lead</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>pct_lead</th>\n",
       "      <td>1.00000</td>\n",
       "      <td>0.59498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Value</th>\n",
       "      <td>0.59498</td>\n",
       "      <td>1.00000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          pct_lead    Value\n",
       "pct_lead   1.00000  0.59498\n",
       "Value      0.59498  1.00000"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df[['pct_lead','Value']].corr()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}