etl_pipeline.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "52bfbcd9",
   "metadata": {},
   "source": [
    "# Cleaning Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "539c434d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import datetime as dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "3af23dad",
   "metadata": {},
   "outputs": [],
   "source": [
    "#url for reading the SS table with state names and abbreviations\n",
    "url = \"https://www.ssa.gov/international/coc-docs/states.html\"\n",
    "\n",
    "#url for reading the data from worldometers data showing covid deaths in every US state\n",
    "url1 = \"https://www.worldometers.info/coronavirus/country/us/\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d8c36de",
   "metadata": {},
   "source": [
    "## Scrapping and cleaning data from Social Security website"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d8df5d9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "state_abbreviations = pd.read_html(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a0212e8e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[                       0   1\n",
      "0                ALABAMA  AL\n",
      "1                 ALASKA  AK\n",
      "2         AMERICAN SAMOA  AS\n",
      "3                ARIZONA  AZ\n",
      "4               ARKANSAS  AR\n",
      "5             CALIFORNIA  CA\n",
      "6               COLORADO  CO\n",
      "7            CONNECTICUT  CT\n",
      "8               DELAWARE  DE\n",
      "9   DISTRICT OF COLUMBIA  DC\n",
      "10               FLORIDA  FL\n",
      "11               GEORGIA  GA\n",
      "12                  GUAM  GU\n",
      "13                HAWAII  HI\n",
      "14                 IDAHO  ID\n",
      "15              ILLINOIS  IL\n",
      "16               INDIANA  IN\n",
      "17                  IOWA  IA\n",
      "18                KANSAS  KS\n",
      "19              KENTUCKY  KY\n",
      "20             LOUISIANA  LA\n",
      "21                 MAINE  ME\n",
      "22              MARYLAND  MD\n",
      "23         MASSACHUSETTS  MA\n",
      "24              MICHIGAN  MI\n",
      "25             MINNESOTA  MN\n",
      "26           MISSISSIPPI  MS\n",
      "27              MISSOURI  MO\n",
      "28               MONTANA  MT\n",
      "29              NEBRASKA  NE\n",
      "30                NEVADA  NV\n",
      "31         NEW HAMPSHIRE  NH\n",
      "32            NEW JERSEY  NJ\n",
      "33            NEW MEXICO  NM\n",
      "34              NEW YORK  NY\n",
      "35        NORTH CAROLINA  NC\n",
      "36          NORTH DAKOTA  ND\n",
      "37   NORTHERN MARIANA IS  MP\n",
      "38                  OHIO  OH\n",
      "39              OKLAHOMA  OK\n",
      "40                OREGON  OR\n",
      "41          PENNSYLVANIA  PA\n",
      "42           PUERTO RICO  PR\n",
      "43          RHODE ISLAND  RI\n",
      "44        SOUTH CAROLINA  SC\n",
      "45          SOUTH DAKOTA  SD\n",
      "46             TENNESSEE  TN\n",
      "47                 TEXAS  TX\n",
      "48                  UTAH  UT\n",
      "49               VERMONT  VT\n",
      "50              VIRGINIA  VA\n",
      "51        VIRGIN ISLANDS  VI\n",
      "52            WASHINGTON  WA\n",
      "53         WEST VIRGINIA  WV\n",
      "54             WISCONSIN  WI\n",
      "55               WYOMING  WY]\n"
     ]
    }
   ],
   "source": [
    "print(state_abbreviations)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "60c30539",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "list"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(state_abbreviations)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "e88166e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = state_abbreviations[0]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5c7b853b",
   "metadata": {},
   "outputs": [],
   "source": [
    "state_abbreviations_df = pd.DataFrame(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "390c9c85",
   "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ALASKA</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AMERICAN SAMOA</td>\n",
       "      <td>AS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ARIZONA</td>\n",
       "      <td>AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ARKANSAS</td>\n",
       "      <td>AR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                0   1\n",
       "0         ALABAMA  AL\n",
       "1          ALASKA  AK\n",
       "2  AMERICAN SAMOA  AS\n",
       "3         ARIZONA  AZ\n",
       "4        ARKANSAS  AR"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_abbreviations_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "44a41fe2",
   "metadata": {},
   "outputs": [],
   "source": [
    "state_abbreviations_df.rename(columns={0: \"state\", 1: \"abbreviation\"}, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5ba087cb",
   "metadata": {},
   "source": [
    "# Scrapping data from Worldometer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "85893e72",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     #    USAState  TotalCases  NewCases  TotalDeaths  NewDeaths  \\\n",
      "0  NaN   USA Total    85699847       NaN    1031218.0        NaN   \n",
      "1  1.0  California     9555733       NaN      91572.0        NaN   \n",
      "2  2.0       Texas     6918774       NaN      88793.0        NaN   \n",
      "3  3.0     Florida     6169047       NaN      74466.0        NaN   \n",
      "4  4.0    New York     5578774       NaN      69439.0        NaN   \n",
      "\n",
      "   TotalRecovered  ActiveCases  Tot Cases/1M pop  Deaths/1M pop    TotalTests  \\\n",
      "0      81998223.0    2670406.0          258910.0         3115.0  1.028176e+09   \n",
      "1       9054856.0     409305.0          241842.0         2318.0  1.683816e+08   \n",
      "2       6725168.0     104813.0          238612.0         3062.0  6.518661e+07   \n",
      "3       5885853.0     208728.0          287230.0         3467.0  5.821500e+07   \n",
      "4       5408954.0     100381.0          286774.0         3569.0  1.107687e+08   \n",
      "\n",
      "   Tests/ 1M pop  Population                            Source    Projections  \n",
      "0      3106247.0         NaN                               NaN            NaN  \n",
      "1      4261506.0  39512223.0              [view by county] [1]  [projections]  \n",
      "2      2248133.0  28995881.0      [view by county] [1] [2] [3]  [projections]  \n",
      "3      2710481.0  21477737.0  [view by county] [1] [2] [3] [4]  [projections]  \n",
      "4      5694007.0  19453561.0      [view by county] [1] [2] [3]  [projections]  \n"
     ]
    }
   ],
   "source": [
    "## Reading data from worldometer site. The data is held in an object named df1 \n",
    "\n",
    "from urllib.request import Request, urlopen\n",
    "\n",
    "req = Request(\"https://www.worldometers.info/coronavirus/country/us/\", headers={'User-Agent': 'Mozilla/5.0'})\n",
    "webpage = urlopen(req).read()\n",
    "\n",
    "\n",
    "tables = pd.read_html(webpage)\n",
    "df1 = tables[0]\n",
    "print(df1.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "cd5b0e44",
   "metadata": {},
   "outputs": [],
   "source": [
    "#converting df1 into a dataframe named state_covid_data\n",
    "state_covid_data = pd.DataFrame(df1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "40161d01",
   "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>#</th>\n",
       "      <th>USAState</th>\n",
       "      <th>TotalCases</th>\n",
       "      <th>NewCases</th>\n",
       "      <th>TotalDeaths</th>\n",
       "      <th>NewDeaths</th>\n",
       "      <th>TotalRecovered</th>\n",
       "      <th>ActiveCases</th>\n",
       "      <th>Tot Cases/1M pop</th>\n",
       "      <th>Deaths/1M pop</th>\n",
       "      <th>TotalTests</th>\n",
       "      <th>Tests/ 1M pop</th>\n",
       "      <th>Population</th>\n",
       "      <th>Source</th>\n",
       "      <th>Projections</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>USA Total</td>\n",
       "      <td>85699847</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1031218.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>81998223.0</td>\n",
       "      <td>2670406.0</td>\n",
       "      <td>258910.0</td>\n",
       "      <td>3115.0</td>\n",
       "      <td>1.028176e+09</td>\n",
       "      <td>3106247.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>California</td>\n",
       "      <td>9555733</td>\n",
       "      <td>NaN</td>\n",
       "      <td>91572.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9054856.0</td>\n",
       "      <td>409305.0</td>\n",
       "      <td>241842.0</td>\n",
       "      <td>2318.0</td>\n",
       "      <td>1.683816e+08</td>\n",
       "      <td>4261506.0</td>\n",
       "      <td>39512223.0</td>\n",
       "      <td>[view by county] [1]</td>\n",
       "      <td>[projections]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.0</td>\n",
       "      <td>Texas</td>\n",
       "      <td>6918774</td>\n",
       "      <td>NaN</td>\n",
       "      <td>88793.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6725168.0</td>\n",
       "      <td>104813.0</td>\n",
       "      <td>238612.0</td>\n",
       "      <td>3062.0</td>\n",
       "      <td>6.518661e+07</td>\n",
       "      <td>2248133.0</td>\n",
       "      <td>28995881.0</td>\n",
       "      <td>[view by county] [1] [2] [3]</td>\n",
       "      <td>[projections]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.0</td>\n",
       "      <td>Florida</td>\n",
       "      <td>6169047</td>\n",
       "      <td>NaN</td>\n",
       "      <td>74466.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5885853.0</td>\n",
       "      <td>208728.0</td>\n",
       "      <td>287230.0</td>\n",
       "      <td>3467.0</td>\n",
       "      <td>5.821500e+07</td>\n",
       "      <td>2710481.0</td>\n",
       "      <td>21477737.0</td>\n",
       "      <td>[view by county] [1] [2] [3] [4]</td>\n",
       "      <td>[projections]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.0</td>\n",
       "      <td>New York</td>\n",
       "      <td>5578774</td>\n",
       "      <td>NaN</td>\n",
       "      <td>69439.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5408954.0</td>\n",
       "      <td>100381.0</td>\n",
       "      <td>286774.0</td>\n",
       "      <td>3569.0</td>\n",
       "      <td>1.107687e+08</td>\n",
       "      <td>5694007.0</td>\n",
       "      <td>19453561.0</td>\n",
       "      <td>[view by county] [1] [2] [3]</td>\n",
       "      <td>[projections]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     #    USAState  TotalCases  NewCases  TotalDeaths  NewDeaths  \\\n",
       "0  NaN   USA Total    85699847       NaN    1031218.0        NaN   \n",
       "1  1.0  California     9555733       NaN      91572.0        NaN   \n",
       "2  2.0       Texas     6918774       NaN      88793.0        NaN   \n",
       "3  3.0     Florida     6169047       NaN      74466.0        NaN   \n",
       "4  4.0    New York     5578774       NaN      69439.0        NaN   \n",
       "\n",
       "   TotalRecovered  ActiveCases  Tot Cases/1M pop  Deaths/1M pop    TotalTests  \\\n",
       "0      81998223.0    2670406.0          258910.0         3115.0  1.028176e+09   \n",
       "1       9054856.0     409305.0          241842.0         2318.0  1.683816e+08   \n",
       "2       6725168.0     104813.0          238612.0         3062.0  6.518661e+07   \n",
       "3       5885853.0     208728.0          287230.0         3467.0  5.821500e+07   \n",
       "4       5408954.0     100381.0          286774.0         3569.0  1.107687e+08   \n",
       "\n",
       "   Tests/ 1M pop  Population                            Source    Projections  \n",
       "0      3106247.0         NaN                               NaN            NaN  \n",
       "1      4261506.0  39512223.0              [view by county] [1]  [projections]  \n",
       "2      2248133.0  28995881.0      [view by county] [1] [2] [3]  [projections]  \n",
       "3      2710481.0  21477737.0  [view by county] [1] [2] [3] [4]  [projections]  \n",
       "4      5694007.0  19453561.0      [view by county] [1] [2] [3]  [projections]  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_covid_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "23ed117c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#cleaning data by removing columns that are not needed\n",
    "#and removing rows with USA total as it is not needed row [0] and row [64]\n",
    "state_covid_data.drop(columns = ['#', 'NewCases', 'NewDeaths','Source', 'Projections', \n",
    "                                'TotalRecovered','ActiveCases', \n",
    "                                 'Deaths/1M pop'], axis = 1, inplace=True)\n",
    "state_covid_data.drop([0, 64], inplace = True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "05833730",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['USAState', 'TotalCases', 'TotalDeaths', 'Tot Cases/1M pop',\n",
       "       'TotalTests', 'Tests/ 1M pop', 'Population'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_covid_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "b15a70be",
   "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>USAState</th>\n",
       "      <th>TotalCases</th>\n",
       "      <th>TotalDeaths</th>\n",
       "      <th>Tot Cases/1M pop</th>\n",
       "      <th>TotalTests</th>\n",
       "      <th>Tests/ 1M pop</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>California</td>\n",
       "      <td>9555733</td>\n",
       "      <td>91572.0</td>\n",
       "      <td>241842.0</td>\n",
       "      <td>168381590.0</td>\n",
       "      <td>4261506.0</td>\n",
       "      <td>39512223.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Texas</td>\n",
       "      <td>6918774</td>\n",
       "      <td>88793.0</td>\n",
       "      <td>238612.0</td>\n",
       "      <td>65186610.0</td>\n",
       "      <td>2248133.0</td>\n",
       "      <td>28995881.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Florida</td>\n",
       "      <td>6169047</td>\n",
       "      <td>74466.0</td>\n",
       "      <td>287230.0</td>\n",
       "      <td>58214996.0</td>\n",
       "      <td>2710481.0</td>\n",
       "      <td>21477737.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>New York</td>\n",
       "      <td>5578774</td>\n",
       "      <td>69439.0</td>\n",
       "      <td>286774.0</td>\n",
       "      <td>110768718.0</td>\n",
       "      <td>5694007.0</td>\n",
       "      <td>19453561.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Illinois</td>\n",
       "      <td>3286377</td>\n",
       "      <td>38127.0</td>\n",
       "      <td>259345.0</td>\n",
       "      <td>57898053.0</td>\n",
       "      <td>4569040.0</td>\n",
       "      <td>12671821.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>Federal Prisons</td>\n",
       "      <td>69267</td>\n",
       "      <td>302.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>129677.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>Navajo Nation</td>\n",
       "      <td>54145</td>\n",
       "      <td>1786.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>529248.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>Grand Princess Ship</td>\n",
       "      <td>122</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>Wuhan Repatriated</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>Diamond Princess Ship</td>\n",
       "      <td>46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>46.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 USAState  TotalCases  TotalDeaths  Tot Cases/1M pop  \\\n",
       "1              California     9555733      91572.0          241842.0   \n",
       "2                   Texas     6918774      88793.0          238612.0   \n",
       "3                 Florida     6169047      74466.0          287230.0   \n",
       "4                New York     5578774      69439.0          286774.0   \n",
       "5                Illinois     3286377      38127.0          259345.0   \n",
       "..                    ...         ...          ...               ...   \n",
       "59        Federal Prisons       69267        302.0               NaN   \n",
       "60          Navajo Nation       54145       1786.0               NaN   \n",
       "61    Grand Princess Ship         122          7.0               NaN   \n",
       "62      Wuhan Repatriated           3          NaN               NaN   \n",
       "63  Diamond Princess Ship          46          NaN               NaN   \n",
       "\n",
       "     TotalTests  Tests/ 1M pop  Population  \n",
       "1   168381590.0      4261506.0  39512223.0  \n",
       "2    65186610.0      2248133.0  28995881.0  \n",
       "3    58214996.0      2710481.0  21477737.0  \n",
       "4   110768718.0      5694007.0  19453561.0  \n",
       "5    57898053.0      4569040.0  12671821.0  \n",
       "..          ...            ...         ...  \n",
       "59     129677.0            NaN         NaN  \n",
       "60     529248.0            NaN         NaN  \n",
       "61          NaN            NaN         NaN  \n",
       "62          3.0            NaN         NaN  \n",
       "63         46.0            NaN         NaN  \n",
       "\n",
       "[63 rows x 7 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_covid_data.head(63)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "eba8bc7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "state_covid_data.rename(columns={\"USAState\": \"state\", 'Tot Cases/1M pop':'Tot_Cases_per_1M_pop', \n",
    "                                 'Tests/ 1M pop' : \"Tests_per_1M_pop\" }, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "d8ebb408",
   "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>state</th>\n",
       "      <th>TotalCases</th>\n",
       "      <th>TotalDeaths</th>\n",
       "      <th>Tot Cases/1M pop</th>\n",
       "      <th>TotalTests</th>\n",
       "      <th>Tests_per_1M_pop</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>California</td>\n",
       "      <td>9555733</td>\n",
       "      <td>91572.0</td>\n",
       "      <td>241842.0</td>\n",
       "      <td>168381590.0</td>\n",
       "      <td>4261506.0</td>\n",
       "      <td>39512223.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Texas</td>\n",
       "      <td>6918774</td>\n",
       "      <td>88793.0</td>\n",
       "      <td>238612.0</td>\n",
       "      <td>65186610.0</td>\n",
       "      <td>2248133.0</td>\n",
       "      <td>28995881.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Florida</td>\n",
       "      <td>6169047</td>\n",
       "      <td>74466.0</td>\n",
       "      <td>287230.0</td>\n",
       "      <td>58214996.0</td>\n",
       "      <td>2710481.0</td>\n",
       "      <td>21477737.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>New York</td>\n",
       "      <td>5578774</td>\n",
       "      <td>69439.0</td>\n",
       "      <td>286774.0</td>\n",
       "      <td>110768718.0</td>\n",
       "      <td>5694007.0</td>\n",
       "      <td>19453561.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Illinois</td>\n",
       "      <td>3286377</td>\n",
       "      <td>38127.0</td>\n",
       "      <td>259345.0</td>\n",
       "      <td>57898053.0</td>\n",
       "      <td>4569040.0</td>\n",
       "      <td>12671821.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        state  TotalCases  TotalDeaths  Tot Cases/1M pop   TotalTests  \\\n",
       "1  California     9555733      91572.0          241842.0  168381590.0   \n",
       "2       Texas     6918774      88793.0          238612.0   65186610.0   \n",
       "3     Florida     6169047      74466.0          287230.0   58214996.0   \n",
       "4    New York     5578774      69439.0          286774.0  110768718.0   \n",
       "5    Illinois     3286377      38127.0          259345.0   57898053.0   \n",
       "\n",
       "   Tests_per_1M_pop  Population  \n",
       "1         4261506.0  39512223.0  \n",
       "2         2248133.0  28995881.0  \n",
       "3         2710481.0  21477737.0  \n",
       "4         5694007.0  19453561.0  \n",
       "5         4569040.0  12671821.0  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_covid_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "45f4cfd6",
   "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>state</th>\n",
       "      <th>abbreviation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ALASKA</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AMERICAN SAMOA</td>\n",
       "      <td>AS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ARIZONA</td>\n",
       "      <td>AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ARKANSAS</td>\n",
       "      <td>AR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            state abbreviation\n",
       "0         ALABAMA           AL\n",
       "1          ALASKA           AK\n",
       "2  AMERICAN SAMOA           AS\n",
       "3         ARIZONA           AZ\n",
       "4        ARKANSAS           AR"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_abbreviations_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "6b227ff3",
   "metadata": {},
   "outputs": [],
   "source": [
    "state_covid_datadf = pd.merge(state_abbreviations_df, state_covid_data, how = 'inner', on = 'state' )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "c9ee8b9d",
   "metadata": {},
   "outputs": [],
   "source": [
    "state_abbreviations_df['state'] = state_abbreviations_df['state'].str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "86dedc7e",
   "metadata": {
    "scrolled": true
   },
   "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>state</th>\n",
       "      <th>abbreviation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alaska</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>american samoa</td>\n",
       "      <td>AS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>arizona</td>\n",
       "      <td>AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>arkansas</td>\n",
       "      <td>AR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            state abbreviation\n",
       "0         alabama           AL\n",
       "1          alaska           AK\n",
       "2  american samoa           AS\n",
       "3         arizona           AZ\n",
       "4        arkansas           AR"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_abbreviations_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "cc300d7e",
   "metadata": {},
   "outputs": [],
   "source": [
    "state_abbreviations_df.to_csv(\"state_abbreviations.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "05dc4267",
   "metadata": {},
   "outputs": [],
   "source": [
    "state_covid_data['state'] = state_covid_data['state'].str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "93bf2ab1",
   "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>state</th>\n",
       "      <th>TotalCases</th>\n",
       "      <th>TotalDeaths</th>\n",
       "      <th>Tot Cases/1M pop</th>\n",
       "      <th>TotalTests</th>\n",
       "      <th>Tests_per_1M_pop</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>california</td>\n",
       "      <td>9555733</td>\n",
       "      <td>91572.0</td>\n",
       "      <td>241842.0</td>\n",
       "      <td>168381590.0</td>\n",
       "      <td>4261506.0</td>\n",
       "      <td>39512223.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>texas</td>\n",
       "      <td>6918774</td>\n",
       "      <td>88793.0</td>\n",
       "      <td>238612.0</td>\n",
       "      <td>65186610.0</td>\n",
       "      <td>2248133.0</td>\n",
       "      <td>28995881.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>florida</td>\n",
       "      <td>6169047</td>\n",
       "      <td>74466.0</td>\n",
       "      <td>287230.0</td>\n",
       "      <td>58214996.0</td>\n",
       "      <td>2710481.0</td>\n",
       "      <td>21477737.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>new york</td>\n",
       "      <td>5578774</td>\n",
       "      <td>69439.0</td>\n",
       "      <td>286774.0</td>\n",
       "      <td>110768718.0</td>\n",
       "      <td>5694007.0</td>\n",
       "      <td>19453561.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>illinois</td>\n",
       "      <td>3286377</td>\n",
       "      <td>38127.0</td>\n",
       "      <td>259345.0</td>\n",
       "      <td>57898053.0</td>\n",
       "      <td>4569040.0</td>\n",
       "      <td>12671821.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        state  TotalCases  TotalDeaths  Tot Cases/1M pop   TotalTests  \\\n",
       "1  california     9555733      91572.0          241842.0  168381590.0   \n",
       "2       texas     6918774      88793.0          238612.0   65186610.0   \n",
       "3     florida     6169047      74466.0          287230.0   58214996.0   \n",
       "4    new york     5578774      69439.0          286774.0  110768718.0   \n",
       "5    illinois     3286377      38127.0          259345.0   57898053.0   \n",
       "\n",
       "   Tests_per_1M_pop  Population  \n",
       "1         4261506.0  39512223.0  \n",
       "2         2248133.0  28995881.0  \n",
       "3         2710481.0  21477737.0  \n",
       "4         5694007.0  19453561.0  \n",
       "5         4569040.0  12671821.0  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_covid_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "6fb5ec59",
   "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>state</th>\n",
       "      <th>TotalCases</th>\n",
       "      <th>TotalDeaths</th>\n",
       "      <th>Tot Cases/1M pop</th>\n",
       "      <th>TotalTests</th>\n",
       "      <th>Tests_per_1M_pop</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>california</td>\n",
       "      <td>9555733</td>\n",
       "      <td>91572.0</td>\n",
       "      <td>241842.0</td>\n",
       "      <td>168381590.0</td>\n",
       "      <td>4261506.0</td>\n",
       "      <td>39512223.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>texas</td>\n",
       "      <td>6918774</td>\n",
       "      <td>88793.0</td>\n",
       "      <td>238612.0</td>\n",
       "      <td>65186610.0</td>\n",
       "      <td>2248133.0</td>\n",
       "      <td>28995881.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>florida</td>\n",
       "      <td>6169047</td>\n",
       "      <td>74466.0</td>\n",
       "      <td>287230.0</td>\n",
       "      <td>58214996.0</td>\n",
       "      <td>2710481.0</td>\n",
       "      <td>21477737.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>new york</td>\n",
       "      <td>5578774</td>\n",
       "      <td>69439.0</td>\n",
       "      <td>286774.0</td>\n",
       "      <td>110768718.0</td>\n",
       "      <td>5694007.0</td>\n",
       "      <td>19453561.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>illinois</td>\n",
       "      <td>3286377</td>\n",
       "      <td>38127.0</td>\n",
       "      <td>259345.0</td>\n",
       "      <td>57898053.0</td>\n",
       "      <td>4569040.0</td>\n",
       "      <td>12671821.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        state  TotalCases  TotalDeaths  Tot Cases/1M pop   TotalTests  \\\n",
       "1  california     9555733      91572.0          241842.0  168381590.0   \n",
       "2       texas     6918774      88793.0          238612.0   65186610.0   \n",
       "3     florida     6169047      74466.0          287230.0   58214996.0   \n",
       "4    new york     5578774      69439.0          286774.0  110768718.0   \n",
       "5    illinois     3286377      38127.0          259345.0   57898053.0   \n",
       "\n",
       "   Tests_per_1M_pop  Population  \n",
       "1         4261506.0  39512223.0  \n",
       "2         2248133.0  28995881.0  \n",
       "3         2710481.0  21477737.0  \n",
       "4         5694007.0  19453561.0  \n",
       "5         4569040.0  12671821.0  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_covid_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "f58a8bc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "state_covid_datadf = pd.merge(state_abbreviations_df, state_covid_data, how = 'inner', on = 'state' )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "88776c92",
   "metadata": {},
   "outputs": [],
   "source": [
    "state_covid_datadf_left = pd.merge(state_abbreviations_df, state_covid_data, how = 'left', on = 'state' )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "f7928c3f",
   "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>state</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>TotalCases</th>\n",
       "      <th>TotalDeaths</th>\n",
       "      <th>Tot Cases/1M pop</th>\n",
       "      <th>TotalTests</th>\n",
       "      <th>Tests_per_1M_pop</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>1312437.0</td>\n",
       "      <td>19658.0</td>\n",
       "      <td>267670.0</td>\n",
       "      <td>7698101.0</td>\n",
       "      <td>1570021.0</td>\n",
       "      <td>4903185.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>251425.0</td>\n",
       "      <td>1252.0</td>\n",
       "      <td>343690.0</td>\n",
       "      <td>4107614.0</td>\n",
       "      <td>5614985.0</td>\n",
       "      <td>731545.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>american samoa</td>\n",
       "      <td>AS</td>\n",
       "      <td>6130.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16964.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>2049627.0</td>\n",
       "      <td>30299.0</td>\n",
       "      <td>281592.0</td>\n",
       "      <td>19534476.0</td>\n",
       "      <td>2683780.0</td>\n",
       "      <td>7278717.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>arkansas</td>\n",
       "      <td>AR</td>\n",
       "      <td>842439.0</td>\n",
       "      <td>11471.0</td>\n",
       "      <td>279156.0</td>\n",
       "      <td>6928561.0</td>\n",
       "      <td>2295895.0</td>\n",
       "      <td>3017804.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>california</td>\n",
       "      <td>CA</td>\n",
       "      <td>9555733.0</td>\n",
       "      <td>91572.0</td>\n",
       "      <td>241842.0</td>\n",
       "      <td>168381590.0</td>\n",
       "      <td>4261506.0</td>\n",
       "      <td>39512223.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>colorado</td>\n",
       "      <td>CO</td>\n",
       "      <td>1441472.0</td>\n",
       "      <td>13326.0</td>\n",
       "      <td>250310.0</td>\n",
       "      <td>18281307.0</td>\n",
       "      <td>3174535.0</td>\n",
       "      <td>5758736.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>connecticut</td>\n",
       "      <td>CT</td>\n",
       "      <td>806467.0</td>\n",
       "      <td>10941.0</td>\n",
       "      <td>226200.0</td>\n",
       "      <td>15534710.0</td>\n",
       "      <td>4357212.0</td>\n",
       "      <td>3565287.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>delaware</td>\n",
       "      <td>DE</td>\n",
       "      <td>274899.0</td>\n",
       "      <td>2956.0</td>\n",
       "      <td>282306.0</td>\n",
       "      <td>1026007.0</td>\n",
       "      <td>1053651.0</td>\n",
       "      <td>973764.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>district of columbia</td>\n",
       "      <td>DC</td>\n",
       "      <td>149271.0</td>\n",
       "      <td>1342.0</td>\n",
       "      <td>211507.0</td>\n",
       "      <td>3067883.0</td>\n",
       "      <td>4346989.0</td>\n",
       "      <td>705749.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>florida</td>\n",
       "      <td>FL</td>\n",
       "      <td>6169047.0</td>\n",
       "      <td>74466.0</td>\n",
       "      <td>287230.0</td>\n",
       "      <td>58214996.0</td>\n",
       "      <td>2710481.0</td>\n",
       "      <td>21477737.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>georgia</td>\n",
       "      <td>GA</td>\n",
       "      <td>2556044.0</td>\n",
       "      <td>38198.0</td>\n",
       "      <td>240741.0</td>\n",
       "      <td>25168138.0</td>\n",
       "      <td>2370456.0</td>\n",
       "      <td>10617423.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>guam</td>\n",
       "      <td>GU</td>\n",
       "      <td>48968.0</td>\n",
       "      <td>363.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>461130.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>hawaii</td>\n",
       "      <td>HI</td>\n",
       "      <td>274495.0</td>\n",
       "      <td>1451.0</td>\n",
       "      <td>193870.0</td>\n",
       "      <td>3848987.0</td>\n",
       "      <td>2718457.0</td>\n",
       "      <td>1415872.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>idaho</td>\n",
       "      <td>ID</td>\n",
       "      <td>450957.0</td>\n",
       "      <td>4940.0</td>\n",
       "      <td>252345.0</td>\n",
       "      <td>2947879.0</td>\n",
       "      <td>1649565.0</td>\n",
       "      <td>1787065.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>illinois</td>\n",
       "      <td>IL</td>\n",
       "      <td>3286377.0</td>\n",
       "      <td>38127.0</td>\n",
       "      <td>259345.0</td>\n",
       "      <td>57898053.0</td>\n",
       "      <td>4569040.0</td>\n",
       "      <td>12671821.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>indiana</td>\n",
       "      <td>IN</td>\n",
       "      <td>1730761.0</td>\n",
       "      <td>23700.0</td>\n",
       "      <td>257086.0</td>\n",
       "      <td>19928562.0</td>\n",
       "      <td>2960177.0</td>\n",
       "      <td>6732219.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>iowa</td>\n",
       "      <td>IA</td>\n",
       "      <td>876666.0</td>\n",
       "      <td>9588.0</td>\n",
       "      <td>277859.0</td>\n",
       "      <td>8492575.0</td>\n",
       "      <td>2691723.0</td>\n",
       "      <td>3155070.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>kansas</td>\n",
       "      <td>KS</td>\n",
       "      <td>785916.0</td>\n",
       "      <td>8939.0</td>\n",
       "      <td>269767.0</td>\n",
       "      <td>2269976.0</td>\n",
       "      <td>779173.0</td>\n",
       "      <td>2913314.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>kentucky</td>\n",
       "      <td>KY</td>\n",
       "      <td>1352114.0</td>\n",
       "      <td>15933.0</td>\n",
       "      <td>302644.0</td>\n",
       "      <td>13675281.0</td>\n",
       "      <td>3060940.0</td>\n",
       "      <td>4467673.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>louisiana</td>\n",
       "      <td>LA</td>\n",
       "      <td>1257148.0</td>\n",
       "      <td>17325.0</td>\n",
       "      <td>270425.0</td>\n",
       "      <td>15750286.0</td>\n",
       "      <td>3388037.0</td>\n",
       "      <td>4648794.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>maine</td>\n",
       "      <td>ME</td>\n",
       "      <td>262097.0</td>\n",
       "      <td>2347.0</td>\n",
       "      <td>194982.0</td>\n",
       "      <td>5292444.0</td>\n",
       "      <td>3937209.0</td>\n",
       "      <td>1344212.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>maryland</td>\n",
       "      <td>MD</td>\n",
       "      <td>1083424.0</td>\n",
       "      <td>14594.0</td>\n",
       "      <td>179206.0</td>\n",
       "      <td>21069442.0</td>\n",
       "      <td>3485041.0</td>\n",
       "      <td>6045680.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>massachusetts</td>\n",
       "      <td>MA</td>\n",
       "      <td>1863077.0</td>\n",
       "      <td>20574.0</td>\n",
       "      <td>270305.0</td>\n",
       "      <td>45061966.0</td>\n",
       "      <td>6537823.0</td>\n",
       "      <td>6892503.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>michigan</td>\n",
       "      <td>MI</td>\n",
       "      <td>2501863.0</td>\n",
       "      <td>36218.0</td>\n",
       "      <td>250516.0</td>\n",
       "      <td>25256797.0</td>\n",
       "      <td>2529004.0</td>\n",
       "      <td>9986857.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>minnesota</td>\n",
       "      <td>MN</td>\n",
       "      <td>1504982.0</td>\n",
       "      <td>12938.0</td>\n",
       "      <td>266858.0</td>\n",
       "      <td>19479012.0</td>\n",
       "      <td>3453951.0</td>\n",
       "      <td>5639632.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>mississippi</td>\n",
       "      <td>MS</td>\n",
       "      <td>806838.0</td>\n",
       "      <td>12466.0</td>\n",
       "      <td>271101.0</td>\n",
       "      <td>6396772.0</td>\n",
       "      <td>2149345.0</td>\n",
       "      <td>2976149.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>missouri</td>\n",
       "      <td>MO</td>\n",
       "      <td>1440814.0</td>\n",
       "      <td>20454.0</td>\n",
       "      <td>234759.0</td>\n",
       "      <td>14632189.0</td>\n",
       "      <td>2384091.0</td>\n",
       "      <td>6137428.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>montana</td>\n",
       "      <td>MT</td>\n",
       "      <td>277497.0</td>\n",
       "      <td>3400.0</td>\n",
       "      <td>259640.0</td>\n",
       "      <td>2789012.0</td>\n",
       "      <td>2609534.0</td>\n",
       "      <td>1068778.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>nebraska</td>\n",
       "      <td>NE</td>\n",
       "      <td>485756.0</td>\n",
       "      <td>4284.0</td>\n",
       "      <td>251114.0</td>\n",
       "      <td>5506012.0</td>\n",
       "      <td>2846355.0</td>\n",
       "      <td>1934408.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>nevada</td>\n",
       "      <td>NV</td>\n",
       "      <td>735021.0</td>\n",
       "      <td>10872.0</td>\n",
       "      <td>238631.0</td>\n",
       "      <td>6875532.0</td>\n",
       "      <td>2232203.0</td>\n",
       "      <td>3080156.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>new hampshire</td>\n",
       "      <td>NH</td>\n",
       "      <td>324373.0</td>\n",
       "      <td>2534.0</td>\n",
       "      <td>238560.0</td>\n",
       "      <td>4829711.0</td>\n",
       "      <td>3552013.0</td>\n",
       "      <td>1359711.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>new jersey</td>\n",
       "      <td>NJ</td>\n",
       "      <td>2381173.0</td>\n",
       "      <td>33678.0</td>\n",
       "      <td>268084.0</td>\n",
       "      <td>17808889.0</td>\n",
       "      <td>2005011.0</td>\n",
       "      <td>8882190.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>new mexico</td>\n",
       "      <td>NM</td>\n",
       "      <td>533867.0</td>\n",
       "      <td>7778.0</td>\n",
       "      <td>254607.0</td>\n",
       "      <td>7195809.0</td>\n",
       "      <td>3431758.0</td>\n",
       "      <td>2096829.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>new york</td>\n",
       "      <td>NY</td>\n",
       "      <td>5578774.0</td>\n",
       "      <td>69439.0</td>\n",
       "      <td>286774.0</td>\n",
       "      <td>110768718.0</td>\n",
       "      <td>5694007.0</td>\n",
       "      <td>19453561.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>north carolina</td>\n",
       "      <td>NC</td>\n",
       "      <td>2744935.0</td>\n",
       "      <td>24644.0</td>\n",
       "      <td>261719.0</td>\n",
       "      <td>26356209.0</td>\n",
       "      <td>2512967.0</td>\n",
       "      <td>10488084.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>north dakota</td>\n",
       "      <td>ND</td>\n",
       "      <td>244098.0</td>\n",
       "      <td>2281.0</td>\n",
       "      <td>320313.0</td>\n",
       "      <td>618121.0</td>\n",
       "      <td>811116.0</td>\n",
       "      <td>762062.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>northern mariana is</td>\n",
       "      <td>MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>ohio</td>\n",
       "      <td>OH</td>\n",
       "      <td>2763123.0</td>\n",
       "      <td>38628.0</td>\n",
       "      <td>236385.0</td>\n",
       "      <td>22272602.0</td>\n",
       "      <td>1905416.0</td>\n",
       "      <td>11689100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>oklahoma</td>\n",
       "      <td>OK</td>\n",
       "      <td>1048988.0</td>\n",
       "      <td>14420.0</td>\n",
       "      <td>265099.0</td>\n",
       "      <td>5473577.0</td>\n",
       "      <td>1383274.0</td>\n",
       "      <td>3956971.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>oregon</td>\n",
       "      <td>OR</td>\n",
       "      <td>760804.0</td>\n",
       "      <td>7611.0</td>\n",
       "      <td>180382.0</td>\n",
       "      <td>11545631.0</td>\n",
       "      <td>2737399.0</td>\n",
       "      <td>4217737.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>pennsylvania</td>\n",
       "      <td>PA</td>\n",
       "      <td>2907324.0</td>\n",
       "      <td>45147.0</td>\n",
       "      <td>227099.0</td>\n",
       "      <td>26467641.0</td>\n",
       "      <td>2067463.0</td>\n",
       "      <td>12801989.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>puerto rico</td>\n",
       "      <td>PR</td>\n",
       "      <td>657428.0</td>\n",
       "      <td>4343.0</td>\n",
       "      <td>194107.0</td>\n",
       "      <td>4268027.0</td>\n",
       "      <td>1260142.0</td>\n",
       "      <td>3386941.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>rhode island</td>\n",
       "      <td>RI</td>\n",
       "      <td>391275.0</td>\n",
       "      <td>3577.0</td>\n",
       "      <td>369350.0</td>\n",
       "      <td>7987471.0</td>\n",
       "      <td>7539895.0</td>\n",
       "      <td>1059361.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>south carolina</td>\n",
       "      <td>SC</td>\n",
       "      <td>1495762.0</td>\n",
       "      <td>17905.0</td>\n",
       "      <td>290512.0</td>\n",
       "      <td>16507819.0</td>\n",
       "      <td>3206202.0</td>\n",
       "      <td>5148714.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>south dakota</td>\n",
       "      <td>SD</td>\n",
       "      <td>239704.0</td>\n",
       "      <td>2927.0</td>\n",
       "      <td>270956.0</td>\n",
       "      <td>2189182.0</td>\n",
       "      <td>2474605.0</td>\n",
       "      <td>884659.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>tennessee</td>\n",
       "      <td>TN</td>\n",
       "      <td>2057484.0</td>\n",
       "      <td>26435.0</td>\n",
       "      <td>301279.0</td>\n",
       "      <td>12901180.0</td>\n",
       "      <td>1889127.0</td>\n",
       "      <td>6829174.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>texas</td>\n",
       "      <td>TX</td>\n",
       "      <td>6918774.0</td>\n",
       "      <td>88793.0</td>\n",
       "      <td>238612.0</td>\n",
       "      <td>65186610.0</td>\n",
       "      <td>2248133.0</td>\n",
       "      <td>28995881.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>utah</td>\n",
       "      <td>UT</td>\n",
       "      <td>948979.0</td>\n",
       "      <td>4777.0</td>\n",
       "      <td>296005.0</td>\n",
       "      <td>9406861.0</td>\n",
       "      <td>2934181.0</td>\n",
       "      <td>3205958.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>vermont</td>\n",
       "      <td>VT</td>\n",
       "      <td>131460.0</td>\n",
       "      <td>661.0</td>\n",
       "      <td>210677.0</td>\n",
       "      <td>3639448.0</td>\n",
       "      <td>5832552.0</td>\n",
       "      <td>623989.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>virginia</td>\n",
       "      <td>VA</td>\n",
       "      <td>1777813.0</td>\n",
       "      <td>20376.0</td>\n",
       "      <td>208284.0</td>\n",
       "      <td>19172533.0</td>\n",
       "      <td>2246206.0</td>\n",
       "      <td>8535519.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>virgin islands</td>\n",
       "      <td>VI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>washington</td>\n",
       "      <td>WA</td>\n",
       "      <td>1573651.0</td>\n",
       "      <td>12919.0</td>\n",
       "      <td>206654.0</td>\n",
       "      <td>14078985.0</td>\n",
       "      <td>1848875.0</td>\n",
       "      <td>7614893.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>west virginia</td>\n",
       "      <td>WV</td>\n",
       "      <td>513953.0</td>\n",
       "      <td>6945.0</td>\n",
       "      <td>286781.0</td>\n",
       "      <td>6190639.0</td>\n",
       "      <td>3454314.0</td>\n",
       "      <td>1792147.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>wisconsin</td>\n",
       "      <td>WI</td>\n",
       "      <td>1669592.0</td>\n",
       "      <td>14594.0</td>\n",
       "      <td>286752.0</td>\n",
       "      <td>16506830.0</td>\n",
       "      <td>2835039.0</td>\n",
       "      <td>5822434.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>wyoming</td>\n",
       "      <td>WY</td>\n",
       "      <td>157861.0</td>\n",
       "      <td>1820.0</td>\n",
       "      <td>272758.0</td>\n",
       "      <td>1396443.0</td>\n",
       "      <td>2412823.0</td>\n",
       "      <td>578759.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   state abbreviation  TotalCases  TotalDeaths  \\\n",
       "0                alabama           AL   1312437.0      19658.0   \n",
       "1                 alaska           AK    251425.0       1252.0   \n",
       "2         american samoa           AS      6130.0         31.0   \n",
       "3                arizona           AZ   2049627.0      30299.0   \n",
       "4               arkansas           AR    842439.0      11471.0   \n",
       "5             california           CA   9555733.0      91572.0   \n",
       "6               colorado           CO   1441472.0      13326.0   \n",
       "7            connecticut           CT    806467.0      10941.0   \n",
       "8               delaware           DE    274899.0       2956.0   \n",
       "9   district of columbia           DC    149271.0       1342.0   \n",
       "10               florida           FL   6169047.0      74466.0   \n",
       "11               georgia           GA   2556044.0      38198.0   \n",
       "12                  guam           GU     48968.0        363.0   \n",
       "13                hawaii           HI    274495.0       1451.0   \n",
       "14                 idaho           ID    450957.0       4940.0   \n",
       "15              illinois           IL   3286377.0      38127.0   \n",
       "16               indiana           IN   1730761.0      23700.0   \n",
       "17                  iowa           IA    876666.0       9588.0   \n",
       "18                kansas           KS    785916.0       8939.0   \n",
       "19              kentucky           KY   1352114.0      15933.0   \n",
       "20             louisiana           LA   1257148.0      17325.0   \n",
       "21                 maine           ME    262097.0       2347.0   \n",
       "22              maryland           MD   1083424.0      14594.0   \n",
       "23         massachusetts           MA   1863077.0      20574.0   \n",
       "24              michigan           MI   2501863.0      36218.0   \n",
       "25             minnesota           MN   1504982.0      12938.0   \n",
       "26           mississippi           MS    806838.0      12466.0   \n",
       "27              missouri           MO   1440814.0      20454.0   \n",
       "28               montana           MT    277497.0       3400.0   \n",
       "29              nebraska           NE    485756.0       4284.0   \n",
       "30                nevada           NV    735021.0      10872.0   \n",
       "31         new hampshire           NH    324373.0       2534.0   \n",
       "32            new jersey           NJ   2381173.0      33678.0   \n",
       "33            new mexico           NM    533867.0       7778.0   \n",
       "34              new york           NY   5578774.0      69439.0   \n",
       "35        north carolina           NC   2744935.0      24644.0   \n",
       "36          north dakota           ND    244098.0       2281.0   \n",
       "37   northern mariana is           MP         NaN          NaN   \n",
       "38                  ohio           OH   2763123.0      38628.0   \n",
       "39              oklahoma           OK   1048988.0      14420.0   \n",
       "40                oregon           OR    760804.0       7611.0   \n",
       "41          pennsylvania           PA   2907324.0      45147.0   \n",
       "42           puerto rico           PR    657428.0       4343.0   \n",
       "43          rhode island           RI    391275.0       3577.0   \n",
       "44        south carolina           SC   1495762.0      17905.0   \n",
       "45          south dakota           SD    239704.0       2927.0   \n",
       "46             tennessee           TN   2057484.0      26435.0   \n",
       "47                 texas           TX   6918774.0      88793.0   \n",
       "48                  utah           UT    948979.0       4777.0   \n",
       "49               vermont           VT    131460.0        661.0   \n",
       "50              virginia           VA   1777813.0      20376.0   \n",
       "51        virgin islands           VI         NaN          NaN   \n",
       "52            washington           WA   1573651.0      12919.0   \n",
       "53         west virginia           WV    513953.0       6945.0   \n",
       "54             wisconsin           WI   1669592.0      14594.0   \n",
       "55               wyoming           WY    157861.0       1820.0   \n",
       "\n",
       "    Tot Cases/1M pop   TotalTests  Tests_per_1M_pop  Population  \n",
       "0           267670.0    7698101.0         1570021.0   4903185.0  \n",
       "1           343690.0    4107614.0         5614985.0    731545.0  \n",
       "2                NaN      16964.0               NaN         NaN  \n",
       "3           281592.0   19534476.0         2683780.0   7278717.0  \n",
       "4           279156.0    6928561.0         2295895.0   3017804.0  \n",
       "5           241842.0  168381590.0         4261506.0  39512223.0  \n",
       "6           250310.0   18281307.0         3174535.0   5758736.0  \n",
       "7           226200.0   15534710.0         4357212.0   3565287.0  \n",
       "8           282306.0    1026007.0         1053651.0    973764.0  \n",
       "9           211507.0    3067883.0         4346989.0    705749.0  \n",
       "10          287230.0   58214996.0         2710481.0  21477737.0  \n",
       "11          240741.0   25168138.0         2370456.0  10617423.0  \n",
       "12               NaN     461130.0               NaN         NaN  \n",
       "13          193870.0    3848987.0         2718457.0   1415872.0  \n",
       "14          252345.0    2947879.0         1649565.0   1787065.0  \n",
       "15          259345.0   57898053.0         4569040.0  12671821.0  \n",
       "16          257086.0   19928562.0         2960177.0   6732219.0  \n",
       "17          277859.0    8492575.0         2691723.0   3155070.0  \n",
       "18          269767.0    2269976.0          779173.0   2913314.0  \n",
       "19          302644.0   13675281.0         3060940.0   4467673.0  \n",
       "20          270425.0   15750286.0         3388037.0   4648794.0  \n",
       "21          194982.0    5292444.0         3937209.0   1344212.0  \n",
       "22          179206.0   21069442.0         3485041.0   6045680.0  \n",
       "23          270305.0   45061966.0         6537823.0   6892503.0  \n",
       "24          250516.0   25256797.0         2529004.0   9986857.0  \n",
       "25          266858.0   19479012.0         3453951.0   5639632.0  \n",
       "26          271101.0    6396772.0         2149345.0   2976149.0  \n",
       "27          234759.0   14632189.0         2384091.0   6137428.0  \n",
       "28          259640.0    2789012.0         2609534.0   1068778.0  \n",
       "29          251114.0    5506012.0         2846355.0   1934408.0  \n",
       "30          238631.0    6875532.0         2232203.0   3080156.0  \n",
       "31          238560.0    4829711.0         3552013.0   1359711.0  \n",
       "32          268084.0   17808889.0         2005011.0   8882190.0  \n",
       "33          254607.0    7195809.0         3431758.0   2096829.0  \n",
       "34          286774.0  110768718.0         5694007.0  19453561.0  \n",
       "35          261719.0   26356209.0         2512967.0  10488084.0  \n",
       "36          320313.0     618121.0          811116.0    762062.0  \n",
       "37               NaN          NaN               NaN         NaN  \n",
       "38          236385.0   22272602.0         1905416.0  11689100.0  \n",
       "39          265099.0    5473577.0         1383274.0   3956971.0  \n",
       "40          180382.0   11545631.0         2737399.0   4217737.0  \n",
       "41          227099.0   26467641.0         2067463.0  12801989.0  \n",
       "42          194107.0    4268027.0         1260142.0   3386941.0  \n",
       "43          369350.0    7987471.0         7539895.0   1059361.0  \n",
       "44          290512.0   16507819.0         3206202.0   5148714.0  \n",
       "45          270956.0    2189182.0         2474605.0    884659.0  \n",
       "46          301279.0   12901180.0         1889127.0   6829174.0  \n",
       "47          238612.0   65186610.0         2248133.0  28995881.0  \n",
       "48          296005.0    9406861.0         2934181.0   3205958.0  \n",
       "49          210677.0    3639448.0         5832552.0    623989.0  \n",
       "50          208284.0   19172533.0         2246206.0   8535519.0  \n",
       "51               NaN          NaN               NaN         NaN  \n",
       "52          206654.0   14078985.0         1848875.0   7614893.0  \n",
       "53          286781.0    6190639.0         3454314.0   1792147.0  \n",
       "54          286752.0   16506830.0         2835039.0   5822434.0  \n",
       "55          272758.0    1396443.0         2412823.0    578759.0  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_covid_datadf_left"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e03627a",
   "metadata": {},
   "source": [
    "In the data there are 50 states and 6 territories. \n",
    "It was decided to keep rows with NaN values as these maybe available in other tables. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "fdf6d49f",
   "metadata": {},
   "outputs": [],
   "source": [
    "state_covid_datadf_left.to_csv(\"national-covid-statistics-us.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b7766e6",
   "metadata": {},
   "source": [
    "----------------------------------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10186325",
   "metadata": {},
   "source": [
    "## NYT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "1260c625",
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_file1 = \"Resources/prisons-covid-NYT-data/systems.csv\"\n",
    "nyt_df1 = pd.read_csv(csv_file1)\n",
    "\n",
    "csv_file2 = \"Resources/prisons-covid-NYT-data/facilities.csv\"\n",
    "nyt_df2 = pd.read_csv(csv_file2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "66c12ebf",
   "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>system</th>\n",
       "      <th>inmate_tests</th>\n",
       "      <th>total_inmate_cases</th>\n",
       "      <th>total_inmate_deaths</th>\n",
       "      <th>latest_inmate_population</th>\n",
       "      <th>max_inmate_population_2020</th>\n",
       "      <th>total_officer_cases</th>\n",
       "      <th>total_officer_deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>15505.0</td>\n",
       "      <td>1601</td>\n",
       "      <td>64</td>\n",
       "      <td>19144.0</td>\n",
       "      <td>21900.0</td>\n",
       "      <td>1019.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>27756.0</td>\n",
       "      <td>2428</td>\n",
       "      <td>5</td>\n",
       "      <td>5011.0</td>\n",
       "      <td>5143.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>43652.0</td>\n",
       "      <td>12229</td>\n",
       "      <td>44</td>\n",
       "      <td>38248.0</td>\n",
       "      <td>41674.0</td>\n",
       "      <td>2739.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11378</td>\n",
       "      <td>52</td>\n",
       "      <td>14729.0</td>\n",
       "      <td>15886.0</td>\n",
       "      <td>424.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>121207.0</td>\n",
       "      <td>49511</td>\n",
       "      <td>217</td>\n",
       "      <td>110471.0</td>\n",
       "      <td>117767.0</td>\n",
       "      <td>16090.0</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       system  inmate_tests  total_inmate_cases  total_inmate_deaths  \\\n",
       "0     Alabama       15505.0                1601                   64   \n",
       "1      Alaska       27756.0                2428                    5   \n",
       "2     Arizona       43652.0               12229                   44   \n",
       "3    Arkansas           0.0               11378                   52   \n",
       "4  California      121207.0               49511                  217   \n",
       "\n",
       "   latest_inmate_population  max_inmate_population_2020  total_officer_cases  \\\n",
       "0                   19144.0                     21900.0               1019.0   \n",
       "1                    5011.0                      5143.0                 85.0   \n",
       "2                   38248.0                     41674.0               2739.0   \n",
       "3                   14729.0                     15886.0                424.0   \n",
       "4                  110471.0                    117767.0              16090.0   \n",
       "\n",
       "   total_officer_deaths  \n",
       "0                   3.0  \n",
       "1                   0.0  \n",
       "2                   0.0  \n",
       "3                   5.0  \n",
       "4                  26.0  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nyt_df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "69d9070e",
   "metadata": {},
   "outputs": [],
   "source": [
    "nyt_df1.rename(columns={\"system\": \"state\"}, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "2061171e",
   "metadata": {},
   "outputs": [],
   "source": [
    "nyt_df1['state'] = nyt_df1['state'].str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "45869c4b",
   "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>state</th>\n",
       "      <th>inmate_tests</th>\n",
       "      <th>total_inmate_cases</th>\n",
       "      <th>total_inmate_deaths</th>\n",
       "      <th>latest_inmate_population</th>\n",
       "      <th>max_inmate_population_2020</th>\n",
       "      <th>total_officer_cases</th>\n",
       "      <th>total_officer_deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>15505.0</td>\n",
       "      <td>1601</td>\n",
       "      <td>64</td>\n",
       "      <td>19144.0</td>\n",
       "      <td>21900.0</td>\n",
       "      <td>1019.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alaska</td>\n",
       "      <td>27756.0</td>\n",
       "      <td>2428</td>\n",
       "      <td>5</td>\n",
       "      <td>5011.0</td>\n",
       "      <td>5143.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>arizona</td>\n",
       "      <td>43652.0</td>\n",
       "      <td>12229</td>\n",
       "      <td>44</td>\n",
       "      <td>38248.0</td>\n",
       "      <td>41674.0</td>\n",
       "      <td>2739.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>arkansas</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11378</td>\n",
       "      <td>52</td>\n",
       "      <td>14729.0</td>\n",
       "      <td>15886.0</td>\n",
       "      <td>424.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>california</td>\n",
       "      <td>121207.0</td>\n",
       "      <td>49511</td>\n",
       "      <td>217</td>\n",
       "      <td>110471.0</td>\n",
       "      <td>117767.0</td>\n",
       "      <td>16090.0</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        state  inmate_tests  total_inmate_cases  total_inmate_deaths  \\\n",
       "0     alabama       15505.0                1601                   64   \n",
       "1      alaska       27756.0                2428                    5   \n",
       "2     arizona       43652.0               12229                   44   \n",
       "3    arkansas           0.0               11378                   52   \n",
       "4  california      121207.0               49511                  217   \n",
       "\n",
       "   latest_inmate_population  max_inmate_population_2020  total_officer_cases  \\\n",
       "0                   19144.0                     21900.0               1019.0   \n",
       "1                    5011.0                      5143.0                 85.0   \n",
       "2                   38248.0                     41674.0               2739.0   \n",
       "3                   14729.0                     15886.0                424.0   \n",
       "4                  110471.0                    117767.0              16090.0   \n",
       "\n",
       "   total_officer_deaths  \n",
       "0                   3.0  \n",
       "1                   0.0  \n",
       "2                   0.0  \n",
       "3                   5.0  \n",
       "4                  26.0  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nyt_df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "ffd12e75",
   "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>nyt_id</th>\n",
       "      <th>facility_name</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>facility_city</th>\n",
       "      <th>facility_county</th>\n",
       "      <th>facility_county_fips</th>\n",
       "      <th>facility_state</th>\n",
       "      <th>facility_lng</th>\n",
       "      <th>facility_lat</th>\n",
       "      <th>latest_inmate_population</th>\n",
       "      <th>max_inmate_population_2020</th>\n",
       "      <th>total_inmate_cases</th>\n",
       "      <th>total_inmate_deaths</th>\n",
       "      <th>total_officer_cases</th>\n",
       "      <th>total_officer_deaths</th>\n",
       "      <th>note</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>F3EFE858</td>\n",
       "      <td>Alex City Work Release prison</td>\n",
       "      <td>Low-security work release</td>\n",
       "      <td>Alex City</td>\n",
       "      <td>Coosa</td>\n",
       "      <td>1037</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>-86.009015</td>\n",
       "      <td>32.904507</td>\n",
       "      <td>188.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>77</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5B910220</td>\n",
       "      <td>Alabama Therapeutic Education Facility prison</td>\n",
       "      <td>State rehabilitation center</td>\n",
       "      <td>Columbiana</td>\n",
       "      <td>Shelby</td>\n",
       "      <td>1117</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>-86.624067</td>\n",
       "      <td>33.180755</td>\n",
       "      <td>272.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>02FB1675</td>\n",
       "      <td>Bibb Correctional Facility</td>\n",
       "      <td>State prison</td>\n",
       "      <td>Brent</td>\n",
       "      <td>Bibb</td>\n",
       "      <td>1007</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>-87.162781</td>\n",
       "      <td>32.920754</td>\n",
       "      <td>1725.0</td>\n",
       "      <td>1825.0</td>\n",
       "      <td>164</td>\n",
       "      <td>3</td>\n",
       "      <td>61</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6378F6C4</td>\n",
       "      <td>Birmingham Women's Community Based Facility an...</td>\n",
       "      <td>State prison</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>Jefferson</td>\n",
       "      <td>1073</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>-86.808344</td>\n",
       "      <td>33.531101</td>\n",
       "      <td>192.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>EAABF900</td>\n",
       "      <td>Bullock Correctional Facility</td>\n",
       "      <td>State prison</td>\n",
       "      <td>Bessemer</td>\n",
       "      <td>Bullock</td>\n",
       "      <td>1011</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>-85.673927</td>\n",
       "      <td>32.147144</td>\n",
       "      <td>1477.0</td>\n",
       "      <td>1577.0</td>\n",
       "      <td>162</td>\n",
       "      <td>5</td>\n",
       "      <td>80</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     nyt_id                                      facility_name  \\\n",
       "0  F3EFE858                      Alex City Work Release prison   \n",
       "1  5B910220      Alabama Therapeutic Education Facility prison   \n",
       "2  02FB1675                         Bibb Correctional Facility   \n",
       "3  6378F6C4  Birmingham Women's Community Based Facility an...   \n",
       "4  EAABF900                      Bullock Correctional Facility   \n",
       "\n",
       "                 facility_type facility_city facility_county  \\\n",
       "0    Low-security work release     Alex City           Coosa   \n",
       "1  State rehabilitation center    Columbiana          Shelby   \n",
       "2                 State prison         Brent            Bibb   \n",
       "3                 State prison    Birmingham       Jefferson   \n",
       "4                 State prison      Bessemer         Bullock   \n",
       "\n",
       "   facility_county_fips facility_state  facility_lng  facility_lat  \\\n",
       "0                  1037        Alabama    -86.009015     32.904507   \n",
       "1                  1117        Alabama    -86.624067     33.180755   \n",
       "2                  1007        Alabama    -87.162781     32.920754   \n",
       "3                  1073        Alabama    -86.808344     33.531101   \n",
       "4                  1011        Alabama    -85.673927     32.147144   \n",
       "\n",
       "   latest_inmate_population  max_inmate_population_2020  total_inmate_cases  \\\n",
       "0                     188.0                         NaN                  77   \n",
       "1                     272.0                         NaN                  11   \n",
       "2                    1725.0                      1825.0                 164   \n",
       "3                     192.0                         NaN                  17   \n",
       "4                    1477.0                      1577.0                 162   \n",
       "\n",
       "   total_inmate_deaths  total_officer_cases  total_officer_deaths note  \n",
       "0                    0                   17                   0.0  NaN  \n",
       "1                    1                    2                   0.0  NaN  \n",
       "2                    3                   61                   0.0  NaN  \n",
       "3                    0                   28                   0.0  NaN  \n",
       "4                    5                   80                   1.0  NaN  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nyt_df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "3eaff860",
   "metadata": {},
   "outputs": [],
   "source": [
    "nyt_df2.rename(columns={\"facility_state\": \"state\"}, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "4d23733d",
   "metadata": {},
   "outputs": [],
   "source": [
    "nyt_df2['state'] = nyt_df2['state'].str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "dfab782b",
   "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>nyt_id</th>\n",
       "      <th>facility_name</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>facility_city</th>\n",
       "      <th>facility_county</th>\n",
       "      <th>facility_county_fips</th>\n",
       "      <th>state</th>\n",
       "      <th>facility_lng</th>\n",
       "      <th>facility_lat</th>\n",
       "      <th>latest_inmate_population</th>\n",
       "      <th>max_inmate_population_2020</th>\n",
       "      <th>total_inmate_cases</th>\n",
       "      <th>total_inmate_deaths</th>\n",
       "      <th>total_officer_cases</th>\n",
       "      <th>total_officer_deaths</th>\n",
       "      <th>note</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>F3EFE858</td>\n",
       "      <td>Alex City Work Release prison</td>\n",
       "      <td>Low-security work release</td>\n",
       "      <td>Alex City</td>\n",
       "      <td>Coosa</td>\n",
       "      <td>1037</td>\n",
       "      <td>alabama</td>\n",
       "      <td>-86.009015</td>\n",
       "      <td>32.904507</td>\n",
       "      <td>188.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>77</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5B910220</td>\n",
       "      <td>Alabama Therapeutic Education Facility prison</td>\n",
       "      <td>State rehabilitation center</td>\n",
       "      <td>Columbiana</td>\n",
       "      <td>Shelby</td>\n",
       "      <td>1117</td>\n",
       "      <td>alabama</td>\n",
       "      <td>-86.624067</td>\n",
       "      <td>33.180755</td>\n",
       "      <td>272.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>02FB1675</td>\n",
       "      <td>Bibb Correctional Facility</td>\n",
       "      <td>State prison</td>\n",
       "      <td>Brent</td>\n",
       "      <td>Bibb</td>\n",
       "      <td>1007</td>\n",
       "      <td>alabama</td>\n",
       "      <td>-87.162781</td>\n",
       "      <td>32.920754</td>\n",
       "      <td>1725.0</td>\n",
       "      <td>1825.0</td>\n",
       "      <td>164</td>\n",
       "      <td>3</td>\n",
       "      <td>61</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6378F6C4</td>\n",
       "      <td>Birmingham Women's Community Based Facility an...</td>\n",
       "      <td>State prison</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>Jefferson</td>\n",
       "      <td>1073</td>\n",
       "      <td>alabama</td>\n",
       "      <td>-86.808344</td>\n",
       "      <td>33.531101</td>\n",
       "      <td>192.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>EAABF900</td>\n",
       "      <td>Bullock Correctional Facility</td>\n",
       "      <td>State prison</td>\n",
       "      <td>Bessemer</td>\n",
       "      <td>Bullock</td>\n",
       "      <td>1011</td>\n",
       "      <td>alabama</td>\n",
       "      <td>-85.673927</td>\n",
       "      <td>32.147144</td>\n",
       "      <td>1477.0</td>\n",
       "      <td>1577.0</td>\n",
       "      <td>162</td>\n",
       "      <td>5</td>\n",
       "      <td>80</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     nyt_id                                      facility_name  \\\n",
       "0  F3EFE858                      Alex City Work Release prison   \n",
       "1  5B910220      Alabama Therapeutic Education Facility prison   \n",
       "2  02FB1675                         Bibb Correctional Facility   \n",
       "3  6378F6C4  Birmingham Women's Community Based Facility an...   \n",
       "4  EAABF900                      Bullock Correctional Facility   \n",
       "\n",
       "                 facility_type facility_city facility_county  \\\n",
       "0    Low-security work release     Alex City           Coosa   \n",
       "1  State rehabilitation center    Columbiana          Shelby   \n",
       "2                 State prison         Brent            Bibb   \n",
       "3                 State prison    Birmingham       Jefferson   \n",
       "4                 State prison      Bessemer         Bullock   \n",
       "\n",
       "   facility_county_fips    state  facility_lng  facility_lat  \\\n",
       "0                  1037  alabama    -86.009015     32.904507   \n",
       "1                  1117  alabama    -86.624067     33.180755   \n",
       "2                  1007  alabama    -87.162781     32.920754   \n",
       "3                  1073  alabama    -86.808344     33.531101   \n",
       "4                  1011  alabama    -85.673927     32.147144   \n",
       "\n",
       "   latest_inmate_population  max_inmate_population_2020  total_inmate_cases  \\\n",
       "0                     188.0                         NaN                  77   \n",
       "1                     272.0                         NaN                  11   \n",
       "2                    1725.0                      1825.0                 164   \n",
       "3                     192.0                         NaN                  17   \n",
       "4                    1477.0                      1577.0                 162   \n",
       "\n",
       "   total_inmate_deaths  total_officer_cases  total_officer_deaths note  \n",
       "0                    0                   17                   0.0  NaN  \n",
       "1                    1                    2                   0.0  NaN  \n",
       "2                    3                   61                   0.0  NaN  \n",
       "3                    0                   28                   0.0  NaN  \n",
       "4                    5                   80                   1.0  NaN  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nyt_df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "eebc35b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "nyt_df1.to_csv(\"total-prison-covid-stats-by-state-nyt.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "4a106324",
   "metadata": {},
   "outputs": [],
   "source": [
    "nyt_df2.to_csv(\"prison-facilities-nyt.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9e27b24",
   "metadata": {},
   "source": [
    "-------------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee13e8ab",
   "metadata": {},
   "source": [
    "# AP-marshall-project-data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "26a3192e",
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_file1 = \"Resources/prisons-covid-AP-marshall-project-data/covid_prison_cases.csv\"\n",
    "ap_data_df1_cases = pd.read_csv(csv_file1)\n",
    "\n",
    "csv_file2 = \"Resources/prisons-covid-AP-marshall-project-data/covid_prison_rates.csv\"\n",
    "ap_data_df2_rates = pd.read_csv(csv_file2)\n",
    "\n",
    "csv_file3 = \"Resources/prisons-covid-AP-marshall-project-data/prison_populations.csv\"\n",
    "ap_data_df3_prisonpop = pd.read_csv(csv_file3)\n",
    "\n",
    "csv_file4 = \"Resources/prisons-covid-AP-marshall-project-data/staff_populations.csv\"\n",
    "ap_data_df4_staffpop = pd.read_csv(csv_file4, dtype = {'state':str, \n",
    "                                                        'abbreviation':str, \n",
    "                                                        'pop':float, \n",
    "                                                        'Notes':str})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "832c6f65",
   "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>name</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>staff_tests</th>\n",
       "      <th>staff_tests_with_multiples</th>\n",
       "      <th>total_staff_cases</th>\n",
       "      <th>staff_recovered</th>\n",
       "      <th>total_staff_deaths</th>\n",
       "      <th>staff_partial_dose</th>\n",
       "      <th>staff_full_dose</th>\n",
       "      <th>prisoner_tests</th>\n",
       "      <th>prisoner_tests_with_multiples</th>\n",
       "      <th>total_prisoner_cases</th>\n",
       "      <th>prisoners_recovered</th>\n",
       "      <th>total_prisoner_deaths</th>\n",
       "      <th>prisoners_partial_dose</th>\n",
       "      <th>prisoners_full_dose</th>\n",
       "      <th>as_of_date</th>\n",
       "      <th>notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1058.0</td>\n",
       "      <td>1048.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>871.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16743.0</td>\n",
       "      <td>1662.0</td>\n",
       "      <td>1595.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>10811.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>06/25/2021</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>332.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38707.0</td>\n",
       "      <td>2440.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3460.0</td>\n",
       "      <td>3097.0</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>vaccines as of June 23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2787.0</td>\n",
       "      <td>2778.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47899.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12328.0</td>\n",
       "      <td>12251.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>AR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11425.0</td>\n",
       "      <td>11353.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>06/23/2021</td>\n",
       "      <td>We have been told vaccinations have begun in t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>CA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35295.0</td>\n",
       "      <td>33474.0</td>\n",
       "      <td>129415.0</td>\n",
       "      <td>1602906.0</td>\n",
       "      <td>49395.0</td>\n",
       "      <td>48517.0</td>\n",
       "      <td>227.0</td>\n",
       "      <td>71317.0</td>\n",
       "      <td>69400.0</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         name abbreviation  staff_tests  staff_tests_with_multiples  \\\n",
       "0     Alabama           AL          NaN                         NaN   \n",
       "1      Alaska           AK          NaN                         NaN   \n",
       "2     Arizona           AZ          NaN                         NaN   \n",
       "3    Arkansas           AR          NaN                         NaN   \n",
       "4  California           CA          NaN                         NaN   \n",
       "\n",
       "   total_staff_cases  staff_recovered  total_staff_deaths  staff_partial_dose  \\\n",
       "0             1058.0           1048.0                 3.0               871.0   \n",
       "1              332.0              NaN                 0.0                 NaN   \n",
       "2             2787.0           2778.0                 NaN                 NaN   \n",
       "3                NaN              NaN                 4.0                 NaN   \n",
       "4                NaN              NaN                 NaN             35295.0   \n",
       "\n",
       "   staff_full_dose  prisoner_tests  prisoner_tests_with_multiples  \\\n",
       "0              NaN             NaN                        16743.0   \n",
       "1              NaN             NaN                        38707.0   \n",
       "2              NaN         47899.0                            NaN   \n",
       "3              NaN             NaN                            NaN   \n",
       "4          33474.0        129415.0                      1602906.0   \n",
       "\n",
       "   total_prisoner_cases  prisoners_recovered  total_prisoner_deaths  \\\n",
       "0                1662.0               1595.0                   66.0   \n",
       "1                2440.0                  NaN                    5.0   \n",
       "2               12328.0              12251.0                   65.0   \n",
       "3               11425.0              11353.0                   52.0   \n",
       "4               49395.0              48517.0                  227.0   \n",
       "\n",
       "   prisoners_partial_dose  prisoners_full_dose  as_of_date  \\\n",
       "0                 10811.0                  NaN  06/25/2021   \n",
       "1                  3460.0               3097.0  06/22/2021   \n",
       "2                     NaN                  NaN  06/22/2021   \n",
       "3                     NaN                  NaN  06/23/2021   \n",
       "4                 71317.0              69400.0  06/22/2021   \n",
       "\n",
       "                                               notes  \n",
       "0                                                NaN  \n",
       "1                             vaccines as of June 23  \n",
       "2                                                NaN  \n",
       "3  We have been told vaccinations have begun in t...  \n",
       "4                                                NaN  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ap_data_df1_cases.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "cb158952",
   "metadata": {
    "scrolled": true
   },
   "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>state</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>staff_tests</th>\n",
       "      <th>staff_tests_with_multiples</th>\n",
       "      <th>total_staff_cases</th>\n",
       "      <th>staff_recovered</th>\n",
       "      <th>total_staff_deaths</th>\n",
       "      <th>staff_partial_dose</th>\n",
       "      <th>staff_full_dose</th>\n",
       "      <th>prisoner_tests</th>\n",
       "      <th>prisoner_tests_with_multiples</th>\n",
       "      <th>total_prisoner_cases</th>\n",
       "      <th>prisoners_recovered</th>\n",
       "      <th>total_prisoner_deaths</th>\n",
       "      <th>prisoners_partial_dose</th>\n",
       "      <th>prisoners_full_dose</th>\n",
       "      <th>as_of_date</th>\n",
       "      <th>notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1058.0</td>\n",
       "      <td>1048.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>871.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16743.0</td>\n",
       "      <td>1662.0</td>\n",
       "      <td>1595.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>10811.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>06/25/2021</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>332.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38707.0</td>\n",
       "      <td>2440.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3460.0</td>\n",
       "      <td>3097.0</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>vaccines as of June 23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2787.0</td>\n",
       "      <td>2778.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47899.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12328.0</td>\n",
       "      <td>12251.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>arkansas</td>\n",
       "      <td>AR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11425.0</td>\n",
       "      <td>11353.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>06/23/2021</td>\n",
       "      <td>We have been told vaccinations have begun in t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>california</td>\n",
       "      <td>CA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35295.0</td>\n",
       "      <td>33474.0</td>\n",
       "      <td>129415.0</td>\n",
       "      <td>1602906.0</td>\n",
       "      <td>49395.0</td>\n",
       "      <td>48517.0</td>\n",
       "      <td>227.0</td>\n",
       "      <td>71317.0</td>\n",
       "      <td>69400.0</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        state abbreviation  staff_tests  staff_tests_with_multiples  \\\n",
       "0     alabama           AL          NaN                         NaN   \n",
       "1      alaska           AK          NaN                         NaN   \n",
       "2     arizona           AZ          NaN                         NaN   \n",
       "3    arkansas           AR          NaN                         NaN   \n",
       "4  california           CA          NaN                         NaN   \n",
       "\n",
       "   total_staff_cases  staff_recovered  total_staff_deaths  staff_partial_dose  \\\n",
       "0             1058.0           1048.0                 3.0               871.0   \n",
       "1              332.0              NaN                 0.0                 NaN   \n",
       "2             2787.0           2778.0                 NaN                 NaN   \n",
       "3                NaN              NaN                 4.0                 NaN   \n",
       "4                NaN              NaN                 NaN             35295.0   \n",
       "\n",
       "   staff_full_dose  prisoner_tests  prisoner_tests_with_multiples  \\\n",
       "0              NaN             NaN                        16743.0   \n",
       "1              NaN             NaN                        38707.0   \n",
       "2              NaN         47899.0                            NaN   \n",
       "3              NaN             NaN                            NaN   \n",
       "4          33474.0        129415.0                      1602906.0   \n",
       "\n",
       "   total_prisoner_cases  prisoners_recovered  total_prisoner_deaths  \\\n",
       "0                1662.0               1595.0                   66.0   \n",
       "1                2440.0                  NaN                    5.0   \n",
       "2               12328.0              12251.0                   65.0   \n",
       "3               11425.0              11353.0                   52.0   \n",
       "4               49395.0              48517.0                  227.0   \n",
       "\n",
       "   prisoners_partial_dose  prisoners_full_dose  as_of_date  \\\n",
       "0                 10811.0                  NaN  06/25/2021   \n",
       "1                  3460.0               3097.0  06/22/2021   \n",
       "2                     NaN                  NaN  06/22/2021   \n",
       "3                     NaN                  NaN  06/23/2021   \n",
       "4                 71317.0              69400.0  06/22/2021   \n",
       "\n",
       "                                               notes  \n",
       "0                                                NaN  \n",
       "1                             vaccines as of June 23  \n",
       "2                                                NaN  \n",
       "3  We have been told vaccinations have begun in t...  \n",
       "4                                                NaN  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ap_data_df1_cases.rename(columns={\"name\": \"state\"}, inplace = True)\n",
    "ap_data_df1_cases['state'] = ap_data_df1_cases['state'].str.lower()\n",
    "ap_data_df1_cases.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "60b0c785",
   "metadata": {},
   "outputs": [],
   "source": [
    "ap_data_df1_cases['as_of_date'] = pd.to_datetime(ap_data_df1_cases['as_of_date'],\n",
    "                                                    infer_datetime_format=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "bc7b4853",
   "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>name</th>\n",
       "      <th>latest_week</th>\n",
       "      <th>cumulative_prisoner_cases</th>\n",
       "      <th>prisoner_cases_pct</th>\n",
       "      <th>prisoner_case_rate</th>\n",
       "      <th>cumulative_prisoner_deaths</th>\n",
       "      <th>prisoner_deaths_pct</th>\n",
       "      <th>prisoner_death_rate</th>\n",
       "      <th>cumulative_staff_cases</th>\n",
       "      <th>staff_cases_pct</th>\n",
       "      <th>staff_case_rate</th>\n",
       "      <th>cumulative_staff_deaths</th>\n",
       "      <th>staff_deaths_pct</th>\n",
       "      <th>staff_death_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>1662</td>\n",
       "      <td>0.078716</td>\n",
       "      <td>1 in 13 prisoners</td>\n",
       "      <td>66</td>\n",
       "      <td>0.003126</td>\n",
       "      <td>1 in 320 prisoners</td>\n",
       "      <td>1058</td>\n",
       "      <td>0.279008</td>\n",
       "      <td>1 in 4 staff</td>\n",
       "      <td>3</td>\n",
       "      <td>0.000791</td>\n",
       "      <td>1 in 1264 staff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>2440</td>\n",
       "      <td>0.510888</td>\n",
       "      <td>1 in 2 prisoners</td>\n",
       "      <td>5</td>\n",
       "      <td>0.001047</td>\n",
       "      <td>1 in 955 prisoners</td>\n",
       "      <td>332</td>\n",
       "      <td>0.224173</td>\n",
       "      <td>1 in 4 staff</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>12328</td>\n",
       "      <td>0.291029</td>\n",
       "      <td>2 in 7 prisoners</td>\n",
       "      <td>65</td>\n",
       "      <td>0.001534</td>\n",
       "      <td>1 in 652 prisoners</td>\n",
       "      <td>2787</td>\n",
       "      <td>0.306432</td>\n",
       "      <td>1 in 3 staff</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000110</td>\n",
       "      <td>1 in 9095 staff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>11425</td>\n",
       "      <td>0.652783</td>\n",
       "      <td>2 in 3 prisoners</td>\n",
       "      <td>52</td>\n",
       "      <td>0.002971</td>\n",
       "      <td>1 in 337 prisoners</td>\n",
       "      <td>813</td>\n",
       "      <td>0.148901</td>\n",
       "      <td>1 in 7 staff</td>\n",
       "      <td>4</td>\n",
       "      <td>0.000733</td>\n",
       "      <td>1 in 1365 staff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>49395</td>\n",
       "      <td>0.419886</td>\n",
       "      <td>2 in 5 prisoners</td>\n",
       "      <td>227</td>\n",
       "      <td>0.001930</td>\n",
       "      <td>1 in 518 prisoners</td>\n",
       "      <td>17002</td>\n",
       "      <td>0.267870</td>\n",
       "      <td>1 in 4 staff</td>\n",
       "      <td>28</td>\n",
       "      <td>0.000441</td>\n",
       "      <td>1 in 2267 staff</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         name latest_week  cumulative_prisoner_cases  prisoner_cases_pct  \\\n",
       "0     Alabama  06/22/2021                       1662            0.078716   \n",
       "1      Alaska  06/22/2021                       2440            0.510888   \n",
       "2     Arizona  06/22/2021                      12328            0.291029   \n",
       "3    Arkansas  06/22/2021                      11425            0.652783   \n",
       "4  California  06/22/2021                      49395            0.419886   \n",
       "\n",
       "  prisoner_case_rate  cumulative_prisoner_deaths  prisoner_deaths_pct  \\\n",
       "0  1 in 13 prisoners                          66             0.003126   \n",
       "1   1 in 2 prisoners                           5             0.001047   \n",
       "2   2 in 7 prisoners                          65             0.001534   \n",
       "3   2 in 3 prisoners                          52             0.002971   \n",
       "4   2 in 5 prisoners                         227             0.001930   \n",
       "\n",
       "  prisoner_death_rate  cumulative_staff_cases  staff_cases_pct  \\\n",
       "0  1 in 320 prisoners                    1058         0.279008   \n",
       "1  1 in 955 prisoners                     332         0.224173   \n",
       "2  1 in 652 prisoners                    2787         0.306432   \n",
       "3  1 in 337 prisoners                     813         0.148901   \n",
       "4  1 in 518 prisoners                   17002         0.267870   \n",
       "\n",
       "  staff_case_rate  cumulative_staff_deaths  staff_deaths_pct staff_death_rate  \n",
       "0    1 in 4 staff                        3          0.000791  1 in 1264 staff  \n",
       "1    1 in 4 staff                        0          0.000000                0  \n",
       "2    1 in 3 staff                        1          0.000110  1 in 9095 staff  \n",
       "3    1 in 7 staff                        4          0.000733  1 in 1365 staff  \n",
       "4    1 in 4 staff                       28          0.000441  1 in 2267 staff  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ap_data_df2_rates.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "0c0bc977",
   "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>state</th>\n",
       "      <th>latest_week</th>\n",
       "      <th>cumulative_prisoner_cases</th>\n",
       "      <th>prisoner_cases_pct</th>\n",
       "      <th>prisoner_case_rate</th>\n",
       "      <th>cumulative_prisoner_deaths</th>\n",
       "      <th>prisoner_deaths_pct</th>\n",
       "      <th>prisoner_death_rate</th>\n",
       "      <th>cumulative_staff_cases</th>\n",
       "      <th>staff_cases_pct</th>\n",
       "      <th>staff_case_rate</th>\n",
       "      <th>cumulative_staff_deaths</th>\n",
       "      <th>staff_deaths_pct</th>\n",
       "      <th>staff_death_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>1662</td>\n",
       "      <td>0.078716</td>\n",
       "      <td>1 in 13 prisoners</td>\n",
       "      <td>66</td>\n",
       "      <td>0.003126</td>\n",
       "      <td>1 in 320 prisoners</td>\n",
       "      <td>1058</td>\n",
       "      <td>0.279008</td>\n",
       "      <td>1 in 4 staff</td>\n",
       "      <td>3</td>\n",
       "      <td>0.000791</td>\n",
       "      <td>1 in 1264 staff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alaska</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>2440</td>\n",
       "      <td>0.510888</td>\n",
       "      <td>1 in 2 prisoners</td>\n",
       "      <td>5</td>\n",
       "      <td>0.001047</td>\n",
       "      <td>1 in 955 prisoners</td>\n",
       "      <td>332</td>\n",
       "      <td>0.224173</td>\n",
       "      <td>1 in 4 staff</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>arizona</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>12328</td>\n",
       "      <td>0.291029</td>\n",
       "      <td>2 in 7 prisoners</td>\n",
       "      <td>65</td>\n",
       "      <td>0.001534</td>\n",
       "      <td>1 in 652 prisoners</td>\n",
       "      <td>2787</td>\n",
       "      <td>0.306432</td>\n",
       "      <td>1 in 3 staff</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000110</td>\n",
       "      <td>1 in 9095 staff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>arkansas</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>11425</td>\n",
       "      <td>0.652783</td>\n",
       "      <td>2 in 3 prisoners</td>\n",
       "      <td>52</td>\n",
       "      <td>0.002971</td>\n",
       "      <td>1 in 337 prisoners</td>\n",
       "      <td>813</td>\n",
       "      <td>0.148901</td>\n",
       "      <td>1 in 7 staff</td>\n",
       "      <td>4</td>\n",
       "      <td>0.000733</td>\n",
       "      <td>1 in 1365 staff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>california</td>\n",
       "      <td>06/22/2021</td>\n",
       "      <td>49395</td>\n",
       "      <td>0.419886</td>\n",
       "      <td>2 in 5 prisoners</td>\n",
       "      <td>227</td>\n",
       "      <td>0.001930</td>\n",
       "      <td>1 in 518 prisoners</td>\n",
       "      <td>17002</td>\n",
       "      <td>0.267870</td>\n",
       "      <td>1 in 4 staff</td>\n",
       "      <td>28</td>\n",
       "      <td>0.000441</td>\n",
       "      <td>1 in 2267 staff</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        state latest_week  cumulative_prisoner_cases  prisoner_cases_pct  \\\n",
       "0     alabama  06/22/2021                       1662            0.078716   \n",
       "1      alaska  06/22/2021                       2440            0.510888   \n",
       "2     arizona  06/22/2021                      12328            0.291029   \n",
       "3    arkansas  06/22/2021                      11425            0.652783   \n",
       "4  california  06/22/2021                      49395            0.419886   \n",
       "\n",
       "  prisoner_case_rate  cumulative_prisoner_deaths  prisoner_deaths_pct  \\\n",
       "0  1 in 13 prisoners                          66             0.003126   \n",
       "1   1 in 2 prisoners                           5             0.001047   \n",
       "2   2 in 7 prisoners                          65             0.001534   \n",
       "3   2 in 3 prisoners                          52             0.002971   \n",
       "4   2 in 5 prisoners                         227             0.001930   \n",
       "\n",
       "  prisoner_death_rate  cumulative_staff_cases  staff_cases_pct  \\\n",
       "0  1 in 320 prisoners                    1058         0.279008   \n",
       "1  1 in 955 prisoners                     332         0.224173   \n",
       "2  1 in 652 prisoners                    2787         0.306432   \n",
       "3  1 in 337 prisoners                     813         0.148901   \n",
       "4  1 in 518 prisoners                   17002         0.267870   \n",
       "\n",
       "  staff_case_rate  cumulative_staff_deaths  staff_deaths_pct staff_death_rate  \n",
       "0    1 in 4 staff                        3          0.000791  1 in 1264 staff  \n",
       "1    1 in 4 staff                        0          0.000000                0  \n",
       "2    1 in 3 staff                        1          0.000110  1 in 9095 staff  \n",
       "3    1 in 7 staff                        4          0.000733  1 in 1365 staff  \n",
       "4    1 in 4 staff                       28          0.000441  1 in 2267 staff  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ap_data_df2_rates.rename(columns={\"name\": \"state\"}, inplace = True)\n",
    "ap_data_df2_rates['state'] = ap_data_df2_rates['state'].str.lower()\n",
    "ap_data_df2_rates.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "acba58b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "#coverting latest_week column datatype to date using datetime\n",
    "ap_data_df2_rates['latest_week'] = pd.to_datetime(ap_data_df2_rates['latest_week'],\n",
    "                                                    infer_datetime_format=True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "5cd5ab18",
   "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>name</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>month</th>\n",
       "      <th>as_of_date</th>\n",
       "      <th>pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>march</td>\n",
       "      <td>03/31/2020</td>\n",
       "      <td>21114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>april</td>\n",
       "      <td>04/30/2020</td>\n",
       "      <td>20655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>june</td>\n",
       "      <td>06/30/2020</td>\n",
       "      <td>19752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>july</td>\n",
       "      <td>07/31/2020</td>\n",
       "      <td>19342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>aug</td>\n",
       "      <td>08/31/2020</td>\n",
       "      <td>18901</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      name abbreviation  month  as_of_date    pop\n",
       "0  Alabama           AL  march  03/31/2020  21114\n",
       "1  Alabama           AL  april  04/30/2020  20655\n",
       "2  Alabama           AL   june  06/30/2020  19752\n",
       "3  Alabama           AL   july  07/31/2020  19342\n",
       "4  Alabama           AL    aug  08/31/2020  18901"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ap_data_df3_prisonpop.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "c9fd1762",
   "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>state</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>month</th>\n",
       "      <th>as_of_date</th>\n",
       "      <th>pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>march</td>\n",
       "      <td>03/31/2020</td>\n",
       "      <td>21114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>april</td>\n",
       "      <td>04/30/2020</td>\n",
       "      <td>20655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>june</td>\n",
       "      <td>06/30/2020</td>\n",
       "      <td>19752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>july</td>\n",
       "      <td>07/31/2020</td>\n",
       "      <td>19342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>aug</td>\n",
       "      <td>08/31/2020</td>\n",
       "      <td>18901</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     state abbreviation  month  as_of_date    pop\n",
       "0  alabama           AL  march  03/31/2020  21114\n",
       "1  alabama           AL  april  04/30/2020  20655\n",
       "2  alabama           AL   june  06/30/2020  19752\n",
       "3  alabama           AL   july  07/31/2020  19342\n",
       "4  alabama           AL    aug  08/31/2020  18901"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ap_data_df3_prisonpop.rename(columns={\"name\": \"state\"}, inplace = True)\n",
    "ap_data_df3_prisonpop['state'] = ap_data_df3_prisonpop['state'].str.lower()\n",
    "ap_data_df3_prisonpop.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "67de96e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#coverting latest_week column datatype to date using datetime\n",
    "ap_data_df3_prisonpop['as_of_date'] = pd.to_datetime(ap_data_df3_prisonpop['as_of_date'],\n",
    "                                                    infer_datetime_format=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "76866072",
   "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>name</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>month</th>\n",
       "      <th>as_of_date</th>\n",
       "      <th>pop</th>\n",
       "      <th>Notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>march</td>\n",
       "      <td>03/31/2020</td>\n",
       "      <td>3686.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>july</td>\n",
       "      <td>06/30/2020</td>\n",
       "      <td>3792.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>march</td>\n",
       "      <td>03/31/2020</td>\n",
       "      <td>1456.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>july</td>\n",
       "      <td>07/31/2020</td>\n",
       "      <td>1481.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>july</td>\n",
       "      <td>08/01/2020</td>\n",
       "      <td>9095.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      name abbreviation  month  as_of_date     pop Notes\n",
       "0  Alabama           AL  march  03/31/2020  3686.0   NaN\n",
       "1  Alabama           AL   july  06/30/2020  3792.0   NaN\n",
       "2   Alaska           AK  march  03/31/2020  1456.0   NaN\n",
       "3   Alaska           AK   july  07/31/2020  1481.0   NaN\n",
       "4  Arizona           AZ   july  08/01/2020  9095.0   NaN"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ap_data_df4_staffpop.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "a09c3761",
   "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>state</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>month</th>\n",
       "      <th>as_of_date</th>\n",
       "      <th>pop</th>\n",
       "      <th>Notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>march</td>\n",
       "      <td>03/31/2020</td>\n",
       "      <td>3686.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>july</td>\n",
       "      <td>06/30/2020</td>\n",
       "      <td>3792.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>march</td>\n",
       "      <td>03/31/2020</td>\n",
       "      <td>1456.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>july</td>\n",
       "      <td>07/31/2020</td>\n",
       "      <td>1481.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>july</td>\n",
       "      <td>08/01/2020</td>\n",
       "      <td>9095.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     state abbreviation  month  as_of_date     pop Notes\n",
       "0  alabama           AL  march  03/31/2020  3686.0   NaN\n",
       "1  alabama           AL   july  06/30/2020  3792.0   NaN\n",
       "2   alaska           AK  march  03/31/2020  1456.0   NaN\n",
       "3   alaska           AK   july  07/31/2020  1481.0   NaN\n",
       "4  arizona           AZ   july  08/01/2020  9095.0   NaN"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ap_data_df4_staffpop.rename(columns={\"name\": \"state\"}, inplace = True)\n",
    "ap_data_df4_staffpop['state'] = ap_data_df4_staffpop['state'].str.lower()\n",
    "\n",
    "ap_data_df4_staffpop.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "62a74114",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Coverting as_of_date into datetime data type\n",
    "ap_data_df4_staffpop['as_of_date'] = pd.to_datetime(ap_data_df4_staffpop['as_of_date'],\n",
    "                                                    infer_datetime_format=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "3860c3bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "ap_data_df1_cases.to_csv(\"covid_prison_cases_apm.csv\")\n",
    "ap_data_df2_rates.to_csv(\"covid_prison_rates_apm.csv\")\n",
    "ap_data_df3_prisonpop.to_csv(\"prison_populations_apm.csv\")\n",
    "ap_data_df4_staffpop.to_csv(\"staff_populations_apm.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "1fda82e9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "state                                 object\n",
       "latest_week                   datetime64[ns]\n",
       "cumulative_prisoner_cases              int64\n",
       "prisoner_cases_pct                   float64\n",
       "prisoner_case_rate                    object\n",
       "cumulative_prisoner_deaths             int64\n",
       "prisoner_deaths_pct                  float64\n",
       "prisoner_death_rate                   object\n",
       "cumulative_staff_cases                 int64\n",
       "staff_cases_pct                      float64\n",
       "staff_case_rate                       object\n",
       "cumulative_staff_deaths                int64\n",
       "staff_deaths_pct                     float64\n",
       "staff_death_rate                      object\n",
       "dtype: object"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ap_data_df2_rates.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "573dfb71",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "state                   object\n",
       "abbreviation            object\n",
       "month                   object\n",
       "as_of_date      datetime64[ns]\n",
       "pop                    float64\n",
       "Notes                   object\n",
       "dtype: object"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ap_data_df4_staffpop.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84cb063b",
   "metadata": {},
   "source": [
    "* There are only 50 states in the USA, this is less than the number of rows on the state_covid_data df * therefore some rows in the state_covid_data df might need to be removed as these show data from US territories not states\n",
    "* howver data from the row with index 59 might be important as it shows data from federal prison which might be important in this project"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24a4eb69",
   "metadata": {},
   "source": [
    "--------------------------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e935aa7e",
   "metadata": {},
   "source": [
    "# LOAD - DB"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c774d31",
   "metadata": {},
   "source": [
    "## our 8 tables\n",
    "* state_abbreviations_df\n",
    "* national_covid_stats\n",
    "* nyt_df1\n",
    "* nyt_df2\n",
    "* ap_data_df1_cases\n",
    "* ap_data_df2_rates\n",
    "* ap_data_df3_prisonpop\n",
    "* ap_data_df4_staffpop"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "31809efd",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "import pandas.io.sql as sqlio\n",
    "import psycopg2 as ps\n",
    "from pg_keys import pg_key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "7106060d",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(f\"postgresql://postgres:{pg_key}@localhost/covid-in-us-prisons\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "d6579433",
   "metadata": {},
   "outputs": [],
   "source": [
    "con=engine.connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea750266",
   "metadata": {},
   "source": [
    "## loading SS data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "c50a6927",
   "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>state</th>\n",
       "      <th>abbreviation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alaska</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>american samoa</td>\n",
       "      <td>AS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>arizona</td>\n",
       "      <td>AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>arkansas</td>\n",
       "      <td>AR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            state abbreviation\n",
       "0         alabama           AL\n",
       "1          alaska           AK\n",
       "2  american samoa           AS\n",
       "3         arizona           AZ\n",
       "4        arkansas           AR"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_abbreviations_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "72d3167d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# state_abbreviations_df.to_sql(\"state_abbreviation\", \n",
    "#                              con = engine, \n",
    "#                                if_exists = 'replace', \n",
    "#                                 index = False,\n",
    "#                             )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dfc1ead0",
   "metadata": {},
   "source": [
    "## loading worldometer data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "28b7433f",
   "metadata": {
    "scrolled": false
   },
   "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>state</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>TotalCases</th>\n",
       "      <th>TotalDeaths</th>\n",
       "      <th>Tot Cases/1M pop</th>\n",
       "      <th>TotalTests</th>\n",
       "      <th>Tests_per_1M_pop</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>1312437.0</td>\n",
       "      <td>19658.0</td>\n",
       "      <td>267670.0</td>\n",
       "      <td>7698101.0</td>\n",
       "      <td>1570021.0</td>\n",
       "      <td>4903185.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>251425.0</td>\n",
       "      <td>1252.0</td>\n",
       "      <td>343690.0</td>\n",
       "      <td>4107614.0</td>\n",
       "      <td>5614985.0</td>\n",
       "      <td>731545.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>american samoa</td>\n",
       "      <td>AS</td>\n",
       "      <td>6130.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16964.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>2049627.0</td>\n",
       "      <td>30299.0</td>\n",
       "      <td>281592.0</td>\n",
       "      <td>19534476.0</td>\n",
       "      <td>2683780.0</td>\n",
       "      <td>7278717.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>arkansas</td>\n",
       "      <td>AR</td>\n",
       "      <td>842439.0</td>\n",
       "      <td>11471.0</td>\n",
       "      <td>279156.0</td>\n",
       "      <td>6928561.0</td>\n",
       "      <td>2295895.0</td>\n",
       "      <td>3017804.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            state abbreviation  TotalCases  TotalDeaths  Tot Cases/1M pop  \\\n",
       "0         alabama           AL   1312437.0      19658.0          267670.0   \n",
       "1          alaska           AK    251425.0       1252.0          343690.0   \n",
       "2  american samoa           AS      6130.0         31.0               NaN   \n",
       "3         arizona           AZ   2049627.0      30299.0          281592.0   \n",
       "4        arkansas           AR    842439.0      11471.0          279156.0   \n",
       "\n",
       "   TotalTests  Tests_per_1M_pop  Population  \n",
       "0   7698101.0         1570021.0   4903185.0  \n",
       "1   4107614.0         5614985.0    731545.0  \n",
       "2     16964.0               NaN         NaN  \n",
       "3  19534476.0         2683780.0   7278717.0  \n",
       "4   6928561.0         2295895.0   3017804.0  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_covid_datadf_left.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "a3795d03",
   "metadata": {},
   "outputs": [],
   "source": [
    "state_covid_datadf_left.rename(columns={\"Tot Cases/1M pop\": \"Tot_Cases_1M_pop\"}, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "f3ef6b7c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['state', 'abbreviation', 'TotalCases', 'TotalDeaths',\n",
       "       'Tot Cases/1M pop', 'TotalTests', 'Tests_per_1M_pop', 'Population'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_covid_datadf_left.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "5d87299c",
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_file_ss = \"Clean-Data/national-covid-statistics-us.csv\"\n",
    "national_covid_stats = pd.read_csv(csv_file_ss)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "7f3f0624",
   "metadata": {},
   "outputs": [],
   "source": [
    "national_covid_stats.drop(columns=\"Unnamed: 0\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "4a32f113",
   "metadata": {
    "scrolled": true
   },
   "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>state</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>TotalCases</th>\n",
       "      <th>TotalDeaths</th>\n",
       "      <th>Tot_Cases_1M_pop</th>\n",
       "      <th>TotalTests</th>\n",
       "      <th>Tests_per_1M_pop</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>1312437.0</td>\n",
       "      <td>19658.0</td>\n",
       "      <td>267670.0</td>\n",
       "      <td>7693519.0</td>\n",
       "      <td>1569086.0</td>\n",
       "      <td>4903185.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>251425.0</td>\n",
       "      <td>1252.0</td>\n",
       "      <td>343690.0</td>\n",
       "      <td>4107614.0</td>\n",
       "      <td>5614985.0</td>\n",
       "      <td>731545.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>american samoa</td>\n",
       "      <td>AS</td>\n",
       "      <td>6130.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16964.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>2049627.0</td>\n",
       "      <td>30299.0</td>\n",
       "      <td>281592.0</td>\n",
       "      <td>19534476.0</td>\n",
       "      <td>2683780.0</td>\n",
       "      <td>7278717.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>arkansas</td>\n",
       "      <td>AR</td>\n",
       "      <td>842439.0</td>\n",
       "      <td>11471.0</td>\n",
       "      <td>279156.0</td>\n",
       "      <td>6928561.0</td>\n",
       "      <td>2295895.0</td>\n",
       "      <td>3017804.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            state abbreviation  TotalCases  TotalDeaths  Tot_Cases_1M_pop  \\\n",
       "0         alabama           AL   1312437.0      19658.0          267670.0   \n",
       "1          alaska           AK    251425.0       1252.0          343690.0   \n",
       "2  american samoa           AS      6130.0         31.0               NaN   \n",
       "3         arizona           AZ   2049627.0      30299.0          281592.0   \n",
       "4        arkansas           AR    842439.0      11471.0          279156.0   \n",
       "\n",
       "   TotalTests  Tests_per_1M_pop  Population  \n",
       "0   7693519.0         1569086.0   4903185.0  \n",
       "1   4107614.0         5614985.0    731545.0  \n",
       "2     16964.0               NaN         NaN  \n",
       "3  19534476.0         2683780.0   7278717.0  \n",
       "4   6928561.0         2295895.0   3017804.0  "
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "national_covid_stats.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "8c673c84",
   "metadata": {},
   "outputs": [],
   "source": [
    "#national_covid_stats.to_sql(\"national_covid_statistics_us\", con = engine, \n",
    "#                         if_exists = 'replace', \n",
    "#                        index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43cb8985",
   "metadata": {},
   "source": [
    "## loading NYT data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "4486ba6f",
   "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>state</th>\n",
       "      <th>inmate_tests</th>\n",
       "      <th>total_inmate_cases</th>\n",
       "      <th>total_inmate_deaths</th>\n",
       "      <th>latest_inmate_population</th>\n",
       "      <th>max_inmate_population_2020</th>\n",
       "      <th>total_officer_cases</th>\n",
       "      <th>total_officer_deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>15505.0</td>\n",
       "      <td>1601</td>\n",
       "      <td>64</td>\n",
       "      <td>19144.0</td>\n",
       "      <td>21900.0</td>\n",
       "      <td>1019.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alaska</td>\n",
       "      <td>27756.0</td>\n",
       "      <td>2428</td>\n",
       "      <td>5</td>\n",
       "      <td>5011.0</td>\n",
       "      <td>5143.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>arizona</td>\n",
       "      <td>43652.0</td>\n",
       "      <td>12229</td>\n",
       "      <td>44</td>\n",
       "      <td>38248.0</td>\n",
       "      <td>41674.0</td>\n",
       "      <td>2739.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>arkansas</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11378</td>\n",
       "      <td>52</td>\n",
       "      <td>14729.0</td>\n",
       "      <td>15886.0</td>\n",
       "      <td>424.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>california</td>\n",
       "      <td>121207.0</td>\n",
       "      <td>49511</td>\n",
       "      <td>217</td>\n",
       "      <td>110471.0</td>\n",
       "      <td>117767.0</td>\n",
       "      <td>16090.0</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        state  inmate_tests  total_inmate_cases  total_inmate_deaths  \\\n",
       "0     alabama       15505.0                1601                   64   \n",
       "1      alaska       27756.0                2428                    5   \n",
       "2     arizona       43652.0               12229                   44   \n",
       "3    arkansas           0.0               11378                   52   \n",
       "4  california      121207.0               49511                  217   \n",
       "\n",
       "   latest_inmate_population  max_inmate_population_2020  total_officer_cases  \\\n",
       "0                   19144.0                     21900.0               1019.0   \n",
       "1                    5011.0                      5143.0                 85.0   \n",
       "2                   38248.0                     41674.0               2739.0   \n",
       "3                   14729.0                     15886.0                424.0   \n",
       "4                  110471.0                    117767.0              16090.0   \n",
       "\n",
       "   total_officer_deaths  \n",
       "0                   3.0  \n",
       "1                   0.0  \n",
       "2                   0.0  \n",
       "3                   5.0  \n",
       "4                  26.0  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nyt_df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "d93d67b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#nyt_df1.to_sql(\"total_prison_covid_stats_by_state_nyt\", con = engine, \n",
    "#                         if_exists = 'append', \n",
    "#                         index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "70b917ff",
   "metadata": {
    "scrolled": true
   },
   "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>nyt_id</th>\n",
       "      <th>facility_name</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>facility_city</th>\n",
       "      <th>facility_county</th>\n",
       "      <th>facility_county_fips</th>\n",
       "      <th>state</th>\n",
       "      <th>facility_lng</th>\n",
       "      <th>facility_lat</th>\n",
       "      <th>latest_inmate_population</th>\n",
       "      <th>max_inmate_population_2020</th>\n",
       "      <th>total_inmate_cases</th>\n",
       "      <th>total_inmate_deaths</th>\n",
       "      <th>total_officer_cases</th>\n",
       "      <th>total_officer_deaths</th>\n",
       "      <th>note</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>F3EFE858</td>\n",
       "      <td>Alex City Work Release prison</td>\n",
       "      <td>Low-security work release</td>\n",
       "      <td>Alex City</td>\n",
       "      <td>Coosa</td>\n",
       "      <td>1037</td>\n",
       "      <td>alabama</td>\n",
       "      <td>-86.009015</td>\n",
       "      <td>32.904507</td>\n",
       "      <td>188.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>77</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5B910220</td>\n",
       "      <td>Alabama Therapeutic Education Facility prison</td>\n",
       "      <td>State rehabilitation center</td>\n",
       "      <td>Columbiana</td>\n",
       "      <td>Shelby</td>\n",
       "      <td>1117</td>\n",
       "      <td>alabama</td>\n",
       "      <td>-86.624067</td>\n",
       "      <td>33.180755</td>\n",
       "      <td>272.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>02FB1675</td>\n",
       "      <td>Bibb Correctional Facility</td>\n",
       "      <td>State prison</td>\n",
       "      <td>Brent</td>\n",
       "      <td>Bibb</td>\n",
       "      <td>1007</td>\n",
       "      <td>alabama</td>\n",
       "      <td>-87.162781</td>\n",
       "      <td>32.920754</td>\n",
       "      <td>1725.0</td>\n",
       "      <td>1825.0</td>\n",
       "      <td>164</td>\n",
       "      <td>3</td>\n",
       "      <td>61</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6378F6C4</td>\n",
       "      <td>Birmingham Women's Community Based Facility an...</td>\n",
       "      <td>State prison</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>Jefferson</td>\n",
       "      <td>1073</td>\n",
       "      <td>alabama</td>\n",
       "      <td>-86.808344</td>\n",
       "      <td>33.531101</td>\n",
       "      <td>192.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>EAABF900</td>\n",
       "      <td>Bullock Correctional Facility</td>\n",
       "      <td>State prison</td>\n",
       "      <td>Bessemer</td>\n",
       "      <td>Bullock</td>\n",
       "      <td>1011</td>\n",
       "      <td>alabama</td>\n",
       "      <td>-85.673927</td>\n",
       "      <td>32.147144</td>\n",
       "      <td>1477.0</td>\n",
       "      <td>1577.0</td>\n",
       "      <td>162</td>\n",
       "      <td>5</td>\n",
       "      <td>80</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     nyt_id                                      facility_name  \\\n",
       "0  F3EFE858                      Alex City Work Release prison   \n",
       "1  5B910220      Alabama Therapeutic Education Facility prison   \n",
       "2  02FB1675                         Bibb Correctional Facility   \n",
       "3  6378F6C4  Birmingham Women's Community Based Facility an...   \n",
       "4  EAABF900                      Bullock Correctional Facility   \n",
       "\n",
       "                 facility_type facility_city facility_county  \\\n",
       "0    Low-security work release     Alex City           Coosa   \n",
       "1  State rehabilitation center    Columbiana          Shelby   \n",
       "2                 State prison         Brent            Bibb   \n",
       "3                 State prison    Birmingham       Jefferson   \n",
       "4                 State prison      Bessemer         Bullock   \n",
       "\n",
       "   facility_county_fips    state  facility_lng  facility_lat  \\\n",
       "0                  1037  alabama    -86.009015     32.904507   \n",
       "1                  1117  alabama    -86.624067     33.180755   \n",
       "2                  1007  alabama    -87.162781     32.920754   \n",
       "3                  1073  alabama    -86.808344     33.531101   \n",
       "4                  1011  alabama    -85.673927     32.147144   \n",
       "\n",
       "   latest_inmate_population  max_inmate_population_2020  total_inmate_cases  \\\n",
       "0                     188.0                         NaN                  77   \n",
       "1                     272.0                         NaN                  11   \n",
       "2                    1725.0                      1825.0                 164   \n",
       "3                     192.0                         NaN                  17   \n",
       "4                    1477.0                      1577.0                 162   \n",
       "\n",
       "   total_inmate_deaths  total_officer_cases  total_officer_deaths note  \n",
       "0                    0                   17                   0.0  NaN  \n",
       "1                    1                    2                   0.0  NaN  \n",
       "2                    3                   61                   0.0  NaN  \n",
       "3                    0                   28                   0.0  NaN  \n",
       "4                    5                   80                   1.0  NaN  "
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nyt_df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "402e3e3e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['nyt_id', 'facility_name', 'facility_type', 'facility_city',\n",
       "       'facility_county', 'facility_county_fips', 'state', 'facility_lng',\n",
       "       'facility_lat', 'latest_inmate_population',\n",
       "       'max_inmate_population_2020', 'total_inmate_cases',\n",
       "       'total_inmate_deaths', 'total_officer_cases', 'total_officer_deaths',\n",
       "       'note'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nyt_df2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "0cfd1828",
   "metadata": {},
   "outputs": [],
   "source": [
    "#nyt_df2.to_sql(\"prison_facilities_nyt\", con = engine, \n",
    "#                         if_exists = 'append', \n",
    "#                         index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b771cbfb",
   "metadata": {},
   "source": [
    "## loading APM data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "17dcd05e",
   "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>state</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>staff_tests</th>\n",
       "      <th>staff_tests_with_multiples</th>\n",
       "      <th>total_staff_cases</th>\n",
       "      <th>staff_recovered</th>\n",
       "      <th>total_staff_deaths</th>\n",
       "      <th>staff_partial_dose</th>\n",
       "      <th>staff_full_dose</th>\n",
       "      <th>prisoner_tests</th>\n",
       "      <th>prisoner_tests_with_multiples</th>\n",
       "      <th>total_prisoner_cases</th>\n",
       "      <th>prisoners_recovered</th>\n",
       "      <th>total_prisoner_deaths</th>\n",
       "      <th>prisoners_partial_dose</th>\n",
       "      <th>prisoners_full_dose</th>\n",
       "      <th>as_of_date</th>\n",
       "      <th>notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1058.0</td>\n",
       "      <td>1048.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>871.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16743.0</td>\n",
       "      <td>1662.0</td>\n",
       "      <td>1595.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>10811.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-06-25</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>332.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38707.0</td>\n",
       "      <td>2440.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3460.0</td>\n",
       "      <td>3097.0</td>\n",
       "      <td>2021-06-22</td>\n",
       "      <td>vaccines as of June 23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2787.0</td>\n",
       "      <td>2778.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47899.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12328.0</td>\n",
       "      <td>12251.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-06-22</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>arkansas</td>\n",
       "      <td>AR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11425.0</td>\n",
       "      <td>11353.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-06-23</td>\n",
       "      <td>We have been told vaccinations have begun in t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>california</td>\n",
       "      <td>CA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35295.0</td>\n",
       "      <td>33474.0</td>\n",
       "      <td>129415.0</td>\n",
       "      <td>1602906.0</td>\n",
       "      <td>49395.0</td>\n",
       "      <td>48517.0</td>\n",
       "      <td>227.0</td>\n",
       "      <td>71317.0</td>\n",
       "      <td>69400.0</td>\n",
       "      <td>2021-06-22</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        state abbreviation  staff_tests  staff_tests_with_multiples  \\\n",
       "0     alabama           AL          NaN                         NaN   \n",
       "1      alaska           AK          NaN                         NaN   \n",
       "2     arizona           AZ          NaN                         NaN   \n",
       "3    arkansas           AR          NaN                         NaN   \n",
       "4  california           CA          NaN                         NaN   \n",
       "\n",
       "   total_staff_cases  staff_recovered  total_staff_deaths  staff_partial_dose  \\\n",
       "0             1058.0           1048.0                 3.0               871.0   \n",
       "1              332.0              NaN                 0.0                 NaN   \n",
       "2             2787.0           2778.0                 NaN                 NaN   \n",
       "3                NaN              NaN                 4.0                 NaN   \n",
       "4                NaN              NaN                 NaN             35295.0   \n",
       "\n",
       "   staff_full_dose  prisoner_tests  prisoner_tests_with_multiples  \\\n",
       "0              NaN             NaN                        16743.0   \n",
       "1              NaN             NaN                        38707.0   \n",
       "2              NaN         47899.0                            NaN   \n",
       "3              NaN             NaN                            NaN   \n",
       "4          33474.0        129415.0                      1602906.0   \n",
       "\n",
       "   total_prisoner_cases  prisoners_recovered  total_prisoner_deaths  \\\n",
       "0                1662.0               1595.0                   66.0   \n",
       "1                2440.0                  NaN                    5.0   \n",
       "2               12328.0              12251.0                   65.0   \n",
       "3               11425.0              11353.0                   52.0   \n",
       "4               49395.0              48517.0                  227.0   \n",
       "\n",
       "   prisoners_partial_dose  prisoners_full_dose as_of_date  \\\n",
       "0                 10811.0                  NaN 2021-06-25   \n",
       "1                  3460.0               3097.0 2021-06-22   \n",
       "2                     NaN                  NaN 2021-06-22   \n",
       "3                     NaN                  NaN 2021-06-23   \n",
       "4                 71317.0              69400.0 2021-06-22   \n",
       "\n",
       "                                               notes  \n",
       "0                                                NaN  \n",
       "1                             vaccines as of June 23  \n",
       "2                                                NaN  \n",
       "3  We have been told vaccinations have begun in t...  \n",
       "4                                                NaN  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ap_data_df1_cases.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "bef67374",
   "metadata": {},
   "outputs": [],
   "source": [
    "#ap_data_df1_cases.to_sql(\"covid_prison_cases_apm\", con, \n",
    "#                            if_exists = 'append', \n",
    "#                            index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "a4f568d3",
   "metadata": {
    "scrolled": true
   },
   "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>state</th>\n",
       "      <th>latest_week</th>\n",
       "      <th>cumulative_prisoner_cases</th>\n",
       "      <th>prisoner_cases_pct</th>\n",
       "      <th>prisoner_case_rate</th>\n",
       "      <th>cumulative_prisoner_deaths</th>\n",
       "      <th>prisoner_deaths_pct</th>\n",
       "      <th>prisoner_death_rate</th>\n",
       "      <th>cumulative_staff_cases</th>\n",
       "      <th>staff_cases_pct</th>\n",
       "      <th>staff_case_rate</th>\n",
       "      <th>cumulative_staff_deaths</th>\n",
       "      <th>staff_deaths_pct</th>\n",
       "      <th>staff_death_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>2021-06-22</td>\n",
       "      <td>1662</td>\n",
       "      <td>0.078716</td>\n",
       "      <td>1 in 13 prisoners</td>\n",
       "      <td>66</td>\n",
       "      <td>0.003126</td>\n",
       "      <td>1 in 320 prisoners</td>\n",
       "      <td>1058</td>\n",
       "      <td>0.279008</td>\n",
       "      <td>1 in 4 staff</td>\n",
       "      <td>3</td>\n",
       "      <td>0.000791</td>\n",
       "      <td>1 in 1264 staff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alaska</td>\n",
       "      <td>2021-06-22</td>\n",
       "      <td>2440</td>\n",
       "      <td>0.510888</td>\n",
       "      <td>1 in 2 prisoners</td>\n",
       "      <td>5</td>\n",
       "      <td>0.001047</td>\n",
       "      <td>1 in 955 prisoners</td>\n",
       "      <td>332</td>\n",
       "      <td>0.224173</td>\n",
       "      <td>1 in 4 staff</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>arizona</td>\n",
       "      <td>2021-06-22</td>\n",
       "      <td>12328</td>\n",
       "      <td>0.291029</td>\n",
       "      <td>2 in 7 prisoners</td>\n",
       "      <td>65</td>\n",
       "      <td>0.001534</td>\n",
       "      <td>1 in 652 prisoners</td>\n",
       "      <td>2787</td>\n",
       "      <td>0.306432</td>\n",
       "      <td>1 in 3 staff</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000110</td>\n",
       "      <td>1 in 9095 staff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>arkansas</td>\n",
       "      <td>2021-06-22</td>\n",
       "      <td>11425</td>\n",
       "      <td>0.652783</td>\n",
       "      <td>2 in 3 prisoners</td>\n",
       "      <td>52</td>\n",
       "      <td>0.002971</td>\n",
       "      <td>1 in 337 prisoners</td>\n",
       "      <td>813</td>\n",
       "      <td>0.148901</td>\n",
       "      <td>1 in 7 staff</td>\n",
       "      <td>4</td>\n",
       "      <td>0.000733</td>\n",
       "      <td>1 in 1365 staff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>california</td>\n",
       "      <td>2021-06-22</td>\n",
       "      <td>49395</td>\n",
       "      <td>0.419886</td>\n",
       "      <td>2 in 5 prisoners</td>\n",
       "      <td>227</td>\n",
       "      <td>0.001930</td>\n",
       "      <td>1 in 518 prisoners</td>\n",
       "      <td>17002</td>\n",
       "      <td>0.267870</td>\n",
       "      <td>1 in 4 staff</td>\n",
       "      <td>28</td>\n",
       "      <td>0.000441</td>\n",
       "      <td>1 in 2267 staff</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        state latest_week  cumulative_prisoner_cases  prisoner_cases_pct  \\\n",
       "0     alabama  2021-06-22                       1662            0.078716   \n",
       "1      alaska  2021-06-22                       2440            0.510888   \n",
       "2     arizona  2021-06-22                      12328            0.291029   \n",
       "3    arkansas  2021-06-22                      11425            0.652783   \n",
       "4  california  2021-06-22                      49395            0.419886   \n",
       "\n",
       "  prisoner_case_rate  cumulative_prisoner_deaths  prisoner_deaths_pct  \\\n",
       "0  1 in 13 prisoners                          66             0.003126   \n",
       "1   1 in 2 prisoners                           5             0.001047   \n",
       "2   2 in 7 prisoners                          65             0.001534   \n",
       "3   2 in 3 prisoners                          52             0.002971   \n",
       "4   2 in 5 prisoners                         227             0.001930   \n",
       "\n",
       "  prisoner_death_rate  cumulative_staff_cases  staff_cases_pct  \\\n",
       "0  1 in 320 prisoners                    1058         0.279008   \n",
       "1  1 in 955 prisoners                     332         0.224173   \n",
       "2  1 in 652 prisoners                    2787         0.306432   \n",
       "3  1 in 337 prisoners                     813         0.148901   \n",
       "4  1 in 518 prisoners                   17002         0.267870   \n",
       "\n",
       "  staff_case_rate  cumulative_staff_deaths  staff_deaths_pct staff_death_rate  \n",
       "0    1 in 4 staff                        3          0.000791  1 in 1264 staff  \n",
       "1    1 in 4 staff                        0          0.000000                0  \n",
       "2    1 in 3 staff                        1          0.000110  1 in 9095 staff  \n",
       "3    1 in 7 staff                        4          0.000733  1 in 1365 staff  \n",
       "4    1 in 4 staff                       28          0.000441  1 in 2267 staff  "
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ap_data_df2_rates.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "13137018",
   "metadata": {},
   "outputs": [],
   "source": [
    "#ap_data_df2_rates.to_sql(\"covid_prison_rates_apm\", con, \n",
    "#                            if_exists = 'append', \n",
    "#                            index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "cc42a2bb",
   "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>state</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>month</th>\n",
       "      <th>as_of_date</th>\n",
       "      <th>pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>march</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>21114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>april</td>\n",
       "      <td>2020-04-30</td>\n",
       "      <td>20655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>june</td>\n",
       "      <td>2020-06-30</td>\n",
       "      <td>19752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>july</td>\n",
       "      <td>2020-07-31</td>\n",
       "      <td>19342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>aug</td>\n",
       "      <td>2020-08-31</td>\n",
       "      <td>18901</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     state abbreviation  month as_of_date    pop\n",
       "0  alabama           AL  march 2020-03-31  21114\n",
       "1  alabama           AL  april 2020-04-30  20655\n",
       "2  alabama           AL   june 2020-06-30  19752\n",
       "3  alabama           AL   july 2020-07-31  19342\n",
       "4  alabama           AL    aug 2020-08-31  18901"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ap_data_df3_prisonpop.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "48ae7ea4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#ap_data_df3_prisonpop.to_sql(\"prison_populations_apm\", con, \n",
    "#                            if_exists = 'append', \n",
    "#                            index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "a1af084a",
   "metadata": {},
   "outputs": [],
   "source": [
    "ap_data_df4_staffpop.rename(columns={\"Notes\": \"notes\"}, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "49392e78",
   "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>state</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>month</th>\n",
       "      <th>as_of_date</th>\n",
       "      <th>pop</th>\n",
       "      <th>notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>march</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>3686.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>july</td>\n",
       "      <td>2020-06-30</td>\n",
       "      <td>3792.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>march</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>1456.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>july</td>\n",
       "      <td>2020-07-31</td>\n",
       "      <td>1481.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>july</td>\n",
       "      <td>2020-08-01</td>\n",
       "      <td>9095.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     state abbreviation  month as_of_date     pop notes\n",
       "0  alabama           AL  march 2020-03-31  3686.0   NaN\n",
       "1  alabama           AL   july 2020-06-30  3792.0   NaN\n",
       "2   alaska           AK  march 2020-03-31  1456.0   NaN\n",
       "3   alaska           AK   july 2020-07-31  1481.0   NaN\n",
       "4  arizona           AZ   july 2020-08-01  9095.0   NaN"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ap_data_df4_staffpop.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "1c4e1ab7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#ap_data_df4_staffpop.to_sql(\"staff_populations_apm\", con, \n",
    "#                            if_exists = 'append', \n",
    "#                            index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f510651c",
   "metadata": {},
   "source": [
    "#### Just in case we run the cells again, we have commented the \"to_sql\" code, so we don't duplicate data."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6362bf5f",
   "metadata": {},
   "source": [
    "--------------------------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18d7848b",
   "metadata": {},
   "source": [
    "## QUERY THE DB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "2ae2514f",
   "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>state</th>\n",
       "      <th>abbreviation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alaska</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>american samoa</td>\n",
       "      <td>AS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>arizona</td>\n",
       "      <td>AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>arkansas</td>\n",
       "      <td>AR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            state abbreviation\n",
       "0         alabama           AL\n",
       "1          alaska           AK\n",
       "2  american samoa           AS\n",
       "3         arizona           AZ\n",
       "4        arkansas           AR"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from state_abbreviation', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "06260c60",
   "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>id</th>\n",
       "      <th>state</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>staff_tests</th>\n",
       "      <th>staff_tests_with_multiples</th>\n",
       "      <th>total_staff_cases</th>\n",
       "      <th>staff_recovered</th>\n",
       "      <th>total_staff_deaths</th>\n",
       "      <th>staff_partial_dose</th>\n",
       "      <th>staff_full_dose</th>\n",
       "      <th>prisoner_tests</th>\n",
       "      <th>prisoner_tests_with_multiples</th>\n",
       "      <th>total_prisoner_cases</th>\n",
       "      <th>prisoners_recovered</th>\n",
       "      <th>total_prisoner_deaths</th>\n",
       "      <th>prisoners_partial_dose</th>\n",
       "      <th>prisoners_full_dose</th>\n",
       "      <th>as_of_date</th>\n",
       "      <th>notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1058.0</td>\n",
       "      <td>1048.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>871.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16743.0</td>\n",
       "      <td>1662.0</td>\n",
       "      <td>1595.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>10811.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-06-25</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>332.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38707.0</td>\n",
       "      <td>2440.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3460.0</td>\n",
       "      <td>3097.0</td>\n",
       "      <td>2021-06-22</td>\n",
       "      <td>vaccines as of June 23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2787.0</td>\n",
       "      <td>2778.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47899.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12328.0</td>\n",
       "      <td>12251.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-06-22</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>arkansas</td>\n",
       "      <td>AR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11425.0</td>\n",
       "      <td>11353.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-06-23</td>\n",
       "      <td>We have been told vaccinations have begun in t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>california</td>\n",
       "      <td>CA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35295.0</td>\n",
       "      <td>33474.0</td>\n",
       "      <td>129415.0</td>\n",
       "      <td>1602906.0</td>\n",
       "      <td>49395.0</td>\n",
       "      <td>48517.0</td>\n",
       "      <td>227.0</td>\n",
       "      <td>71317.0</td>\n",
       "      <td>69400.0</td>\n",
       "      <td>2021-06-22</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id       state abbreviation  staff_tests  staff_tests_with_multiples  \\\n",
       "0   1     alabama           AL          NaN                         NaN   \n",
       "1   2      alaska           AK          NaN                         NaN   \n",
       "2   3     arizona           AZ          NaN                         NaN   \n",
       "3   4    arkansas           AR          NaN                         NaN   \n",
       "4   5  california           CA          NaN                         NaN   \n",
       "\n",
       "   total_staff_cases  staff_recovered  total_staff_deaths  staff_partial_dose  \\\n",
       "0             1058.0           1048.0                 3.0               871.0   \n",
       "1              332.0              NaN                 0.0                 NaN   \n",
       "2             2787.0           2778.0                 NaN                 NaN   \n",
       "3                NaN              NaN                 4.0                 NaN   \n",
       "4                NaN              NaN                 NaN             35295.0   \n",
       "\n",
       "   staff_full_dose  prisoner_tests  prisoner_tests_with_multiples  \\\n",
       "0              NaN             NaN                        16743.0   \n",
       "1              NaN             NaN                        38707.0   \n",
       "2              NaN         47899.0                            NaN   \n",
       "3              NaN             NaN                            NaN   \n",
       "4          33474.0        129415.0                      1602906.0   \n",
       "\n",
       "   total_prisoner_cases  prisoners_recovered  total_prisoner_deaths  \\\n",
       "0                1662.0               1595.0                   66.0   \n",
       "1                2440.0                  NaN                    5.0   \n",
       "2               12328.0              12251.0                   65.0   \n",
       "3               11425.0              11353.0                   52.0   \n",
       "4               49395.0              48517.0                  227.0   \n",
       "\n",
       "   prisoners_partial_dose  prisoners_full_dose  as_of_date  \\\n",
       "0                 10811.0                  NaN  2021-06-25   \n",
       "1                  3460.0               3097.0  2021-06-22   \n",
       "2                     NaN                  NaN  2021-06-22   \n",
       "3                     NaN                  NaN  2021-06-23   \n",
       "4                 71317.0              69400.0  2021-06-22   \n",
       "\n",
       "                                               notes  \n",
       "0                                               None  \n",
       "1                             vaccines as of June 23  \n",
       "2                                               None  \n",
       "3  We have been told vaccinations have begun in t...  \n",
       "4                                               None  "
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from covid_prison_cases_apm', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "c301caa6",
   "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>state</th>\n",
       "      <th>latest_week</th>\n",
       "      <th>cumulative_prisoner_cases</th>\n",
       "      <th>prisoner_cases_pct</th>\n",
       "      <th>prisoner_case_rate</th>\n",
       "      <th>cumulative_prisoner_deaths</th>\n",
       "      <th>prisoner_deaths_pct</th>\n",
       "      <th>prisoner_death_rate</th>\n",
       "      <th>cumulative_staff_cases</th>\n",
       "      <th>staff_cases_pct</th>\n",
       "      <th>staff_case_rate</th>\n",
       "      <th>cumulative_staff_deaths</th>\n",
       "      <th>staff_deaths_pct</th>\n",
       "      <th>staff_death_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>2021-06-22</td>\n",
       "      <td>1662</td>\n",
       "      <td>0.078716</td>\n",
       "      <td>1 in 13 prisoners</td>\n",
       "      <td>66</td>\n",
       "      <td>0.003126</td>\n",
       "      <td>1 in 320 prisoners</td>\n",
       "      <td>1058</td>\n",
       "      <td>0.279008</td>\n",
       "      <td>1 in 4 staff</td>\n",
       "      <td>3</td>\n",
       "      <td>0.000791</td>\n",
       "      <td>1 in 1264 staff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alaska</td>\n",
       "      <td>2021-06-22</td>\n",
       "      <td>2440</td>\n",
       "      <td>0.510888</td>\n",
       "      <td>1 in 2 prisoners</td>\n",
       "      <td>5</td>\n",
       "      <td>0.001047</td>\n",
       "      <td>1 in 955 prisoners</td>\n",
       "      <td>332</td>\n",
       "      <td>0.224173</td>\n",
       "      <td>1 in 4 staff</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>arizona</td>\n",
       "      <td>2021-06-22</td>\n",
       "      <td>12328</td>\n",
       "      <td>0.291029</td>\n",
       "      <td>2 in 7 prisoners</td>\n",
       "      <td>65</td>\n",
       "      <td>0.001534</td>\n",
       "      <td>1 in 652 prisoners</td>\n",
       "      <td>2787</td>\n",
       "      <td>0.306432</td>\n",
       "      <td>1 in 3 staff</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000110</td>\n",
       "      <td>1 in 9095 staff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>arkansas</td>\n",
       "      <td>2021-06-22</td>\n",
       "      <td>11425</td>\n",
       "      <td>0.652783</td>\n",
       "      <td>2 in 3 prisoners</td>\n",
       "      <td>52</td>\n",
       "      <td>0.002971</td>\n",
       "      <td>1 in 337 prisoners</td>\n",
       "      <td>813</td>\n",
       "      <td>0.148901</td>\n",
       "      <td>1 in 7 staff</td>\n",
       "      <td>4</td>\n",
       "      <td>0.000733</td>\n",
       "      <td>1 in 1365 staff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>california</td>\n",
       "      <td>2021-06-22</td>\n",
       "      <td>49395</td>\n",
       "      <td>0.419886</td>\n",
       "      <td>2 in 5 prisoners</td>\n",
       "      <td>227</td>\n",
       "      <td>0.001930</td>\n",
       "      <td>1 in 518 prisoners</td>\n",
       "      <td>17002</td>\n",
       "      <td>0.267870</td>\n",
       "      <td>1 in 4 staff</td>\n",
       "      <td>28</td>\n",
       "      <td>0.000441</td>\n",
       "      <td>1 in 2267 staff</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        state latest_week  cumulative_prisoner_cases  prisoner_cases_pct  \\\n",
       "0     alabama  2021-06-22                       1662            0.078716   \n",
       "1      alaska  2021-06-22                       2440            0.510888   \n",
       "2     arizona  2021-06-22                      12328            0.291029   \n",
       "3    arkansas  2021-06-22                      11425            0.652783   \n",
       "4  california  2021-06-22                      49395            0.419886   \n",
       "\n",
       "  prisoner_case_rate  cumulative_prisoner_deaths  prisoner_deaths_pct  \\\n",
       "0  1 in 13 prisoners                          66             0.003126   \n",
       "1   1 in 2 prisoners                           5             0.001047   \n",
       "2   2 in 7 prisoners                          65             0.001534   \n",
       "3   2 in 3 prisoners                          52             0.002971   \n",
       "4   2 in 5 prisoners                         227             0.001930   \n",
       "\n",
       "  prisoner_death_rate  cumulative_staff_cases  staff_cases_pct  \\\n",
       "0  1 in 320 prisoners                    1058         0.279008   \n",
       "1  1 in 955 prisoners                     332         0.224173   \n",
       "2  1 in 652 prisoners                    2787         0.306432   \n",
       "3  1 in 337 prisoners                     813         0.148901   \n",
       "4  1 in 518 prisoners                   17002         0.267870   \n",
       "\n",
       "  staff_case_rate  cumulative_staff_deaths  staff_deaths_pct staff_death_rate  \n",
       "0    1 in 4 staff                        3          0.000791  1 in 1264 staff  \n",
       "1    1 in 4 staff                        0          0.000000                0  \n",
       "2    1 in 3 staff                        1          0.000110  1 in 9095 staff  \n",
       "3    1 in 7 staff                        4          0.000733  1 in 1365 staff  \n",
       "4    1 in 4 staff                       28          0.000441  1 in 2267 staff  "
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from covid_prison_rates_apm', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "6b253b37",
   "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>state</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>TotalCases</th>\n",
       "      <th>TotalDeaths</th>\n",
       "      <th>Tot_Cases_1M_pop</th>\n",
       "      <th>TotalTests</th>\n",
       "      <th>Tests_per_1M_pop</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>1312437.0</td>\n",
       "      <td>19658.0</td>\n",
       "      <td>267670.0</td>\n",
       "      <td>7693519.0</td>\n",
       "      <td>1569086.0</td>\n",
       "      <td>4903185.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>251425.0</td>\n",
       "      <td>1252.0</td>\n",
       "      <td>343690.0</td>\n",
       "      <td>4107614.0</td>\n",
       "      <td>5614985.0</td>\n",
       "      <td>731545.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>american samoa</td>\n",
       "      <td>AS</td>\n",
       "      <td>6130.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16964.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>2049627.0</td>\n",
       "      <td>30299.0</td>\n",
       "      <td>281592.0</td>\n",
       "      <td>19534476.0</td>\n",
       "      <td>2683780.0</td>\n",
       "      <td>7278717.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>arkansas</td>\n",
       "      <td>AR</td>\n",
       "      <td>842439.0</td>\n",
       "      <td>11471.0</td>\n",
       "      <td>279156.0</td>\n",
       "      <td>6928561.0</td>\n",
       "      <td>2295895.0</td>\n",
       "      <td>3017804.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            state abbreviation  TotalCases  TotalDeaths  Tot_Cases_1M_pop  \\\n",
       "0         alabama           AL   1312437.0      19658.0          267670.0   \n",
       "1          alaska           AK    251425.0       1252.0          343690.0   \n",
       "2  american samoa           AS      6130.0         31.0               NaN   \n",
       "3         arizona           AZ   2049627.0      30299.0          281592.0   \n",
       "4        arkansas           AR    842439.0      11471.0          279156.0   \n",
       "\n",
       "   TotalTests  Tests_per_1M_pop  Population  \n",
       "0   7693519.0         1569086.0   4903185.0  \n",
       "1   4107614.0         5614985.0    731545.0  \n",
       "2     16964.0               NaN         NaN  \n",
       "3  19534476.0         2683780.0   7278717.0  \n",
       "4   6928561.0         2295895.0   3017804.0  "
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from national_covid_statistics_us', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "5b4956a6",
   "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>id</th>\n",
       "      <th>nyt_id</th>\n",
       "      <th>facility_name</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>facility_city</th>\n",
       "      <th>facility_county</th>\n",
       "      <th>facility_county_fips</th>\n",
       "      <th>state</th>\n",
       "      <th>facility_lng</th>\n",
       "      <th>facility_lat</th>\n",
       "      <th>latest_inmate_population</th>\n",
       "      <th>max_inmate_population_2020</th>\n",
       "      <th>total_inmate_cases</th>\n",
       "      <th>total_inmate_deaths</th>\n",
       "      <th>total_officer_cases</th>\n",
       "      <th>total_officer_deaths</th>\n",
       "      <th>note</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>F3EFE858</td>\n",
       "      <td>Alex City Work Release prison</td>\n",
       "      <td>Low-security work release</td>\n",
       "      <td>Alex City</td>\n",
       "      <td>Coosa</td>\n",
       "      <td>1037</td>\n",
       "      <td>alabama</td>\n",
       "      <td>-86.009015</td>\n",
       "      <td>32.904507</td>\n",
       "      <td>188.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>77</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>5B910220</td>\n",
       "      <td>Alabama Therapeutic Education Facility prison</td>\n",
       "      <td>State rehabilitation center</td>\n",
       "      <td>Columbiana</td>\n",
       "      <td>Shelby</td>\n",
       "      <td>1117</td>\n",
       "      <td>alabama</td>\n",
       "      <td>-86.624067</td>\n",
       "      <td>33.180755</td>\n",
       "      <td>272.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>02FB1675</td>\n",
       "      <td>Bibb Correctional Facility</td>\n",
       "      <td>State prison</td>\n",
       "      <td>Brent</td>\n",
       "      <td>Bibb</td>\n",
       "      <td>1007</td>\n",
       "      <td>alabama</td>\n",
       "      <td>-87.162781</td>\n",
       "      <td>32.920754</td>\n",
       "      <td>1725.0</td>\n",
       "      <td>1825.0</td>\n",
       "      <td>164</td>\n",
       "      <td>3</td>\n",
       "      <td>61</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>6378F6C4</td>\n",
       "      <td>Birmingham Women's Community Based Facility an...</td>\n",
       "      <td>State prison</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>Jefferson</td>\n",
       "      <td>1073</td>\n",
       "      <td>alabama</td>\n",
       "      <td>-86.808344</td>\n",
       "      <td>33.531101</td>\n",
       "      <td>192.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>EAABF900</td>\n",
       "      <td>Bullock Correctional Facility</td>\n",
       "      <td>State prison</td>\n",
       "      <td>Bessemer</td>\n",
       "      <td>Bullock</td>\n",
       "      <td>1011</td>\n",
       "      <td>alabama</td>\n",
       "      <td>-85.673927</td>\n",
       "      <td>32.147144</td>\n",
       "      <td>1477.0</td>\n",
       "      <td>1577.0</td>\n",
       "      <td>162</td>\n",
       "      <td>5</td>\n",
       "      <td>80</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id    nyt_id                                      facility_name  \\\n",
       "0   1  F3EFE858                      Alex City Work Release prison   \n",
       "1   2  5B910220      Alabama Therapeutic Education Facility prison   \n",
       "2   3  02FB1675                         Bibb Correctional Facility   \n",
       "3   4  6378F6C4  Birmingham Women's Community Based Facility an...   \n",
       "4   5  EAABF900                      Bullock Correctional Facility   \n",
       "\n",
       "                 facility_type facility_city facility_county  \\\n",
       "0    Low-security work release     Alex City           Coosa   \n",
       "1  State rehabilitation center    Columbiana          Shelby   \n",
       "2                 State prison         Brent            Bibb   \n",
       "3                 State prison    Birmingham       Jefferson   \n",
       "4                 State prison      Bessemer         Bullock   \n",
       "\n",
       "   facility_county_fips    state  facility_lng  facility_lat  \\\n",
       "0                  1037  alabama    -86.009015     32.904507   \n",
       "1                  1117  alabama    -86.624067     33.180755   \n",
       "2                  1007  alabama    -87.162781     32.920754   \n",
       "3                  1073  alabama    -86.808344     33.531101   \n",
       "4                  1011  alabama    -85.673927     32.147144   \n",
       "\n",
       "   latest_inmate_population  max_inmate_population_2020  total_inmate_cases  \\\n",
       "0                     188.0                         NaN                  77   \n",
       "1                     272.0                         NaN                  11   \n",
       "2                    1725.0                      1825.0                 164   \n",
       "3                     192.0                         NaN                  17   \n",
       "4                    1477.0                      1577.0                 162   \n",
       "\n",
       "   total_inmate_deaths  total_officer_cases  total_officer_deaths  note  \n",
       "0                    0                   17                   0.0  None  \n",
       "1                    1                    2                   0.0  None  \n",
       "2                    3                   61                   0.0  None  \n",
       "3                    0                   28                   0.0  None  \n",
       "4                    5                   80                   1.0  None  "
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from prison_facilities_nyt', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "d33c4858",
   "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>id</th>\n",
       "      <th>state</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>month</th>\n",
       "      <th>as_of_date</th>\n",
       "      <th>pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>march</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>21114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>april</td>\n",
       "      <td>2020-04-30</td>\n",
       "      <td>20655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>june</td>\n",
       "      <td>2020-06-30</td>\n",
       "      <td>19752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>july</td>\n",
       "      <td>2020-07-31</td>\n",
       "      <td>19342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>aug</td>\n",
       "      <td>2020-08-31</td>\n",
       "      <td>18901</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id    state abbreviation  month  as_of_date    pop\n",
       "0   1  alabama           AL  march  2020-03-31  21114\n",
       "1   2  alabama           AL  april  2020-04-30  20655\n",
       "2   3  alabama           AL   june  2020-06-30  19752\n",
       "3   4  alabama           AL   july  2020-07-31  19342\n",
       "4   5  alabama           AL    aug  2020-08-31  18901"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from prison_populations_apm', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "cad86287",
   "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>id</th>\n",
       "      <th>state</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>month</th>\n",
       "      <th>as_of_date</th>\n",
       "      <th>pop</th>\n",
       "      <th>notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>march</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>3686.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>july</td>\n",
       "      <td>2020-06-30</td>\n",
       "      <td>3792.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>march</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>1456.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>july</td>\n",
       "      <td>2020-07-31</td>\n",
       "      <td>1481.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>july</td>\n",
       "      <td>2020-08-01</td>\n",
       "      <td>9095.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id    state abbreviation  month  as_of_date     pop notes\n",
       "0   1  alabama           AL  march  2020-03-31  3686.0  None\n",
       "1   2  alabama           AL   july  2020-06-30  3792.0  None\n",
       "2   3   alaska           AK  march  2020-03-31  1456.0  None\n",
       "3   4   alaska           AK   july  2020-07-31  1481.0  None\n",
       "4   5  arizona           AZ   july  2020-08-01  9095.0  None"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from staff_populations_apm', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "d82b144f",
   "metadata": {
    "scrolled": true
   },
   "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>state</th>\n",
       "      <th>inmate_tests</th>\n",
       "      <th>total_inmate_cases</th>\n",
       "      <th>total_inmate_deaths</th>\n",
       "      <th>latest_inmate_population</th>\n",
       "      <th>max_inmate_population_2020</th>\n",
       "      <th>total_officer_cases</th>\n",
       "      <th>total_officer_deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>15505.0</td>\n",
       "      <td>1601</td>\n",
       "      <td>64</td>\n",
       "      <td>19144.0</td>\n",
       "      <td>21900.0</td>\n",
       "      <td>1019.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alaska</td>\n",
       "      <td>27756.0</td>\n",
       "      <td>2428</td>\n",
       "      <td>5</td>\n",
       "      <td>5011.0</td>\n",
       "      <td>5143.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>arizona</td>\n",
       "      <td>43652.0</td>\n",
       "      <td>12229</td>\n",
       "      <td>44</td>\n",
       "      <td>38248.0</td>\n",
       "      <td>41674.0</td>\n",
       "      <td>2739.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>arkansas</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11378</td>\n",
       "      <td>52</td>\n",
       "      <td>14729.0</td>\n",
       "      <td>15886.0</td>\n",
       "      <td>424.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>california</td>\n",
       "      <td>121207.0</td>\n",
       "      <td>49511</td>\n",
       "      <td>217</td>\n",
       "      <td>110471.0</td>\n",
       "      <td>117767.0</td>\n",
       "      <td>16090.0</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        state  inmate_tests  total_inmate_cases  total_inmate_deaths  \\\n",
       "0     alabama       15505.0                1601                   64   \n",
       "1      alaska       27756.0                2428                    5   \n",
       "2     arizona       43652.0               12229                   44   \n",
       "3    arkansas           0.0               11378                   52   \n",
       "4  california      121207.0               49511                  217   \n",
       "\n",
       "   latest_inmate_population  max_inmate_population_2020  total_officer_cases  \\\n",
       "0                   19144.0                     21900.0               1019.0   \n",
       "1                    5011.0                      5143.0                 85.0   \n",
       "2                   38248.0                     41674.0               2739.0   \n",
       "3                   14729.0                     15886.0                424.0   \n",
       "4                  110471.0                    117767.0              16090.0   \n",
       "\n",
       "   total_officer_deaths  \n",
       "0                   3.0  \n",
       "1                   0.0  \n",
       "2                   0.0  \n",
       "3                   5.0  \n",
       "4                  26.0  "
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from total_prison_covid_stats_by_state_nyt', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "b280cc5c",
   "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>state</th>\n",
       "      <th>cumulative_prisoner_cases</th>\n",
       "      <th>cumulative_prisoner_deaths</th>\n",
       "      <th>cumulative_staff_cases</th>\n",
       "      <th>cumulative_staff_deaths</th>\n",
       "      <th>prisoners_recovered</th>\n",
       "      <th>prisoners_partial_dose</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alabama</td>\n",
       "      <td>1662</td>\n",
       "      <td>66</td>\n",
       "      <td>1058</td>\n",
       "      <td>3</td>\n",
       "      <td>1595.0</td>\n",
       "      <td>10811.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>alaska</td>\n",
       "      <td>2440</td>\n",
       "      <td>5</td>\n",
       "      <td>332</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3460.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>arizona</td>\n",
       "      <td>12328</td>\n",
       "      <td>65</td>\n",
       "      <td>2787</td>\n",
       "      <td>1</td>\n",
       "      <td>12251.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>arkansas</td>\n",
       "      <td>11425</td>\n",
       "      <td>52</td>\n",
       "      <td>813</td>\n",
       "      <td>4</td>\n",
       "      <td>11353.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>california</td>\n",
       "      <td>49395</td>\n",
       "      <td>227</td>\n",
       "      <td>17002</td>\n",
       "      <td>28</td>\n",
       "      <td>48517.0</td>\n",
       "      <td>71317.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        state  cumulative_prisoner_cases  cumulative_prisoner_deaths  \\\n",
       "0     alabama                       1662                          66   \n",
       "1      alaska                       2440                           5   \n",
       "2     arizona                      12328                          65   \n",
       "3    arkansas                      11425                          52   \n",
       "4  california                      49395                         227   \n",
       "\n",
       "   cumulative_staff_cases  cumulative_staff_deaths  prisoners_recovered  \\\n",
       "0                    1058                        3               1595.0   \n",
       "1                     332                        0                  NaN   \n",
       "2                    2787                        1              12251.0   \n",
       "3                     813                        4              11353.0   \n",
       "4                   17002                       28              48517.0   \n",
       "\n",
       "   prisoners_partial_dose  \n",
       "0                 10811.0  \n",
       "1                  3460.0  \n",
       "2                     NaN  \n",
       "3                     NaN  \n",
       "4                 71317.0  "
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "    \"\"\"\n",
    "    SELECT \n",
    "\tpr.state, \n",
    "\tpr.cumulative_prisoner_cases, \n",
    "    pr.cumulative_prisoner_deaths, \n",
    "\tpr.cumulative_staff_cases, \n",
    "\tpr.cumulative_staff_deaths, \n",
    "\tpc.prisoners_recovered,\n",
    "    pc.prisoners_partial_dose\n",
    "from \n",
    "\tcovid_prison_rates_apm pr\n",
    "left join \n",
    "\tcovid_prison_cases_apm pc\n",
    "on \n",
    "\tpr.state = pc.state;\n",
    "    \"\"\",\n",
    "    con=engine).head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "46c781e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03fe4bdd",
   "metadata": {},
   "source": [
    "--------------------------------------------------------------\n",
    "ETL BY KUDZ NOSH & DI"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}