CDC_Overdose_Deaths_webscrape.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Project 3\n",
    "### Scrape data from CDC Opioid Deaths website"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import dependencies\n",
    "from splinter import Browser\n",
    "from bs4 import BeautifulSoup as soup\n",
    "from webdriver_manager.chrome import ChromeDriverManager\n",
    "# import matplotlib.pyplot as plt\n",
    "# import matplotlib.dates as mdates\n",
    "# import mplcursors\n",
    "import pandas as pd\n",
    "# from datetime import datetime\n",
    "# from datetime import timezone\n",
    "# import numpy as np\n",
    "import os\n",
    "import glob\n",
    "import time\n",
    "import requests\n",
    "import bs4"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[CDC Drug Overdose Deaths Site](https://www.cdc.gov/drugoverdose/deaths/index.html). Inspect the page to identify which elements to scrape."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://www.cdc.gov/drugoverdose/deaths/index.html\n",
    "# this is the baseurl - needs year concatenated to get to the data pages\n",
    "basestateUrl = 'https://www.cdc.gov/drugoverdose/deaths/'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# list of years to use with the baseurl to create a working url\n",
    "years = [2014, 2015, 2016, 2017, 2018, 2019, 2020]\n",
    "# years = [2020]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://gist.github.com/rogerallen/1583593\n",
    "#  added District Of Columbia\n",
    "us_state_to_abbrev = {\n",
    "    \"Alabama\": \"AL\",\n",
    "    \"Alaska\": \"AK\",\n",
    "    \"Arizona\": \"AZ\",\n",
    "    \"Arkansas\": \"AR\",\n",
    "    \"California\": \"CA\",\n",
    "    \"Colorado\": \"CO\",\n",
    "    \"Connecticut\": \"CT\",\n",
    "    \"Delaware\": \"DE\",\n",
    "    \"Florida\": \"FL\",\n",
    "    \"Georgia\": \"GA\",\n",
    "    \"Hawaii\": \"HI\",\n",
    "    \"Idaho\": \"ID\",\n",
    "    \"Illinois\": \"IL\",\n",
    "    \"Indiana\": \"IN\",\n",
    "    \"Iowa\": \"IA\",\n",
    "    \"Kansas\": \"KS\",\n",
    "    \"Kentucky\": \"KY\",\n",
    "    \"Louisiana\": \"LA\",\n",
    "    \"Maine\": \"ME\",\n",
    "    \"Maryland\": \"MD\",\n",
    "    \"Massachusetts\": \"MA\",\n",
    "    \"Michigan\": \"MI\",\n",
    "    \"Minnesota\": \"MN\",\n",
    "    \"Mississippi\": \"MS\",\n",
    "    \"Missouri\": \"MO\",\n",
    "    \"Montana\": \"MT\",\n",
    "    \"Nebraska\": \"NE\",\n",
    "    \"Nevada\": \"NV\",\n",
    "    \"New Hampshire\": \"NH\",\n",
    "    \"New Jersey\": \"NJ\",\n",
    "    \"New Mexico\": \"NM\",\n",
    "    \"New York\": \"NY\",\n",
    "    \"North Carolina\": \"NC\",\n",
    "    \"North Dakota\": \"ND\",\n",
    "    \"Ohio\": \"OH\",\n",
    "    \"Oklahoma\": \"OK\",\n",
    "    \"Oregon\": \"OR\",\n",
    "    \"Pennsylvania\": \"PA\",\n",
    "    \"Rhode Island\": \"RI\",\n",
    "    \"South Carolina\": \"SC\",\n",
    "    \"South Dakota\": \"SD\",\n",
    "    \"Tennessee\": \"TN\",\n",
    "    \"Texas\": \"TX\",\n",
    "    \"Utah\": \"UT\",\n",
    "    \"Vermont\": \"VT\",\n",
    "    \"Virginia\": \"VA\",\n",
    "    \"Washington\": \"WA\",\n",
    "    \"West Virginia\": \"WV\",\n",
    "    \"Wisconsin\": \"WI\",\n",
    "    \"Wyoming\": \"WY\",\n",
    "    # \"District of Columbia\": \"DC\",\n",
    "    \"District Of Columbia\": \"DC\",\n",
    "    \"American Samoa\": \"AS\",\n",
    "    \"Guam\": \"GU\",\n",
    "    \"Northern Mariana Islands\": \"MP\",\n",
    "    \"Puerto Rico\": \"PR\",\n",
    "    \"United States Minor Outlying Islands\": \"UM\",\n",
    "    \"U.S. Virgin Islands\": \"VI\",\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this opens the test browser\n",
    "executable_path = {'executable_path': ChromeDriverManager().install()}\n",
    "browser = Browser('chrome', **executable_path, headless=False)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "need to start for loop here\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.cdc.gov/drugoverdose/deaths/2014.html\n",
      "https://www.cdc.gov/drugoverdose/deaths/2015.html\n",
      "https://www.cdc.gov/drugoverdose/deaths/2016.html\n",
      "https://www.cdc.gov/drugoverdose/deaths/2017.html\n",
      "https://www.cdc.gov/drugoverdose/deaths/2018.html\n",
      "https://www.cdc.gov/drugoverdose/deaths/2019.html\n",
      "https://www.cdc.gov/drugoverdose/deaths/2020.html\n"
     ]
    }
   ],
   "source": [
    "# THIS WILL BE A HUGE CHUNK \n",
    "# check url and visit website\n",
    "\n",
    "for year in years:\n",
    "\n",
    "    url = f'{basestateUrl}{year}.html'\n",
    "    print(url)\n",
    "    browser.visit(url)\n",
    "\n",
    "    time.sleep(5)\n",
    "    \n",
    "    # read the loaded html into a table\n",
    "    html = browser.html\n",
    "    tablelist = pd.read_html(html)\n",
    "    tabledf = tablelist[0]\n",
    "    # https://www.geeksforgeeks.org/rename-column-by-index-in-pandas/\n",
    "    tabledf.columns.values[0] = 'State'\n",
    "    tabledf.columns.values[1] = 'Range_category'\n",
    "    tabledf.columns.values[2] = 'Age_adjusted_rate'\n",
    "    tabledf.columns.values[3] = 'Number_of_Deaths'\n",
    "    tabledf['Year'] = year\n",
    "    tabledf['Abbreviation'] = tabledf['State'].map(us_state_to_abbrev)\n",
    "    tabledff=tabledf[['State','Abbreviation','Age_adjusted_rate','Number_of_Deaths','Year']]\n",
    "    tabledff.to_csv(f'Resources/ODs/DeathState{year}.csv', index = False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# close browser\n",
    "browser.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['DeathState2014.csv',\n",
       " 'DeathState2015.csv',\n",
       " 'DeathState2016.csv',\n",
       " 'DeathState2017.csv',\n",
       " 'DeathState2018.csv',\n",
       " 'DeathState2019.csv',\n",
       " 'DeathState2020.csv']"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get list of csvs in State directory\n",
    "allFiles = os.listdir(\"Resources/ODs\")    \n",
    "csvFiles = list(filter(lambda f: f.endswith('.csv'), allFiles))\n",
    "csvFiles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create list of dataframes\n",
    "dfs = []\n",
    "for csvFile in csvFiles:\n",
    "    dfs.append(pd.read_csv(f'Resources/ODs/{csvFile}'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     State Abbreviation  Age_adjusted_rate  Number_of_Deaths  Year\n",
      "0  Alabama           AL               18.0               835  2017\n",
      "1   Alaska           AK               20.2               147  2017\n"
     ]
    },
    {
     "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>Age_adjusted_rate</th>\n",
       "      <th>Number_of_Deaths</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>22.3</td>\n",
       "      <td>1029</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>22.0</td>\n",
       "      <td>160</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     State Abbreviation  Age_adjusted_rate  Number_of_Deaths  Year\n",
       "0  Alabama           AL               22.3              1029  2020\n",
       "1   Alaska           AK               22.0               160  2020"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  check some dfs in list\n",
    "print(dfs[3].head(2))\n",
    "dfs[6].head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2020"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check return on year\n",
    "yearcheck = dfs[6]['Year'].min()\n",
    "yearcheck"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dlite will be used to recreate csv for Brandon's choropleth\n",
    "dlite = []\n",
    "for d in dfs:\n",
    "    year = d['Year'].min()\n",
    "    dd = d.rename(columns = {'Number_of_Deaths':f'{year}_Number_of_Deaths','Abbreviation': 'code','State':'state'}) \n",
    "    # ddd = dd.drop(columns = ['Year','Age_adjusted_rate'])\n",
    "    dd['category'] = 'state'\n",
    "    ddd = dd[['code','state','category',f'{year}_Number_of_Deaths']]\n",
    "    dlite.append(ddd)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(dlite)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  code    state category  2017_Number_of_Deaths\n",
      "0   AL  Alabama    state                    835\n",
      "1   AK   Alaska    state                    147\n"
     ]
    },
    {
     "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>code</th>\n",
       "      <th>state</th>\n",
       "      <th>category</th>\n",
       "      <th>2018_Number_of_Deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>state</td>\n",
       "      <td>775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>state</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code    state category  2018_Number_of_Deaths\n",
       "0   AL  Alabama    state                    775\n",
       "1   AK   Alaska    state                    110"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  check some dfs in list\n",
    "print(dlite[3].head(2))\n",
    "dlite[4].head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "wideFramedf0 = pd.merge(dlite[0],dlite[1], on=['code','state','category'], how = 'outer')\n",
    "wideFramedf1 = pd.merge(dlite[2],dlite[3], on=['code','state','category'], how = 'outer')\n",
    "wideFramedf2 = pd.merge(dlite[4],dlite[5], on=['code','state','category'], how = 'outer')\n",
    "# wideFramedf3 = pd.merge(dlite[6],dlite[7], on=['code','state'], how = 'outer')\n",
    "# wideFramedf4 = pd.merge(dlite[8],dlite[9], on=['code','state'], how = 'outer')\n",
    "# wideFramedf5 = pd.merge(dlite[10],dlite[11], on=['code','state'], how = 'outer')\n",
    "# wideFramedf6 = pd.merge(dlite[12],dlite[13], on=['code','state'], how = 'outer')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "wf0 = pd.merge(wideFramedf0,wideFramedf1, on=['code','state','category'], how = 'outer')\n",
    "wf1 = pd.merge(wideFramedf2,dlite[6], on=['code','state','category'], how = 'outer')\n",
    "# wf2 = pd.merge(wideFramedf4,wideFramedf5, on=['code','state'], how = 'outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this merge level not needed\n",
    "# wff0 = pd.merge(wf0,wf1, on=['code','state'], how = 'outer')\n",
    "# wff1 = pd.merge(wf2, wideFramedf6, on=['code','state'], how = 'outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "wideframe = pd.merge(wf0, wf1, on=['code','state','category'], how = 'outer')\n",
    "wideframe['total_deaths'] = wideframe['2014_Number_of_Deaths'] + wideframe['2014_Number_of_Deaths'] + wideframe['2015_Number_of_Deaths'] + wideframe['2016_Number_of_Deaths'] + wideframe['2017_Number_of_Deaths'] + wideframe['2018_Number_of_Deaths'] + wideframe['2019_Number_of_Deaths'] \n",
    "wideframe2 = wideframe[['code', 'state', 'category', 'total_deaths', '2014_Number_of_Deaths',\n",
    "       '2015_Number_of_Deaths', '2016_Number_of_Deaths',\n",
    "       '2017_Number_of_Deaths', '2018_Number_of_Deaths',\n",
    "       '2019_Number_of_Deaths', '2020_Number_of_Deaths']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['code', 'state', 'category', '2014_Number_of_Deaths',\n",
       "       '2015_Number_of_Deaths', '2016_Number_of_Deaths',\n",
       "       '2017_Number_of_Deaths', '2018_Number_of_Deaths',\n",
       "       '2019_Number_of_Deaths', '2020_Number_of_Deaths', 'total_deaths'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wideframe.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>state</th>\n",
       "      <th>category</th>\n",
       "      <th>total_deaths</th>\n",
       "      <th>2014_Number_of_Deaths</th>\n",
       "      <th>2015_Number_of_Deaths</th>\n",
       "      <th>2016_Number_of_Deaths</th>\n",
       "      <th>2017_Number_of_Deaths</th>\n",
       "      <th>2018_Number_of_Deaths</th>\n",
       "      <th>2019_Number_of_Deaths</th>\n",
       "      <th>2020_Number_of_Deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>state</td>\n",
       "      <td>5316</td>\n",
       "      <td>723</td>\n",
       "      <td>736</td>\n",
       "      <td>756</td>\n",
       "      <td>835</td>\n",
       "      <td>775</td>\n",
       "      <td>768</td>\n",
       "      <td>1029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>state</td>\n",
       "      <td>887</td>\n",
       "      <td>124</td>\n",
       "      <td>122</td>\n",
       "      <td>128</td>\n",
       "      <td>147</td>\n",
       "      <td>110</td>\n",
       "      <td>132</td>\n",
       "      <td>160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AZ</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>state</td>\n",
       "      <td>10187</td>\n",
       "      <td>1211</td>\n",
       "      <td>1274</td>\n",
       "      <td>1382</td>\n",
       "      <td>1532</td>\n",
       "      <td>1670</td>\n",
       "      <td>1907</td>\n",
       "      <td>2550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AR</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>state</td>\n",
       "      <td>2783</td>\n",
       "      <td>356</td>\n",
       "      <td>392</td>\n",
       "      <td>401</td>\n",
       "      <td>446</td>\n",
       "      <td>444</td>\n",
       "      <td>388</td>\n",
       "      <td>546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CA</td>\n",
       "      <td>California</td>\n",
       "      <td>state</td>\n",
       "      <td>34769</td>\n",
       "      <td>4521</td>\n",
       "      <td>4659</td>\n",
       "      <td>4654</td>\n",
       "      <td>4868</td>\n",
       "      <td>5348</td>\n",
       "      <td>6198</td>\n",
       "      <td>8908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CO</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>state</td>\n",
       "      <td>6698</td>\n",
       "      <td>899</td>\n",
       "      <td>869</td>\n",
       "      <td>942</td>\n",
       "      <td>1015</td>\n",
       "      <td>995</td>\n",
       "      <td>1079</td>\n",
       "      <td>1492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CT</td>\n",
       "      <td>Connecticut</td>\n",
       "      <td>state</td>\n",
       "      <td>6372</td>\n",
       "      <td>623</td>\n",
       "      <td>800</td>\n",
       "      <td>971</td>\n",
       "      <td>1072</td>\n",
       "      <td>1069</td>\n",
       "      <td>1214</td>\n",
       "      <td>1371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>DE</td>\n",
       "      <td>Delaware</td>\n",
       "      <td>state</td>\n",
       "      <td>2032</td>\n",
       "      <td>189</td>\n",
       "      <td>198</td>\n",
       "      <td>282</td>\n",
       "      <td>338</td>\n",
       "      <td>401</td>\n",
       "      <td>435</td>\n",
       "      <td>444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>DC</td>\n",
       "      <td>District Of Columbia</td>\n",
       "      <td>state</td>\n",
       "      <td>1461</td>\n",
       "      <td>96</td>\n",
       "      <td>125</td>\n",
       "      <td>269</td>\n",
       "      <td>310</td>\n",
       "      <td>254</td>\n",
       "      <td>311</td>\n",
       "      <td>424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>FL</td>\n",
       "      <td>Florida</td>\n",
       "      <td>state</td>\n",
       "      <td>28278</td>\n",
       "      <td>2634</td>\n",
       "      <td>3228</td>\n",
       "      <td>4728</td>\n",
       "      <td>5088</td>\n",
       "      <td>4698</td>\n",
       "      <td>5268</td>\n",
       "      <td>7231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>GA</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>state</td>\n",
       "      <td>9457</td>\n",
       "      <td>1206</td>\n",
       "      <td>1302</td>\n",
       "      <td>1394</td>\n",
       "      <td>1537</td>\n",
       "      <td>1404</td>\n",
       "      <td>1408</td>\n",
       "      <td>1916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>HI</td>\n",
       "      <td>Hawaii</td>\n",
       "      <td>state</td>\n",
       "      <td>1332</td>\n",
       "      <td>157</td>\n",
       "      <td>169</td>\n",
       "      <td>191</td>\n",
       "      <td>203</td>\n",
       "      <td>213</td>\n",
       "      <td>242</td>\n",
       "      <td>274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>ID</td>\n",
       "      <td>Idaho</td>\n",
       "      <td>state</td>\n",
       "      <td>1636</td>\n",
       "      <td>212</td>\n",
       "      <td>218</td>\n",
       "      <td>243</td>\n",
       "      <td>236</td>\n",
       "      <td>250</td>\n",
       "      <td>265</td>\n",
       "      <td>287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>IL</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>state</td>\n",
       "      <td>15946</td>\n",
       "      <td>1705</td>\n",
       "      <td>1835</td>\n",
       "      <td>2411</td>\n",
       "      <td>2778</td>\n",
       "      <td>2722</td>\n",
       "      <td>2790</td>\n",
       "      <td>3549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>IN</td>\n",
       "      <td>Indiana</td>\n",
       "      <td>state</td>\n",
       "      <td>10295</td>\n",
       "      <td>1172</td>\n",
       "      <td>1245</td>\n",
       "      <td>1526</td>\n",
       "      <td>1852</td>\n",
       "      <td>1629</td>\n",
       "      <td>1699</td>\n",
       "      <td>2321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>IA</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>state</td>\n",
       "      <td>2131</td>\n",
       "      <td>264</td>\n",
       "      <td>309</td>\n",
       "      <td>314</td>\n",
       "      <td>341</td>\n",
       "      <td>287</td>\n",
       "      <td>352</td>\n",
       "      <td>432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>KS</td>\n",
       "      <td>Kansas</td>\n",
       "      <td>state</td>\n",
       "      <td>2387</td>\n",
       "      <td>332</td>\n",
       "      <td>329</td>\n",
       "      <td>313</td>\n",
       "      <td>333</td>\n",
       "      <td>345</td>\n",
       "      <td>403</td>\n",
       "      <td>490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>KY</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>state</td>\n",
       "      <td>9107</td>\n",
       "      <td>1077</td>\n",
       "      <td>1273</td>\n",
       "      <td>1419</td>\n",
       "      <td>1566</td>\n",
       "      <td>1315</td>\n",
       "      <td>1380</td>\n",
       "      <td>2083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>LA</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>state</td>\n",
       "      <td>6926</td>\n",
       "      <td>777</td>\n",
       "      <td>861</td>\n",
       "      <td>996</td>\n",
       "      <td>1108</td>\n",
       "      <td>1140</td>\n",
       "      <td>1267</td>\n",
       "      <td>1896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>ME</td>\n",
       "      <td>Maine</td>\n",
       "      <td>state</td>\n",
       "      <td>2194</td>\n",
       "      <td>216</td>\n",
       "      <td>269</td>\n",
       "      <td>353</td>\n",
       "      <td>424</td>\n",
       "      <td>345</td>\n",
       "      <td>371</td>\n",
       "      <td>496</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>MD</td>\n",
       "      <td>Maryland</td>\n",
       "      <td>state</td>\n",
       "      <td>12409</td>\n",
       "      <td>1070</td>\n",
       "      <td>1285</td>\n",
       "      <td>2044</td>\n",
       "      <td>2247</td>\n",
       "      <td>2324</td>\n",
       "      <td>2369</td>\n",
       "      <td>2771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>MA</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>state</td>\n",
       "      <td>13148</td>\n",
       "      <td>1289</td>\n",
       "      <td>1724</td>\n",
       "      <td>2227</td>\n",
       "      <td>2168</td>\n",
       "      <td>2241</td>\n",
       "      <td>2210</td>\n",
       "      <td>2302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>MI</td>\n",
       "      <td>Michigan</td>\n",
       "      <td>state</td>\n",
       "      <td>15521</td>\n",
       "      <td>1762</td>\n",
       "      <td>1980</td>\n",
       "      <td>2347</td>\n",
       "      <td>2694</td>\n",
       "      <td>2591</td>\n",
       "      <td>2385</td>\n",
       "      <td>2759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>MN</td>\n",
       "      <td>Minnesota</td>\n",
       "      <td>state</td>\n",
       "      <td>4448</td>\n",
       "      <td>517</td>\n",
       "      <td>581</td>\n",
       "      <td>672</td>\n",
       "      <td>733</td>\n",
       "      <td>636</td>\n",
       "      <td>792</td>\n",
       "      <td>1050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>MS</td>\n",
       "      <td>Mississippi</td>\n",
       "      <td>state</td>\n",
       "      <td>2433</td>\n",
       "      <td>336</td>\n",
       "      <td>351</td>\n",
       "      <td>352</td>\n",
       "      <td>354</td>\n",
       "      <td>310</td>\n",
       "      <td>394</td>\n",
       "      <td>586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>MO</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>state</td>\n",
       "      <td>9131</td>\n",
       "      <td>1067</td>\n",
       "      <td>1066</td>\n",
       "      <td>1371</td>\n",
       "      <td>1367</td>\n",
       "      <td>1610</td>\n",
       "      <td>1583</td>\n",
       "      <td>1875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>MT</td>\n",
       "      <td>Montana</td>\n",
       "      <td>state</td>\n",
       "      <td>894</td>\n",
       "      <td>125</td>\n",
       "      <td>138</td>\n",
       "      <td>119</td>\n",
       "      <td>119</td>\n",
       "      <td>125</td>\n",
       "      <td>143</td>\n",
       "      <td>162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>NE</td>\n",
       "      <td>Nebraska</td>\n",
       "      <td>state</td>\n",
       "      <td>947</td>\n",
       "      <td>125</td>\n",
       "      <td>126</td>\n",
       "      <td>120</td>\n",
       "      <td>152</td>\n",
       "      <td>138</td>\n",
       "      <td>161</td>\n",
       "      <td>214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>NV</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>state</td>\n",
       "      <td>4385</td>\n",
       "      <td>545</td>\n",
       "      <td>619</td>\n",
       "      <td>665</td>\n",
       "      <td>676</td>\n",
       "      <td>688</td>\n",
       "      <td>647</td>\n",
       "      <td>832</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>NH</td>\n",
       "      <td>New Hampshire</td>\n",
       "      <td>state</td>\n",
       "      <td>2897</td>\n",
       "      <td>334</td>\n",
       "      <td>422</td>\n",
       "      <td>481</td>\n",
       "      <td>467</td>\n",
       "      <td>452</td>\n",
       "      <td>407</td>\n",
       "      <td>393</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>NJ</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>state</td>\n",
       "      <td>14406</td>\n",
       "      <td>1253</td>\n",
       "      <td>1454</td>\n",
       "      <td>2056</td>\n",
       "      <td>2685</td>\n",
       "      <td>2900</td>\n",
       "      <td>2805</td>\n",
       "      <td>2840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>NM</td>\n",
       "      <td>New Mexico</td>\n",
       "      <td>state</td>\n",
       "      <td>3724</td>\n",
       "      <td>547</td>\n",
       "      <td>501</td>\n",
       "      <td>500</td>\n",
       "      <td>493</td>\n",
       "      <td>537</td>\n",
       "      <td>599</td>\n",
       "      <td>784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>NY</td>\n",
       "      <td>New York</td>\n",
       "      <td>state</td>\n",
       "      <td>22227</td>\n",
       "      <td>2300</td>\n",
       "      <td>2754</td>\n",
       "      <td>3638</td>\n",
       "      <td>3921</td>\n",
       "      <td>3697</td>\n",
       "      <td>3617</td>\n",
       "      <td>4965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>NC</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>state</td>\n",
       "      <td>13178</td>\n",
       "      <td>1358</td>\n",
       "      <td>1567</td>\n",
       "      <td>1956</td>\n",
       "      <td>2414</td>\n",
       "      <td>2259</td>\n",
       "      <td>2266</td>\n",
       "      <td>3146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>ND</td>\n",
       "      <td>North Dakota</td>\n",
       "      <td>state</td>\n",
       "      <td>444</td>\n",
       "      <td>43</td>\n",
       "      <td>61</td>\n",
       "      <td>77</td>\n",
       "      <td>68</td>\n",
       "      <td>70</td>\n",
       "      <td>82</td>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>OH</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>state</td>\n",
       "      <td>26469</td>\n",
       "      <td>2744</td>\n",
       "      <td>3310</td>\n",
       "      <td>4329</td>\n",
       "      <td>5111</td>\n",
       "      <td>3980</td>\n",
       "      <td>4251</td>\n",
       "      <td>5204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>OK</td>\n",
       "      <td>Oklahoma</td>\n",
       "      <td>state</td>\n",
       "      <td>5228</td>\n",
       "      <td>777</td>\n",
       "      <td>725</td>\n",
       "      <td>813</td>\n",
       "      <td>775</td>\n",
       "      <td>716</td>\n",
       "      <td>645</td>\n",
       "      <td>762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>OR</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>state</td>\n",
       "      <td>3747</td>\n",
       "      <td>522</td>\n",
       "      <td>505</td>\n",
       "      <td>506</td>\n",
       "      <td>530</td>\n",
       "      <td>547</td>\n",
       "      <td>615</td>\n",
       "      <td>803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>PA</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>state</td>\n",
       "      <td>27535</td>\n",
       "      <td>2732</td>\n",
       "      <td>3264</td>\n",
       "      <td>4627</td>\n",
       "      <td>5388</td>\n",
       "      <td>4415</td>\n",
       "      <td>4377</td>\n",
       "      <td>5168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>RI</td>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>state</td>\n",
       "      <td>2074</td>\n",
       "      <td>247</td>\n",
       "      <td>310</td>\n",
       "      <td>326</td>\n",
       "      <td>320</td>\n",
       "      <td>317</td>\n",
       "      <td>307</td>\n",
       "      <td>397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>SC</td>\n",
       "      <td>South Carolina</td>\n",
       "      <td>state</td>\n",
       "      <td>6302</td>\n",
       "      <td>701</td>\n",
       "      <td>761</td>\n",
       "      <td>879</td>\n",
       "      <td>1008</td>\n",
       "      <td>1125</td>\n",
       "      <td>1127</td>\n",
       "      <td>1739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>SD</td>\n",
       "      <td>South Dakota</td>\n",
       "      <td>state</td>\n",
       "      <td>476</td>\n",
       "      <td>63</td>\n",
       "      <td>65</td>\n",
       "      <td>69</td>\n",
       "      <td>73</td>\n",
       "      <td>57</td>\n",
       "      <td>86</td>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>TN</td>\n",
       "      <td>Tennessee</td>\n",
       "      <td>state</td>\n",
       "      <td>11313</td>\n",
       "      <td>1269</td>\n",
       "      <td>1457</td>\n",
       "      <td>1630</td>\n",
       "      <td>1776</td>\n",
       "      <td>1823</td>\n",
       "      <td>2089</td>\n",
       "      <td>3034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>TX</td>\n",
       "      <td>Texas</td>\n",
       "      <td>state</td>\n",
       "      <td>19751</td>\n",
       "      <td>2601</td>\n",
       "      <td>2588</td>\n",
       "      <td>2831</td>\n",
       "      <td>2989</td>\n",
       "      <td>3005</td>\n",
       "      <td>3136</td>\n",
       "      <td>4172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>UT</td>\n",
       "      <td>Utah</td>\n",
       "      <td>state</td>\n",
       "      <td>4332</td>\n",
       "      <td>603</td>\n",
       "      <td>646</td>\n",
       "      <td>635</td>\n",
       "      <td>650</td>\n",
       "      <td>624</td>\n",
       "      <td>571</td>\n",
       "      <td>622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>VT</td>\n",
       "      <td>Vermont</td>\n",
       "      <td>state</td>\n",
       "      <td>810</td>\n",
       "      <td>83</td>\n",
       "      <td>99</td>\n",
       "      <td>125</td>\n",
       "      <td>134</td>\n",
       "      <td>153</td>\n",
       "      <td>133</td>\n",
       "      <td>190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>VA</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>state</td>\n",
       "      <td>8906</td>\n",
       "      <td>980</td>\n",
       "      <td>1039</td>\n",
       "      <td>1405</td>\n",
       "      <td>1507</td>\n",
       "      <td>1448</td>\n",
       "      <td>1547</td>\n",
       "      <td>2240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>WA</td>\n",
       "      <td>Washington</td>\n",
       "      <td>state</td>\n",
       "      <td>7746</td>\n",
       "      <td>979</td>\n",
       "      <td>1094</td>\n",
       "      <td>1102</td>\n",
       "      <td>1169</td>\n",
       "      <td>1164</td>\n",
       "      <td>1259</td>\n",
       "      <td>1733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>WV</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>state</td>\n",
       "      <td>5563</td>\n",
       "      <td>627</td>\n",
       "      <td>725</td>\n",
       "      <td>884</td>\n",
       "      <td>974</td>\n",
       "      <td>856</td>\n",
       "      <td>870</td>\n",
       "      <td>1330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>WI</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>state</td>\n",
       "      <td>7115</td>\n",
       "      <td>853</td>\n",
       "      <td>878</td>\n",
       "      <td>1074</td>\n",
       "      <td>1177</td>\n",
       "      <td>1079</td>\n",
       "      <td>1201</td>\n",
       "      <td>1531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>WY</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>state</td>\n",
       "      <td>627</td>\n",
       "      <td>109</td>\n",
       "      <td>96</td>\n",
       "      <td>99</td>\n",
       "      <td>69</td>\n",
       "      <td>66</td>\n",
       "      <td>79</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   code                 state category  total_deaths  2014_Number_of_Deaths  \\\n",
       "0    AL               Alabama    state          5316                    723   \n",
       "1    AK                Alaska    state           887                    124   \n",
       "2    AZ               Arizona    state         10187                   1211   \n",
       "3    AR              Arkansas    state          2783                    356   \n",
       "4    CA            California    state         34769                   4521   \n",
       "5    CO              Colorado    state          6698                    899   \n",
       "6    CT           Connecticut    state          6372                    623   \n",
       "7    DE              Delaware    state          2032                    189   \n",
       "8    DC  District Of Columbia    state          1461                     96   \n",
       "9    FL               Florida    state         28278                   2634   \n",
       "10   GA               Georgia    state          9457                   1206   \n",
       "11   HI                Hawaii    state          1332                    157   \n",
       "12   ID                 Idaho    state          1636                    212   \n",
       "13   IL              Illinois    state         15946                   1705   \n",
       "14   IN               Indiana    state         10295                   1172   \n",
       "15   IA                  Iowa    state          2131                    264   \n",
       "16   KS                Kansas    state          2387                    332   \n",
       "17   KY              Kentucky    state          9107                   1077   \n",
       "18   LA             Louisiana    state          6926                    777   \n",
       "19   ME                 Maine    state          2194                    216   \n",
       "20   MD              Maryland    state         12409                   1070   \n",
       "21   MA         Massachusetts    state         13148                   1289   \n",
       "22   MI              Michigan    state         15521                   1762   \n",
       "23   MN             Minnesota    state          4448                    517   \n",
       "24   MS           Mississippi    state          2433                    336   \n",
       "25   MO              Missouri    state          9131                   1067   \n",
       "26   MT               Montana    state           894                    125   \n",
       "27   NE              Nebraska    state           947                    125   \n",
       "28   NV                Nevada    state          4385                    545   \n",
       "29   NH         New Hampshire    state          2897                    334   \n",
       "30   NJ            New Jersey    state         14406                   1253   \n",
       "31   NM            New Mexico    state          3724                    547   \n",
       "32   NY              New York    state         22227                   2300   \n",
       "33   NC        North Carolina    state         13178                   1358   \n",
       "34   ND          North Dakota    state           444                     43   \n",
       "35   OH                  Ohio    state         26469                   2744   \n",
       "36   OK              Oklahoma    state          5228                    777   \n",
       "37   OR                Oregon    state          3747                    522   \n",
       "38   PA          Pennsylvania    state         27535                   2732   \n",
       "39   RI          Rhode Island    state          2074                    247   \n",
       "40   SC        South Carolina    state          6302                    701   \n",
       "41   SD          South Dakota    state           476                     63   \n",
       "42   TN             Tennessee    state         11313                   1269   \n",
       "43   TX                 Texas    state         19751                   2601   \n",
       "44   UT                  Utah    state          4332                    603   \n",
       "45   VT               Vermont    state           810                     83   \n",
       "46   VA              Virginia    state          8906                    980   \n",
       "47   WA            Washington    state          7746                    979   \n",
       "48   WV         West Virginia    state          5563                    627   \n",
       "49   WI             Wisconsin    state          7115                    853   \n",
       "50   WY               Wyoming    state           627                    109   \n",
       "\n",
       "    2015_Number_of_Deaths  2016_Number_of_Deaths  2017_Number_of_Deaths  \\\n",
       "0                     736                    756                    835   \n",
       "1                     122                    128                    147   \n",
       "2                    1274                   1382                   1532   \n",
       "3                     392                    401                    446   \n",
       "4                    4659                   4654                   4868   \n",
       "5                     869                    942                   1015   \n",
       "6                     800                    971                   1072   \n",
       "7                     198                    282                    338   \n",
       "8                     125                    269                    310   \n",
       "9                    3228                   4728                   5088   \n",
       "10                   1302                   1394                   1537   \n",
       "11                    169                    191                    203   \n",
       "12                    218                    243                    236   \n",
       "13                   1835                   2411                   2778   \n",
       "14                   1245                   1526                   1852   \n",
       "15                    309                    314                    341   \n",
       "16                    329                    313                    333   \n",
       "17                   1273                   1419                   1566   \n",
       "18                    861                    996                   1108   \n",
       "19                    269                    353                    424   \n",
       "20                   1285                   2044                   2247   \n",
       "21                   1724                   2227                   2168   \n",
       "22                   1980                   2347                   2694   \n",
       "23                    581                    672                    733   \n",
       "24                    351                    352                    354   \n",
       "25                   1066                   1371                   1367   \n",
       "26                    138                    119                    119   \n",
       "27                    126                    120                    152   \n",
       "28                    619                    665                    676   \n",
       "29                    422                    481                    467   \n",
       "30                   1454                   2056                   2685   \n",
       "31                    501                    500                    493   \n",
       "32                   2754                   3638                   3921   \n",
       "33                   1567                   1956                   2414   \n",
       "34                     61                     77                     68   \n",
       "35                   3310                   4329                   5111   \n",
       "36                    725                    813                    775   \n",
       "37                    505                    506                    530   \n",
       "38                   3264                   4627                   5388   \n",
       "39                    310                    326                    320   \n",
       "40                    761                    879                   1008   \n",
       "41                     65                     69                     73   \n",
       "42                   1457                   1630                   1776   \n",
       "43                   2588                   2831                   2989   \n",
       "44                    646                    635                    650   \n",
       "45                     99                    125                    134   \n",
       "46                   1039                   1405                   1507   \n",
       "47                   1094                   1102                   1169   \n",
       "48                    725                    884                    974   \n",
       "49                    878                   1074                   1177   \n",
       "50                     96                     99                     69   \n",
       "\n",
       "    2018_Number_of_Deaths  2019_Number_of_Deaths  2020_Number_of_Deaths  \n",
       "0                     775                    768                   1029  \n",
       "1                     110                    132                    160  \n",
       "2                    1670                   1907                   2550  \n",
       "3                     444                    388                    546  \n",
       "4                    5348                   6198                   8908  \n",
       "5                     995                   1079                   1492  \n",
       "6                    1069                   1214                   1371  \n",
       "7                     401                    435                    444  \n",
       "8                     254                    311                    424  \n",
       "9                    4698                   5268                   7231  \n",
       "10                   1404                   1408                   1916  \n",
       "11                    213                    242                    274  \n",
       "12                    250                    265                    287  \n",
       "13                   2722                   2790                   3549  \n",
       "14                   1629                   1699                   2321  \n",
       "15                    287                    352                    432  \n",
       "16                    345                    403                    490  \n",
       "17                   1315                   1380                   2083  \n",
       "18                   1140                   1267                   1896  \n",
       "19                    345                    371                    496  \n",
       "20                   2324                   2369                   2771  \n",
       "21                   2241                   2210                   2302  \n",
       "22                   2591                   2385                   2759  \n",
       "23                    636                    792                   1050  \n",
       "24                    310                    394                    586  \n",
       "25                   1610                   1583                   1875  \n",
       "26                    125                    143                    162  \n",
       "27                    138                    161                    214  \n",
       "28                    688                    647                    832  \n",
       "29                    452                    407                    393  \n",
       "30                   2900                   2805                   2840  \n",
       "31                    537                    599                    784  \n",
       "32                   3697                   3617                   4965  \n",
       "33                   2259                   2266                   3146  \n",
       "34                     70                     82                    114  \n",
       "35                   3980                   4251                   5204  \n",
       "36                    716                    645                    762  \n",
       "37                    547                    615                    803  \n",
       "38                   4415                   4377                   5168  \n",
       "39                    317                    307                    397  \n",
       "40                   1125                   1127                   1739  \n",
       "41                     57                     86                     83  \n",
       "42                   1823                   2089                   3034  \n",
       "43                   3005                   3136                   4172  \n",
       "44                    624                    571                    622  \n",
       "45                    153                    133                    190  \n",
       "46                   1448                   1547                   2240  \n",
       "47                   1164                   1259                   1733  \n",
       "48                    856                    870                   1330  \n",
       "49                   1079                   1201                   1531  \n",
       "50                     66                     79                     99  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "# this creates OpioidDeathsAll-cleaned-3.csv  and this data will be transposed for use in the barchart\n",
    "wideframe2.to_csv('Resources/ODs/OpioidDeathsWideframe.csv', index = False)\n",
    "wideframe2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# update table to prep for transposing in next step\n",
    "wf = wideframe.drop(columns = ['state','category','total_deaths'])\n",
    "wff = wf.rename(columns = {'code':'Overdose_Deaths_per_Year','2014_Number_of_Deaths':'2014',\n",
    "       '2015_Number_of_Deaths':'2015', '2016_Number_of_Deaths':'2016',\n",
    "       '2017_Number_of_Deaths':'2017', '2018_Number_of_Deaths':'2018',\n",
    "       '2019_Number_of_Deaths':'2019', '2020_Number_of_Deaths':'2020'})\n",
    "\n",
    "\n",
    "wfff = wff.set_index('Overdose_Deaths_per_Year')# to prepare for next step - transpose table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Overdose_Deaths_per_Year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AL</th>\n",
       "      <td>723</td>\n",
       "      <td>736</td>\n",
       "      <td>756</td>\n",
       "      <td>835</td>\n",
       "      <td>775</td>\n",
       "      <td>768</td>\n",
       "      <td>1029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AK</th>\n",
       "      <td>124</td>\n",
       "      <td>122</td>\n",
       "      <td>128</td>\n",
       "      <td>147</td>\n",
       "      <td>110</td>\n",
       "      <td>132</td>\n",
       "      <td>160</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          2014  2015  2016  2017  2018  2019  2020\n",
       "Overdose_Deaths_per_Year                                          \n",
       "AL                         723   736   756   835   775   768  1029\n",
       "AK                         124   122   128   147   110   132   160"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wfff.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Overdose_Deaths_per_Year</th>\n",
       "      <th>Year</th>\n",
       "      <th>AL</th>\n",
       "      <th>AK</th>\n",
       "      <th>AZ</th>\n",
       "      <th>AR</th>\n",
       "      <th>CA</th>\n",
       "      <th>CO</th>\n",
       "      <th>CT</th>\n",
       "      <th>DE</th>\n",
       "      <th>DC</th>\n",
       "      <th>...</th>\n",
       "      <th>SD</th>\n",
       "      <th>TN</th>\n",
       "      <th>TX</th>\n",
       "      <th>UT</th>\n",
       "      <th>VT</th>\n",
       "      <th>VA</th>\n",
       "      <th>WA</th>\n",
       "      <th>WV</th>\n",
       "      <th>WI</th>\n",
       "      <th>WY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014</td>\n",
       "      <td>723</td>\n",
       "      <td>124</td>\n",
       "      <td>1211</td>\n",
       "      <td>356</td>\n",
       "      <td>4521</td>\n",
       "      <td>899</td>\n",
       "      <td>623</td>\n",
       "      <td>189</td>\n",
       "      <td>96</td>\n",
       "      <td>...</td>\n",
       "      <td>63</td>\n",
       "      <td>1269</td>\n",
       "      <td>2601</td>\n",
       "      <td>603</td>\n",
       "      <td>83</td>\n",
       "      <td>980</td>\n",
       "      <td>979</td>\n",
       "      <td>627</td>\n",
       "      <td>853</td>\n",
       "      <td>109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "      <td>736</td>\n",
       "      <td>122</td>\n",
       "      <td>1274</td>\n",
       "      <td>392</td>\n",
       "      <td>4659</td>\n",
       "      <td>869</td>\n",
       "      <td>800</td>\n",
       "      <td>198</td>\n",
       "      <td>125</td>\n",
       "      <td>...</td>\n",
       "      <td>65</td>\n",
       "      <td>1457</td>\n",
       "      <td>2588</td>\n",
       "      <td>646</td>\n",
       "      <td>99</td>\n",
       "      <td>1039</td>\n",
       "      <td>1094</td>\n",
       "      <td>725</td>\n",
       "      <td>878</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 52 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Overdose_Deaths_per_Year  Year   AL   AK    AZ   AR    CA   CO   CT   DE   DC  \\\n",
       "0                         2014  723  124  1211  356  4521  899  623  189   96   \n",
       "1                         2015  736  122  1274  392  4659  869  800  198  125   \n",
       "\n",
       "Overdose_Deaths_per_Year  ...  SD    TN    TX   UT  VT    VA    WA   WV   WI  \\\n",
       "0                         ...  63  1269  2601  603  83   980   979  627  853   \n",
       "1                         ...  65  1457  2588  646  99  1039  1094  725  878   \n",
       "\n",
       "Overdose_Deaths_per_Year   WY  \n",
       "0                         109  \n",
       "1                          96  \n",
       "\n",
       "[2 rows x 52 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# transpose table into format for barchart use and save to csv\n",
    "wideframetrans = pd.DataFrame(wfff).transpose().reset_index()\n",
    "# wideframetrans\n",
    "# wft = wideframetrans.reset_index()\n",
    "wft= wideframetrans.rename(columns = {'index': 'Year'})\n",
    "wft.to_csv('Resources/ODs/OpioidDeathsAllTransposed.csv', index = False)\n",
    "wft.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create json file from csv - json will be used for interactive barcharts on the website\n",
    "df = pd.read_csv (r'Resources/ODs/OpioidDeathsAllTransposed.csv')\n",
    "df.to_json (r'Resources/ODs/OpioidDeathsAllTransposed.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             State Abbreviation  Age_adjusted_rate  Number_of_Deaths  Year\n",
      "0          Alabama           AL               15.2               723  2014\n",
      "1           Alaska           AK               16.8               124  2014\n",
      "2          Arizona           AZ               18.2              1211  2014\n",
      "3         Arkansas           AR               12.6               356  2014\n",
      "4       California           CA               11.1              4521  2014\n",
      "..             ...          ...                ...               ...   ...\n",
      "352       Virginia           VA               26.6              2240  2020\n",
      "353     Washington           WA               22.0              1733  2020\n",
      "354  West Virginia           WV               81.4              1330  2020\n",
      "355      Wisconsin           WI               27.7              1531  2020\n",
      "356        Wyoming           WY               17.4                99  2020\n",
      "\n",
      "[357 rows x 5 columns]\n"
     ]
    }
   ],
   "source": [
    "# do we need this?\n",
    "\n",
    "bigFrame = pd.concat(dfs, ignore_index = True)\n",
    "bigFrame.to_csv('Resources/ODs/OpioidDeathsbigFrame.csv', index = False)\n",
    "# bigFrame = bigFrame2[['State', 'Abbreviation', 'Opioid Dispensing Rate per 100', 'Year']]\n",
    "print(bigFrame)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PythonDataOne",
   "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.7.7 (default, May  6 2020, 11:45:54) [MSC v.1916 64 bit (AMD64)]"
  },
  "vscode": {
   "interpreter": {
    "hash": "e09424b516901a071045f3ed6bcd6beb164e9564e937a7754100a941fb2722f2"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}