Donald_Final.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Annette Donald\n",
    "Coding Exercise 18"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Problem Statement"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Despite available vaccines, COVID-19 continues to surge across the United States and hospitals need to anticipate these surges to ensure they have enough resources: physicians, surgeons, nurses, janitorial staff, beds, medical supplies and equipment, and even entire buildings (Supady, 2021; Emanual, Persad, Upshur, Thome, Parker, Glickman, Zhang, Boyle, Smith, & Phillips, 2020; Callander & McInnes, 2020; Gessler, et al., 2021). For instance, in April 2020, the Commonwealth of Massachusetts reopened a Boston Medical Center facility to provide care to unhoused patients recovering from COVID-19 due to resource shortages (Komaromy & Tomanovich, 2020). \n",
    "\n",
    "Understanding the extent to which hospitals have reallocated resources from other healthcare units before and during COVID-19 can help predict future COVID-19 surges&mdash;as well as RSV and flu surges&mdash;and give insight into hospital resource management, adding to Pfeffer and Salancik’s Resource Dependency Theory. \n",
    "\n",
    "Using Johns Hopkins’s state-level COVID severity measures (cases and deaths) paired with the American Hospital Association’s annual survey data (2018 through 2020), __this study aims to predict resource needs for U.S. hospitals for upcoming COVID-19 surges and future epidemics with a supervised machine learning model__."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Source"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. American Hospital Association's Annual Survey Data\n",
    "    1. Before COVID-19\n",
    "        1. __2018 annual survey__\n",
    "        2. __2019 annual survey__\n",
    "    2. During COVID-19\n",
    "        1. __2020 annual survey__\n",
    "2. Johns Hopkins University Center for System Science and Engineering Repository\n",
    "    1. COVID-19 Severity Measures\n",
    "        1. __Cases by state__\n",
    "        2. __Deaths by state__\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The American Hospital Association (AHA) annual survey includes over 6,000 hospitals for each year and samples bed count (total and specific healthcare unit bed type), employment status (including vacancies), total operating rooms, gross square footage of the physical hospitals, and more. Participation is voluntary and not all questions require a response for the survey to be considered \"complete,\" which may be how the AHA reports a response rate of greater than 80%. \n",
    "\n",
    "The AHA does not gather much COVID-19 data&mdash;in 2020, the survey began recording total adult ventilators at the start and end of the reporting period. To predict hospitals' resource needs for upcoming COVID-19 surges and future epidemics, data from the Johns Hopkins Coronavirus Resource Center (CRC) supplments the AHA data.\n",
    "\n",
    "The Johns Hopkins Coronavirus Resource Center (CRC) is a continuously updated source of COVID-19 data. The center collects and analyzes data on cases, deaths, tests, hospitalizations, and vaccines to help the public, policymakers, and healthcare professionals respond to the pandemic. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd \n",
    "import glob"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Files in the Folder: ['AHA_df.csv', 'JHU_death.csv', 'south_2020_cases.png', 'AHA_2020_df.csv', 'AHA_2018_df.csv', '.DS_Store', 'JHU_death_2020_df.csv', 'JHU_case_2020_df.csv', 'time_series_covid19_confirmed_US.csv', 'south_2021_cases.png', 'idkdec5riyee3dt3.csv', 'territories_2021_cases.png', 'northeast_2021_cases.png', 'JHU_case_2021_df.csv', 'midwest_2021_cases.png', 'west_2020_cases.png', 'AHA_2019_df.csv', 'JHU_death_2021_df.csv', 'time_series_covid19_deaths_US.csv', 'JHU_case.csv', 'territories_2020_cases.png', 'northeast_2020_cases.png', 'midwest_2020_cases.png', 'west_2021_cases.png']\n"
     ]
    }
   ],
   "source": [
    "os.chdir('/Users/annettedblackburn/Desktop/CompSoc/Data/Final_Project')\n",
    "print('Files in the Folder:', os.listdir())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### JHU Data Info\n",
    "JHU data from https://github.com/CSSEGISandData/COVID-19/tree/master/csse_covid_19_data/\n",
    " - time_series_covid19_confirmed_US.csv includes confirmed cases at the country level\n",
    " - time_series_covid19_deaths_US.csv includes deaths reported at the county level     \n",
    "- note: updated daily so NEED TO COMB THROUGH AND SORT INTO 2020 AND 2021\n",
    " - includes time series summary tables, including confirmed, deaths, and recovered\n",
    "\n",
    "Deaths CSV and Cases CSV\n",
    "- can drop:\n",
    "    - UID\n",
    "    - iso2 \n",
    "    - iso3\n",
    "    - code3\n",
    "    - FIPS\n",
    "    - Admin2\n",
    "    - Country_Region\n",
    "    - Combined_Key\n",
    "    - Population (only in Deaths CSV)\n",
    "    - 1/1/22 through 11/14/22\n",
    "- can keep:\n",
    "    - Province_State\n",
    "    - Lat\n",
    "    - Long_ \n",
    "    - 1/22/2020 through 12/31/20\n",
    "    - 1/1/21 through 12/31/21\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['UID', 'iso2', 'iso3', 'code3', 'FIPS', 'Admin2', 'Province_State',\n",
      "       'Country_Region', 'Lat', 'Long_',\n",
      "       ...\n",
      "       '11/5/22', '11/6/22', '11/7/22', '11/8/22', '11/9/22', '11/10/22',\n",
      "       '11/11/22', '11/12/22', '11/13/22', '11/14/22'],\n",
      "      dtype='object', length=1040)\n"
     ]
    }
   ],
   "source": [
    "JHU_death_df = pd.read_csv('time_series_covid19_deaths_US.csv')\n",
    "print(JHU_death_df.columns)\n",
    "\n",
    "# 1/22/20 - 12/31/20 is column 13 (M) - 357 (MS)\n",
    "# 1/1/21 - 12/31/21 is column 358 (MT) - 722 (AAT)\n",
    "# 1/1/22 - 11/14/22 is column 723 (AAU) - 1040 (AMZ) \n",
    "\n",
    "JHU_death_df.drop(JHU_death_df.iloc[:, 723:1040], inplace=True, axis=1)\n",
    "# Create new Total COVID death count variable \n",
    "JHU_death_df['Total COVID death count'] = JHU_death_df.iloc[:, 13:1040].sum(axis=1)\n",
    "\n",
    "\n",
    "# with dropped 2022 data, create 2 new DFs (one for 2020 and 2021)\n",
    "# drop 2021 data \n",
    "JHU_death_2020_df = JHU_death_df.drop(JHU_death_df.iloc[:, 358:722], axis=1)\n",
    "# drop unnecessary columns: UID, iso2, iso3, code3, FIPS, Admin2, Country_Region, Combined_Ke, Population \n",
    "JHU_death_2020_df.drop('UID', inplace=True, axis=1)\n",
    "JHU_death_2020_df.drop('iso2', inplace=True, axis=1)\n",
    "JHU_death_2020_df.drop('iso3', inplace=True, axis=1)\n",
    "JHU_death_2020_df.drop('code3', inplace=True, axis=1)\n",
    "JHU_death_2020_df.drop('FIPS', inplace=True, axis=1)\n",
    "JHU_death_2020_df.drop('Admin2', inplace=True, axis=1)\n",
    "JHU_death_2020_df.drop('Country_Region', inplace=True, axis=1)\n",
    "JHU_death_2020_df.drop('Combined_Key', inplace=True, axis=1)\n",
    "JHU_death_2020_df.drop('Population', inplace=True, axis=1)\n",
    "# Create new Total COVID death count varibale \n",
    "JHU_death_2020_df['Total COVID death count'] = JHU_death_2020_df.iloc[:, 723:1040].sum(axis=1)\n",
    "\n",
    "\n",
    "# drop 2020 data \n",
    "JHU_death_2021_df = JHU_death_df.drop(JHU_death_df.iloc[:, 13:357], axis=1)\n",
    "# drop unnecessary columns: UID, iso2, iso3, code3, FIPS, Admin2, Country_Region, Combined_Ke, Population \n",
    "JHU_death_2021_df.drop('UID', inplace=True, axis=1)\n",
    "JHU_death_2021_df.drop('iso2', inplace=True, axis=1)\n",
    "JHU_death_2021_df.drop('iso3', inplace=True, axis=1)\n",
    "JHU_death_2021_df.drop('code3', inplace=True, axis=1)\n",
    "JHU_death_2021_df.drop('FIPS', inplace=True, axis=1)\n",
    "JHU_death_2021_df.drop('Admin2', inplace=True, axis=1)\n",
    "JHU_death_2021_df.drop('Country_Region', inplace=True, axis=1)\n",
    "JHU_death_2021_df.drop('Combined_Key', inplace=True, axis=1)\n",
    "JHU_death_2021_df.drop('Population', inplace=True, axis=1)\n",
    "# Create new Total COVID death count varibale \n",
    "JHU_death_2021_df['Total COVID death count'] = JHU_death_2021_df.iloc[:, 358:722].sum(axis=1)\n",
    "\n",
    "# save pandas DF as new CSV file\n",
    "JHU_death_df.to_csv('JHU_death.csv')\n",
    "JHU_death_2020_df.to_csv('JHU_death_2020_df.csv')\n",
    "JHU_death_2021_df.to_csv('JHU_death_2021_df.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['UID', 'iso2', 'iso3', 'code3', 'FIPS', 'Admin2', 'Province_State',\n",
      "       'Country_Region', 'Lat', 'Long_',\n",
      "       ...\n",
      "       '11/5/22', '11/6/22', '11/7/22', '11/8/22', '11/9/22', '11/10/22',\n",
      "       '11/11/22', '11/12/22', '11/13/22', '11/14/22'],\n",
      "      dtype='object', length=1039)\n"
     ]
    }
   ],
   "source": [
    "JHU_case_df = pd.read_csv('time_series_covid19_confirmed_US.csv')\n",
    "print(JHU_case_df.columns)\n",
    "\n",
    "# 1/22/20 - 12/31/20 is column 12 (L) - 356 (MR)\n",
    "# 1/1/21 - 12/31/21 is column 357 (MS) - 721 (AAS)\n",
    "# 1/1/22 - 11/14/22 is column 722 (AAT) - 1039 (AMY) \n",
    "\n",
    "JHU_case_df.drop(JHU_case_df.iloc[:, 722:1039], axis=1)\n",
    "# Create new Total COVID case count variable \n",
    "JHU_case_df['Total COVID case count'] = JHU_case_df.iloc[:, 12:1039].sum(axis=1)\n",
    "\n",
    "# with dropped 2022 data, create 2 new DFs (one for 2020 and 2021)\n",
    "#drop 2021 data \n",
    "JHU_case_2020_df = JHU_case_df.drop(JHU_case_df.iloc[:, 356:], axis=1)\n",
    "# drop unnecessary columns: UID, iso2, iso3, code3, FIPS, Admin2, Country_Region, Combined_Key \n",
    "JHU_case_2020_df.drop('UID', inplace=True, axis=1)\n",
    "JHU_case_2020_df.drop('iso2', inplace=True, axis=1)\n",
    "JHU_case_2020_df.drop('iso3', inplace=True, axis=1)\n",
    "JHU_case_2020_df.drop('code3', inplace=True, axis=1)\n",
    "JHU_case_2020_df.drop('FIPS', inplace=True, axis=1)\n",
    "JHU_case_2020_df.drop('Admin2', inplace=True, axis=1)\n",
    "JHU_case_2020_df.drop('Country_Region', inplace=True, axis=1)\n",
    "JHU_case_2020_df.drop('Combined_Key', inplace=True, axis=1)\n",
    "# Create new Total COVID case count varibale \n",
    "JHU_case_2020_df['Total COVID case count'] = JHU_case_2020_df.iloc[:, 12:356].sum(axis=1)\n",
    "\n",
    "# drop 2020 data \n",
    "JHU_case_2021_df = JHU_case_df.drop(JHU_case_df.iloc[:, 12:356], axis=1)\n",
    "JHU_case_2021_df = JHU_case_2021_df.drop(JHU_case_df.iloc[:, 721:], axis=1)\n",
    "# drop unnecessary columns: UID, iso2, iso3, code3, FIPS, Admin2, Country_Region, Combined_Key\n",
    "JHU_case_2021_df.drop('UID', inplace=True, axis=1)\n",
    "JHU_case_2021_df.drop('iso2', inplace=True, axis=1)\n",
    "JHU_case_2021_df.drop('iso3', inplace=True, axis=1)\n",
    "JHU_case_2021_df.drop('code3', inplace=True, axis=1)\n",
    "JHU_case_2021_df.drop('FIPS', inplace=True, axis=1)\n",
    "JHU_case_2021_df.drop('Admin2', inplace=True, axis=1)\n",
    "JHU_case_2021_df.drop('Country_Region', inplace=True, axis=1)\n",
    "JHU_case_2021_df.drop('Combined_Key', inplace=True, axis=1)\n",
    "# Create new Total COVID case count varibale \n",
    "JHU_case_2021_df['Total COVID case count'] = JHU_case_2021_df.iloc[:, 357:721].sum(axis=1)\n",
    "\n",
    "# save pandas DF as new CSV file\n",
    "JHU_case_df.to_csv('JHU_case.csv')\n",
    "JHU_case_2020_df.to_csv('JHU_case_2020_df.csv')\n",
    "JHU_case_2021_df.to_csv('JHU_case_2021_df.csv') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Files in the Folder: ['AHA_df.csv', 'JHU_death.csv', 'south_2020_cases.png', 'AHA_2020_df.csv', 'AHA_2018_df.csv', '.DS_Store', 'JHU_death_2020_df.csv', 'JHU_case_2020_df.csv', 'time_series_covid19_confirmed_US.csv', 'south_2021_cases.png', 'idkdec5riyee3dt3.csv', 'territories_2021_cases.png', 'northeast_2021_cases.png', 'JHU_case_2021_df.csv', 'midwest_2021_cases.png', 'west_2020_cases.png', 'AHA_2019_df.csv', 'JHU_death_2021_df.csv', 'time_series_covid19_deaths_US.csv', 'JHU_case.csv', 'territories_2020_cases.png', 'northeast_2020_cases.png', 'midwest_2020_cases.png', 'west_2021_cases.png']\n"
     ]
    }
   ],
   "source": [
    "print('Files in the Folder:', os.listdir())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### AHA Data Info\n",
    "\n",
    "Variables:\n",
    "Staff Vaccancies: VMD, VRES, VTTRN, VRN, VLPN, VAST, VLAB, VPHR, VPHT, VRSP, VOTHl, VTOTL, VRNH, VTNH"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### AHA DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   YEAR  STCD  GENBD  PEDBD  OBBD  MSICBD  CICBD  NICBD  NINTBD  PEDICBD  ...  \\\n",
      "0  2018     3    NaN    NaN   NaN     NaN    NaN    NaN     NaN      NaN  ...   \n",
      "1  2019     3    NaN    NaN   NaN     NaN    NaN    NaN     NaN      NaN  ...   \n",
      "2  2020     3    NaN    NaN   NaN     NaN    NaN    NaN     NaN      NaN  ...   \n",
      "3  2018     4    NaN    NaN   NaN     NaN    NaN    NaN     NaN      NaN  ...   \n",
      "4  2019     4    NaN    NaN   NaN     NaN    NaN    NaN     NaN      NaN  ...   \n",
      "\n",
      "   VRAD  VLAB  VPHR  VPHT  VRSP  VOTHl  VTOTL  VRNH  VTNH  TETOT  \n",
      "0   NaN   NaN   NaN   NaN   NaN    NaN    NaN   NaN   NaN    NaN  \n",
      "1   NaN   NaN   NaN   NaN   NaN    NaN    NaN   NaN   NaN    NaN  \n",
      "2   NaN   NaN   NaN   NaN   NaN    NaN    NaN   NaN   NaN    NaN  \n",
      "3   NaN   NaN   NaN   NaN   NaN    NaN    NaN   NaN   NaN    NaN  \n",
      "4   NaN   NaN   NaN   NaN   NaN    NaN    NaN   NaN   NaN    NaN  \n",
      "\n",
      "[5 rows x 54 columns]\n"
     ]
    }
   ],
   "source": [
    "AHA_df = pd.read_csv('idkdec5riyee3dt3.csv')\n",
    "AHA_df.drop(['OSPOTH', 'OTHIC', 'OTHOTH', 'MTYPE', 'FYR'], axis=1, inplace=True)\n",
    "AHA_df.drop(['BDH', 'ADMH', 'IPDH', 'LBEDLA', 'ID'], axis=1, inplace=True)\n",
    "print(AHA_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       GENBD  BRNBD  PSYBD\n",
      "0       30.0    0.0    0.0\n",
      "1       30.0    0.0    0.0\n",
      "2       30.0    0.0    0.0\n",
      "3       30.0    0.0    0.0\n",
      "4       30.0    0.0    0.0\n",
      "...      ...    ...    ...\n",
      "18540   30.0    0.0    0.0\n",
      "18541   30.0    0.0    0.0\n",
      "18542   30.0    0.0    0.0\n",
      "18543   30.0    0.0    0.0\n",
      "18544   30.0    0.0    0.0\n",
      "\n",
      "[18545 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "# Impute null bed values for AHA DF\n",
    "bed_cols = ['GENBD', 'PEDBD', 'OBBD', 'MSICBD', \n",
    "                'CICBD', 'NICBD', 'NINTBD', 'PEDICBD', \n",
    "                'BRNBD', 'SPCICBD', 'OTHICBD', 'REHABBD', \n",
    "                'ALCHBD', 'PSYBD', 'SNBD88', 'ICFBD88', \n",
    "                'ACULTBD', 'OTHLBD94', 'OTHBD94']\n",
    "for column in bed_cols:\n",
    "    AHA_df[column] = AHA_df[column].fillna(AHA_df[column].median())\n",
    "\n",
    "print(AHA_df[['GENBD', 'BRNBD', 'PSYBD']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       TETOT     GFEET  OPRA\n",
      "0       12.0  224028.0   5.0\n",
      "1       12.0  224028.0   5.0\n",
      "2       12.0  224028.0   5.0\n",
      "3       12.0  224028.0   5.0\n",
      "4       12.0  224028.0   5.0\n",
      "...      ...       ...   ...\n",
      "18540   12.0  224028.0   5.0\n",
      "18541   12.0  224028.0   5.0\n",
      "18542   12.0  224028.0   5.0\n",
      "18543   12.0  224028.0   5.0\n",
      "18544   12.0  224028.0   5.0\n",
      "\n",
      "[18545 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "# Impute null total employed for AHA DF\n",
    "AHA_df['TETOT'] = AHA_df['TETOT'].fillna(AHA_df['TETOT'].median())\n",
    "\n",
    "# Impute null gross total square feet for AHA DF\n",
    "AHA_df['GFEET'] = AHA_df['GFEET'].fillna(AHA_df['GFEET'].median())\n",
    "\n",
    "# Impute null operating rooms for AHA DF\n",
    "AHA_df['OPRA'] = AHA_df['OPRA'].fillna(AHA_df['OPRA'].median())\n",
    "\n",
    "print(AHA_df[['TETOT', 'GFEET', 'OPRA']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       VRN  VAST  VTOTL\n",
      "0      8.0   3.0   29.0\n",
      "1      8.0   3.0   29.0\n",
      "2      8.0   3.0   29.0\n",
      "3      8.0   3.0   29.0\n",
      "4      8.0   3.0   29.0\n",
      "...    ...   ...    ...\n",
      "18540  8.0   3.0   29.0\n",
      "18541  8.0   3.0   29.0\n",
      "18542  8.0   3.0   29.0\n",
      "18543  8.0   3.0   29.0\n",
      "18544  8.0   3.0   29.0\n",
      "\n",
      "[18545 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "# Impute null staff vaccancy values for AHA DF\n",
    "vacancy_cols = ['VMD', 'VRES', 'VTTRN', 'VRN', \n",
    "                'VLPN', 'VAST', 'VLAB', 'VPHR', \n",
    "                'VPHT', 'VRSP', 'VOTHl', 'VTOTL', \n",
    "                'VRNH', 'VTNH', 'VRAD']\n",
    "for column in vacancy_cols:\n",
    "    AHA_df[column] = AHA_df[column].fillna(AHA_df[column].median())\n",
    "\n",
    "print(AHA_df[['VRN', 'VAST', 'VTOTL']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save 2018-2020 AHA DF to CSV\n",
    "AHA_df.to_csv('AHA_df.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### AHA 2018 DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    YEAR  STCD  GENBD  PEDBD  OBBD  MSICBD  CICBD  NICBD  NINTBD  PEDICBD  \\\n",
      "0   2018     3   30.0    0.0   3.0     5.0    0.0    0.0     0.0      0.0   \n",
      "3   2018     4   30.0    0.0   3.0     5.0    0.0    0.0     0.0      0.0   \n",
      "6   2018     4   30.0    0.0   3.0     5.0    0.0    0.0     0.0      0.0   \n",
      "9   2018     4   30.0    0.0   3.0     5.0    0.0    0.0     0.0      0.0   \n",
      "12  2018     4   89.0   18.0  30.0     8.0    0.0    0.0     0.0      0.0   \n",
      "\n",
      "    ...  VRAD  VLAB  VPHR  VPHT  VRSP  VOTHl  VTOTL  VRNH  VTNH  TETOT  \n",
      "0   ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0   12.0  \n",
      "3   ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0   12.0  \n",
      "6   ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0   12.0  \n",
      "9   ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0   12.0  \n",
      "12  ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0    0.0  \n",
      "\n",
      "[5 rows x 54 columns]\n"
     ]
    }
   ],
   "source": [
    "# 2018 DF\n",
    "AHA_2018_df = AHA_df[AHA_df['YEAR'] == 2018]\n",
    "print(AHA_2018_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       GENBD  BRNBD  PSYBD\n",
      "0       30.0    0.0    0.0\n",
      "3       30.0    0.0    0.0\n",
      "6       30.0    0.0    0.0\n",
      "9       30.0    0.0    0.0\n",
      "12      89.0    0.0    0.0\n",
      "...      ...    ...    ...\n",
      "18530   72.0    0.0   30.0\n",
      "18533   30.0    0.0    0.0\n",
      "18536  111.0    0.0   29.0\n",
      "18539   30.0    0.0    0.0\n",
      "18542   30.0    0.0    0.0\n",
      "\n",
      "[6218 rows x 3 columns]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1737683800.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1737683800.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1737683800.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1737683800.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1737683800.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1737683800.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1737683800.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1737683800.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1737683800.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1737683800.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1737683800.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1737683800.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1737683800.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1737683800.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1737683800.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1737683800.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1737683800.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1737683800.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1737683800.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n"
     ]
    }
   ],
   "source": [
    "# Impute null bed values for 2018 AHA DF\n",
    "\n",
    "bed_cols = ['GENBD', 'PEDBD', 'OBBD', 'MSICBD', \n",
    "                'CICBD', 'NICBD', 'NINTBD', 'PEDICBD', \n",
    "                'BRNBD', 'SPCICBD', 'OTHICBD', 'REHABBD', \n",
    "                'ALCHBD', 'PSYBD', 'SNBD88', 'ICFBD88', \n",
    "                'ACULTBD', 'OTHLBD94', 'OTHBD94']\n",
    "for column in bed_cols:\n",
    "    AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
    "\n",
    "print(AHA_2018_df[['GENBD', 'BRNBD', 'PSYBD']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       TETOT     GFEET  OPRA\n",
      "0       12.0  224028.0   5.0\n",
      "3       12.0  224028.0   5.0\n",
      "6       12.0  224028.0   5.0\n",
      "9       12.0  224028.0   5.0\n",
      "12       0.0  224028.0   5.0\n",
      "...      ...       ...   ...\n",
      "18530  137.0  224028.0  12.0\n",
      "18533   12.0  224028.0   5.0\n",
      "18536    9.0  210060.0   4.0\n",
      "18539   12.0  224028.0   5.0\n",
      "18542   12.0  224028.0   5.0\n",
      "\n",
      "[6218 rows x 3 columns]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/2127246283.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df['TETOT'] = AHA_2018_df['TETOT'].fillna(AHA_2018_df['TETOT'].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/2127246283.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df['GFEET'] = AHA_2018_df['GFEET'].fillna(AHA_2018_df['GFEET'].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/2127246283.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df['OPRA'] = AHA_2018_df['OPRA'].fillna(AHA_2018_df['OPRA'].median())\n"
     ]
    }
   ],
   "source": [
    "# Impute null total employed for 2018 AHA DF\n",
    "AHA_2018_df['TETOT'] = AHA_2018_df['TETOT'].fillna(AHA_2018_df['TETOT'].median())\n",
    "\n",
    "# Impute null gross total square feet for 2018 AHA DF\n",
    "AHA_2018_df['GFEET'] = AHA_2018_df['GFEET'].fillna(AHA_2018_df['GFEET'].median())\n",
    "\n",
    "# Impute null operating rooms for 2018 AHA DF\n",
    "AHA_2018_df['OPRA'] = AHA_2018_df['OPRA'].fillna(AHA_2018_df['OPRA'].median())\n",
    "\n",
    "print(AHA_2018_df[['TETOT', 'GFEET', 'OPRA']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        VRN  VAST  VTOTL\n",
      "0       8.0   3.0   29.0\n",
      "3       8.0   3.0   29.0\n",
      "6       8.0   3.0   29.0\n",
      "9       8.0   3.0   29.0\n",
      "12      8.0   3.0   29.0\n",
      "...     ...   ...    ...\n",
      "18530  54.0  10.0  288.0\n",
      "18533   8.0   3.0   29.0\n",
      "18536   8.0   3.0   59.0\n",
      "18539   8.0   3.0   29.0\n",
      "18542   8.0   3.0   29.0\n",
      "\n",
      "[6218 rows x 3 columns]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1322461561.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1322461561.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1322461561.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1322461561.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1322461561.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1322461561.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1322461561.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1322461561.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1322461561.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1322461561.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1322461561.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1322461561.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1322461561.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1322461561.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1322461561.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n"
     ]
    }
   ],
   "source": [
    "# Impute null staff vaccancy values for 2018 AHA DF\n",
    "vacancy_cols = ['VMD', 'VRES', 'VTTRN', 'VRN', \n",
    "                'VLPN', 'VAST', 'VLAB', 'VPHR', \n",
    "                'VPHT', 'VRSP', 'VOTHl', 'VTOTL', \n",
    "                'VRNH', 'VTNH', 'VRAD']\n",
    "for column in vacancy_cols:\n",
    "    AHA_2018_df[column] = AHA_2018_df[column].fillna(AHA_2018_df[column].median())\n",
    "\n",
    "print(AHA_2018_df[['VRN', 'VAST', 'VTOTL']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save 2018 AHA DF to CSV\n",
    "AHA_2018_df.to_csv('AHA_2018_df.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### AHA 2019 DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    YEAR  STCD  GENBD  PEDBD  OBBD  MSICBD  CICBD  NICBD  NINTBD  PEDICBD  \\\n",
      "1   2019     3   30.0    0.0   3.0     5.0    0.0    0.0     0.0      0.0   \n",
      "4   2019     4   30.0    0.0   3.0     5.0    0.0    0.0     0.0      0.0   \n",
      "7   2019     4   30.0    0.0   3.0     5.0    0.0    0.0     0.0      0.0   \n",
      "10  2019     4   30.0    0.0   3.0     5.0    0.0    0.0     0.0      0.0   \n",
      "13  2019     4   89.0   18.0  30.0     8.0    0.0    0.0     0.0      0.0   \n",
      "\n",
      "    ...  VRAD  VLAB  VPHR  VPHT  VRSP  VOTHl  VTOTL  VRNH  VTNH  TETOT  \n",
      "1   ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0   12.0  \n",
      "4   ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0   12.0  \n",
      "7   ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0   12.0  \n",
      "10  ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0   12.0  \n",
      "13  ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0   12.0  \n",
      "\n",
      "[5 rows x 54 columns]\n"
     ]
    }
   ],
   "source": [
    "# 2019 DF\n",
    "AHA_2019_df = AHA_df[AHA_df['YEAR'] == 2019]\n",
    "print(AHA_2019_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       GENBD  BRNBD  PSYBD\n",
      "1       30.0    0.0    0.0\n",
      "4       30.0    0.0    0.0\n",
      "7       30.0    0.0    0.0\n",
      "10      30.0    0.0    0.0\n",
      "13      89.0    0.0    0.0\n",
      "...      ...    ...    ...\n",
      "18531   60.0    0.0   12.0\n",
      "18534   30.0    0.0    0.0\n",
      "18537  103.0    0.0   29.0\n",
      "18540   30.0    0.0    0.0\n",
      "18543   30.0    0.0    0.0\n",
      "\n",
      "[6162 rows x 3 columns]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1451418921.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1451418921.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1451418921.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1451418921.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1451418921.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1451418921.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1451418921.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1451418921.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1451418921.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1451418921.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1451418921.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1451418921.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1451418921.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1451418921.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1451418921.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1451418921.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1451418921.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1451418921.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/1451418921.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n"
     ]
    }
   ],
   "source": [
    "# Impute null bed values for 2019 AHA DF\n",
    "\n",
    "bed_cols = ['GENBD', 'PEDBD', 'OBBD', 'MSICBD', \n",
    "                'CICBD', 'NICBD', 'NINTBD', 'PEDICBD', \n",
    "                'BRNBD', 'SPCICBD', 'OTHICBD', 'REHABBD', \n",
    "                'ALCHBD', 'PSYBD', 'SNBD88', 'ICFBD88', \n",
    "                'ACULTBD', 'OTHLBD94', 'OTHBD94']\n",
    "for column in bed_cols:\n",
    "    AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
    "\n",
    "print(AHA_2019_df[['GENBD', 'BRNBD', 'PSYBD']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       TETOT     GFEET  OPRA\n",
      "1       12.0  224028.0   5.0\n",
      "4       12.0  224028.0   5.0\n",
      "7       12.0  224028.0   5.0\n",
      "10      12.0  224028.0   5.0\n",
      "13      12.0  224028.0   5.0\n",
      "...      ...       ...   ...\n",
      "18531  319.0  224028.0   8.0\n",
      "18534   12.0  224028.0   5.0\n",
      "18537    9.0  210060.0   4.0\n",
      "18540   12.0  224028.0   5.0\n",
      "18543   12.0  224028.0   5.0\n",
      "\n",
      "[6162 rows x 3 columns]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3643963942.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df['TETOT'] = AHA_2019_df['TETOT'].fillna(AHA_2019_df['TETOT'].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3643963942.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df['GFEET'] = AHA_2019_df['GFEET'].fillna(AHA_2019_df['GFEET'].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3643963942.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df['OPRA'] = AHA_2019_df['OPRA'].fillna(AHA_2019_df['OPRA'].median())\n"
     ]
    }
   ],
   "source": [
    "# Impute null total employed for 2019 AHA DF\n",
    "AHA_2019_df['TETOT'] = AHA_2019_df['TETOT'].fillna(AHA_2019_df['TETOT'].median())\n",
    "\n",
    "# Impute null gross total square feet for 2019 AHA DF\n",
    "AHA_2019_df['GFEET'] = AHA_2019_df['GFEET'].fillna(AHA_2019_df['GFEET'].median())\n",
    "\n",
    "# Impute null operating rooms for 2019 AHA DF\n",
    "AHA_2019_df['OPRA'] = AHA_2019_df['OPRA'].fillna(AHA_2019_df['OPRA'].median())\n",
    "\n",
    "print(AHA_2019_df[['TETOT', 'GFEET', 'OPRA']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       VMD   VRN  VTOTL\n",
      "1      0.0   8.0   29.0\n",
      "4      0.0   8.0   29.0\n",
      "7      0.0   8.0   29.0\n",
      "10     0.0   8.0   29.0\n",
      "13     0.0   8.0   29.0\n",
      "...    ...   ...    ...\n",
      "18531  7.0  91.0  465.0\n",
      "18534  0.0   8.0   29.0\n",
      "18537  2.0  12.0   33.0\n",
      "18540  0.0   8.0   29.0\n",
      "18543  0.0   8.0   29.0\n",
      "\n",
      "[6162 rows x 3 columns]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3411243594.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3411243594.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3411243594.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3411243594.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3411243594.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3411243594.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3411243594.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3411243594.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3411243594.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3411243594.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3411243594.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3411243594.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3411243594.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3411243594.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3411243594.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n"
     ]
    }
   ],
   "source": [
    "# Impute null staff vaccancy values for 2019 AHA DF\n",
    "vacancy_cols = ['VMD', 'VRES', 'VTTRN', 'VRN',\n",
    "                'VLPN', 'VAST', 'VLAB', 'VPHR', \n",
    "                'VPHT', 'VRSP', 'VOTHl', 'VTOTL', \n",
    "                'VRNH', 'VTNH', 'VRAD']\n",
    "for column in vacancy_cols:\n",
    "    AHA_2019_df[column] = AHA_2019_df[column].fillna(AHA_2019_df[column].median())\n",
    "\n",
    "print(AHA_2019_df[['VMD', 'VRN', 'VTOTL']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save 2019 AHA DF to CSV\n",
    "AHA_2019_df.to_csv('AHA_2019_df.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### AHA 2020 DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    YEAR  STCD  GENBD  PEDBD  OBBD  MSICBD  CICBD  NICBD  NINTBD  PEDICBD  \\\n",
      "2   2020     3   30.0    0.0   3.0     5.0    0.0    0.0     0.0      0.0   \n",
      "5   2020     4   30.0    0.0   3.0     5.0    0.0    0.0     0.0      0.0   \n",
      "8   2020     4   30.0    0.0   3.0     5.0    0.0    0.0     0.0      0.0   \n",
      "11  2020     4   30.0    0.0   3.0     5.0    0.0    0.0     0.0      0.0   \n",
      "14  2020     4   30.0    0.0   3.0     5.0    0.0    0.0     0.0      0.0   \n",
      "\n",
      "    ...  VRAD  VLAB  VPHR  VPHT  VRSP  VOTHl  VTOTL  VRNH  VTNH  TETOT  \n",
      "2   ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0   12.0  \n",
      "5   ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0   12.0  \n",
      "8   ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0   12.0  \n",
      "11  ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0   12.0  \n",
      "14  ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0   12.0  \n",
      "\n",
      "[5 rows x 54 columns]\n"
     ]
    }
   ],
   "source": [
    "# 2020 DF\n",
    "AHA_2020_df = AHA_df[AHA_df['YEAR'] == 2020]\n",
    "print(AHA_2020_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       GENBD  BRNBD  PSYBD\n",
      "2       30.0    0.0    0.0\n",
      "5       30.0    0.0    0.0\n",
      "8       30.0    0.0    0.0\n",
      "11      30.0    0.0    0.0\n",
      "14      30.0    0.0    0.0\n",
      "...      ...    ...    ...\n",
      "18532   30.0    0.0    0.0\n",
      "18535   30.0    0.0    0.0\n",
      "18538  103.0    0.0   29.0\n",
      "18541   30.0    0.0    0.0\n",
      "18544   30.0    0.0    0.0\n",
      "\n",
      "[6165 rows x 3 columns]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/133853483.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/133853483.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/133853483.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/133853483.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/133853483.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/133853483.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/133853483.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/133853483.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/133853483.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/133853483.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/133853483.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/133853483.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/133853483.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/133853483.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/133853483.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/133853483.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/133853483.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/133853483.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/133853483.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n"
     ]
    }
   ],
   "source": [
    "# Impute null bed values for 2020 AHA DF\n",
    "bed_cols = ['GENBD', 'PEDBD', 'OBBD', 'MSICBD', \n",
    "                'CICBD', 'NICBD', 'NINTBD', 'PEDICBD', \n",
    "                'BRNBD', 'SPCICBD', 'OTHICBD', 'REHABBD', \n",
    "                'ALCHBD', 'PSYBD', 'SNBD88', 'ICFBD88', \n",
    "                'ACULTBD', 'OTHLBD94', 'OTHBD94']\n",
    "for column in bed_cols:\n",
    "    AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
    "\n",
    "print(AHA_2020_df[['GENBD', 'BRNBD', 'PSYBD']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       TETOT     GFEET  OPRA\n",
      "2       12.0  224028.0   5.0\n",
      "5       12.0  224028.0   5.0\n",
      "8       12.0  224028.0   5.0\n",
      "11      12.0  224028.0   5.0\n",
      "14      12.0  224028.0   5.0\n",
      "...      ...       ...   ...\n",
      "18532   12.0  224028.0   5.0\n",
      "18535   12.0  224028.0   5.0\n",
      "18538   12.0  210060.0   4.0\n",
      "18541   12.0  224028.0   5.0\n",
      "18544   12.0  224028.0   5.0\n",
      "\n",
      "[6165 rows x 3 columns]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3474941413.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df['TETOT'] = AHA_2020_df['TETOT'].fillna(AHA_2020_df['TETOT'].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3474941413.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df['GFEET'] = AHA_2020_df['GFEET'].fillna(AHA_2020_df['GFEET'].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/3474941413.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df['OPRA'] = AHA_2020_df['OPRA'].fillna(AHA_2020_df['OPRA'].median())\n"
     ]
    }
   ],
   "source": [
    "# Impute null total employed for 2020 AHA DF\n",
    "AHA_2020_df['TETOT'] = AHA_2020_df['TETOT'].fillna(AHA_2020_df['TETOT'].median())\n",
    "\n",
    "# Impute null gross total square feet for 2020 AHA DF\n",
    "AHA_2020_df['GFEET'] = AHA_2020_df['GFEET'].fillna(AHA_2020_df['GFEET'].median())\n",
    "\n",
    "# Impute null operating rooms for 2020 AHA DF\n",
    "AHA_2020_df['OPRA'] = AHA_2020_df['OPRA'].fillna(AHA_2020_df['OPRA'].median())\n",
    "\n",
    "print(AHA_2020_df[['TETOT', 'GFEET', 'OPRA']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       VMD  VRN  VTOTL\n",
      "2      0.0  8.0   29.0\n",
      "5      0.0  8.0   29.0\n",
      "8      0.0  8.0   29.0\n",
      "11     0.0  8.0   29.0\n",
      "14     0.0  8.0   29.0\n",
      "...    ...  ...    ...\n",
      "18532  0.0  8.0   29.0\n",
      "18535  0.0  8.0   29.0\n",
      "18538  0.0  8.0   29.0\n",
      "18541  0.0  8.0   29.0\n",
      "18544  0.0  8.0   29.0\n",
      "\n",
      "[6165 rows x 3 columns]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/442409705.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/442409705.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/442409705.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/442409705.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/442409705.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/442409705.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/442409705.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/442409705.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/442409705.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/442409705.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/442409705.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/442409705.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/442409705.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
      "/var/folders/_j/yd9ttlns727476l3p83m83nr0000gn/T/ipykernel_7455/442409705.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n"
     ]
    }
   ],
   "source": [
    "# Impute null staff vaccancy values for 2020 AHA DF\n",
    "vacancy_cols = ['VMD', 'VRES', 'VTTRN', 'VRN',\n",
    "                'VLPN', 'VAST', 'VLAB', 'VPHR', \n",
    "                'VPHT', 'VRSP', 'VOTHl', 'VTOTL', \n",
    "                'VRNH', 'VTNH']\n",
    "for column in vacancy_cols:\n",
    "    AHA_2020_df[column] = AHA_2020_df[column].fillna(AHA_2020_df[column].median())\n",
    "\n",
    "print(AHA_2020_df[['VMD', 'VRN', 'VTOTL']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save 2020 AHA DF to CSV\n",
    "AHA_2020_df.to_csv('AHA_2020_df.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Descriptive Stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "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>YEAR</th>\n",
       "      <th>STCD</th>\n",
       "      <th>GENBD</th>\n",
       "      <th>PEDBD</th>\n",
       "      <th>OBBD</th>\n",
       "      <th>MSICBD</th>\n",
       "      <th>CICBD</th>\n",
       "      <th>NICBD</th>\n",
       "      <th>NINTBD</th>\n",
       "      <th>PEDICBD</th>\n",
       "      <th>...</th>\n",
       "      <th>VRAD</th>\n",
       "      <th>VLAB</th>\n",
       "      <th>VPHR</th>\n",
       "      <th>VPHT</th>\n",
       "      <th>VRSP</th>\n",
       "      <th>VOTHl</th>\n",
       "      <th>VTOTL</th>\n",
       "      <th>VRNH</th>\n",
       "      <th>VTNH</th>\n",
       "      <th>TETOT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>6218.0</td>\n",
       "      <td>6218.000000</td>\n",
       "      <td>6218.000000</td>\n",
       "      <td>6218.000000</td>\n",
       "      <td>6218.00000</td>\n",
       "      <td>6218.000000</td>\n",
       "      <td>6218.000000</td>\n",
       "      <td>6218.000000</td>\n",
       "      <td>6218.000000</td>\n",
       "      <td>6218.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>6218.000000</td>\n",
       "      <td>6218.000000</td>\n",
       "      <td>6218.000000</td>\n",
       "      <td>6218.000000</td>\n",
       "      <td>6218.000000</td>\n",
       "      <td>6218.000000</td>\n",
       "      <td>6218.000000</td>\n",
       "      <td>6218.000000</td>\n",
       "      <td>6218.000000</td>\n",
       "      <td>6218.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>55.771631</td>\n",
       "      <td>64.774043</td>\n",
       "      <td>4.062721</td>\n",
       "      <td>9.03313</td>\n",
       "      <td>9.098102</td>\n",
       "      <td>2.336121</td>\n",
       "      <td>3.687681</td>\n",
       "      <td>1.156964</td>\n",
       "      <td>0.827758</td>\n",
       "      <td>...</td>\n",
       "      <td>0.919749</td>\n",
       "      <td>0.912673</td>\n",
       "      <td>0.337568</td>\n",
       "      <td>0.476037</td>\n",
       "      <td>0.540528</td>\n",
       "      <td>23.434545</td>\n",
       "      <td>57.243004</td>\n",
       "      <td>0.095368</td>\n",
       "      <td>0.405597</td>\n",
       "      <td>56.131875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.0</td>\n",
       "      <td>23.675836</td>\n",
       "      <td>95.301661</td>\n",
       "      <td>17.485652</td>\n",
       "      <td>16.04910</td>\n",
       "      <td>14.613525</td>\n",
       "      <td>8.054358</td>\n",
       "      <td>12.423868</td>\n",
       "      <td>5.673220</td>\n",
       "      <td>4.776062</td>\n",
       "      <td>...</td>\n",
       "      <td>3.318127</td>\n",
       "      <td>4.091199</td>\n",
       "      <td>2.170045</td>\n",
       "      <td>2.401106</td>\n",
       "      <td>1.930108</td>\n",
       "      <td>66.632576</td>\n",
       "      <td>131.357098</td>\n",
       "      <td>0.851764</td>\n",
       "      <td>3.155533</td>\n",
       "      <td>193.482286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>39.000000</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>7.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>54.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.00000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>12.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>74.000000</td>\n",
       "      <td>69.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.00000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>14.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>95.000000</td>\n",
       "      <td>1304.000000</td>\n",
       "      <td>355.000000</td>\n",
       "      <td>199.00000</td>\n",
       "      <td>207.000000</td>\n",
       "      <td>150.000000</td>\n",
       "      <td>260.000000</td>\n",
       "      <td>154.000000</td>\n",
       "      <td>120.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>64.000000</td>\n",
       "      <td>116.000000</td>\n",
       "      <td>115.000000</td>\n",
       "      <td>81.000000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>1992.000000</td>\n",
       "      <td>3440.000000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>106.000000</td>\n",
       "      <td>3257.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 54 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         YEAR         STCD        GENBD        PEDBD        OBBD       MSICBD  \\\n",
       "count  6218.0  6218.000000  6218.000000  6218.000000  6218.00000  6218.000000   \n",
       "mean   2018.0    55.771631    64.774043     4.062721     9.03313     9.098102   \n",
       "std       0.0    23.675836    95.301661    17.485652    16.04910    14.613525   \n",
       "min    2018.0     3.000000     0.000000     0.000000     0.00000     0.000000   \n",
       "25%    2018.0    39.000000    20.000000     0.000000     0.00000     0.000000   \n",
       "50%    2018.0    54.000000    30.000000     0.000000     3.00000     5.000000   \n",
       "75%    2018.0    74.000000    69.000000     0.000000    10.00000    10.000000   \n",
       "max    2018.0    95.000000  1304.000000   355.000000   199.00000   207.000000   \n",
       "\n",
       "             CICBD        NICBD       NINTBD      PEDICBD  ...         VRAD  \\\n",
       "count  6218.000000  6218.000000  6218.000000  6218.000000  ...  6218.000000   \n",
       "mean      2.336121     3.687681     1.156964     0.827758  ...     0.919749   \n",
       "std       8.054358    12.423868     5.673220     4.776062  ...     3.318127   \n",
       "min       0.000000     0.000000     0.000000     0.000000  ...     0.000000   \n",
       "25%       0.000000     0.000000     0.000000     0.000000  ...     0.000000   \n",
       "50%       0.000000     0.000000     0.000000     0.000000  ...     0.000000   \n",
       "75%       0.000000     0.000000     0.000000     0.000000  ...     0.000000   \n",
       "max     150.000000   260.000000   154.000000   120.000000  ...    64.000000   \n",
       "\n",
       "              VLAB         VPHR         VPHT         VRSP        VOTHl  \\\n",
       "count  6218.000000  6218.000000  6218.000000  6218.000000  6218.000000   \n",
       "mean      0.912673     0.337568     0.476037     0.540528    23.434545   \n",
       "std       4.091199     2.170045     2.401106     1.930108    66.632576   \n",
       "min       0.000000     0.000000     0.000000     0.000000     0.000000   \n",
       "25%       0.000000     0.000000     0.000000     0.000000    10.000000   \n",
       "50%       0.000000     0.000000     0.000000     0.000000    10.000000   \n",
       "75%       0.000000     0.000000     0.000000     0.000000    10.000000   \n",
       "max     116.000000   115.000000    81.000000    32.000000  1992.000000   \n",
       "\n",
       "             VTOTL         VRNH         VTNH        TETOT  \n",
       "count  6218.000000  6218.000000  6218.000000  6218.000000  \n",
       "mean     57.243004     0.095368     0.405597    56.131875  \n",
       "std     131.357098     0.851764     3.155533   193.482286  \n",
       "min       0.000000     0.000000     0.000000     0.000000  \n",
       "25%      29.000000     0.000000     0.000000     7.000000  \n",
       "50%      29.000000     0.000000     0.000000    12.000000  \n",
       "75%      29.000000     0.000000     0.000000    14.000000  \n",
       "max    3440.000000    24.000000   106.000000  3257.000000  \n",
       "\n",
       "[8 rows x 54 columns]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "AHA_2018_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "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>YEAR</th>\n",
       "      <th>STCD</th>\n",
       "      <th>GENBD</th>\n",
       "      <th>PEDBD</th>\n",
       "      <th>OBBD</th>\n",
       "      <th>MSICBD</th>\n",
       "      <th>CICBD</th>\n",
       "      <th>NICBD</th>\n",
       "      <th>NINTBD</th>\n",
       "      <th>PEDICBD</th>\n",
       "      <th>...</th>\n",
       "      <th>VRAD</th>\n",
       "      <th>VLAB</th>\n",
       "      <th>VPHR</th>\n",
       "      <th>VPHT</th>\n",
       "      <th>VRSP</th>\n",
       "      <th>VOTHl</th>\n",
       "      <th>VTOTL</th>\n",
       "      <th>VRNH</th>\n",
       "      <th>VTNH</th>\n",
       "      <th>TETOT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>6162.0</td>\n",
       "      <td>6162.000000</td>\n",
       "      <td>6162.000000</td>\n",
       "      <td>6162.000000</td>\n",
       "      <td>6162.000000</td>\n",
       "      <td>6162.000000</td>\n",
       "      <td>6162.000000</td>\n",
       "      <td>6162.000000</td>\n",
       "      <td>6162.000000</td>\n",
       "      <td>6162.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>6162.000000</td>\n",
       "      <td>6162.000000</td>\n",
       "      <td>6162.000000</td>\n",
       "      <td>6162.000000</td>\n",
       "      <td>6162.000000</td>\n",
       "      <td>6162.000000</td>\n",
       "      <td>6162.000000</td>\n",
       "      <td>6162.000000</td>\n",
       "      <td>6162.000000</td>\n",
       "      <td>6162.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>55.804771</td>\n",
       "      <td>64.219247</td>\n",
       "      <td>3.954236</td>\n",
       "      <td>8.878611</td>\n",
       "      <td>9.004057</td>\n",
       "      <td>2.373905</td>\n",
       "      <td>3.686141</td>\n",
       "      <td>1.139403</td>\n",
       "      <td>0.839987</td>\n",
       "      <td>...</td>\n",
       "      <td>1.147679</td>\n",
       "      <td>1.090068</td>\n",
       "      <td>0.365628</td>\n",
       "      <td>0.529698</td>\n",
       "      <td>0.656443</td>\n",
       "      <td>24.776371</td>\n",
       "      <td>61.270042</td>\n",
       "      <td>0.076599</td>\n",
       "      <td>0.400519</td>\n",
       "      <td>61.362382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.0</td>\n",
       "      <td>23.673913</td>\n",
       "      <td>96.319024</td>\n",
       "      <td>17.633416</td>\n",
       "      <td>15.995542</td>\n",
       "      <td>14.546496</td>\n",
       "      <td>8.410103</td>\n",
       "      <td>12.525416</td>\n",
       "      <td>5.724895</td>\n",
       "      <td>4.924560</td>\n",
       "      <td>...</td>\n",
       "      <td>4.786085</td>\n",
       "      <td>5.523158</td>\n",
       "      <td>2.020558</td>\n",
       "      <td>2.726721</td>\n",
       "      <td>2.547446</td>\n",
       "      <td>77.487374</td>\n",
       "      <td>153.005418</td>\n",
       "      <td>0.665646</td>\n",
       "      <td>3.227698</td>\n",
       "      <td>208.107291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>39.000000</td>\n",
       "      <td>21.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>9.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>54.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>12.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>74.000000</td>\n",
       "      <td>62.750000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>15.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2019.0</td>\n",
       "      <td>95.000000</td>\n",
       "      <td>1302.000000</td>\n",
       "      <td>355.000000</td>\n",
       "      <td>203.000000</td>\n",
       "      <td>216.000000</td>\n",
       "      <td>154.000000</td>\n",
       "      <td>253.000000</td>\n",
       "      <td>154.000000</td>\n",
       "      <td>120.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>184.000000</td>\n",
       "      <td>183.000000</td>\n",
       "      <td>42.000000</td>\n",
       "      <td>73.000000</td>\n",
       "      <td>56.000000</td>\n",
       "      <td>2067.000000</td>\n",
       "      <td>3503.000000</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>89.000000</td>\n",
       "      <td>3495.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 54 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         YEAR         STCD        GENBD        PEDBD         OBBD  \\\n",
       "count  6162.0  6162.000000  6162.000000  6162.000000  6162.000000   \n",
       "mean   2019.0    55.804771    64.219247     3.954236     8.878611   \n",
       "std       0.0    23.673913    96.319024    17.633416    15.995542   \n",
       "min    2019.0     3.000000     0.000000     0.000000     0.000000   \n",
       "25%    2019.0    39.000000    21.000000     0.000000     0.000000   \n",
       "50%    2019.0    54.000000    30.000000     0.000000     3.000000   \n",
       "75%    2019.0    74.000000    62.750000     0.000000    10.000000   \n",
       "max    2019.0    95.000000  1302.000000   355.000000   203.000000   \n",
       "\n",
       "            MSICBD        CICBD        NICBD       NINTBD      PEDICBD  ...  \\\n",
       "count  6162.000000  6162.000000  6162.000000  6162.000000  6162.000000  ...   \n",
       "mean      9.004057     2.373905     3.686141     1.139403     0.839987  ...   \n",
       "std      14.546496     8.410103    12.525416     5.724895     4.924560  ...   \n",
       "min       0.000000     0.000000     0.000000     0.000000     0.000000  ...   \n",
       "25%       0.000000     0.000000     0.000000     0.000000     0.000000  ...   \n",
       "50%       5.000000     0.000000     0.000000     0.000000     0.000000  ...   \n",
       "75%      10.000000     0.000000     0.000000     0.000000     0.000000  ...   \n",
       "max     216.000000   154.000000   253.000000   154.000000   120.000000  ...   \n",
       "\n",
       "              VRAD         VLAB         VPHR         VPHT         VRSP  \\\n",
       "count  6162.000000  6162.000000  6162.000000  6162.000000  6162.000000   \n",
       "mean      1.147679     1.090068     0.365628     0.529698     0.656443   \n",
       "std       4.786085     5.523158     2.020558     2.726721     2.547446   \n",
       "min       0.000000     0.000000     0.000000     0.000000     0.000000   \n",
       "25%       0.000000     0.000000     0.000000     0.000000     0.000000   \n",
       "50%       0.000000     0.000000     0.000000     0.000000     0.000000   \n",
       "75%       0.000000     0.000000     0.000000     0.000000     0.000000   \n",
       "max     184.000000   183.000000    42.000000    73.000000    56.000000   \n",
       "\n",
       "             VOTHl        VTOTL         VRNH         VTNH        TETOT  \n",
       "count  6162.000000  6162.000000  6162.000000  6162.000000  6162.000000  \n",
       "mean     24.776371    61.270042     0.076599     0.400519    61.362382  \n",
       "std      77.487374   153.005418     0.665646     3.227698   208.107291  \n",
       "min       0.000000     0.000000     0.000000     0.000000     0.000000  \n",
       "25%      10.000000    29.000000     0.000000     0.000000     9.000000  \n",
       "50%      10.000000    29.000000     0.000000     0.000000    12.000000  \n",
       "75%      10.000000    29.000000     0.000000     0.000000    15.000000  \n",
       "max    2067.000000  3503.000000    20.000000    89.000000  3495.000000  \n",
       "\n",
       "[8 rows x 54 columns]"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "AHA_2019_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "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>YEAR</th>\n",
       "      <th>STCD</th>\n",
       "      <th>GENBD</th>\n",
       "      <th>PEDBD</th>\n",
       "      <th>OBBD</th>\n",
       "      <th>MSICBD</th>\n",
       "      <th>CICBD</th>\n",
       "      <th>NICBD</th>\n",
       "      <th>NINTBD</th>\n",
       "      <th>PEDICBD</th>\n",
       "      <th>...</th>\n",
       "      <th>VRAD</th>\n",
       "      <th>VLAB</th>\n",
       "      <th>VPHR</th>\n",
       "      <th>VPHT</th>\n",
       "      <th>VRSP</th>\n",
       "      <th>VOTHl</th>\n",
       "      <th>VTOTL</th>\n",
       "      <th>VRNH</th>\n",
       "      <th>VTNH</th>\n",
       "      <th>TETOT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>6165.0</td>\n",
       "      <td>6165.000000</td>\n",
       "      <td>6165.000000</td>\n",
       "      <td>6165.000000</td>\n",
       "      <td>6165.000000</td>\n",
       "      <td>6165.000000</td>\n",
       "      <td>6165.000000</td>\n",
       "      <td>6165.000000</td>\n",
       "      <td>6165.000000</td>\n",
       "      <td>6165.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>6165.000000</td>\n",
       "      <td>6165.000000</td>\n",
       "      <td>6165.000000</td>\n",
       "      <td>6165.000000</td>\n",
       "      <td>6165.000000</td>\n",
       "      <td>6165.000000</td>\n",
       "      <td>6165.000000</td>\n",
       "      <td>6165.000000</td>\n",
       "      <td>6165.000000</td>\n",
       "      <td>6165.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2020.0</td>\n",
       "      <td>55.888078</td>\n",
       "      <td>64.300568</td>\n",
       "      <td>3.712895</td>\n",
       "      <td>8.711436</td>\n",
       "      <td>9.357826</td>\n",
       "      <td>2.343228</td>\n",
       "      <td>3.655961</td>\n",
       "      <td>1.039903</td>\n",
       "      <td>0.810381</td>\n",
       "      <td>...</td>\n",
       "      <td>1.146310</td>\n",
       "      <td>1.303325</td>\n",
       "      <td>0.330576</td>\n",
       "      <td>0.533496</td>\n",
       "      <td>0.864071</td>\n",
       "      <td>24.721006</td>\n",
       "      <td>64.222709</td>\n",
       "      <td>0.075750</td>\n",
       "      <td>0.434874</td>\n",
       "      <td>64.558962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.0</td>\n",
       "      <td>23.642497</td>\n",
       "      <td>98.170859</td>\n",
       "      <td>17.030605</td>\n",
       "      <td>16.010023</td>\n",
       "      <td>15.571071</td>\n",
       "      <td>8.388538</td>\n",
       "      <td>12.559716</td>\n",
       "      <td>4.978379</td>\n",
       "      <td>4.844555</td>\n",
       "      <td>...</td>\n",
       "      <td>4.355846</td>\n",
       "      <td>5.365620</td>\n",
       "      <td>1.932436</td>\n",
       "      <td>2.505359</td>\n",
       "      <td>3.170478</td>\n",
       "      <td>73.819867</td>\n",
       "      <td>155.680328</td>\n",
       "      <td>0.608669</td>\n",
       "      <td>3.607240</td>\n",
       "      <td>228.688254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2020.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2020.0</td>\n",
       "      <td>39.000000</td>\n",
       "      <td>21.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2020.0</td>\n",
       "      <td>54.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>12.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2020.0</td>\n",
       "      <td>74.000000</td>\n",
       "      <td>58.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>17.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2020.0</td>\n",
       "      <td>95.000000</td>\n",
       "      <td>1407.000000</td>\n",
       "      <td>353.000000</td>\n",
       "      <td>243.000000</td>\n",
       "      <td>218.000000</td>\n",
       "      <td>150.000000</td>\n",
       "      <td>253.000000</td>\n",
       "      <td>117.000000</td>\n",
       "      <td>120.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>104.000000</td>\n",
       "      <td>153.000000</td>\n",
       "      <td>55.000000</td>\n",
       "      <td>72.000000</td>\n",
       "      <td>86.000000</td>\n",
       "      <td>2002.000000</td>\n",
       "      <td>3367.000000</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>114.000000</td>\n",
       "      <td>5562.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 54 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         YEAR         STCD        GENBD        PEDBD         OBBD  \\\n",
       "count  6165.0  6165.000000  6165.000000  6165.000000  6165.000000   \n",
       "mean   2020.0    55.888078    64.300568     3.712895     8.711436   \n",
       "std       0.0    23.642497    98.170859    17.030605    16.010023   \n",
       "min    2020.0     3.000000     0.000000     0.000000     0.000000   \n",
       "25%    2020.0    39.000000    21.000000     0.000000     0.000000   \n",
       "50%    2020.0    54.000000    30.000000     0.000000     3.000000   \n",
       "75%    2020.0    74.000000    58.000000     0.000000     9.000000   \n",
       "max    2020.0    95.000000  1407.000000   353.000000   243.000000   \n",
       "\n",
       "            MSICBD        CICBD        NICBD       NINTBD      PEDICBD  ...  \\\n",
       "count  6165.000000  6165.000000  6165.000000  6165.000000  6165.000000  ...   \n",
       "mean      9.357826     2.343228     3.655961     1.039903     0.810381  ...   \n",
       "std      15.571071     8.388538    12.559716     4.978379     4.844555  ...   \n",
       "min       0.000000     0.000000     0.000000     0.000000     0.000000  ...   \n",
       "25%       0.000000     0.000000     0.000000     0.000000     0.000000  ...   \n",
       "50%       5.000000     0.000000     0.000000     0.000000     0.000000  ...   \n",
       "75%       9.000000     0.000000     0.000000     0.000000     0.000000  ...   \n",
       "max     218.000000   150.000000   253.000000   117.000000   120.000000  ...   \n",
       "\n",
       "              VRAD         VLAB         VPHR         VPHT         VRSP  \\\n",
       "count  6165.000000  6165.000000  6165.000000  6165.000000  6165.000000   \n",
       "mean      1.146310     1.303325     0.330576     0.533496     0.864071   \n",
       "std       4.355846     5.365620     1.932436     2.505359     3.170478   \n",
       "min       0.000000     0.000000     0.000000     0.000000     0.000000   \n",
       "25%       0.000000     0.000000     0.000000     0.000000     0.000000   \n",
       "50%       0.000000     0.000000     0.000000     0.000000     0.000000   \n",
       "75%       0.000000     0.000000     0.000000     0.000000     0.000000   \n",
       "max     104.000000   153.000000    55.000000    72.000000    86.000000   \n",
       "\n",
       "             VOTHl        VTOTL         VRNH         VTNH        TETOT  \n",
       "count  6165.000000  6165.000000  6165.000000  6165.000000  6165.000000  \n",
       "mean     24.721006    64.222709     0.075750     0.434874    64.558962  \n",
       "std      73.819867   155.680328     0.608669     3.607240   228.688254  \n",
       "min       0.000000     0.000000     0.000000     0.000000     0.000000  \n",
       "25%      10.000000    29.000000     0.000000     0.000000    10.000000  \n",
       "50%      10.000000    29.000000     0.000000     0.000000    12.000000  \n",
       "75%      10.000000    29.000000     0.000000     0.000000    17.000000  \n",
       "max    2002.000000  3367.000000    17.000000   114.000000  5562.000000  \n",
       "\n",
       "[8 rows x 54 columns]"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "AHA_2020_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "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>Lat</th>\n",
       "      <th>Long_</th>\n",
       "      <th>1/22/20</th>\n",
       "      <th>1/23/20</th>\n",
       "      <th>1/24/20</th>\n",
       "      <th>1/25/20</th>\n",
       "      <th>1/26/20</th>\n",
       "      <th>1/27/20</th>\n",
       "      <th>1/28/20</th>\n",
       "      <th>1/29/20</th>\n",
       "      <th>...</th>\n",
       "      <th>12/23/20</th>\n",
       "      <th>12/24/20</th>\n",
       "      <th>12/25/20</th>\n",
       "      <th>12/26/20</th>\n",
       "      <th>12/27/20</th>\n",
       "      <th>12/28/20</th>\n",
       "      <th>12/29/20</th>\n",
       "      <th>12/30/20</th>\n",
       "      <th>12/31/20</th>\n",
       "      <th>Total COVID case count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3.342000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>36.721617</td>\n",
       "      <td>-88.642045</td>\n",
       "      <td>0.000299</td>\n",
       "      <td>0.000299</td>\n",
       "      <td>0.000598</td>\n",
       "      <td>0.000598</td>\n",
       "      <td>0.001496</td>\n",
       "      <td>0.001496</td>\n",
       "      <td>0.001496</td>\n",
       "      <td>0.001795</td>\n",
       "      <td>...</td>\n",
       "      <td>5569.152902</td>\n",
       "      <td>5632.448833</td>\n",
       "      <td>5670.005685</td>\n",
       "      <td>5735.224117</td>\n",
       "      <td>5775.961700</td>\n",
       "      <td>5828.260622</td>\n",
       "      <td>5890.096349</td>\n",
       "      <td>5955.558348</td>\n",
       "      <td>6049.448235</td>\n",
       "      <td>5.173041e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>9.079322</td>\n",
       "      <td>21.776287</td>\n",
       "      <td>0.017298</td>\n",
       "      <td>0.017298</td>\n",
       "      <td>0.024459</td>\n",
       "      <td>0.024459</td>\n",
       "      <td>0.038656</td>\n",
       "      <td>0.038656</td>\n",
       "      <td>0.038656</td>\n",
       "      <td>0.042340</td>\n",
       "      <td>...</td>\n",
       "      <td>20025.502032</td>\n",
       "      <td>20301.413800</td>\n",
       "      <td>20407.856387</td>\n",
       "      <td>20836.007756</td>\n",
       "      <td>21061.031698</td>\n",
       "      <td>21343.690761</td>\n",
       "      <td>21600.056216</td>\n",
       "      <td>21838.045802</td>\n",
       "      <td>22225.401902</td>\n",
       "      <td>2.034329e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>-14.271000</td>\n",
       "      <td>-174.159600</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>33.896803</td>\n",
       "      <td>-97.803595</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>544.500000</td>\n",
       "      <td>551.500000</td>\n",
       "      <td>556.000000</td>\n",
       "      <td>559.500000</td>\n",
       "      <td>565.000000</td>\n",
       "      <td>570.000000</td>\n",
       "      <td>578.000000</td>\n",
       "      <td>589.000000</td>\n",
       "      <td>596.250000</td>\n",
       "      <td>3.746350e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>38.005610</td>\n",
       "      <td>-89.488865</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>1396.000000</td>\n",
       "      <td>1411.000000</td>\n",
       "      <td>1420.000000</td>\n",
       "      <td>1426.500000</td>\n",
       "      <td>1429.500000</td>\n",
       "      <td>1437.500000</td>\n",
       "      <td>1461.500000</td>\n",
       "      <td>1479.500000</td>\n",
       "      <td>1496.500000</td>\n",
       "      <td>1.061975e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>41.579255</td>\n",
       "      <td>-82.313398</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>3728.250000</td>\n",
       "      <td>3771.500000</td>\n",
       "      <td>3779.000000</td>\n",
       "      <td>3806.250000</td>\n",
       "      <td>3835.750000</td>\n",
       "      <td>3862.500000</td>\n",
       "      <td>3896.750000</td>\n",
       "      <td>3935.500000</td>\n",
       "      <td>3989.500000</td>\n",
       "      <td>2.980605e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>69.314792</td>\n",
       "      <td>145.673900</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>665036.000000</td>\n",
       "      <td>678040.000000</td>\n",
       "      <td>678040.000000</td>\n",
       "      <td>707463.000000</td>\n",
       "      <td>719960.000000</td>\n",
       "      <td>734860.000000</td>\n",
       "      <td>746666.000000</td>\n",
       "      <td>756840.000000</td>\n",
       "      <td>771519.000000</td>\n",
       "      <td>5.998834e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 348 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Lat        Long_      1/22/20      1/23/20      1/24/20  \\\n",
       "count  3342.000000  3342.000000  3342.000000  3342.000000  3342.000000   \n",
       "mean     36.721617   -88.642045     0.000299     0.000299     0.000598   \n",
       "std       9.079322    21.776287     0.017298     0.017298     0.024459   \n",
       "min     -14.271000  -174.159600     0.000000     0.000000     0.000000   \n",
       "25%      33.896803   -97.803595     0.000000     0.000000     0.000000   \n",
       "50%      38.005610   -89.488865     0.000000     0.000000     0.000000   \n",
       "75%      41.579255   -82.313398     0.000000     0.000000     0.000000   \n",
       "max      69.314792   145.673900     1.000000     1.000000     1.000000   \n",
       "\n",
       "           1/25/20      1/26/20      1/27/20      1/28/20      1/29/20  ...  \\\n",
       "count  3342.000000  3342.000000  3342.000000  3342.000000  3342.000000  ...   \n",
       "mean      0.000598     0.001496     0.001496     0.001496     0.001795  ...   \n",
       "std       0.024459     0.038656     0.038656     0.038656     0.042340  ...   \n",
       "min       0.000000     0.000000     0.000000     0.000000     0.000000  ...   \n",
       "25%       0.000000     0.000000     0.000000     0.000000     0.000000  ...   \n",
       "50%       0.000000     0.000000     0.000000     0.000000     0.000000  ...   \n",
       "75%       0.000000     0.000000     0.000000     0.000000     0.000000  ...   \n",
       "max       1.000000     1.000000     1.000000     1.000000     1.000000  ...   \n",
       "\n",
       "            12/23/20       12/24/20       12/25/20       12/26/20  \\\n",
       "count    3342.000000    3342.000000    3342.000000    3342.000000   \n",
       "mean     5569.152902    5632.448833    5670.005685    5735.224117   \n",
       "std     20025.502032   20301.413800   20407.856387   20836.007756   \n",
       "min         0.000000       0.000000       0.000000       0.000000   \n",
       "25%       544.500000     551.500000     556.000000     559.500000   \n",
       "50%      1396.000000    1411.000000    1420.000000    1426.500000   \n",
       "75%      3728.250000    3771.500000    3779.000000    3806.250000   \n",
       "max    665036.000000  678040.000000  678040.000000  707463.000000   \n",
       "\n",
       "            12/27/20       12/28/20       12/29/20       12/30/20  \\\n",
       "count    3342.000000    3342.000000    3342.000000    3342.000000   \n",
       "mean     5775.961700    5828.260622    5890.096349    5955.558348   \n",
       "std     21061.031698   21343.690761   21600.056216   21838.045802   \n",
       "min         0.000000       0.000000       0.000000       0.000000   \n",
       "25%       565.000000     570.000000     578.000000     589.000000   \n",
       "50%      1429.500000    1437.500000    1461.500000    1479.500000   \n",
       "75%      3835.750000    3862.500000    3896.750000    3935.500000   \n",
       "max    719960.000000  734860.000000  746666.000000  756840.000000   \n",
       "\n",
       "            12/31/20  Total COVID case count  \n",
       "count    3342.000000            3.342000e+03  \n",
       "mean     6049.448235            5.173041e+05  \n",
       "std     22225.401902            2.034329e+06  \n",
       "min         0.000000            0.000000e+00  \n",
       "25%       596.250000            3.746350e+04  \n",
       "50%      1496.500000            1.061975e+05  \n",
       "75%      3989.500000            2.980605e+05  \n",
       "max    771519.000000            5.998834e+07  \n",
       "\n",
       "[8 rows x 348 columns]"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "JHU_case_2020_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "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>Lat</th>\n",
       "      <th>Long_</th>\n",
       "      <th>1/22/20</th>\n",
       "      <th>1/1/21</th>\n",
       "      <th>1/2/21</th>\n",
       "      <th>1/3/21</th>\n",
       "      <th>1/4/21</th>\n",
       "      <th>1/5/21</th>\n",
       "      <th>1/6/21</th>\n",
       "      <th>1/7/21</th>\n",
       "      <th>...</th>\n",
       "      <th>12/23/21</th>\n",
       "      <th>12/24/21</th>\n",
       "      <th>12/25/21</th>\n",
       "      <th>12/26/21</th>\n",
       "      <th>12/27/21</th>\n",
       "      <th>12/28/21</th>\n",
       "      <th>12/29/21</th>\n",
       "      <th>12/30/21</th>\n",
       "      <th>12/31/21</th>\n",
       "      <th>Total COVID case count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>3.342000e+03</td>\n",
       "      <td>3.342000e+03</td>\n",
       "      <td>3.342000e+03</td>\n",
       "      <td>3.342000e+03</td>\n",
       "      <td>3.342000e+03</td>\n",
       "      <td>3.342000e+03</td>\n",
       "      <td>3.342000e+03</td>\n",
       "      <td>3.342000e+03</td>\n",
       "      <td>3.342000e+03</td>\n",
       "      <td>3.342000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>36.721617</td>\n",
       "      <td>-88.642045</td>\n",
       "      <td>0.000299</td>\n",
       "      <td>6102.580790</td>\n",
       "      <td>6184.103531</td>\n",
       "      <td>6244.894674</td>\n",
       "      <td>6300.830640</td>\n",
       "      <td>6370.272890</td>\n",
       "      <td>6448.033513</td>\n",
       "      <td>6533.315978</td>\n",
       "      <td>...</td>\n",
       "      <td>1.552518e+04</td>\n",
       "      <td>1.559945e+04</td>\n",
       "      <td>1.562587e+04</td>\n",
       "      <td>1.568090e+04</td>\n",
       "      <td>1.583233e+04</td>\n",
       "      <td>1.594367e+04</td>\n",
       "      <td>1.609670e+04</td>\n",
       "      <td>1.627548e+04</td>\n",
       "      <td>1.642866e+04</td>\n",
       "      <td>1.891348e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>9.079322</td>\n",
       "      <td>21.776287</td>\n",
       "      <td>0.017298</td>\n",
       "      <td>22543.840882</td>\n",
       "      <td>22907.374726</td>\n",
       "      <td>23196.040013</td>\n",
       "      <td>23458.242737</td>\n",
       "      <td>23732.473352</td>\n",
       "      <td>24030.894934</td>\n",
       "      <td>24427.049577</td>\n",
       "      <td>...</td>\n",
       "      <td>5.061685e+04</td>\n",
       "      <td>5.114409e+04</td>\n",
       "      <td>5.120485e+04</td>\n",
       "      <td>5.156810e+04</td>\n",
       "      <td>5.206336e+04</td>\n",
       "      <td>5.240796e+04</td>\n",
       "      <td>5.289675e+04</td>\n",
       "      <td>5.349858e+04</td>\n",
       "      <td>5.460546e+04</td>\n",
       "      <td>6.201859e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>-14.271000</td>\n",
       "      <td>-174.159600</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>33.896803</td>\n",
       "      <td>-97.803595</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>601.250000</td>\n",
       "      <td>611.000000</td>\n",
       "      <td>616.000000</td>\n",
       "      <td>620.000000</td>\n",
       "      <td>624.250000</td>\n",
       "      <td>633.000000</td>\n",
       "      <td>641.250000</td>\n",
       "      <td>...</td>\n",
       "      <td>1.624250e+03</td>\n",
       "      <td>1.624250e+03</td>\n",
       "      <td>1.627000e+03</td>\n",
       "      <td>1.627000e+03</td>\n",
       "      <td>1.643250e+03</td>\n",
       "      <td>1.644000e+03</td>\n",
       "      <td>1.652750e+03</td>\n",
       "      <td>1.677500e+03</td>\n",
       "      <td>1.691500e+03</td>\n",
       "      <td>1.967550e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>38.005610</td>\n",
       "      <td>-89.488865</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1511.500000</td>\n",
       "      <td>1526.500000</td>\n",
       "      <td>1546.000000</td>\n",
       "      <td>1555.500000</td>\n",
       "      <td>1574.500000</td>\n",
       "      <td>1599.000000</td>\n",
       "      <td>1623.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>4.151000e+03</td>\n",
       "      <td>4.151000e+03</td>\n",
       "      <td>4.151000e+03</td>\n",
       "      <td>4.152500e+03</td>\n",
       "      <td>4.184000e+03</td>\n",
       "      <td>4.193000e+03</td>\n",
       "      <td>4.216500e+03</td>\n",
       "      <td>4.241500e+03</td>\n",
       "      <td>4.254000e+03</td>\n",
       "      <td>5.005150e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>41.579255</td>\n",
       "      <td>-82.313398</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4020.250000</td>\n",
       "      <td>4089.250000</td>\n",
       "      <td>4112.250000</td>\n",
       "      <td>4151.750000</td>\n",
       "      <td>4191.000000</td>\n",
       "      <td>4254.000000</td>\n",
       "      <td>4304.750000</td>\n",
       "      <td>...</td>\n",
       "      <td>1.080450e+04</td>\n",
       "      <td>1.081200e+04</td>\n",
       "      <td>1.081650e+04</td>\n",
       "      <td>1.082500e+04</td>\n",
       "      <td>1.092475e+04</td>\n",
       "      <td>1.098650e+04</td>\n",
       "      <td>1.104050e+04</td>\n",
       "      <td>1.113575e+04</td>\n",
       "      <td>1.121625e+04</td>\n",
       "      <td>1.307775e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>69.314792</td>\n",
       "      <td>145.673900</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>790582.000000</td>\n",
       "      <td>807185.000000</td>\n",
       "      <td>818698.000000</td>\n",
       "      <td>829549.000000</td>\n",
       "      <td>841392.000000</td>\n",
       "      <td>853440.000000</td>\n",
       "      <td>872204.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>1.585313e+06</td>\n",
       "      <td>1.595239e+06</td>\n",
       "      <td>1.595239e+06</td>\n",
       "      <td>1.616033e+06</td>\n",
       "      <td>1.623442e+06</td>\n",
       "      <td>1.632893e+06</td>\n",
       "      <td>1.649376e+06</td>\n",
       "      <td>1.669545e+06</td>\n",
       "      <td>1.696582e+06</td>\n",
       "      <td>1.937773e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 369 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Lat        Long_      1/22/20         1/1/21         1/2/21  \\\n",
       "count  3342.000000  3342.000000  3342.000000    3342.000000    3342.000000   \n",
       "mean     36.721617   -88.642045     0.000299    6102.580790    6184.103531   \n",
       "std       9.079322    21.776287     0.017298   22543.840882   22907.374726   \n",
       "min     -14.271000  -174.159600     0.000000       0.000000       0.000000   \n",
       "25%      33.896803   -97.803595     0.000000     601.250000     611.000000   \n",
       "50%      38.005610   -89.488865     0.000000    1511.500000    1526.500000   \n",
       "75%      41.579255   -82.313398     0.000000    4020.250000    4089.250000   \n",
       "max      69.314792   145.673900     1.000000  790582.000000  807185.000000   \n",
       "\n",
       "              1/3/21         1/4/21         1/5/21         1/6/21  \\\n",
       "count    3342.000000    3342.000000    3342.000000    3342.000000   \n",
       "mean     6244.894674    6300.830640    6370.272890    6448.033513   \n",
       "std     23196.040013   23458.242737   23732.473352   24030.894934   \n",
       "min         0.000000       0.000000       0.000000       0.000000   \n",
       "25%       616.000000     620.000000     624.250000     633.000000   \n",
       "50%      1546.000000    1555.500000    1574.500000    1599.000000   \n",
       "75%      4112.250000    4151.750000    4191.000000    4254.000000   \n",
       "max    818698.000000  829549.000000  841392.000000  853440.000000   \n",
       "\n",
       "              1/7/21  ...      12/23/21      12/24/21      12/25/21  \\\n",
       "count    3342.000000  ...  3.342000e+03  3.342000e+03  3.342000e+03   \n",
       "mean     6533.315978  ...  1.552518e+04  1.559945e+04  1.562587e+04   \n",
       "std     24427.049577  ...  5.061685e+04  5.114409e+04  5.120485e+04   \n",
       "min         0.000000  ...  0.000000e+00  0.000000e+00  0.000000e+00   \n",
       "25%       641.250000  ...  1.624250e+03  1.624250e+03  1.627000e+03   \n",
       "50%      1623.000000  ...  4.151000e+03  4.151000e+03  4.151000e+03   \n",
       "75%      4304.750000  ...  1.080450e+04  1.081200e+04  1.081650e+04   \n",
       "max    872204.000000  ...  1.585313e+06  1.595239e+06  1.595239e+06   \n",
       "\n",
       "           12/26/21      12/27/21      12/28/21      12/29/21      12/30/21  \\\n",
       "count  3.342000e+03  3.342000e+03  3.342000e+03  3.342000e+03  3.342000e+03   \n",
       "mean   1.568090e+04  1.583233e+04  1.594367e+04  1.609670e+04  1.627548e+04   \n",
       "std    5.156810e+04  5.206336e+04  5.240796e+04  5.289675e+04  5.349858e+04   \n",
       "min    0.000000e+00  0.000000e+00  0.000000e+00  0.000000e+00  0.000000e+00   \n",
       "25%    1.627000e+03  1.643250e+03  1.644000e+03  1.652750e+03  1.677500e+03   \n",
       "50%    4.152500e+03  4.184000e+03  4.193000e+03  4.216500e+03  4.241500e+03   \n",
       "75%    1.082500e+04  1.092475e+04  1.098650e+04  1.104050e+04  1.113575e+04   \n",
       "max    1.616033e+06  1.623442e+06  1.632893e+06  1.649376e+06  1.669545e+06   \n",
       "\n",
       "           12/31/21  Total COVID case count  \n",
       "count  3.342000e+03            3.342000e+03  \n",
       "mean   1.642866e+04            1.891348e+05  \n",
       "std    5.460546e+04            6.201859e+05  \n",
       "min    0.000000e+00            0.000000e+00  \n",
       "25%    1.691500e+03            1.967550e+04  \n",
       "50%    4.254000e+03            5.005150e+04  \n",
       "75%    1.121625e+04            1.307775e+05  \n",
       "max    1.696582e+06            1.937773e+07  \n",
       "\n",
       "[8 rows x 369 columns]"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "JHU_case_2021_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "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>Lat</th>\n",
       "      <th>Long_</th>\n",
       "      <th>1/22/20</th>\n",
       "      <th>1/23/20</th>\n",
       "      <th>1/24/20</th>\n",
       "      <th>1/25/20</th>\n",
       "      <th>1/26/20</th>\n",
       "      <th>1/27/20</th>\n",
       "      <th>1/28/20</th>\n",
       "      <th>1/29/20</th>\n",
       "      <th>...</th>\n",
       "      <th>12/25/20</th>\n",
       "      <th>12/26/20</th>\n",
       "      <th>12/27/20</th>\n",
       "      <th>12/28/20</th>\n",
       "      <th>12/29/20</th>\n",
       "      <th>12/30/20</th>\n",
       "      <th>12/31/20</th>\n",
       "      <th>1/1/21</th>\n",
       "      <th>1/1/22</th>\n",
       "      <th>Total COVID death count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>36.721617</td>\n",
       "      <td>-88.642045</td>\n",
       "      <td>0.000299</td>\n",
       "      <td>0.000299</td>\n",
       "      <td>0.000299</td>\n",
       "      <td>0.000299</td>\n",
       "      <td>0.000299</td>\n",
       "      <td>0.000299</td>\n",
       "      <td>0.000299</td>\n",
       "      <td>0.000299</td>\n",
       "      <td>...</td>\n",
       "      <td>100.129563</td>\n",
       "      <td>100.701676</td>\n",
       "      <td>101.129862</td>\n",
       "      <td>101.698085</td>\n",
       "      <td>102.767504</td>\n",
       "      <td>103.921305</td>\n",
       "      <td>104.893776</td>\n",
       "      <td>105.553561</td>\n",
       "      <td>247.161879</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>9.079322</td>\n",
       "      <td>21.776287</td>\n",
       "      <td>0.017298</td>\n",
       "      <td>0.017298</td>\n",
       "      <td>0.017298</td>\n",
       "      <td>0.017298</td>\n",
       "      <td>0.017298</td>\n",
       "      <td>0.017298</td>\n",
       "      <td>0.017298</td>\n",
       "      <td>0.017298</td>\n",
       "      <td>...</td>\n",
       "      <td>391.994132</td>\n",
       "      <td>393.933376</td>\n",
       "      <td>395.099487</td>\n",
       "      <td>396.640271</td>\n",
       "      <td>400.189349</td>\n",
       "      <td>404.040304</td>\n",
       "      <td>407.841012</td>\n",
       "      <td>410.501319</td>\n",
       "      <td>935.010371</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>-14.271000</td>\n",
       "      <td>-174.159600</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>33.896803</td>\n",
       "      <td>-97.803595</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>38.005610</td>\n",
       "      <td>-89.488865</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>72.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>41.579255</td>\n",
       "      <td>-82.313398</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>61.000000</td>\n",
       "      <td>61.750000</td>\n",
       "      <td>62.000000</td>\n",
       "      <td>62.000000</td>\n",
       "      <td>63.000000</td>\n",
       "      <td>64.000000</td>\n",
       "      <td>64.750000</td>\n",
       "      <td>65.000000</td>\n",
       "      <td>178.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>69.314792</td>\n",
       "      <td>145.673900</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>9305.000000</td>\n",
       "      <td>9441.000000</td>\n",
       "      <td>9485.000000</td>\n",
       "      <td>9564.000000</td>\n",
       "      <td>9806.000000</td>\n",
       "      <td>10068.000000</td>\n",
       "      <td>10359.000000</td>\n",
       "      <td>10552.000000</td>\n",
       "      <td>27637.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 350 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Lat        Long_      1/22/20      1/23/20      1/24/20  \\\n",
       "count  3342.000000  3342.000000  3342.000000  3342.000000  3342.000000   \n",
       "mean     36.721617   -88.642045     0.000299     0.000299     0.000299   \n",
       "std       9.079322    21.776287     0.017298     0.017298     0.017298   \n",
       "min     -14.271000  -174.159600     0.000000     0.000000     0.000000   \n",
       "25%      33.896803   -97.803595     0.000000     0.000000     0.000000   \n",
       "50%      38.005610   -89.488865     0.000000     0.000000     0.000000   \n",
       "75%      41.579255   -82.313398     0.000000     0.000000     0.000000   \n",
       "max      69.314792   145.673900     1.000000     1.000000     1.000000   \n",
       "\n",
       "           1/25/20      1/26/20      1/27/20      1/28/20      1/29/20  ...  \\\n",
       "count  3342.000000  3342.000000  3342.000000  3342.000000  3342.000000  ...   \n",
       "mean      0.000299     0.000299     0.000299     0.000299     0.000299  ...   \n",
       "std       0.017298     0.017298     0.017298     0.017298     0.017298  ...   \n",
       "min       0.000000     0.000000     0.000000     0.000000     0.000000  ...   \n",
       "25%       0.000000     0.000000     0.000000     0.000000     0.000000  ...   \n",
       "50%       0.000000     0.000000     0.000000     0.000000     0.000000  ...   \n",
       "75%       0.000000     0.000000     0.000000     0.000000     0.000000  ...   \n",
       "max       1.000000     1.000000     1.000000     1.000000     1.000000  ...   \n",
       "\n",
       "          12/25/20     12/26/20     12/27/20     12/28/20     12/29/20  \\\n",
       "count  3342.000000  3342.000000  3342.000000  3342.000000  3342.000000   \n",
       "mean    100.129563   100.701676   101.129862   101.698085   102.767504   \n",
       "std     391.994132   393.933376   395.099487   396.640271   400.189349   \n",
       "min       0.000000     0.000000     0.000000     0.000000     0.000000   \n",
       "25%       7.000000     8.000000     8.000000     8.000000     8.000000   \n",
       "50%      23.000000    24.000000    24.000000    24.000000    24.000000   \n",
       "75%      61.000000    61.750000    62.000000    62.000000    63.000000   \n",
       "max    9305.000000  9441.000000  9485.000000  9564.000000  9806.000000   \n",
       "\n",
       "           12/30/20      12/31/20        1/1/21        1/1/22  \\\n",
       "count   3342.000000   3342.000000   3342.000000   3342.000000   \n",
       "mean     103.921305    104.893776    105.553561    247.161879   \n",
       "std      404.040304    407.841012    410.501319    935.010371   \n",
       "min        0.000000      0.000000      0.000000      0.000000   \n",
       "25%        8.000000      8.000000      8.000000     27.000000   \n",
       "50%       25.000000     25.000000     25.000000     72.000000   \n",
       "75%       64.000000     64.750000     65.000000    178.000000   \n",
       "max    10068.000000  10359.000000  10552.000000  27637.000000   \n",
       "\n",
       "       Total COVID death count  \n",
       "count                   3342.0  \n",
       "mean                       0.0  \n",
       "std                        0.0  \n",
       "min                        0.0  \n",
       "25%                        0.0  \n",
       "50%                        0.0  \n",
       "75%                        0.0  \n",
       "max                        0.0  \n",
       "\n",
       "[8 rows x 350 columns]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "JHU_death_2020_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "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>Lat</th>\n",
       "      <th>Long_</th>\n",
       "      <th>1/22/20</th>\n",
       "      <th>1/1/21</th>\n",
       "      <th>1/2/21</th>\n",
       "      <th>1/3/21</th>\n",
       "      <th>1/4/21</th>\n",
       "      <th>1/5/21</th>\n",
       "      <th>1/6/21</th>\n",
       "      <th>1/7/21</th>\n",
       "      <th>...</th>\n",
       "      <th>12/24/21</th>\n",
       "      <th>12/25/21</th>\n",
       "      <th>12/26/21</th>\n",
       "      <th>12/27/21</th>\n",
       "      <th>12/28/21</th>\n",
       "      <th>12/29/21</th>\n",
       "      <th>12/30/21</th>\n",
       "      <th>12/31/21</th>\n",
       "      <th>1/1/22</th>\n",
       "      <th>Total COVID death count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3342.000000</td>\n",
       "      <td>3.342000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>36.721617</td>\n",
       "      <td>-88.642045</td>\n",
       "      <td>0.000299</td>\n",
       "      <td>105.553561</td>\n",
       "      <td>106.307899</td>\n",
       "      <td>106.734590</td>\n",
       "      <td>107.337822</td>\n",
       "      <td>108.406643</td>\n",
       "      <td>109.565829</td>\n",
       "      <td>110.762418</td>\n",
       "      <td>...</td>\n",
       "      <td>244.247756</td>\n",
       "      <td>244.320168</td>\n",
       "      <td>244.430880</td>\n",
       "      <td>244.983244</td>\n",
       "      <td>245.668761</td>\n",
       "      <td>246.377319</td>\n",
       "      <td>246.823160</td>\n",
       "      <td>247.041891</td>\n",
       "      <td>247.161879</td>\n",
       "      <td>8.406314e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>9.079322</td>\n",
       "      <td>21.776287</td>\n",
       "      <td>0.017298</td>\n",
       "      <td>410.501319</td>\n",
       "      <td>413.134698</td>\n",
       "      <td>414.746895</td>\n",
       "      <td>416.487608</td>\n",
       "      <td>420.015109</td>\n",
       "      <td>424.013203</td>\n",
       "      <td>428.262929</td>\n",
       "      <td>...</td>\n",
       "      <td>928.075867</td>\n",
       "      <td>928.274684</td>\n",
       "      <td>928.461975</td>\n",
       "      <td>929.725583</td>\n",
       "      <td>930.651079</td>\n",
       "      <td>932.254957</td>\n",
       "      <td>933.831795</td>\n",
       "      <td>934.583228</td>\n",
       "      <td>935.010371</td>\n",
       "      <td>3.318176e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>-14.271000</td>\n",
       "      <td>-174.159600</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>33.896803</td>\n",
       "      <td>-97.803595</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>7.299000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>38.005610</td>\n",
       "      <td>-89.488865</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>26.000000</td>\n",
       "      <td>26.000000</td>\n",
       "      <td>26.000000</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>70.000000</td>\n",
       "      <td>70.000000</td>\n",
       "      <td>70.000000</td>\n",
       "      <td>70.000000</td>\n",
       "      <td>71.000000</td>\n",
       "      <td>71.000000</td>\n",
       "      <td>71.000000</td>\n",
       "      <td>71.000000</td>\n",
       "      <td>72.000000</td>\n",
       "      <td>2.108250e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>41.579255</td>\n",
       "      <td>-82.313398</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>65.000000</td>\n",
       "      <td>66.000000</td>\n",
       "      <td>66.000000</td>\n",
       "      <td>66.000000</td>\n",
       "      <td>67.000000</td>\n",
       "      <td>68.000000</td>\n",
       "      <td>68.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>175.000000</td>\n",
       "      <td>175.000000</td>\n",
       "      <td>175.000000</td>\n",
       "      <td>176.000000</td>\n",
       "      <td>177.000000</td>\n",
       "      <td>177.000000</td>\n",
       "      <td>177.750000</td>\n",
       "      <td>178.000000</td>\n",
       "      <td>178.000000</td>\n",
       "      <td>5.167400e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>69.314792</td>\n",
       "      <td>145.673900</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>10552.000000</td>\n",
       "      <td>10688.000000</td>\n",
       "      <td>10773.000000</td>\n",
       "      <td>10852.000000</td>\n",
       "      <td>11089.000000</td>\n",
       "      <td>11349.000000</td>\n",
       "      <td>11554.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>27533.000000</td>\n",
       "      <td>27533.000000</td>\n",
       "      <td>27546.000000</td>\n",
       "      <td>27555.000000</td>\n",
       "      <td>27576.000000</td>\n",
       "      <td>27601.000000</td>\n",
       "      <td>27625.000000</td>\n",
       "      <td>27637.000000</td>\n",
       "      <td>27637.000000</td>\n",
       "      <td>1.031484e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 370 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Lat        Long_      1/22/20        1/1/21        1/2/21  \\\n",
       "count  3342.000000  3342.000000  3342.000000   3342.000000   3342.000000   \n",
       "mean     36.721617   -88.642045     0.000299    105.553561    106.307899   \n",
       "std       9.079322    21.776287     0.017298    410.501319    413.134698   \n",
       "min     -14.271000  -174.159600     0.000000      0.000000      0.000000   \n",
       "25%      33.896803   -97.803595     0.000000      8.000000      8.000000   \n",
       "50%      38.005610   -89.488865     0.000000     25.000000     25.000000   \n",
       "75%      41.579255   -82.313398     0.000000     65.000000     66.000000   \n",
       "max      69.314792   145.673900     1.000000  10552.000000  10688.000000   \n",
       "\n",
       "             1/3/21        1/4/21        1/5/21        1/6/21        1/7/21  \\\n",
       "count   3342.000000   3342.000000   3342.000000   3342.000000   3342.000000   \n",
       "mean     106.734590    107.337822    108.406643    109.565829    110.762418   \n",
       "std      414.746895    416.487608    420.015109    424.013203    428.262929   \n",
       "min        0.000000      0.000000      0.000000      0.000000      0.000000   \n",
       "25%        8.000000      8.000000      8.000000      9.000000      9.000000   \n",
       "50%       26.000000     26.000000     26.000000     27.000000     27.000000   \n",
       "75%       66.000000     66.000000     67.000000     68.000000     68.000000   \n",
       "max    10773.000000  10852.000000  11089.000000  11349.000000  11554.000000   \n",
       "\n",
       "       ...      12/24/21      12/25/21      12/26/21      12/27/21  \\\n",
       "count  ...   3342.000000   3342.000000   3342.000000   3342.000000   \n",
       "mean   ...    244.247756    244.320168    244.430880    244.983244   \n",
       "std    ...    928.075867    928.274684    928.461975    929.725583   \n",
       "min    ...      0.000000      0.000000      0.000000      0.000000   \n",
       "25%    ...     27.000000     27.000000     27.000000     27.000000   \n",
       "50%    ...     70.000000     70.000000     70.000000     70.000000   \n",
       "75%    ...    175.000000    175.000000    175.000000    176.000000   \n",
       "max    ...  27533.000000  27533.000000  27546.000000  27555.000000   \n",
       "\n",
       "           12/28/21      12/29/21      12/30/21      12/31/21        1/1/22  \\\n",
       "count   3342.000000   3342.000000   3342.000000   3342.000000   3342.000000   \n",
       "mean     245.668761    246.377319    246.823160    247.041891    247.161879   \n",
       "std      930.651079    932.254957    933.831795    934.583228    935.010371   \n",
       "min        0.000000      0.000000      0.000000      0.000000      0.000000   \n",
       "25%       27.000000     27.000000     27.000000     27.000000     27.000000   \n",
       "50%       71.000000     71.000000     71.000000     71.000000     72.000000   \n",
       "75%      177.000000    177.000000    177.750000    178.000000    178.000000   \n",
       "max    27576.000000  27601.000000  27625.000000  27637.000000  27637.000000   \n",
       "\n",
       "       Total COVID death count  \n",
       "count             3.342000e+03  \n",
       "mean              8.406314e+04  \n",
       "std               3.318176e+05  \n",
       "min               0.000000e+00  \n",
       "25%               7.299000e+03  \n",
       "50%               2.108250e+04  \n",
       "75%               5.167400e+04  \n",
       "max               1.031484e+07  \n",
       "\n",
       "[8 rows x 370 columns]"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "JHU_death_2021_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(JHU_case_2020_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Visualization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Getting AL daily COVID case count for 2020\n",
    "Alabama = JHU_case_2020_df.loc[JHU_case_2020_df['Province_State']=='Alabama']\n",
    "Alabama = Alabama.iloc[:,3:-1]\n",
    "Alabama_totals = Alabama.sum(axis=0)\n",
    "Alabama_totals.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# States by Regions according to the U.S. Census\n",
    "regions = {\n",
    "        # Northeast\n",
    "           \"Connecticut\": \"Northeast\",\n",
    "           \"Maine\": \"Northeast\",\n",
    "           \"Massachusetts\": \"Northeast\",\n",
    "           \"New Hampshire\": \"Northeast\",\n",
    "           \"Rhode Island\": \"Northeast\",\n",
    "           \"Vermont\": \"Northeast\",\n",
    "           \"New Jersey\": \"Northeast\",\n",
    "           \"New York\": \"Northeast\",\n",
    "           \"Pennsylvania\": \"Northeast\",\n",
    "\n",
    "        # Midwest  \n",
    "           \"Illinois\": \"Midwest\",\n",
    "           \"Indiana\": \"Midwest\",\n",
    "           \"Michigan\": \"Midwest\",\n",
    "           \"Ohio\": \"Midwest\",\n",
    "           \"Wisconsin\": \"Midwest\",\n",
    "           \"Iowa\": \"Midwest\",\n",
    "           \"Kansas\": \"Midwest\",\n",
    "           \"Minnesota\": \"Midwest\",\n",
    "           \"Missouri\": \"Midwest\",\n",
    "           \"Nebraska\": \"Midwest\",\n",
    "           \"North Dakota\": \"Midwest\",\n",
    "           \"South Dakota\": \"Midwest\",\n",
    "\n",
    "        # South\n",
    "           \"District of Columbia\": \"South\",\n",
    "           \"Delaware\": \"South\",\n",
    "           \"Florida\": \"South\",\n",
    "           \"Georgia\": \"South\",\n",
    "           \"Maryland\": \"South\",\n",
    "           \"North Carolina\": \"South\",\n",
    "           \"South Carolina\": \"South\",\n",
    "           \"Virginia\": \"South\",\n",
    "           \"West Virginia\": \"South\",\n",
    "           \"Alabama\": \"South\",\n",
    "           \"Kentucky\": \"South\",\n",
    "           \"Mississippi\": \"South\",\n",
    "           \"Tennessee\": \"South\",\n",
    "           \"Arkansas\": \"South\",\n",
    "           \"Louisiana\": \"South\",\n",
    "           \"Oklahoma\": \"South\",\n",
    "           \"Texas\": \"South\",\n",
    "\n",
    "        # West\n",
    "           \"Arizona\": \"West\",\n",
    "           \"Colorado\": \"West\",\n",
    "           \"Idaho\": \"West\",\n",
    "           \"Montana\": \"West\",\n",
    "           \"Nevada\": \"West\",\n",
    "           \"New Mexico\": \"West\",\n",
    "           \"Utah\": \"West\",\n",
    "           \"Wyoming\": \"West\",\n",
    "           \"Alaska\": \"West\",\n",
    "           \"California\": \"West\",\n",
    "           \"Hawaii\": \"West\",\n",
    "           \"Oregon\": \"West\",\n",
    "           \"Washington\": \"West\",\n",
    "\n",
    "        # Territories\n",
    "            \"American Samoa\": \"Territory\",\n",
    "            \"Guam\": \"Territory\",\n",
    "            \"Northern Mariana Islands\": \"Territory\",\n",
    "            \"Puerto Rico\": \"Territory\",\n",
    "            \"Virgin Islands\": \"Territory\",\n",
    "            \"Diamond Princess\": \"Cruise Ship\",\n",
    "            \"Grand Princess\": \"Cruise Ship\",\n",
    "           }\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# States by Regions according to the U.S. Census Colors for Plots\n",
    "state_colors = {\n",
    "        # Northeast Colors\n",
    "           \"Connecticut\": \"darkorange\",\n",
    "           \"Maine\": \"limegreen\",\n",
    "           \"Massachusetts\": \"cornflowerblue\",\n",
    "           \"New Hampshire\": \"mediumorchid\",\n",
    "           \"Rhode Island\": \"teal\",\n",
    "           \"Vermont\": \"deepskyblue\",\n",
    "           \"New Jersey\": \"orangered\",\n",
    "           \"New York\": \"lawngreen\",\n",
    "           \"Pennsylvania\": \"mediumslateblue\",\n",
    "\n",
    "        # Midwest Colors\n",
    "           \"Illinois\": \"darkorange\",\n",
    "           \"Indiana\": \"limegreen\",\n",
    "           \"Michigan\": \"cornflowerblue\",\n",
    "           \"Ohio\": \"mediumorchid\",\n",
    "           \"Wisconsin\": \"teal\",\n",
    "           \"Iowa\": \"deepskyblue\",\n",
    "           \"Kansas\": \"orangered\",\n",
    "           \"Minnesota\": \"lawngreen\",\n",
    "           \"Missouri\": \"mediumslateblue\",\n",
    "           \"Nebraska\": \"maroon\",\n",
    "           \"North Dakota\": \"darkmagenta\",\n",
    "           \"South Dakota\": \"teal\",\n",
    "\n",
    "        # South Colors\n",
    "           \"District of Columbia\": \"darkorange\",\n",
    "           \"Delaware\": \"limegreen\",\n",
    "           \"Florida\": \"cornflowerblue\",\n",
    "           \"Georgia\": \"mediumorchid\",\n",
    "           \"Maryland\": \"teal\",\n",
    "           \"North Carolina\": \"deepskyblue\",\n",
    "           \"South Carolina\": \"orangered\",\n",
    "           \"Virginia\": \"lawngreen\",\n",
    "           \"West Virginia\": \"mediumslateblue\",\n",
    "           \"Alabama\": \"maroon\",\n",
    "           \"Kentucky\": \"darkmagenta\",\n",
    "           \"Mississippi\": \"teal\",\n",
    "           \"Tennessee\": \"midnightblue\",\n",
    "           \"Arkansas\": \"violet\",\n",
    "           \"Louisiana\": \"olive\",\n",
    "           \"Oklahoma\": \"coral\",\n",
    "           \"Texas\": \"mediumaquamarine\",\n",
    "\n",
    "        # West Colors\n",
    "           \"Arizona\": \"darkorange\",\n",
    "           \"Colorado\": \"limegreen\",\n",
    "           \"Idaho\": \"cornflowerblue\",\n",
    "           \"Montana\": \"mediumorchid\",\n",
    "           \"Nevada\": \"teal\",\n",
    "           \"New Mexico\": \"deepskyblue\",\n",
    "           \"Utah\": \"orangered\",\n",
    "           \"Wyoming\": \"lawngreen\",\n",
    "           \"Alaska\": \"mediumslateblue\",\n",
    "           \"California\": \"maroon\",\n",
    "           \"Hawaii\": \"darkmagenta\",\n",
    "           \"Oregon\": \"teal\",\n",
    "           \"Washington\": \"midnightblue\",\n",
    "\n",
    "        # Territories Colors\n",
    "            \"American Samoa\": \"darkorange\",\n",
    "            \"Guam\": \"limegreen\",\n",
    "            \"Northern Mariana Islands\": \"cornflowerblue\",\n",
    "            \"Puerto Rico\": \"mediumorchid\",\n",
    "            \"Virgin Islands\": \"teal\",\n",
    "            \"Diamond Princess\": \"deepskyblue\",\n",
    "            \"Grand Princess\": \"orangered\",\n",
    "           }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating Array of States for 2020 Cases\n",
    "import numpy as np\n",
    "states = np.unique(JHU_case_2020_df['Province_State'].to_numpy())\n",
    "states\n",
    "\n",
    "state_case_totals = {}\n",
    "\n",
    "# Using AL code to create for loop for all states\n",
    "for state in states:\n",
    "    case_state_df = JHU_case_2020_df.loc[JHU_case_2020_df['Province_State']==state]\n",
    "    case_state_df = case_state_df.iloc[:,3:-1]\n",
    "    state_case_totals[state] = case_state_df.sum(axis=0)\n",
    "\n",
    "\n",
    "Alabama = JHU_case_2020_df.loc[JHU_case_2020_df['Province_State']=='Alabama']\n",
    "Alabama = Alabama.iloc[:,3:-1]\n",
    "Alabama_totals = Alabama.sum(axis=0)\n",
    "\n",
    "Alabama_totals.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "plt.rcParams[\"figure.figsize\"] = (20,10)\n",
    "\n",
    "TITLE_FONTSIZE = 20\n",
    "AXIS_FONTSIZE = 14\n",
    "TICK_FONTSIZE = 12\n",
    "\n",
    "x = Alabama_totals.to_numpy()\n",
    "AL_fig, AL_ax = plt.subplots()\n",
    "\n",
    "AL_ax.plot(x, linewidth = 6.0, color = \"cornflowerblue\")\n",
    "AL_ax.set_xlabel(\"COVID cases in Alabama\", fontsize=AXIS_FONTSIZE)\n",
    "AL_ax.set_ylabel(\"Total Daily COVID case count\", fontsize=AXIS_FONTSIZE)\n",
    "AL_ax.set_title(\"2020 COVID-19 Daily Cases in Alabama\", fontsize=TITLE_FONTSIZE)\n",
    "AL_ax.set_yscale('linear')\n",
    "x_labels = ['0','1/22/20', '3/12/20', '5/1/20', '6/20/20', '8/9/20', '9/28/20', '11/17/20', '12/31/20']\n",
    "AL_ax.set_xticklabels(x_labels, fontsize=TICK_FONTSIZE)\n",
    "AL_ax.set_yticklabels(AL_ax.get_yticklabels(), fontsize=TICK_FONTSIZE)\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "\n",
    "LINE_WIDTH = 6.0\n",
    "TITLE_FONTSIZE = 20\n",
    "AXIS_FONTSIZE = 14\n",
    "TICK_FONTSIZE = 12\n",
    "\n",
    "NE_fig, NE_ax = plt.subplots()\n",
    "MW_fig, MW_ax = plt.subplots()\n",
    "SO_fig, SO_ax = plt.subplots()\n",
    "WE_fig, WE_ax = plt.subplots()\n",
    "TR_fig, TR_ax = plt.subplots()\n",
    "\n",
    "NE_legend = []\n",
    "MW_legend = []\n",
    "SO_legend = []\n",
    "WE_legend = []\n",
    "TR_legend = []\n",
    "\n",
    "for state in states:\n",
    "    state_data = state_case_totals[state]\n",
    "    x = state_data.to_numpy()\n",
    "    state_region = regions[state]\n",
    "    if state_region == 'Northeast':\n",
    "        NE_ax.plot(x, linewidth=LINE_WIDTH, color = state_colors[state])\n",
    "        NE_legend.append(state)\n",
    "    \n",
    "    elif state_region == 'Midwest':\n",
    "        MW_ax.plot(x, linewidth=LINE_WIDTH, color = state_colors[state])\n",
    "        MW_legend.append(state)\n",
    "    \n",
    "    elif state_region == 'South':\n",
    "        SO_ax.plot(x, linewidth=LINE_WIDTH, color = state_colors[state])\n",
    "        SO_legend.append(state)\n",
    "    \n",
    "    elif state_region == 'West':\n",
    "        WE_ax.plot(x, linewidth=LINE_WIDTH, color = state_colors[state])\n",
    "        WE_legend.append(state)\n",
    "\n",
    "    elif state_region == 'Territory':\n",
    "        TR_ax.plot(x, linewidth=LINE_WIDTH, color = state_colors[state])\n",
    "        TR_legend.append(state)\n",
    "\n",
    "#Northeast Plot Format\n",
    "NE_ax.set_xlabel(\"COVID-19 Cases in U.S. Northeast\", fontsize=AXIS_FONTSIZE)\n",
    "NE_ax.set_ylabel(\"Total Daily COVID-19 Case Count\", fontsize=AXIS_FONTSIZE)\n",
    "NE_ax.set_title(\"2020 COVID-19 Daily Cases in U.S. Northeast\", fontsize=TITLE_FONTSIZE)\n",
    "NE_ax.legend(NE_legend)\n",
    "NE_ax.set_yscale('linear')\n",
    "x_labels = ['0','1/22/20', '3/12/20', '5/1/20', '6/20/20', '8/9/20', '9/28/20', '11/17/20', '12/31/20']\n",
    "NE_ax.set_xticklabels(x_labels, fontsize =TICK_FONTSIZE)\n",
    "NE_ax.set_yticklabels(AL_ax.get_yticklabels(), fontsize =TICK_FONTSIZE)\n",
    "NE_fig.savefig('northeast_2020_cases.png')\n",
    "\n",
    "#Midwest Plot Format\n",
    "MW_ax.set_xlabel(\"COVID-19 Cases in U.S. Midwest\", fontsize=AXIS_FONTSIZE)\n",
    "MW_ax.set_ylabel(\"Total Daily COVID-19 Case Count\", fontsize=AXIS_FONTSIZE)\n",
    "MW_ax.set_title(\"2020 COVID-19 Daily Cases in U.S. Midwest\", fontsize=TITLE_FONTSIZE)\n",
    "MW_ax.legend(MW_legend)\n",
    "MW_ax.set_yscale('linear')\n",
    "x_labels = ['0','1/22/20', '3/12/20', '5/1/20', '6/20/20', '8/9/20', '9/28/20', '11/17/20', '12/31/20']\n",
    "MW_ax.set_xticklabels(x_labels, fontsize =TICK_FONTSIZE)\n",
    "MW_ax.set_yticklabels(AL_ax.get_yticklabels(), fontsize =TICK_FONTSIZE)\n",
    "MW_fig.savefig('midwest_2020_cases.png')\n",
    "\n",
    "#South Plot Format\n",
    "SO_ax.set_ylabel(\"Total Daily COVID-19 Case Count\", fontsize=AXIS_FONTSIZE)\n",
    "SO_ax.set_xlabel(\"COVID-19 Cases in U.S. South\", fontsize=AXIS_FONTSIZE)\n",
    "SO_ax.set_title(\"2020 COVID-19 Daily Cases in U.S. South\", fontsize=TITLE_FONTSIZE)\n",
    "SO_ax.legend(SO_legend)\n",
    "SO_ax.set_yscale('linear')\n",
    "x_labels = ['0','1/22/20', '3/12/20', '5/1/20', '6/20/20', '8/9/20', '9/28/20', '11/17/20', '12/31/20']\n",
    "SO_ax.set_xticklabels(x_labels, fontsize =TICK_FONTSIZE)\n",
    "SO_ax.set_yticklabels(AL_ax.get_yticklabels(), fontsize =TICK_FONTSIZE)\n",
    "SO_fig.savefig('south_2020_cases.png')\n",
    "\n",
    "#West Plot Format\n",
    "WE_ax.set_xlabel(\"COVID-19 Cases in U.S. West\", fontsize=AXIS_FONTSIZE)\n",
    "WE_ax.set_ylabel(\"Total Daily COVID-19 Case Count\", fontsize=AXIS_FONTSIZE)\n",
    "WE_ax.set_title(\"2020 COVID-19 Daily Cases in U.S. West\", fontsize=TITLE_FONTSIZE)\n",
    "WE_ax.legend(WE_legend)\n",
    "WE_ax.set_yscale('linear')\n",
    "x_labels = ['0','1/22/20', '3/12/20', '5/1/20', '6/20/20', '8/9/20', '9/28/20', '11/17/20', '12/31/20']\n",
    "WE_ax.set_xticklabels(x_labels, fontsize =TICK_FONTSIZE)\n",
    "WE_ax.set_yticklabels(AL_ax.get_yticklabels(), fontsize =TICK_FONTSIZE)\n",
    "WE_fig.savefig('west_2020_cases.png')\n",
    "\n",
    "#Territories Plot Format\n",
    "TR_ax.set_xlabel(\"COVID-19 Cases in U.S. Territories\", fontsize=AXIS_FONTSIZE)\n",
    "TR_ax.set_ylabel(\"Total Daily COVID-19 Case Count\", fontsize=AXIS_FONTSIZE)\n",
    "TR_ax.set_title(\"2020 COVID-19 Daily Cases in U.S. Territories\", fontsize=TITLE_FONTSIZE)\n",
    "TR_ax.legend(TR_legend)\n",
    "TR_ax.set_yscale('linear')\n",
    "x_labels = ['0','1/22/20', '3/12/20', '5/1/20', '6/20/20', '8/9/20', '9/28/20', '11/17/20', '12/31/20']\n",
    "TR_ax.set_xticklabels(x_labels, fontsize =TICK_FONTSIZE)\n",
    "TR_ax.set_yticklabels(AL_ax.get_yticklabels(), fontsize =TICK_FONTSIZE)\n",
    "TR_fig.savefig('territories_2020_cases.png')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating Array of States for 2021 Cases\n",
    "import numpy as np\n",
    "states = np.unique(JHU_case_2021_df['Province_State'].to_numpy())\n",
    "states\n",
    "\n",
    "state_case_totals = {}\n",
    "\n",
    "# Using AL code to create for loop for all states\n",
    "for state in states:\n",
    "    case_state_df = JHU_case_2021_df.loc[JHU_case_2021_df['Province_State']==state]\n",
    "    case_state_df = case_state_df.iloc[:,3:-1]\n",
    "    state_case_totals[state] = case_state_df.sum(axis=0)\n",
    "\n",
    "\n",
    "Alabama = JHU_case_2021_df.loc[JHU_case_2021_df['Province_State']=='Alabama']\n",
    "Alabama = Alabama.iloc[:,3:-1]\n",
    "Alabama_totals = Alabama.sum(axis=0)\n",
    "\n",
    "Alabama_totals.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "\n",
    "LINE_WIDTH = 6.0\n",
    "TITLE_FONTSIZE = 20\n",
    "AXIS_FONTSIZE = 14\n",
    "TICK_FONTSIZE = 12\n",
    "\n",
    "NE_fig, NE_ax = plt.subplots()\n",
    "MW_fig, MW_ax = plt.subplots()\n",
    "SO_fig, SO_ax = plt.subplots()\n",
    "WE_fig, WE_ax = plt.subplots()\n",
    "TR_fig, TR_ax = plt.subplots()\n",
    "\n",
    "NE_legend = []\n",
    "MW_legend = []\n",
    "SO_legend = []\n",
    "WE_legend = []\n",
    "TR_legend = []\n",
    "\n",
    "for state in states:\n",
    "    state_data = state_case_totals[state]\n",
    "    x = state_data.to_numpy()\n",
    "    state_region = regions[state]\n",
    "    if state_region == 'Northeast':\n",
    "        NE_ax.plot(x, linewidth=LINE_WIDTH, color = state_colors[state])\n",
    "        NE_legend.append(state)\n",
    "    \n",
    "    elif state_region == 'Midwest':\n",
    "        MW_ax.plot(x, linewidth=LINE_WIDTH, color = state_colors[state])\n",
    "        MW_legend.append(state)\n",
    "    \n",
    "    elif state_region == 'South':\n",
    "        SO_ax.plot(x, linewidth=LINE_WIDTH, color = state_colors[state])\n",
    "        SO_legend.append(state)\n",
    "    \n",
    "    elif state_region == 'West':\n",
    "        WE_ax.plot(x, linewidth=LINE_WIDTH, color = state_colors[state])\n",
    "        WE_legend.append(state)\n",
    "    elif state_region == 'Territory':\n",
    "        TR_ax.plot(x, linewidth=LINE_WIDTH, color = state_colors[state])\n",
    "        TR_legend.append(state)\n",
    "\n",
    "\n",
    "#Northeast Plot Format\n",
    "NE_ax.set_xlabel(\"COVID-19 Cases in U.S. Northeast\", fontsize=AXIS_FONTSIZE)\n",
    "NE_ax.set_ylabel(\"Total Daily COVID-19 Case Count\", fontsize=AXIS_FONTSIZE)\n",
    "NE_ax.set_title(\"2021 COVID-19 Daily Cases in U.S. Northeast\", fontsize=TITLE_FONTSIZE)\n",
    "NE_ax.legend(NE_legend)\n",
    "NE_ax.set_yscale('linear')\n",
    "x_labels = ['0','1/1/21', '2/20/21', '4/11/21', '5/31/21', '7/20/21', '9/8/21', '10/28/21', '12/17/21']\n",
    "NE_ax.set_xticklabels(x_labels, fontsize =TICK_FONTSIZE)\n",
    "NE_ax.set_yticklabels(AL_ax.get_yticklabels(), fontsize =TICK_FONTSIZE)\n",
    "NE_fig.savefig('northeast_2021_cases.png')\n",
    "\n",
    "#Midwest Plot Format\n",
    "MW_ax.set_xlabel(\"COVID-19 Cases in U.S. Midwest\", fontsize=AXIS_FONTSIZE)\n",
    "MW_ax.set_ylabel(\"Total Daily COVID-19 Case Count\", fontsize=AXIS_FONTSIZE)\n",
    "MW_ax.set_title(\"2021 COVID-19 Daily Cases in U.S. Midwest\", fontsize=TITLE_FONTSIZE)\n",
    "MW_ax.legend(MW_legend)\n",
    "MW_ax.set_yscale('linear')\n",
    "x_labels = ['0','1/1/21', '2/20/21', '4/11/21', '5/31/21', '7/20/21', '9/8/21', '10/28/21', '12/17/21']\n",
    "MW_ax.set_xticklabels(x_labels, fontsize =TICK_FONTSIZE)\n",
    "MW_ax.set_yticklabels(AL_ax.get_yticklabels(), fontsize =TICK_FONTSIZE)\n",
    "MW_fig.savefig('midwest_2021_cases.png')\n",
    "\n",
    "#South Plot Format\n",
    "SO_ax.set_ylabel(\"Total Daily COVID-19 Case Count\", fontsize=AXIS_FONTSIZE)\n",
    "SO_ax.set_xlabel(\"COVID-19 Cases in U.S. South\", fontsize=AXIS_FONTSIZE)\n",
    "SO_ax.set_title(\"2021 COVID-19 Daily Cases in U.S. South\", fontsize=TITLE_FONTSIZE)\n",
    "SO_ax.legend(SO_legend)\n",
    "SO_ax.set_yscale('linear')\n",
    "x_labels = ['0','1/1/21', '2/20/21', '4/11/21', '5/31/21', '7/20/21', '9/8/21', '10/28/21', '12/17/21']\n",
    "SO_ax.set_xticklabels(x_labels, fontsize =TICK_FONTSIZE)\n",
    "SO_ax.set_yticklabels(AL_ax.get_yticklabels(), fontsize =TICK_FONTSIZE)\n",
    "SO_fig.savefig('south_2021_cases.png')\n",
    "\n",
    "#West Plot Format\n",
    "WE_ax.set_xlabel(\"COVID-19 Cases in U.S. West\", fontsize=AXIS_FONTSIZE)\n",
    "WE_ax.set_ylabel(\"Total Daily COVID-19 Case Count\", fontsize=AXIS_FONTSIZE)\n",
    "WE_ax.set_title(\"2021 COVID-19 Daily Cases in U.S. West\", fontsize=TITLE_FONTSIZE)\n",
    "WE_ax.legend(WE_legend)\n",
    "WE_ax.set_yscale('linear')\n",
    "x_labels = ['0','1/1/21', '2/20/21', '4/11/21', '5/31/21', '7/20/21', '9/8/21', '10/28/21', '12/17/21']\n",
    "WE_ax.set_xticklabels(x_labels, fontsize =TICK_FONTSIZE)\n",
    "WE_ax.set_yticklabels(AL_ax.get_yticklabels(), fontsize =TICK_FONTSIZE)\n",
    "WE_fig.savefig('west_2021_cases.png')\n",
    "\n",
    "#Territories Plot Format\n",
    "TR_ax.set_xlabel(\"COVID-19 Cases in U.S. Territories\", fontsize=AXIS_FONTSIZE)\n",
    "TR_ax.set_ylabel(\"Total Daily COVID-19 Case Count\", fontsize=AXIS_FONTSIZE)\n",
    "TR_ax.set_title(\"2021 COVID-19 Daily Cases in U.S. Territories\", fontsize=TITLE_FONTSIZE)\n",
    "TR_ax.legend(TR_legend)\n",
    "TR_ax.set_yscale('linear')\n",
    "x_labels = ['0','1/1/21', '2/20/21', '4/11/21', '5/31/21', '7/20/21', '9/8/21', '10/28/21', '12/17/21']\n",
    "TR_ax.set_xticklabels(x_labels, fontsize =TICK_FONTSIZE)\n",
    "TR_ax.set_yticklabels(AL_ax.get_yticklabels(), fontsize =TICK_FONTSIZE)\n",
    "TR_fig.savefig('territories_2021_cases.png')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Analysis: SVM and RFR"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Support Vector Machines (SVM)__\n",
    "\n",
    "__Variables__: \n",
    "- beds\n",
    "    - general medical and surgical care (adult) beds (GENBD)\n",
    "    - general medical and surgical care (pediatric) beds (PEDBD)\n",
    "    - obstetric care beds (OBBD)\n",
    "    - medical/surgical intensive care beds (MSICBD)\n",
    "    - cardiac intensive care beds (CICBD)\n",
    "    - neonatal intensive care beds (NICBD)\n",
    "    - neonatal intermediate care beds (NINTBD)\n",
    "    - pediatric intensive care beds (PEDICBD)\n",
    "    - burn card beds (BRNBD)\n",
    "    - beds - other special care (SPCICBD)\n",
    "    - other intensive care beds (OTHICBD)\n",
    "    - physical rehabilitation care beds (REHABBD)\n",
    "    - alcohol/drug abuse or dependency inpatient care beds (ALCHBD)\n",
    "    - psychiatric care beds (PSYBD)\n",
    "    - skilled nursing care beds (SNBD88)\n",
    "    - intermediate nursing care beds (ICFBD88)\n",
    "    - acute long-term care beds (ACULTBD)\n",
    "    - other long-term care beds (OTHLBD94)\n",
    "    - other care beds (OTHBD94) \n",
    "- employee vacancies\n",
    "    - total facility personnel (VTOTL)\n",
    "    - total nursing home type unit/facility personnel (VTNH)\n",
    "    - physican and dentists (VMD)\n",
    "    - medical and dental residents/interns (VRES)\n",
    "    - pharmacists (licensed) (VPHR)\n",
    "    - pharmacy technicians (VPHT)\n",
    "    - registered nurses (VRN)\n",
    "    - licensed practical (vocational) nurses (VLPN)\n",
    "    - nursing home type unit/facility registered nurses (VRNH)\n",
    "    - nursing assistive personnel (VAST)\n",
    "    - laboratory technicians (VLAB)\n",
    "    - radiology technicians (VRAD)\n",
    "    - respiratory therapists (VRSP)\n",
    "    - all other personnel (VOTHl)\n",
    "    - other trainees (VTTRN)\n",
    "- COVID-19 case count\n",
    "    - 2020 cases: JHU_case_2020_df\n",
    "    - 2021 cases: JHU_case_2021_df\n",
    "    - Imaginary case data: JHU_imag_case_df\n",
    "- COVID-19 death count\n",
    "    - 2020 deaths: JHU_death_2020_df\n",
    "    - 2021 deaths: JHU_death_2021_df\n",
    "    - Imaginary death data: JHU_imag_death_df\n",
    "\n",
    "__Training__ (when hospitals had \"enough\" beds and staff): 2018 and 2019\n",
    "- augmented with imaginary COVID-19 data in 2018 and 2019 so that the model will have an idea for what to do with COVID-19 cases in 2020\n",
    "    - will augment 2018 and 2019 data by imputing some 2020 COVID-19 case data into new columns in the 2018 and 2019 dataframes \n",
    "\n",
    "__Test__ (when hospitals didn't have \"enough\" beds and staff): 2020\n",
    "\n",
    "__Difficulty Rating__: Not Very Difficult"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>YEAR</th>\n",
       "      <th>STCD</th>\n",
       "      <th>HOSPBD</th>\n",
       "      <th>BDTOT</th>\n",
       "      <th>ADMTOT</th>\n",
       "      <th>IPDTOT</th>\n",
       "      <th>SUROPIP</th>\n",
       "      <th>SUROPOP</th>\n",
       "      <th>SUROPTOT</th>\n",
       "      <th>OPRA</th>\n",
       "      <th>...</th>\n",
       "      <th>VRAD</th>\n",
       "      <th>VLAB</th>\n",
       "      <th>VPHR</th>\n",
       "      <th>VPHT</th>\n",
       "      <th>VRSP</th>\n",
       "      <th>VOTHl</th>\n",
       "      <th>VTOTL</th>\n",
       "      <th>VRNH</th>\n",
       "      <th>VTNH</th>\n",
       "      <th>TETOT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018</td>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>654</td>\n",
       "      <td>1384</td>\n",
       "      <td>278</td>\n",
       "      <td>984</td>\n",
       "      <td>1262</td>\n",
       "      <td>5.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019</td>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>654</td>\n",
       "      <td>1384</td>\n",
       "      <td>278</td>\n",
       "      <td>984</td>\n",
       "      <td>1262</td>\n",
       "      <td>5.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020</td>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>495</td>\n",
       "      <td>1054</td>\n",
       "      <td>220</td>\n",
       "      <td>946</td>\n",
       "      <td>1166</td>\n",
       "      <td>5.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "      <td>150</td>\n",
       "      <td>150</td>\n",
       "      <td>4336</td>\n",
       "      <td>24838</td>\n",
       "      <td>965</td>\n",
       "      <td>2032</td>\n",
       "      <td>2997</td>\n",
       "      <td>5.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019</td>\n",
       "      <td>4</td>\n",
       "      <td>150</td>\n",
       "      <td>150</td>\n",
       "      <td>4339</td>\n",
       "      <td>24851</td>\n",
       "      <td>966</td>\n",
       "      <td>2034</td>\n",
       "      <td>3000</td>\n",
       "      <td>5.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 35 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   YEAR  STCD  HOSPBD  BDTOT  ADMTOT  IPDTOT  SUROPIP  SUROPOP  SUROPTOT  \\\n",
       "0  2018     3      14     14     654    1384      278      984      1262   \n",
       "1  2019     3      14     14     654    1384      278      984      1262   \n",
       "2  2020     3      14     14     495    1054      220      946      1166   \n",
       "3  2018     4     150    150    4336   24838      965     2032      2997   \n",
       "4  2019     4     150    150    4339   24851      966     2034      3000   \n",
       "\n",
       "   OPRA  ...  VRAD  VLAB  VPHR  VPHT  VRSP  VOTHl  VTOTL  VRNH  VTNH  TETOT  \n",
       "0   5.0  ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0   12.0  \n",
       "1   5.0  ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0   12.0  \n",
       "2   5.0  ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0   12.0  \n",
       "3   5.0  ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0   12.0  \n",
       "4   5.0  ...   0.0   0.0   0.0   0.0   0.0   10.0   29.0   0.0   0.0   12.0  \n",
       "\n",
       "[5 rows x 35 columns]"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Features\n",
    "# X = employee vacancies, COVID-19 case and death count \n",
    "bed_cols = ['GENBD', 'PEDBD', 'OBBD', 'MSICBD', \n",
    "                'CICBD', 'NICBD', 'NINTBD', 'PEDICBD', \n",
    "                'BRNBD', 'SPCICBD', 'OTHICBD', 'REHABBD', \n",
    "                'ALCHBD', 'PSYBD', 'SNBD88', 'ICFBD88', \n",
    "                'ACULTBD', 'OTHLBD94', 'OTHBD94']\n",
    "\n",
    "X = pd.concat([AHA_df.iloc[:,0:2], AHA_df.iloc[:,21:]], axis=1)#.to_numpy()\n",
    "\n",
    "# Target\n",
    "# Y = beds \n",
    "y = AHA_df[bed_cols]\n",
    "\n",
    "X.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>YEAR</th>\n",
       "      <th>STCD</th>\n",
       "      <th>HOSPBD</th>\n",
       "      <th>BDTOT</th>\n",
       "      <th>ADMTOT</th>\n",
       "      <th>IPDTOT</th>\n",
       "      <th>SUROPIP</th>\n",
       "      <th>SUROPOP</th>\n",
       "      <th>SUROPTOT</th>\n",
       "      <th>OPRA</th>\n",
       "      <th>...</th>\n",
       "      <th>VRAD</th>\n",
       "      <th>VLAB</th>\n",
       "      <th>VPHR</th>\n",
       "      <th>VPHT</th>\n",
       "      <th>VRSP</th>\n",
       "      <th>VOTHl</th>\n",
       "      <th>VTOTL</th>\n",
       "      <th>VRNH</th>\n",
       "      <th>VTNH</th>\n",
       "      <th>TETOT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>18545.000000</td>\n",
       "      <td>18545.000000</td>\n",
       "      <td>18545.000000</td>\n",
       "      <td>18545.000000</td>\n",
       "      <td>18545.000000</td>\n",
       "      <td>18545.000000</td>\n",
       "      <td>18545.000000</td>\n",
       "      <td>18545.000000</td>\n",
       "      <td>18545.000000</td>\n",
       "      <td>18545.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>18545.000000</td>\n",
       "      <td>18545.000000</td>\n",
       "      <td>18545.000000</td>\n",
       "      <td>18545.000000</td>\n",
       "      <td>18545.000000</td>\n",
       "      <td>18545.000000</td>\n",
       "      <td>18545.000000</td>\n",
       "      <td>18545.000000</td>\n",
       "      <td>18545.000000</td>\n",
       "      <td>18545.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2018.997142</td>\n",
       "      <td>55.821353</td>\n",
       "      <td>150.700836</td>\n",
       "      <td>150.700836</td>\n",
       "      <td>5769.810785</td>\n",
       "      <td>35831.545322</td>\n",
       "      <td>1462.655594</td>\n",
       "      <td>3125.525155</td>\n",
       "      <td>4588.180750</td>\n",
       "      <td>7.430035</td>\n",
       "      <td>...</td>\n",
       "      <td>1.070801</td>\n",
       "      <td>1.101483</td>\n",
       "      <td>0.344567</td>\n",
       "      <td>0.512968</td>\n",
       "      <td>0.686600</td>\n",
       "      <td>24.308061</td>\n",
       "      <td>60.901375</td>\n",
       "      <td>0.082610</td>\n",
       "      <td>0.413642</td>\n",
       "      <td>60.671286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.817163</td>\n",
       "      <td>23.662894</td>\n",
       "      <td>193.266472</td>\n",
       "      <td>193.266472</td>\n",
       "      <td>9051.597945</td>\n",
       "      <td>53293.382626</td>\n",
       "      <td>2816.224702</td>\n",
       "      <td>5376.774469</td>\n",
       "      <td>7861.889406</td>\n",
       "      <td>10.308579</td>\n",
       "      <td>...</td>\n",
       "      <td>4.197598</td>\n",
       "      <td>5.034057</td>\n",
       "      <td>2.043691</td>\n",
       "      <td>2.547575</td>\n",
       "      <td>2.600799</td>\n",
       "      <td>72.767459</td>\n",
       "      <td>147.061098</td>\n",
       "      <td>0.716706</td>\n",
       "      <td>3.335416</td>\n",
       "      <td>210.559197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2018.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2018.000000</td>\n",
       "      <td>39.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>569.000000</td>\n",
       "      <td>4696.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>140.000000</td>\n",
       "      <td>164.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>9.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2019.000000</td>\n",
       "      <td>54.000000</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>1927.000000</td>\n",
       "      <td>16702.000000</td>\n",
       "      <td>337.000000</td>\n",
       "      <td>1560.000000</td>\n",
       "      <td>1969.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>12.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2020.000000</td>\n",
       "      <td>74.000000</td>\n",
       "      <td>195.000000</td>\n",
       "      <td>195.000000</td>\n",
       "      <td>7331.000000</td>\n",
       "      <td>44726.000000</td>\n",
       "      <td>1639.000000</td>\n",
       "      <td>3889.000000</td>\n",
       "      <td>5747.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>15.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2020.000000</td>\n",
       "      <td>95.000000</td>\n",
       "      <td>3890.000000</td>\n",
       "      <td>3890.000000</td>\n",
       "      <td>146072.000000</td>\n",
       "      <td>775202.000000</td>\n",
       "      <td>43562.000000</td>\n",
       "      <td>138765.000000</td>\n",
       "      <td>171020.000000</td>\n",
       "      <td>230.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>184.000000</td>\n",
       "      <td>183.000000</td>\n",
       "      <td>115.000000</td>\n",
       "      <td>81.000000</td>\n",
       "      <td>86.000000</td>\n",
       "      <td>2067.000000</td>\n",
       "      <td>3503.000000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>114.000000</td>\n",
       "      <td>5562.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 35 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               YEAR          STCD        HOSPBD         BDTOT         ADMTOT  \\\n",
       "count  18545.000000  18545.000000  18545.000000  18545.000000   18545.000000   \n",
       "mean    2018.997142     55.821353    150.700836    150.700836    5769.810785   \n",
       "std        0.817163     23.662894    193.266472    193.266472    9051.597945   \n",
       "min     2018.000000      3.000000      2.000000      2.000000       2.000000   \n",
       "25%     2018.000000     39.000000     30.000000     30.000000     569.000000   \n",
       "50%     2019.000000     54.000000     80.000000     80.000000    1927.000000   \n",
       "75%     2020.000000     74.000000    195.000000    195.000000    7331.000000   \n",
       "max     2020.000000     95.000000   3890.000000   3890.000000  146072.000000   \n",
       "\n",
       "              IPDTOT       SUROPIP        SUROPOP       SUROPTOT  \\\n",
       "count   18545.000000  18545.000000   18545.000000   18545.000000   \n",
       "mean    35831.545322   1462.655594    3125.525155    4588.180750   \n",
       "std     53293.382626   2816.224702    5376.774469    7861.889406   \n",
       "min         6.000000      0.000000       0.000000       0.000000   \n",
       "25%      4696.000000      2.000000     140.000000     164.000000   \n",
       "50%     16702.000000    337.000000    1560.000000    1969.000000   \n",
       "75%     44726.000000   1639.000000    3889.000000    5747.000000   \n",
       "max    775202.000000  43562.000000  138765.000000  171020.000000   \n",
       "\n",
       "               OPRA  ...          VRAD          VLAB          VPHR  \\\n",
       "count  18545.000000  ...  18545.000000  18545.000000  18545.000000   \n",
       "mean       7.430035  ...      1.070801      1.101483      0.344567   \n",
       "std       10.308579  ...      4.197598      5.034057      2.043691   \n",
       "min        0.000000  ...      0.000000      0.000000      0.000000   \n",
       "25%        3.000000  ...      0.000000      0.000000      0.000000   \n",
       "50%        5.000000  ...      0.000000      0.000000      0.000000   \n",
       "75%        7.000000  ...      0.000000      0.000000      0.000000   \n",
       "max      230.000000  ...    184.000000    183.000000    115.000000   \n",
       "\n",
       "               VPHT          VRSP         VOTHl         VTOTL          VRNH  \\\n",
       "count  18545.000000  18545.000000  18545.000000  18545.000000  18545.000000   \n",
       "mean       0.512968      0.686600     24.308061     60.901375      0.082610   \n",
       "std        2.547575      2.600799     72.767459    147.061098      0.716706   \n",
       "min        0.000000      0.000000      0.000000      0.000000      0.000000   \n",
       "25%        0.000000      0.000000     10.000000     29.000000      0.000000   \n",
       "50%        0.000000      0.000000     10.000000     29.000000      0.000000   \n",
       "75%        0.000000      0.000000     10.000000     29.000000      0.000000   \n",
       "max       81.000000     86.000000   2067.000000   3503.000000     24.000000   \n",
       "\n",
       "               VTNH         TETOT  \n",
       "count  18545.000000  18545.000000  \n",
       "mean       0.413642     60.671286  \n",
       "std        3.335416    210.559197  \n",
       "min        0.000000      0.000000  \n",
       "25%        0.000000      9.000000  \n",
       "50%        0.000000     12.000000  \n",
       "75%        0.000000     15.000000  \n",
       "max      114.000000   5562.000000  \n",
       "\n",
       "[8 rows x 35 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(14836, 35)"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Splitting dataset into Training and Test\n",
    "from sklearn.model_selection import train_test_split\n",
    "X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.20, random_state=90)\n",
    "X_train.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/anaconda3/envs/compsoc/lib/python3.8/site-packages/sklearn/svm/_base.py:1225: ConvergenceWarning: Liblinear failed to converge, increase the number of iterations.\n",
      "  warnings.warn(\n",
      "/opt/anaconda3/envs/compsoc/lib/python3.8/site-packages/sklearn/svm/_base.py:1225: ConvergenceWarning: Liblinear failed to converge, increase the number of iterations.\n",
      "  warnings.warn(\n",
      "/opt/anaconda3/envs/compsoc/lib/python3.8/site-packages/sklearn/svm/_base.py:1225: ConvergenceWarning: Liblinear failed to converge, increase the number of iterations.\n",
      "  warnings.warn(\n",
      "/opt/anaconda3/envs/compsoc/lib/python3.8/site-packages/sklearn/svm/_base.py:1225: ConvergenceWarning: Liblinear failed to converge, increase the number of iterations.\n",
      "  warnings.warn(\n",
      "/opt/anaconda3/envs/compsoc/lib/python3.8/site-packages/sklearn/svm/_base.py:1225: ConvergenceWarning: Liblinear failed to converge, increase the number of iterations.\n",
      "  warnings.warn(\n",
      "/opt/anaconda3/envs/compsoc/lib/python3.8/site-packages/sklearn/svm/_base.py:1225: ConvergenceWarning: Liblinear failed to converge, increase the number of iterations.\n",
      "  warnings.warn(\n",
      "/opt/anaconda3/envs/compsoc/lib/python3.8/site-packages/sklearn/svm/_base.py:1225: ConvergenceWarning: Liblinear failed to converge, increase the number of iterations.\n",
      "  warnings.warn(\n",
      "/opt/anaconda3/envs/compsoc/lib/python3.8/site-packages/sklearn/svm/_base.py:1225: ConvergenceWarning: Liblinear failed to converge, increase the number of iterations.\n",
      "  warnings.warn(\n",
      "/opt/anaconda3/envs/compsoc/lib/python3.8/site-packages/sklearn/svm/_base.py:1225: ConvergenceWarning: Liblinear failed to converge, increase the number of iterations.\n",
      "  warnings.warn(\n",
      "/opt/anaconda3/envs/compsoc/lib/python3.8/site-packages/sklearn/svm/_base.py:1225: ConvergenceWarning: Liblinear failed to converge, increase the number of iterations.\n",
      "  warnings.warn(\n",
      "/opt/anaconda3/envs/compsoc/lib/python3.8/site-packages/sklearn/svm/_base.py:1225: ConvergenceWarning: Liblinear failed to converge, increase the number of iterations.\n",
      "  warnings.warn(\n",
      "/opt/anaconda3/envs/compsoc/lib/python3.8/site-packages/sklearn/svm/_base.py:1225: ConvergenceWarning: Liblinear failed to converge, increase the number of iterations.\n",
      "  warnings.warn(\n",
      "/opt/anaconda3/envs/compsoc/lib/python3.8/site-packages/sklearn/svm/_base.py:1225: ConvergenceWarning: Liblinear failed to converge, increase the number of iterations.\n",
      "  warnings.warn(\n",
      "/opt/anaconda3/envs/compsoc/lib/python3.8/site-packages/sklearn/svm/_base.py:1225: ConvergenceWarning: Liblinear failed to converge, increase the number of iterations.\n",
      "  warnings.warn(\n",
      "/opt/anaconda3/envs/compsoc/lib/python3.8/site-packages/sklearn/svm/_base.py:1225: ConvergenceWarning: Liblinear failed to converge, increase the number of iterations.\n",
      "  warnings.warn(\n",
      "/opt/anaconda3/envs/compsoc/lib/python3.8/site-packages/sklearn/svm/_base.py:1225: ConvergenceWarning: Liblinear failed to converge, increase the number of iterations.\n",
      "  warnings.warn(\n",
      "/opt/anaconda3/envs/compsoc/lib/python3.8/site-packages/sklearn/svm/_base.py:1225: ConvergenceWarning: Liblinear failed to converge, increase the number of iterations.\n",
      "  warnings.warn(\n",
      "/opt/anaconda3/envs/compsoc/lib/python3.8/site-packages/sklearn/svm/_base.py:1225: ConvergenceWarning: Liblinear failed to converge, increase the number of iterations.\n",
      "  warnings.warn(\n",
      "/opt/anaconda3/envs/compsoc/lib/python3.8/site-packages/sklearn/svm/_base.py:1225: ConvergenceWarning: Liblinear failed to converge, increase the number of iterations.\n",
      "  warnings.warn(\n"
     ]
    }
   ],
   "source": [
    "# Support Vector Regressor \n",
    "from sklearn import svm\n",
    "\n",
    "models = {}\n",
    "x_train = X_train.to_numpy()\n",
    "x_test = X_test.to_numpy()\n",
    "\n",
    "for column, current_y_train in y_train.items():\n",
    "\n",
    "    model = svm.LinearSVR()\n",
    "    current_y_test = y_test[column].to_numpy()\n",
    "\n",
    "    model.fit(x_train, current_y_train.to_numpy())\n",
    "    y_hat = model.predict(x_test)\n",
    "    test_accuracy = model.score(x_test, current_y_test)\n",
    "    models[column] = (model, y_hat, test_accuracy)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Support Vector Regressor Results\n",
    " Model failed to converge."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "19it [03:51, 12.18s/it]\n"
     ]
    }
   ],
   "source": [
    "from tqdm import tqdm \n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "\n",
    "models = {}\n",
    "x_train = X_train.to_numpy()\n",
    "x_test = X_test.to_numpy()\n",
    "\n",
    "for column, current_y_train in tqdm(y_train.items()):\n",
    "\n",
    "    model = RandomForestRegressor()\n",
    "    current_y_test = y_test[column].to_numpy()\n",
    "\n",
    "    model.fit(x_train, current_y_train.to_numpy())\n",
    "    y_hat = model.predict(x_test)\n",
    "    test_accuracy = model.score(x_test, current_y_test)\n",
    "    models[column] = (model, y_hat, test_accuracy)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Random Forest Regressor Results\n",
    "\n",
    "- General Medical and Surgical (Adult) Beds - Accuracy: 93.98%\n",
    "- Medical and Surgical Intensive Care Beds - Accuracy: 86.12%\n",
    "- Cardiac Intensive Care Beds - Accuracy:\t69.01%\n",
    "- General Medical and Surgical (Pediatric) Beds - Accuracy: 73.01%\n",
    "- Pediatric Intensive Care Beds - Accuracy: 62.92%\n",
    "- Neonatal Intensive Care Beds - Accuracy: 71.16%\n",
    "- Neonatal Intermediate Care Beds - Accuracy: 44.49%\n",
    "- Obstetric Care Beds - Accuracy: 79.94%\n",
    "- Burn Care Beds - Accuracy: 33.86%\n",
    "- Physical Rehabilitation Care Beds - Accuracy: 60.38%\n",
    "- Alcohol/Drug Abuse or Dependency Inpatient Care Beds - Accuracy: 35.96%\n",
    "- Psychiatric Care Beds - Accuracy: 80.57%\n",
    "- Skilled Nursing Care Beds - Accuracy: 73.32%\n",
    "- Intermediate Nursing Care Beds - Accuracy: -46.42%\n",
    "- Acute Long-Term Care Beds - Accuracy: 62.09%\n",
    "- Other Long-Term Care Beds  - Accuracy: 52.74%\n",
    "- Other Special Care Beds - Accuracy: 40.77%\n",
    "- Other Intensive Care Beds  - Accuracy: 36.06%\n",
    "- Other Care Beds - Accuracy:\t25.16%\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'GENBD': (RandomForestRegressor(),\n",
       "  array([  0.  ,   6.71,   0.  , ..., 200.1 ,  18.97,  49.29]),\n",
       "  0.9398755104317876),\n",
       " 'PEDBD': (RandomForestRegressor(),\n",
       "  array([0.  , 0.  , 0.08, ..., 6.42, 0.03, 0.99]),\n",
       "  0.7300895957133813),\n",
       " 'OBBD': (RandomForestRegressor(),\n",
       "  array([ 0.08,  0.  ,  0.  , ..., 37.65,  0.  ,  1.19]),\n",
       "  0.7994813661823276),\n",
       " 'MSICBD': (RandomForestRegressor(),\n",
       "  array([ 0.  ,  0.  ,  0.05, ..., 23.46,  0.06,  1.43]),\n",
       "  0.8612834812080208),\n",
       " 'CICBD': (RandomForestRegressor(),\n",
       "  array([0.04, 0.  , 0.  , ..., 7.39, 0.  , 0.  ]),\n",
       "  0.6900346616943049),\n",
       " 'NICBD': (RandomForestRegressor(),\n",
       "  array([0.  , 0.  , 0.  , ..., 4.75, 0.  , 0.  ]),\n",
       "  0.7116513125363768),\n",
       " 'NINTBD': (RandomForestRegressor(),\n",
       "  array([0.  , 0.  , 0.  , ..., 2.54, 0.  , 0.  ]),\n",
       "  0.44490453450495604),\n",
       " 'PEDICBD': (RandomForestRegressor(),\n",
       "  array([0.  , 0.  , 0.  , ..., 0.22, 0.  , 0.  ]),\n",
       "  0.6292313637563013),\n",
       " 'BRNBD': (RandomForestRegressor(),\n",
       "  array([0.  , 0.  , 0.  , ..., 0.24, 0.  , 0.  ]),\n",
       "  0.3386487974002933),\n",
       " 'SPCICBD': (RandomForestRegressor(),\n",
       "  array([0.  , 0.  , 0.1 , ..., 3.57, 0.06, 0.84]),\n",
       "  0.40770164776309525),\n",
       " 'OTHICBD': (RandomForestRegressor(),\n",
       "  array([0.  , 0.  , 0.  , ..., 1.3 , 0.38, 0.  ]),\n",
       "  0.3606313374453901),\n",
       " 'REHABBD': (RandomForestRegressor(),\n",
       "  array([ 0.1 ,  6.62, 24.15, ..., 11.03,  0.75,  0.67]),\n",
       "  0.6038663796302547),\n",
       " 'ALCHBD': (RandomForestRegressor(),\n",
       "  array([2.66, 0.  , 0.  , ..., 0.45, 0.  , 2.  ]),\n",
       "  0.35969607372515144),\n",
       " 'PSYBD': (RandomForestRegressor(),\n",
       "  array([101.49,  13.95,   2.48, ...,  14.78,   0.  ,   0.87]),\n",
       "  0.8057555866516801),\n",
       " 'SNBD88': (RandomForestRegressor(),\n",
       "  array([0.  , 0.04, 0.  , ..., 0.25, 4.68, 2.36]),\n",
       "  0.7332377409051133),\n",
       " 'ICFBD88': (RandomForestRegressor(),\n",
       "  array([0.  , 0.  , 0.  , ..., 5.99, 0.  , 0.12]),\n",
       "  -0.46419366772801074),\n",
       " 'ACULTBD': (RandomForestRegressor(),\n",
       "  array([0.  , 0.  , 0.59, ..., 0.  , 6.24, 0.  ]),\n",
       "  0.6209965419260188),\n",
       " 'OTHLBD94': (RandomForestRegressor(),\n",
       "  array([0., 0., 0., ..., 0., 0., 0.]),\n",
       "  0.527438092889634),\n",
       " 'OTHBD94': (RandomForestRegressor(),\n",
       "  array([0.  , 0.  , 0.  , ..., 0.36, 1.2 , 4.06]),\n",
       "  0.2516590707341033)}"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "models "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Discussion and Results \n",
    "The Random Forest Regressor accurately predicts the bed count for certain bed types: general medical and surgical (adult) beds and medical and surgical intensive care beds (both above 86%). The RFR relatively accurately predicts the bed count for others: general and medical surgical (pediatric) beds, neonatal intensive care beds, obstetric care beds, psychiatric care beds, and skilled nursing care beds (all above 70%). The RFR has a poor accuracy of predicting burn care bed count, alcohol/drug abuse or dependency inpatient care bed count, other intensive care beds, and other care beds (all below 40%). The RFR’s worst accuracy is in predicting intermediate nursing care beds: -46.42%."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Limitations\n",
    "- Since the American Hospital Association will not release their 2021 data until late December 2022, there is no 2021 data to include in this model. As such, COVID-19 vaccine and booster data cannot be used to supplement the models, because the vaccines were released in late December 2020.\n",
    "- The death count reports may be inaccurate (both 2020 and 2021 report 3,342 total deaths) in addition to being very small."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.12 ('compsoc')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.12"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "00ac8e58fd34d6f88815c9706e59bbba54c9b5305a15e21d31f60207a45715df"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}