Data_munge.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing dependancies for data munge\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>State Name</th>\n",
       "      <th>Indicator</th>\n",
       "      <th>Data Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2017</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>Psychostimulants with abuse potential (T43.6)</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>Natural &amp; semi-synthetic opioids (T40.2)</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2017</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>Number of Drug Overdose Deaths</td>\n",
       "      <td>117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2017</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>Cocaine (T40.5)</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2017</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>Heroin (T40.1)</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5683</td>\n",
       "      <td>2017</td>\n",
       "      <td>New York City</td>\n",
       "      <td>Percent with drugs specified</td>\n",
       "      <td>99.66974901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5684</td>\n",
       "      <td>2017</td>\n",
       "      <td>New York City</td>\n",
       "      <td>Natural, semi-synthetic, &amp; synthetic opioids, ...</td>\n",
       "      <td>1,016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5685</td>\n",
       "      <td>2017</td>\n",
       "      <td>New York City</td>\n",
       "      <td>Cocaine (T40.5)</td>\n",
       "      <td>615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5686</td>\n",
       "      <td>2017</td>\n",
       "      <td>New York City</td>\n",
       "      <td>Number of Deaths</td>\n",
       "      <td>54,331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5687</td>\n",
       "      <td>2017</td>\n",
       "      <td>New York City</td>\n",
       "      <td>Natural &amp; semi-synthetic opioids, incl. methad...</td>\n",
       "      <td>446</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5688 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Year     State Name                                          Indicator  \\\n",
       "0     2017         Alaska      Psychostimulants with abuse potential (T43.6)   \n",
       "1     2017         Alaska           Natural & semi-synthetic opioids (T40.2)   \n",
       "2     2017         Alaska                     Number of Drug Overdose Deaths   \n",
       "3     2017         Alaska                                    Cocaine (T40.5)   \n",
       "4     2017         Alaska                                     Heroin (T40.1)   \n",
       "...    ...            ...                                                ...   \n",
       "5683  2017  New York City                       Percent with drugs specified   \n",
       "5684  2017  New York City  Natural, semi-synthetic, & synthetic opioids, ...   \n",
       "5685  2017  New York City                                    Cocaine (T40.5)   \n",
       "5686  2017  New York City                                   Number of Deaths   \n",
       "5687  2017  New York City  Natural & semi-synthetic opioids, incl. methad...   \n",
       "\n",
       "       Data Value  \n",
       "0              52  \n",
       "1              30  \n",
       "2             117  \n",
       "3              16  \n",
       "4              43  \n",
       "...           ...  \n",
       "5683  99.66974901  \n",
       "5684        1,016  \n",
       "5685          615  \n",
       "5686       54,331  \n",
       "5687          446  \n",
       "\n",
       "[5688 rows x 4 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Importing Dataset\n",
    "df = pd.read_csv(\"Resources/VSRR_Provisional_Drug_Overdose_Death_Counts.csv\")\n",
    "# Isolating 2017 as year used, keeping only relevant data\n",
    "df_year = df.set_index(\"Year\")\n",
    "year_loc = df_year.loc[2017, [\"State Name\", \"Indicator\", \"Data Value\"]]\n",
    "year_df = pd.DataFrame(year_loc).reset_index()\n",
    "\n",
    "year_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(                                               Indicator   Data Value\n",
       " Group                                                                \n",
       " 1          Psychostimulants with abuse potential (T43.6)           28\n",
       " 1               Natural & semi-synthetic opioids (T40.2)          209\n",
       " 1                         Number of Drug Overdose Deaths        1,018\n",
       " 1                                        Cocaine (T40.5)          267\n",
       " 1                                         Heroin (T40.1)          465\n",
       " ...                                                  ...          ...\n",
       " 1                           Percent with drugs specified  99.66974901\n",
       " 1      Natural, semi-synthetic, & synthetic opioids, ...        1,016\n",
       " 1                                        Cocaine (T40.5)          615\n",
       " 1                                       Number of Deaths       54,331\n",
       " 1      Natural & semi-synthetic opioids, incl. methad...          446\n",
       " \n",
       " [1213 rows x 2 columns],\n",
       "                                                Indicator   Data Value\n",
       " Group                                                                \n",
       " 2          Psychostimulants with abuse potential (T43.6)           85\n",
       " 2               Natural & semi-synthetic opioids (T40.2)           81\n",
       " 2                         Number of Drug Overdose Deaths          319\n",
       " 2                                        Cocaine (T40.5)           17\n",
       " 2                                         Heroin (T40.1)           51\n",
       " ...                                                  ...          ...\n",
       " 2                         Number of Drug Overdose Deaths           75\n",
       " 2                           Percent with drugs specified  97.33333333\n",
       " 2      Natural, semi-synthetic, & synthetic opioids, ...           29\n",
       " 2                                       Number of Deaths        8,154\n",
       " 2      Natural & semi-synthetic opioids, incl. methad...           21\n",
       " \n",
       " [799 rows x 2 columns],\n",
       "                             Indicator   Data Value\n",
       " Group                                             \n",
       " 3      Number of Drug Overdose Deaths          795\n",
       " 3                    Number of Deaths       51,794\n",
       " 3        Percent with drugs specified  61.76100629\n",
       " 3      Number of Drug Overdose Deaths          808\n",
       " 3                    Number of Deaths       52,075\n",
       " ...                               ...          ...\n",
       " 3      Number of Drug Overdose Deaths        2,879\n",
       " 3                    Number of Deaths      198,519\n",
       " 3      Number of Drug Overdose Deaths        2,868\n",
       " 3        Percent with drugs specified  87.90097629\n",
       " 3                    Number of Deaths      197,641\n",
       " \n",
       " [1293 rows x 2 columns],\n",
       "                                                Indicator   Data Value\n",
       " Group                                                                \n",
       " 4          Psychostimulants with abuse potential (T43.6)           52\n",
       " 4               Natural & semi-synthetic opioids (T40.2)           30\n",
       " 4                         Number of Drug Overdose Deaths          117\n",
       " 4                                        Cocaine (T40.5)           16\n",
       " 4                                         Heroin (T40.1)           43\n",
       " ...                                                  ...          ...\n",
       " 4                           Percent with drugs specified  97.24473258\n",
       " 4      Natural, semi-synthetic, & synthetic opioids, ...          351\n",
       " 4                                        Cocaine (T40.5)           45\n",
       " 4                                       Number of Deaths       18,490\n",
       " 4      Natural & semi-synthetic opioids, incl. methad...          302\n",
       " \n",
       " [1028 rows x 2 columns])"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Grouping states by Region as defined by U.S. Census Beauro:\n",
    "reg_a = year_df.loc[(year_df[\"State Name\"]==\"Connecticut\")| (year_df[\"State Name\"]==\"Maine\")| (year_df[\"State Name\"]==\"Massachusetts\")\n",
    "               | (year_df[\"State Name\"]==\"New Hampshire\")| (year_df[\"State Name\"]==\"Rhode Island\")| (year_df[\"State Name\"]==\"Vermont\")\n",
    "               | (year_df[\"State Name\"]==\"New York\")| (year_df[\"State Name\"]==\"New York City\") \n",
    "                    | (year_df[\"State Name\"]==\"Pennsylvania\") | (year_df[\"State Name\"]==\"New Jersey\"), [\"Indicator\", \"Data Value\"]]\n",
    "\n",
    "reg_b = year_df.loc[(year_df[\"State Name\"]==\"Illinois\")| (year_df[\"State Name\"]==\"Indiana\")| (year_df[\"State Name\"]==\"Iowa\")|\n",
    "                    (year_df[\"State Name\"]==\"Kansas\")| (year_df[\"State Name\"]==\"Michigan\")| (year_df[\"State Name\"]==\"Minnesota\")|\n",
    "                    (year_df[\"State Name\"]==\"Missouri\")| (year_df[\"State Name\"]==\"Nebraska\")| (year_df[\"State Name\"]==\"North Dakota\")|\n",
    "                    (year_df[\"State Name\"]==\"Ohio\")| (year_df[\"State Name\"]==\"South Dakota\"), [\"Indicator\", \"Data Value\"]]\n",
    "\n",
    "reg_c = year_df.loc[(year_df[\"State Name\"]==\"Alabama\")| (year_df[\"State Name\"]==\"Arkansas\")| (year_df[\"State Name\"]==\"Delaware\")| \n",
    "                    (year_df[\"State Name\"]==\"District of Columbia\")| (year_df[\"State Name\"]==\"Florida\")| (year_df[\"State Name\"]==\"Georgia\")| \n",
    "                    (year_df[\"State Name\"]==\"Kentucky\")| (year_df[\"State Name\"]==\"Louisiana\")| (year_df[\"State Name\"]==\"Maryland\")| \n",
    "                    (year_df[\"State Name\"]==\"Mississippi\")| (year_df[\"State Name\"]==\"North Carolina\")| (year_df[\"State Name\"]==\"Oklahoma\")| \n",
    "                    (year_df[\"State Name\"]==\"South Carolina\")| (year_df[\"State Name\"]==\"Tennessee\")| (year_df[\"State Name\"]==\"Texas\"),\n",
    "                     [\"Indicator\", \"Data Value\"]]\n",
    "\n",
    "reg_d = year_df.loc[(year_df[\"State Name\"]==\"Alaska\")| (year_df[\"State Name\"]==\"Arizona\")| (year_df[\"State Name\"]==\"California\")| \n",
    "                    (year_df[\"State Name\"]==\"Colorado\")| (year_df[\"State Name\"]==\"Hawaii\")| (year_df[\"State Name\"]==\"Idaho\")| \n",
    "                    (year_df[\"State Name\"]==\"Montana\")| (year_df[\"State Name\"]==\"Nevada\")| (year_df[\"State Name\"]==\"New Mexico\")| \n",
    "                    (year_df[\"State Name\"]==\"Oregon\")| (year_df[\"State Name\"]==\"Utah\"), [\"Indicator\", \"Data Value\"]]\n",
    "# Cleaning above(Adding columns to ind. datasets, munging)\n",
    "reg_a[\"Group\"] = \"1\"\n",
    "reg_b[\"Group\"] = \"2\"\n",
    "reg_c[\"Group\"] = \"3\"\n",
    "reg_d[\"Group\"] = \"4\"\n",
    "\n",
    "reg_a_df = pd.DataFrame(reg_a).set_index(\"Group\")\n",
    "reg_b_df = pd.DataFrame(reg_b).set_index(\"Group\")\n",
    "reg_c_df = pd.DataFrame(reg_c).set_index(\"Group\")\n",
    "reg_d_df = pd.DataFrame(reg_d).set_index(\"Group\")\n",
    "\n",
    "reg_a_df.dropna(), reg_b_df.dropna(), reg_c_df.dropna(), reg_d_df.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Group</th>\n",
       "      <th>Subject</th>\n",
       "      <th>Data Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Psychostimulants with abuse potential (T43.6)</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Number of Drug Overdose Deaths</td>\n",
       "      <td>1018.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Cocaine (T40.5)</td>\n",
       "      <td>267.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Heroin (T40.1)</td>\n",
       "      <td>465.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>Number of Deaths</td>\n",
       "      <td>31103.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2515</td>\n",
       "      <td>4</td>\n",
       "      <td>Number of Drug Overdose Deaths</td>\n",
       "      <td>617.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2516</td>\n",
       "      <td>4</td>\n",
       "      <td>Heroin (T40.1)</td>\n",
       "      <td>144.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2517</td>\n",
       "      <td>4</td>\n",
       "      <td>Natural, semi-synthetic, &amp; synthetic opioids, ...</td>\n",
       "      <td>351.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2518</td>\n",
       "      <td>4</td>\n",
       "      <td>Cocaine (T40.5)</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2519</td>\n",
       "      <td>4</td>\n",
       "      <td>Number of Deaths</td>\n",
       "      <td>18490.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2520 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Group                                            Subject  Data Value\n",
       "0        1      Psychostimulants with abuse potential (T43.6)        28.0\n",
       "1        1                     Number of Drug Overdose Deaths      1018.0\n",
       "2        1                                    Cocaine (T40.5)       267.0\n",
       "3        1                                     Heroin (T40.1)       465.0\n",
       "4        1                                   Number of Deaths     31103.0\n",
       "...    ...                                                ...         ...\n",
       "2515     4                     Number of Drug Overdose Deaths       617.0\n",
       "2516     4                                     Heroin (T40.1)       144.0\n",
       "2517     4  Natural, semi-synthetic, & synthetic opioids, ...       351.0\n",
       "2518     4                                    Cocaine (T40.5)        45.0\n",
       "2519     4                                   Number of Deaths     18490.0\n",
       "\n",
       "[2520 rows x 3 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Combining datasets\n",
    "reg_df = reg_a_df.append(reg_b_df)\n",
    "reg_df = reg_df.append(reg_c_df)\n",
    "reg_df = reg_df.append(reg_d_df)\n",
    "\n",
    "# Eliminating redundant and irrelevant data\n",
    "reg_df = reg_df.loc[(reg_df[\"Indicator\"]==\"Psychostimulants with abuse potential (T43.6)\")| (reg_df[\"Indicator\"]==\"Cocaine (T40.5)\")|\n",
    "                   (reg_df[\"Indicator\"]==\"Heroin (T40.1)\")| (reg_df[\"Indicator\"]==\"Natural, semi-synthetic, & synthetic opioids, incl. methadone (T40.2-T40.4)\")|\n",
    "                   (reg_df[\"Indicator\"]==\"Number of Drug Overdose Deaths\")| (reg_df[\"Indicator\"]==\"Number of Deaths\"), :].reset_index()\n",
    "reg_df[\"Data Value\"] = reg_df[\"Data Value\"].str.replace(',','').astype(np.float64)\n",
    "reg_df = reg_df.rename(columns={\"Indicator\": \"Subject\"})\n",
    "reg_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Group</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Subject</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Cocaine (T40.5)</td>\n",
       "      <td>28465.0</td>\n",
       "      <td>25611.0</td>\n",
       "      <td>23322.0</td>\n",
       "      <td>3361.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Heroin (T40.1)</td>\n",
       "      <td>30446.0</td>\n",
       "      <td>29933.0</td>\n",
       "      <td>24580.0</td>\n",
       "      <td>9153.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Natural, semi-synthetic, &amp; synthetic opioids, incl. methadone (T40.2-T40.4)</td>\n",
       "      <td>75698.0</td>\n",
       "      <td>64572.0</td>\n",
       "      <td>75752.0</td>\n",
       "      <td>18440.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Number of Deaths</td>\n",
       "      <td>5967066.0</td>\n",
       "      <td>7023764.0</td>\n",
       "      <td>12094654.0</td>\n",
       "      <td>6006006.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Number of Drug Overdose Deaths</td>\n",
       "      <td>193993.0</td>\n",
       "      <td>180124.0</td>\n",
       "      <td>270516.0</td>\n",
       "      <td>124627.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Psychostimulants with abuse potential (T43.6)</td>\n",
       "      <td>3774.0</td>\n",
       "      <td>8039.0</td>\n",
       "      <td>14732.0</td>\n",
       "      <td>14288.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Group                                                       1          2  \\\n",
       "Subject                                                                    \n",
       "Cocaine (T40.5)                                       28465.0    25611.0   \n",
       "Heroin (T40.1)                                        30446.0    29933.0   \n",
       "Natural, semi-synthetic, & synthetic opioids, i...    75698.0    64572.0   \n",
       "Number of Deaths                                    5967066.0  7023764.0   \n",
       "Number of Drug Overdose Deaths                       193993.0   180124.0   \n",
       "Psychostimulants with abuse potential (T43.6)          3774.0     8039.0   \n",
       "\n",
       "Group                                                        3          4  \n",
       "Subject                                                                    \n",
       "Cocaine (T40.5)                                        23322.0     3361.0  \n",
       "Heroin (T40.1)                                         24580.0     9153.0  \n",
       "Natural, semi-synthetic, & synthetic opioids, i...     75752.0    18440.0  \n",
       "Number of Deaths                                    12094654.0  6006006.0  \n",
       "Number of Drug Overdose Deaths                        270516.0   124627.0  \n",
       "Psychostimulants with abuse potential (T43.6)          14732.0    14288.0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Resulting pivot\n",
    "reg_df = pd.DataFrame(reg_df)\n",
    "reg_pivot = reg_df.pivot_table(index=\"Subject\", columns =\"Group\", values=\"Data Value\", aggfunc=np.sum)\n",
    "reg_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Group</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Subject</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Cocaine (T40.5)</td>\n",
       "      <td>28465.0</td>\n",
       "      <td>25611.0</td>\n",
       "      <td>23322.0</td>\n",
       "      <td>3361.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Heroin (T40.1)</td>\n",
       "      <td>30446.0</td>\n",
       "      <td>29933.0</td>\n",
       "      <td>24580.0</td>\n",
       "      <td>9153.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Natural, semi-synthetic, &amp; synthetic opioids, incl. methadone (T40.2-T40.4)</td>\n",
       "      <td>75698.0</td>\n",
       "      <td>64572.0</td>\n",
       "      <td>75752.0</td>\n",
       "      <td>18440.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Number of Deaths</td>\n",
       "      <td>5967066.0</td>\n",
       "      <td>7023764.0</td>\n",
       "      <td>12094654.0</td>\n",
       "      <td>6006006.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Number of Drug Overdose Deaths</td>\n",
       "      <td>193993.0</td>\n",
       "      <td>180124.0</td>\n",
       "      <td>270516.0</td>\n",
       "      <td>124627.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Psychostimulants with abuse potential (T43.6)</td>\n",
       "      <td>3774.0</td>\n",
       "      <td>8039.0</td>\n",
       "      <td>14732.0</td>\n",
       "      <td>14288.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Alcohol</td>\n",
       "      <td>4785.0</td>\n",
       "      <td>7338.0</td>\n",
       "      <td>12037.0</td>\n",
       "      <td>11664.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Group                                                       1          2  \\\n",
       "Subject                                                                    \n",
       "Cocaine (T40.5)                                       28465.0    25611.0   \n",
       "Heroin (T40.1)                                        30446.0    29933.0   \n",
       "Natural, semi-synthetic, & synthetic opioids, i...    75698.0    64572.0   \n",
       "Number of Deaths                                    5967066.0  7023764.0   \n",
       "Number of Drug Overdose Deaths                       193993.0   180124.0   \n",
       "Psychostimulants with abuse potential (T43.6)          3774.0     8039.0   \n",
       "Alcohol                                                4785.0     7338.0   \n",
       "\n",
       "Group                                                        3          4  \n",
       "Subject                                                                    \n",
       "Cocaine (T40.5)                                        23322.0     3361.0  \n",
       "Heroin (T40.1)                                         24580.0     9153.0  \n",
       "Natural, semi-synthetic, & synthetic opioids, i...     75752.0    18440.0  \n",
       "Number of Deaths                                    12094654.0  6006006.0  \n",
       "Number of Drug Overdose Deaths                        270516.0   124627.0  \n",
       "Psychostimulants with abuse potential (T43.6)          14732.0    14288.0  \n",
       "Alcohol                                                12037.0    11664.0  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Adding alcohol, cleaning, totalling\n",
    "alch_piv = pd.read_csv(\"Resources/2017 CDC Alcohol Deaths _2.csv\").reset_index()\n",
    "alch_piv = alch_piv.rename(columns={\"Deaths\": \"Alcohol\"})\n",
    "alch_piv = alch_piv.drop(4)\n",
    "alch_piv[\"index\"] = (\"1\",\"2\",\"3\",\"4\")\n",
    "alch_piv = alch_piv.set_index(\"index\")\n",
    "alch_data = alch_piv[\"Alcohol\"]\n",
    "reg_pivot = reg_pivot.append(alch_data)\n",
    "reg_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Group</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Subject</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Cocaine (T40.5)</td>\n",
       "      <td>28465.0</td>\n",
       "      <td>25611.0</td>\n",
       "      <td>23322.0</td>\n",
       "      <td>3361.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Heroin (T40.1)</td>\n",
       "      <td>30446.0</td>\n",
       "      <td>29933.0</td>\n",
       "      <td>24580.0</td>\n",
       "      <td>9153.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Natural, semi-synthetic, &amp; synthetic opioids, incl. methadone (T40.2-T40.4)</td>\n",
       "      <td>75698.0</td>\n",
       "      <td>64572.0</td>\n",
       "      <td>75752.0</td>\n",
       "      <td>18440.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Number of Deaths</td>\n",
       "      <td>5967066.0</td>\n",
       "      <td>7023764.0</td>\n",
       "      <td>12094654.0</td>\n",
       "      <td>6006006.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Number of Drug Overdose Deaths</td>\n",
       "      <td>193993.0</td>\n",
       "      <td>180124.0</td>\n",
       "      <td>270516.0</td>\n",
       "      <td>124627.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Psychostimulants with abuse potential (T43.6)</td>\n",
       "      <td>3774.0</td>\n",
       "      <td>8039.0</td>\n",
       "      <td>14732.0</td>\n",
       "      <td>14288.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Alcohol</td>\n",
       "      <td>4785.0</td>\n",
       "      <td>7338.0</td>\n",
       "      <td>12037.0</td>\n",
       "      <td>11664.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug Arrests</td>\n",
       "      <td>228158.0</td>\n",
       "      <td>201279.0</td>\n",
       "      <td>552370.0</td>\n",
       "      <td>321451.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Group                                                       1          2  \\\n",
       "Subject                                                                    \n",
       "Cocaine (T40.5)                                       28465.0    25611.0   \n",
       "Heroin (T40.1)                                        30446.0    29933.0   \n",
       "Natural, semi-synthetic, & synthetic opioids, i...    75698.0    64572.0   \n",
       "Number of Deaths                                    5967066.0  7023764.0   \n",
       "Number of Drug Overdose Deaths                       193993.0   180124.0   \n",
       "Psychostimulants with abuse potential (T43.6)          3774.0     8039.0   \n",
       "Alcohol                                                4785.0     7338.0   \n",
       "Drug Arrests                                         228158.0   201279.0   \n",
       "\n",
       "Group                                                        3          4  \n",
       "Subject                                                                    \n",
       "Cocaine (T40.5)                                        23322.0     3361.0  \n",
       "Heroin (T40.1)                                         24580.0     9153.0  \n",
       "Natural, semi-synthetic, & synthetic opioids, i...     75752.0    18440.0  \n",
       "Number of Deaths                                    12094654.0  6006006.0  \n",
       "Number of Drug Overdose Deaths                        270516.0   124627.0  \n",
       "Psychostimulants with abuse potential (T43.6)          14732.0    14288.0  \n",
       "Alcohol                                                12037.0    11664.0  \n",
       "Drug Arrests                                          552370.0   321451.0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Adding arrest data, cleaning\n",
    "arrest_df = pd.read_csv(\"Resources/drug-arrests_region.csv\").reset_index()\n",
    "arrest_df = arrest_df.rename(columns={\"2017 Arrests\":\"Drug Arrests\"})\n",
    "arrest_df[\"index\"] = (\"1\",\"2\",\"3\",\"4\")\n",
    "arrest_df = arrest_df.set_index(\"index\")\n",
    "arrest_data = arrest_df[\"Drug Arrests\"]\n",
    "arrest_df\n",
    "reg_pivot = reg_pivot.append(arrest_data)\n",
    "reg_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Group</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Subject</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Cocaine (T40.5)</td>\n",
       "      <td>28465.0</td>\n",
       "      <td>25611.0</td>\n",
       "      <td>23322.0</td>\n",
       "      <td>3361.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Heroin (T40.1)</td>\n",
       "      <td>30446.0</td>\n",
       "      <td>29933.0</td>\n",
       "      <td>24580.0</td>\n",
       "      <td>9153.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Natural, semi-synthetic, &amp; synthetic opioids, incl. methadone (T40.2-T40.4)</td>\n",
       "      <td>75698.0</td>\n",
       "      <td>64572.0</td>\n",
       "      <td>75752.0</td>\n",
       "      <td>18440.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Number of Deaths</td>\n",
       "      <td>5967066.0</td>\n",
       "      <td>7023764.0</td>\n",
       "      <td>12094654.0</td>\n",
       "      <td>6006006.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Number of Drug Overdose Deaths</td>\n",
       "      <td>193993.0</td>\n",
       "      <td>180124.0</td>\n",
       "      <td>270516.0</td>\n",
       "      <td>124627.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Psychostimulants with abuse potential (T43.6)</td>\n",
       "      <td>3774.0</td>\n",
       "      <td>8039.0</td>\n",
       "      <td>14732.0</td>\n",
       "      <td>14288.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Alcohol</td>\n",
       "      <td>4785.0</td>\n",
       "      <td>7338.0</td>\n",
       "      <td>12037.0</td>\n",
       "      <td>11664.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug Arrests</td>\n",
       "      <td>228158.0</td>\n",
       "      <td>201279.0</td>\n",
       "      <td>552370.0</td>\n",
       "      <td>321451.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Homeless Population</td>\n",
       "      <td>139271.0</td>\n",
       "      <td>58663.0</td>\n",
       "      <td>122074.0</td>\n",
       "      <td>201342.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Group                                                       1          2  \\\n",
       "Subject                                                                    \n",
       "Cocaine (T40.5)                                       28465.0    25611.0   \n",
       "Heroin (T40.1)                                        30446.0    29933.0   \n",
       "Natural, semi-synthetic, & synthetic opioids, i...    75698.0    64572.0   \n",
       "Number of Deaths                                    5967066.0  7023764.0   \n",
       "Number of Drug Overdose Deaths                       193993.0   180124.0   \n",
       "Psychostimulants with abuse potential (T43.6)          3774.0     8039.0   \n",
       "Alcohol                                                4785.0     7338.0   \n",
       "Drug Arrests                                         228158.0   201279.0   \n",
       "Homeless Population                                  139271.0    58663.0   \n",
       "\n",
       "Group                                                        3          4  \n",
       "Subject                                                                    \n",
       "Cocaine (T40.5)                                        23322.0     3361.0  \n",
       "Heroin (T40.1)                                         24580.0     9153.0  \n",
       "Natural, semi-synthetic, & synthetic opioids, i...     75752.0    18440.0  \n",
       "Number of Deaths                                    12094654.0  6006006.0  \n",
       "Number of Drug Overdose Deaths                        270516.0   124627.0  \n",
       "Psychostimulants with abuse potential (T43.6)          14732.0    14288.0  \n",
       "Alcohol                                                12037.0    11664.0  \n",
       "Drug Arrests                                          552370.0   321451.0  \n",
       "Homeless Population                                   122074.0   201342.0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Importing homelessness data, adding\n",
    "homeless_df = pd.read_csv(\"Resources/homelessness_by_region.csv\")\n",
    "homeless_df[\"Subject\"] = homeless_df[\"Region\"]\n",
    "homeless_df[\"Subject\"] = (\"1\",\"2\",\"3\",\"4\")\n",
    "homeless_df = homeless_df.set_index(\"Subject\")\n",
    "homeless_df = homeless_df.rename(columns={\"Homeless Population (2017)\":\"Homeless Population\"})\n",
    "homeless_data = homeless_df[\"Homeless Population\"]\n",
    "reg_pivot = reg_pivot.append(homeless_data)\n",
    "reg_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Subject</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Total population</td>\n",
       "      <td>56,470,581</td>\n",
       "      <td>68,179,351</td>\n",
       "      <td>123,658,624</td>\n",
       "      <td>77,410,622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Median household income (dollars)</td>\n",
       "      <td>66,998</td>\n",
       "      <td>57,778</td>\n",
       "      <td>55,135</td>\n",
       "      <td>66,485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>No health insurance coverage</td>\n",
       "      <td>5.6%</td>\n",
       "      <td>6.5%</td>\n",
       "      <td>12.0%</td>\n",
       "      <td>7.7%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Poplulation Poverty Rate</td>\n",
       "      <td>12.0%</td>\n",
       "      <td>12.7%</td>\n",
       "      <td>14.7%</td>\n",
       "      <td>12.9%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Number of Drug Overdose Deaths</td>\n",
       "      <td>193993</td>\n",
       "      <td>180124</td>\n",
       "      <td>270516</td>\n",
       "      <td>124627</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            1           2            3  \\\n",
       "Subject                                                                  \n",
       "Total population                   56,470,581  68,179,351  123,658,624   \n",
       "Median household income (dollars)      66,998      57,778       55,135   \n",
       "No health insurance coverage             5.6%        6.5%        12.0%   \n",
       "Poplulation Poverty Rate                12.0%       12.7%        14.7%   \n",
       "Number of Drug Overdose Deaths         193993      180124       270516   \n",
       "\n",
       "                                            4  \n",
       "Subject                                        \n",
       "Total population                   77,410,622  \n",
       "Median household income (dollars)      66,485  \n",
       "No health insurance coverage             7.7%  \n",
       "Poplulation Poverty Rate                12.9%  \n",
       "Number of Drug Overdose Deaths         124627  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Importing census data, adding fatality data\n",
    "census_df = pd.read_csv(\"Resources/2017 Census Population Statistics.csv\")\n",
    "census_df = census_df.rename(columns={\"Northeast Region\": \"1\", \"Midwest Region\": \"2\", \n",
    "                                      \"South Region\": \"3\", \"West Region\": \"4\"}).set_index(\"Subject\")\n",
    "drug_death = reg_pivot.loc[\"Number of Drug Overdose Deaths\"]\n",
    "census_df = census_df.append(drug_death)\n",
    "census_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "reg_pivot.to_csv(r\"Output/drug-overdose_region.csv\")\n",
    "census_df.to_csv(r\"Output/census_df_complete.csv\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}