1.1. Capestone Data setup_1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Predicting Prescriber induced Overdose"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DATA SCIENCE IMMERSIVE FINAL CAPE STONE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This capestone was initaited with an idea that why are so much more physicians presecribing medications that can lead to overdose. I have worked with patients for more than a year in a clinic as well as a hospital and saw significant amount of patients requesting pain relief medications again and again. I have seen people i know been affected with the chronic and life threatening overdose.\n",
    "\n",
    "The objective of this capestone is to develop models to identify types of prescribers that are a high-risk for opioid related fatalities and predict most influential opioids leading to deaths."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The public health data was extracted from [CDC](https://wonder.cdc.gov/) I downloaded into individual tab separated text files as dowloading the 3.2GB data at once and extracting needs significant processing power besides I noticed some anomalies when extracting data from Wonder in large due to the system or functionality of wonder tool they have to group the needed data. The otherData was extraced from CMS Medicare [Part D Opioid Prescriber Data](\thttps://www.cms.gov/Research-Statistics-Data-and-Systems/Statistics-Trends-and-Reports/Medicare-Provider-Charge-Data/Part-D-Prescriber.html) and [cms](https://www.cms.gov/Research-Statistics-Data-and-Systems/Statistics-Trends-and-Reports/Medicare-Provider-Charge-Data/OpioidMap.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this notebook, i am going to analyze the datasets i would use for the prediction. In particular, in this note book i will use the Exploratory Data Analysis(eda) in order to understand the statistical structure of the data. Furthermore, in the following notebooks i will build a predictive machine learning model to predict a certain target variable (prescribers) from other attributes that are linked with the features that i will learn from the eda. The datasets are collected from a real world datasets currently available  and they contains a substantial amount of missing values. In this notebook, i will try to set up my datasets and in 2.1 i will cean all the datasets that i will use in the future and try to understand their relationships and in the next notebook. In Notebook 3.1  i will visualize the most important features and associated ones based on the data collected  that will be used for the next work. All the datasets were collected from different sources and validated. Somedatasets are official querried from respective government institutions using private usercode and adjusted to the subject after cleaning."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Author : Kiros Gebremariam**\n",
    "\n",
    "Cohorts of the Data Science Immersive, General Assembly @ Washington DC campus"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Data Cleaning,munging and EDA"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Loadding Necessary Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os                                                                    # os library\n",
    "import numpy as np                                                           # numpy library\n",
    "import pandas as pd                                                          # pandas library\n",
    "import matplotlib.pylab as plt                                               # plotting and visualization library\n",
    "import seaborn as sns                                                        # plotting\n",
    "import statsmodels.api as sm                                                 # for running regressions\n",
    "np.random.seed(2018)                                                         # Random seed for replication \n",
    "\n",
    "\n",
    "\n",
    "# Configure visual settings:\n",
    "plt.style.use('fivethirtyeight')\n",
    "sns.set(font_scale=2)\n",
    "cmap = sns.diverging_palette(220, 10, as_cmap=True) # one of the many color mappings from \n",
    "sns.set_style('whitegrid')\n",
    "plt.rcParams['figure.figsize'] = (10.0, 8.0) \n",
    "%config InlineBackend.figure_format = 'retina'\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['.DS_Store', '.git', '.gitignore', '.ipynb_checkpoints', '1.1. Capestone Data setup_1.ipynb', '1.2. Capestone Data Cleaning_1.ipynb', '2.1. Capestone Finaleda.ipynb', 'inputs', 'LICENSE', 'README.md']\n"
     ]
    }
   ],
   "source": [
    "import os                      # just ensuring the files using as working Directory(pwd)\n",
    "print(os.listdir())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prescriber related datasets disaggregated by state,county,zip and rate \n",
    "pres_info_df = pd.read_csv('https://data.cms.gov/api/views/6wg9-kwip/rows.csv?accessType=DOWNLOAD')\n",
    "Prescriber_state_df =pd.read_csv('./inputs/Prescriber_state.csv')\n",
    "Prescriber_county_df =pd.read_csv('./inputs/Prescriber_county.csv')\n",
    "Prescriber_zip_df =pd.read_csv('./inputs/Prescriber_zip.csv')\n",
    "psr_df= pd.read_csv('./inputs/prescriber_state_rate.csv')\n",
    "pcr_df = pd.read_csv('./inputs/prescriber_county_rate.csv')\n",
    "pzr_df= pd.read_csv('./inputs/prescriber_zip_rate.csv')\n",
    "# Drugs, Mental health and Deaths datasets different datasources\n",
    "KFFMentalHealthspendState_df =pd.read_csv('./inputs/KFFMentalHealthspendState.csv')\n",
    "pop_urban_df =pd.read_csv('./inputs/pop-urban-pct-historical_cleaned_up.csv')\n",
    "rawdataKFFopioiddeath_df = pd.read_csv('./inputs/rawdataKFFopioiddeath.csv', engine =\"python\")\n",
    "Wonder_Drug_Death_Data1_df = pd.read_csv('./inputs/Wonder_Drug_Death_Data_1.csv')\n",
    "Wonder_Drug_Death_Data2_df = pd.read_csv('./inputs/Wonder_Drug_Death_Data_2.csv')\n",
    "drugs_df = pd.read_csv('./inputs/opioids.csv')\n",
    "# crime related data both property and nonviolent\n",
    "violentcrime_df =pd.read_csv('./inputs/violentcrime19992014.csv')\n",
    "propertycrime_df=pd.read_csv('./inputs/propertycrime19992014.csv')\n",
    "tcrime_df =pd.read_csv('./inputs/TotalVncrime.csv')\n",
    "\n",
    "# National Health Expenditure from 2009-2016\n",
    "NHE2016_df = pd.read_csv('./inputs/NHE2016.csv',engine='python')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Checking Data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NPI</th>\n",
       "      <th>NPPES Provider Last Name</th>\n",
       "      <th>NPPES Provider First Name</th>\n",
       "      <th>NPPES Provider ZIP Code</th>\n",
       "      <th>NPPES Provider State</th>\n",
       "      <th>Specialty Description</th>\n",
       "      <th>Total Claim Count</th>\n",
       "      <th>Opioid Claim Count</th>\n",
       "      <th>Opioid Prescribing Rate</th>\n",
       "      <th>Extended-Release Opioid Claims</th>\n",
       "      <th>Extended-Release Opioid Prescribing Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1003000126</td>\n",
       "      <td>ENKESHAFI</td>\n",
       "      <td>ARDALAN</td>\n",
       "      <td>21502.0</td>\n",
       "      <td>MD</td>\n",
       "      <td>Internal Medicine</td>\n",
       "      <td>545</td>\n",
       "      <td>23.0</td>\n",
       "      <td>4.22%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1003000142</td>\n",
       "      <td>KHALIL</td>\n",
       "      <td>RASHID</td>\n",
       "      <td>43623.0</td>\n",
       "      <td>OH</td>\n",
       "      <td>Anesthesiology</td>\n",
       "      <td>1733</td>\n",
       "      <td>1004.0</td>\n",
       "      <td>57.93%</td>\n",
       "      <td>63.0</td>\n",
       "      <td>6.27%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          NPI NPPES Provider Last Name NPPES Provider First Name  \\\n",
       "0  1003000126                ENKESHAFI                   ARDALAN   \n",
       "1  1003000142                   KHALIL                    RASHID   \n",
       "\n",
       "   NPPES Provider ZIP Code NPPES Provider State Specialty Description  \\\n",
       "0                  21502.0                   MD     Internal Medicine   \n",
       "1                  43623.0                   OH        Anesthesiology   \n",
       "\n",
       "   Total Claim Count  Opioid Claim Count Opioid Prescribing Rate  \\\n",
       "0                545                23.0                   4.22%   \n",
       "1               1733              1004.0                  57.93%   \n",
       "\n",
       "   Extended-Release Opioid Claims Extended-Release Opioid Prescribing Rate  \n",
       "0                             NaN                                      NaN  \n",
       "1                            63.0                                    6.27%  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pres_info_df.head()[:2]  # the data is about the Medicare part D prescriber summary extracted from cms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State_Name</th>\n",
       "      <th>State_Abbreviation</th>\n",
       "      <th>State_FIPS</th>\n",
       "      <th>2013_Part_ D_Prescribers</th>\n",
       "      <th>2013_Part_Opioid_Prescribers</th>\n",
       "      <th>2013_Opioid Claims</th>\n",
       "      <th>2013_Extended_Release_Opioid_Claims</th>\n",
       "      <th>2013_Overall_Claims</th>\n",
       "      <th>2013_Opioid_Prescribing_Rate</th>\n",
       "      <th>2013_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "      <th>...</th>\n",
       "      <th>2016_Part D Opioid Prescribers</th>\n",
       "      <th>2016_Opioid Claims</th>\n",
       "      <th>2016_Extended Release Opioid Claims</th>\n",
       "      <th>2016_Overall_Claims</th>\n",
       "      <th>2016_Opioid_Prescribing_Rate</th>\n",
       "      <th>2016_Extended_Release_Opioid_Prescribing_ Rate</th>\n",
       "      <th>2013_2016_Change in Opioid Prescribing Rate</th>\n",
       "      <th>2013_2016_Change in Extended Release Opioid Prescribing Rate</th>\n",
       "      <th>2013_2015_Change_in_Opioid_Prescribing_Rate</th>\n",
       "      <th>2013_2015_Change_in_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>National</td>\n",
       "      <td>National</td>\n",
       "      <td>National</td>\n",
       "      <td>1,037,770</td>\n",
       "      <td>491,500</td>\n",
       "      <td>78,045,683</td>\n",
       "      <td>4,773,286</td>\n",
       "      <td>1,342,096,654</td>\n",
       "      <td>5.82</td>\n",
       "      <td>6.12</td>\n",
       "      <td>...</td>\n",
       "      <td>496350</td>\n",
       "      <td>77716608</td>\n",
       "      <td>5381275</td>\n",
       "      <td>1452978228</td>\n",
       "      <td>5.34</td>\n",
       "      <td>6.92</td>\n",
       "      <td>-0.47</td>\n",
       "      <td>0.80</td>\n",
       "      <td>-0.30</td>\n",
       "      <td>0.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>1</td>\n",
       "      <td>12,820</td>\n",
       "      <td>7,422</td>\n",
       "      <td>2,260,284</td>\n",
       "      <td>118,523</td>\n",
       "      <td>29,160,952</td>\n",
       "      <td>7.75</td>\n",
       "      <td>5.24</td>\n",
       "      <td>...</td>\n",
       "      <td>7,004</td>\n",
       "      <td>2,156,504</td>\n",
       "      <td>120,579</td>\n",
       "      <td>28,882,148</td>\n",
       "      <td>7.47</td>\n",
       "      <td>5.59</td>\n",
       "      <td>-0.28</td>\n",
       "      <td>0.35</td>\n",
       "      <td>-0.21</td>\n",
       "      <td>0.31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 35 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  State_Name State_Abbreviation State_FIPS 2013_Part_ D_Prescribers  \\\n",
       "0   National           National   National                1,037,770   \n",
       "1    Alabama                 AL          1                   12,820   \n",
       "\n",
       "  2013_Part_Opioid_Prescribers 2013_Opioid Claims  \\\n",
       "0                      491,500         78,045,683   \n",
       "1                        7,422          2,260,284   \n",
       "\n",
       "  2013_Extended_Release_Opioid_Claims 2013_Overall_Claims  \\\n",
       "0                           4,773,286       1,342,096,654   \n",
       "1                             118,523          29,160,952   \n",
       "\n",
       "   2013_Opioid_Prescribing_Rate  \\\n",
       "0                          5.82   \n",
       "1                          7.75   \n",
       "\n",
       "   2013_Extended_Release_Opioid_Prescribing_Rate  \\\n",
       "0                                           6.12   \n",
       "1                                           5.24   \n",
       "\n",
       "                               ...                               \\\n",
       "0                              ...                                \n",
       "1                              ...                                \n",
       "\n",
       "  2016_Part D Opioid Prescribers 2016_Opioid Claims  \\\n",
       "0                         496350           77716608   \n",
       "1                          7,004          2,156,504   \n",
       "\n",
       "  2016_Extended Release Opioid Claims 2016_Overall_Claims  \\\n",
       "0                             5381275          1452978228   \n",
       "1                             120,579          28,882,148   \n",
       "\n",
       "  2016_Opioid_Prescribing_Rate  \\\n",
       "0                         5.34   \n",
       "1                         7.47   \n",
       "\n",
       "   2016_Extended_Release_Opioid_Prescribing_ Rate  \\\n",
       "0                                            6.92   \n",
       "1                                            5.59   \n",
       "\n",
       "   2013_2016_Change in Opioid Prescribing Rate  \\\n",
       "0                                        -0.47   \n",
       "1                                        -0.28   \n",
       "\n",
       "  2013_2016_Change in Extended Release Opioid Prescribing Rate   \\\n",
       "0                                               0.80              \n",
       "1                                               0.35              \n",
       "\n",
       "  2013_2015_Change_in_Opioid_Prescribing_Rate  \\\n",
       "0                                       -0.30   \n",
       "1                                       -0.21   \n",
       "\n",
       "  2013_2015_Change_in_Extended_Release_Opioid_Prescribing_Rate   \n",
       "0                                               0.59             \n",
       "1                                               0.31             \n",
       "\n",
       "[2 rows x 35 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  extracted from from CMS Medicare Part D Opioid Prescribing Geographic 2013-2016 Excel file and \n",
    "# created CSV files for the three taps 1) state 2) county and 3) zip\n",
    "# Stored presciber-state file in a variable(df2)\n",
    "Prescriber_state_df.fillna('National')[:2]   # the data is about the prescriber/Physician_Locator"
   ]
  },
  {
   "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></th>\n",
       "      <th>State_Name</th>\n",
       "      <th>State_Abbreviation</th>\n",
       "      <th>State_FIPS</th>\n",
       "      <th>2013_Part_ D_Prescribers</th>\n",
       "      <th>2013_Part_Opioid_Prescribers</th>\n",
       "      <th>2013_Opioid Claims</th>\n",
       "      <th>2013_Extended_Release_Opioid_Claims</th>\n",
       "      <th>2013_Overall_Claims</th>\n",
       "      <th>2013_Opioid_Prescribing_Rate</th>\n",
       "      <th>2013_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "      <th>...</th>\n",
       "      <th>2016_Part D Opioid Prescribers</th>\n",
       "      <th>2016_Opioid Claims</th>\n",
       "      <th>2016_Extended Release Opioid Claims</th>\n",
       "      <th>2016_Overall_Claims</th>\n",
       "      <th>2016_Opioid_Prescribing_Rate</th>\n",
       "      <th>2016_Extended_Release_Opioid_Prescribing_ Rate</th>\n",
       "      <th>2013_2016_Change in Opioid Prescribing Rate</th>\n",
       "      <th>2013_2016_Change in Extended Release Opioid Prescribing Rate</th>\n",
       "      <th>2013_2015_Change_in_Opioid_Prescribing_Rate</th>\n",
       "      <th>2013_2015_Change_in_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>National</td>\n",
       "      <td>National</td>\n",
       "      <td>National</td>\n",
       "      <td>1,037,770</td>\n",
       "      <td>491,500</td>\n",
       "      <td>78,045,683</td>\n",
       "      <td>4,773,286</td>\n",
       "      <td>1,342,096,654</td>\n",
       "      <td>5.82</td>\n",
       "      <td>6.12</td>\n",
       "      <td>...</td>\n",
       "      <td>496350</td>\n",
       "      <td>77716608</td>\n",
       "      <td>5381275</td>\n",
       "      <td>1452978228</td>\n",
       "      <td>5.34</td>\n",
       "      <td>6.92</td>\n",
       "      <td>-0.47</td>\n",
       "      <td>0.80</td>\n",
       "      <td>-0.30</td>\n",
       "      <td>0.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>1</td>\n",
       "      <td>12,820</td>\n",
       "      <td>7,422</td>\n",
       "      <td>2,260,284</td>\n",
       "      <td>118,523</td>\n",
       "      <td>29,160,952</td>\n",
       "      <td>7.75</td>\n",
       "      <td>5.24</td>\n",
       "      <td>...</td>\n",
       "      <td>7,004</td>\n",
       "      <td>2,156,504</td>\n",
       "      <td>120,579</td>\n",
       "      <td>28,882,148</td>\n",
       "      <td>7.47</td>\n",
       "      <td>5.59</td>\n",
       "      <td>-0.28</td>\n",
       "      <td>0.35</td>\n",
       "      <td>-0.21</td>\n",
       "      <td>0.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>2</td>\n",
       "      <td>2,275</td>\n",
       "      <td>1,099</td>\n",
       "      <td>86,517</td>\n",
       "      <td>8,602</td>\n",
       "      <td>1,281,057</td>\n",
       "      <td>6.75</td>\n",
       "      <td>9.94</td>\n",
       "      <td>...</td>\n",
       "      <td>1,043</td>\n",
       "      <td>82,557</td>\n",
       "      <td>9,074</td>\n",
       "      <td>1,225,327</td>\n",
       "      <td>6.74</td>\n",
       "      <td>10.99</td>\n",
       "      <td>-0.01</td>\n",
       "      <td>1.05</td>\n",
       "      <td>-0.09</td>\n",
       "      <td>0.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>4</td>\n",
       "      <td>20,542</td>\n",
       "      <td>10,510</td>\n",
       "      <td>1,545,138</td>\n",
       "      <td>151,086</td>\n",
       "      <td>22,126,421</td>\n",
       "      <td>6.98</td>\n",
       "      <td>9.78</td>\n",
       "      <td>...</td>\n",
       "      <td>11,026</td>\n",
       "      <td>1,621,223</td>\n",
       "      <td>173,171</td>\n",
       "      <td>24,891,001</td>\n",
       "      <td>6.51</td>\n",
       "      <td>10.68</td>\n",
       "      <td>-0.47</td>\n",
       "      <td>0.90</td>\n",
       "      <td>-0.20</td>\n",
       "      <td>0.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>AR</td>\n",
       "      <td>5</td>\n",
       "      <td>7,909</td>\n",
       "      <td>4,700</td>\n",
       "      <td>1,128,356</td>\n",
       "      <td>57,519</td>\n",
       "      <td>16,759,116</td>\n",
       "      <td>6.73</td>\n",
       "      <td>5.10</td>\n",
       "      <td>...</td>\n",
       "      <td>4,854</td>\n",
       "      <td>1,155,171</td>\n",
       "      <td>65,819</td>\n",
       "      <td>18,139,502</td>\n",
       "      <td>6.37</td>\n",
       "      <td>5.70</td>\n",
       "      <td>-0.36</td>\n",
       "      <td>0.60</td>\n",
       "      <td>-0.36</td>\n",
       "      <td>0.49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 35 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  State_Name State_Abbreviation State_FIPS 2013_Part_ D_Prescribers  \\\n",
       "0   National           National   National                1,037,770   \n",
       "1    Alabama                 AL          1                   12,820   \n",
       "2     Alaska                 AK          2                    2,275   \n",
       "3    Arizona                 AZ          4                   20,542   \n",
       "4   Arkansas                 AR          5                    7,909   \n",
       "\n",
       "  2013_Part_Opioid_Prescribers 2013_Opioid Claims  \\\n",
       "0                      491,500         78,045,683   \n",
       "1                        7,422          2,260,284   \n",
       "2                        1,099             86,517   \n",
       "3                       10,510          1,545,138   \n",
       "4                        4,700          1,128,356   \n",
       "\n",
       "  2013_Extended_Release_Opioid_Claims 2013_Overall_Claims  \\\n",
       "0                           4,773,286       1,342,096,654   \n",
       "1                             118,523          29,160,952   \n",
       "2                               8,602           1,281,057   \n",
       "3                             151,086          22,126,421   \n",
       "4                              57,519          16,759,116   \n",
       "\n",
       "   2013_Opioid_Prescribing_Rate  \\\n",
       "0                          5.82   \n",
       "1                          7.75   \n",
       "2                          6.75   \n",
       "3                          6.98   \n",
       "4                          6.73   \n",
       "\n",
       "   2013_Extended_Release_Opioid_Prescribing_Rate  \\\n",
       "0                                           6.12   \n",
       "1                                           5.24   \n",
       "2                                           9.94   \n",
       "3                                           9.78   \n",
       "4                                           5.10   \n",
       "\n",
       "                               ...                               \\\n",
       "0                              ...                                \n",
       "1                              ...                                \n",
       "2                              ...                                \n",
       "3                              ...                                \n",
       "4                              ...                                \n",
       "\n",
       "  2016_Part D Opioid Prescribers 2016_Opioid Claims  \\\n",
       "0                         496350           77716608   \n",
       "1                          7,004          2,156,504   \n",
       "2                          1,043             82,557   \n",
       "3                         11,026          1,621,223   \n",
       "4                          4,854          1,155,171   \n",
       "\n",
       "  2016_Extended Release Opioid Claims 2016_Overall_Claims  \\\n",
       "0                             5381275          1452978228   \n",
       "1                             120,579          28,882,148   \n",
       "2                               9,074           1,225,327   \n",
       "3                             173,171          24,891,001   \n",
       "4                              65,819          18,139,502   \n",
       "\n",
       "  2016_Opioid_Prescribing_Rate  \\\n",
       "0                         5.34   \n",
       "1                         7.47   \n",
       "2                         6.74   \n",
       "3                         6.51   \n",
       "4                         6.37   \n",
       "\n",
       "   2016_Extended_Release_Opioid_Prescribing_ Rate  \\\n",
       "0                                            6.92   \n",
       "1                                            5.59   \n",
       "2                                           10.99   \n",
       "3                                           10.68   \n",
       "4                                            5.70   \n",
       "\n",
       "   2013_2016_Change in Opioid Prescribing Rate  \\\n",
       "0                                        -0.47   \n",
       "1                                        -0.28   \n",
       "2                                        -0.01   \n",
       "3                                        -0.47   \n",
       "4                                        -0.36   \n",
       "\n",
       "  2013_2016_Change in Extended Release Opioid Prescribing Rate   \\\n",
       "0                                               0.80              \n",
       "1                                               0.35              \n",
       "2                                               1.05              \n",
       "3                                               0.90              \n",
       "4                                               0.60              \n",
       "\n",
       "  2013_2015_Change_in_Opioid_Prescribing_Rate  \\\n",
       "0                                       -0.30   \n",
       "1                                       -0.21   \n",
       "2                                       -0.09   \n",
       "3                                       -0.20   \n",
       "4                                       -0.36   \n",
       "\n",
       "  2013_2015_Change_in_Extended_Release_Opioid_Prescribing_Rate   \n",
       "0                                               0.59             \n",
       "1                                               0.31             \n",
       "2                                               0.71             \n",
       "3                                               0.48             \n",
       "4                                               0.49             \n",
       "\n",
       "[5 rows x 35 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# here just filling the NaN value due to the columns mismatach as there is no need fo data on \n",
    "# state abrevation and state state FIPs\n",
    "Prescriber_state_df.fillna(\"National\")[:5] "
   ]
  },
  {
   "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></th>\n",
       "      <th>State_Name</th>\n",
       "      <th>State_Abbreviation</th>\n",
       "      <th>2013_Opioid_Prescribing_Rate</th>\n",
       "      <th>2013_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "      <th>2014_Opioid_Prescribing_Rate</th>\n",
       "      <th>2014_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "      <th>2015_Opioid_Prescribing_Rate</th>\n",
       "      <th>2015_Extended_Release_Opioid_Prescribing_ Rate</th>\n",
       "      <th>2016_Opioid_Prescribing_Rate</th>\n",
       "      <th>2016_Extended_Release_Opioid_Prescribing_ Rate</th>\n",
       "      <th>2013_2016_Change in Opioid Prescribing Rate</th>\n",
       "      <th>2013_2016_Change in Extended Release Opioid Prescribing Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>National</td>\n",
       "      <td>National</td>\n",
       "      <td>5.82</td>\n",
       "      <td>6.12</td>\n",
       "      <td>5.74</td>\n",
       "      <td>6.27</td>\n",
       "      <td>5.52</td>\n",
       "      <td>6.71</td>\n",
       "      <td>5.34</td>\n",
       "      <td>6.92</td>\n",
       "      <td>-0.47</td>\n",
       "      <td>0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>7.75</td>\n",
       "      <td>5.24</td>\n",
       "      <td>7.86</td>\n",
       "      <td>5.24</td>\n",
       "      <td>7.54</td>\n",
       "      <td>5.55</td>\n",
       "      <td>7.47</td>\n",
       "      <td>5.59</td>\n",
       "      <td>-0.28</td>\n",
       "      <td>0.35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State_Name State_Abbreviation  2013_Opioid_Prescribing_Rate  \\\n",
       "0   National           National                          5.82   \n",
       "1    Alabama                 AL                          7.75   \n",
       "\n",
       "   2013_Extended_Release_Opioid_Prescribing_Rate  \\\n",
       "0                                           6.12   \n",
       "1                                           5.24   \n",
       "\n",
       "   2014_Opioid_Prescribing_Rate  \\\n",
       "0                          5.74   \n",
       "1                          7.86   \n",
       "\n",
       "   2014_Extended_Release_Opioid_Prescribing_Rate  \\\n",
       "0                                           6.27   \n",
       "1                                           5.24   \n",
       "\n",
       "   2015_Opioid_Prescribing_Rate  \\\n",
       "0                          5.52   \n",
       "1                          7.54   \n",
       "\n",
       "   2015_Extended_Release_Opioid_Prescribing_ Rate  \\\n",
       "0                                            6.71   \n",
       "1                                            5.55   \n",
       "\n",
       "   2016_Opioid_Prescribing_Rate  \\\n",
       "0                          5.34   \n",
       "1                          7.47   \n",
       "\n",
       "   2016_Extended_Release_Opioid_Prescribing_ Rate  \\\n",
       "0                                            6.92   \n",
       "1                                            5.59   \n",
       "\n",
       "   2013_2016_Change in Opioid Prescribing Rate  \\\n",
       "0                                        -0.47   \n",
       "1                                        -0.28   \n",
       "\n",
       "   2013_2016_Change in Extended Release Opioid Prescribing Rate   \n",
       "0                                               0.80              \n",
       "1                                               0.35              "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Selected the opioid prescribe rate columsn for years 2013-2016 by prescriber state(psr)\n",
    "# this was extracted from df2 so that it will be important to see for features in the next stages\n",
    "psr_df.head()[:2]\n",
    "psr_df.fillna(\"National\")[:2]"
   ]
  },
  {
   "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></th>\n",
       "      <th>State_Name</th>\n",
       "      <th>State_Abbreviation</th>\n",
       "      <th>2013_Opioid_Prescribing_Rate</th>\n",
       "      <th>2013_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "      <th>2014_Opioid_Prescribing_Rate</th>\n",
       "      <th>2014_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "      <th>2015_Opioid_Prescribing_Rate</th>\n",
       "      <th>2015_Extended_Release_Opioid_Prescribing_ Rate</th>\n",
       "      <th>2016_Opioid_Prescribing_Rate</th>\n",
       "      <th>2016_Extended_Release_Opioid_Prescribing_ Rate</th>\n",
       "      <th>2013_2016_Change in Opioid Prescribing Rate</th>\n",
       "      <th>2013_2016_Change in Extended Release Opioid Prescribing Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>National</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.82</td>\n",
       "      <td>6.12</td>\n",
       "      <td>5.74</td>\n",
       "      <td>6.27</td>\n",
       "      <td>5.52</td>\n",
       "      <td>6.71</td>\n",
       "      <td>5.34</td>\n",
       "      <td>6.92</td>\n",
       "      <td>-0.47</td>\n",
       "      <td>0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>7.75</td>\n",
       "      <td>5.24</td>\n",
       "      <td>7.86</td>\n",
       "      <td>5.24</td>\n",
       "      <td>7.54</td>\n",
       "      <td>5.55</td>\n",
       "      <td>7.47</td>\n",
       "      <td>5.59</td>\n",
       "      <td>-0.28</td>\n",
       "      <td>0.35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State_Name State_Abbreviation  2013_Opioid_Prescribing_Rate  \\\n",
       "0   National                NaN                          5.82   \n",
       "1    Alabama                 AL                          7.75   \n",
       "\n",
       "   2013_Extended_Release_Opioid_Prescribing_Rate  \\\n",
       "0                                           6.12   \n",
       "1                                           5.24   \n",
       "\n",
       "   2014_Opioid_Prescribing_Rate  \\\n",
       "0                          5.74   \n",
       "1                          7.86   \n",
       "\n",
       "   2014_Extended_Release_Opioid_Prescribing_Rate  \\\n",
       "0                                           6.27   \n",
       "1                                           5.24   \n",
       "\n",
       "   2015_Opioid_Prescribing_Rate  \\\n",
       "0                          5.52   \n",
       "1                          7.54   \n",
       "\n",
       "   2015_Extended_Release_Opioid_Prescribing_ Rate  \\\n",
       "0                                            6.71   \n",
       "1                                            5.55   \n",
       "\n",
       "   2016_Opioid_Prescribing_Rate  \\\n",
       "0                          5.34   \n",
       "1                          7.47   \n",
       "\n",
       "   2016_Extended_Release_Opioid_Prescribing_ Rate  \\\n",
       "0                                            6.92   \n",
       "1                                            5.59   \n",
       "\n",
       "   2013_2016_Change in Opioid Prescribing Rate  \\\n",
       "0                                        -0.47   \n",
       "1                                        -0.28   \n",
       "\n",
       "   2013_2016_Change in Extended Release Opioid Prescribing Rate   \n",
       "0                                               0.80              \n",
       "1                                               0.35              "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "psr_df.fillna(\"National\")\n",
    "psr_df.head()[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State_Name</th>\n",
       "      <th>2013_2016_Change in Opioid Prescribing Rate</th>\n",
       "      <th>2013_2016_Change in Extended Release Opioid Prescribing Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>National</td>\n",
       "      <td>-0.47</td>\n",
       "      <td>0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>-0.28</td>\n",
       "      <td>0.35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State_Name  2013_2016_Change in Opioid Prescribing Rate  \\\n",
       "0   National                                        -0.47   \n",
       "1    Alabama                                        -0.28   \n",
       "\n",
       "   2013_2016_Change in Extended Release Opioid Prescribing Rate   \n",
       "0                                               0.80              \n",
       "1                                               0.35              "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "psr_changeex_df = psr_df[['State_Name','2013_2016_Change in Opioid Prescribing Rate', '2013_2016_Change in Extended Release Opioid Prescribing Rate ']]\n",
    "psr_changeex_df .head()[:2]"
   ]
  },
  {
   "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>State_Name</th>\n",
       "      <th>State_Abbreviation</th>\n",
       "      <th>2013_Opioid_Prescribing_Rate</th>\n",
       "      <th>2013_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "      <th>2014_Opioid_Prescribing_Rate</th>\n",
       "      <th>2014_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "      <th>2015_Opioid_Prescribing_Rate</th>\n",
       "      <th>2015_Extended_Release_Opioid_Prescribing_ Rate</th>\n",
       "      <th>2016_Opioid_Prescribing_Rate</th>\n",
       "      <th>2016_Extended_Release_Opioid_Prescribing_ Rate</th>\n",
       "      <th>2013_2016_Change in Opioid Prescribing Rate</th>\n",
       "      <th>2013_2016_Change in Extended Release Opioid Prescribing Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>National</td>\n",
       "      <td>National</td>\n",
       "      <td>5.82</td>\n",
       "      <td>6.12</td>\n",
       "      <td>5.74</td>\n",
       "      <td>6.27</td>\n",
       "      <td>5.52</td>\n",
       "      <td>6.71</td>\n",
       "      <td>5.34</td>\n",
       "      <td>6.92</td>\n",
       "      <td>-0.47</td>\n",
       "      <td>0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>7.75</td>\n",
       "      <td>5.24</td>\n",
       "      <td>7.86</td>\n",
       "      <td>5.24</td>\n",
       "      <td>7.54</td>\n",
       "      <td>5.55</td>\n",
       "      <td>7.47</td>\n",
       "      <td>5.59</td>\n",
       "      <td>-0.28</td>\n",
       "      <td>0.35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State_Name State_Abbreviation  2013_Opioid_Prescribing_Rate  \\\n",
       "0   National           National                          5.82   \n",
       "1    Alabama                 AL                          7.75   \n",
       "\n",
       "   2013_Extended_Release_Opioid_Prescribing_Rate  \\\n",
       "0                                           6.12   \n",
       "1                                           5.24   \n",
       "\n",
       "   2014_Opioid_Prescribing_Rate  \\\n",
       "0                          5.74   \n",
       "1                          7.86   \n",
       "\n",
       "   2014_Extended_Release_Opioid_Prescribing_Rate  \\\n",
       "0                                           6.27   \n",
       "1                                           5.24   \n",
       "\n",
       "   2015_Opioid_Prescribing_Rate  \\\n",
       "0                          5.52   \n",
       "1                          7.54   \n",
       "\n",
       "   2015_Extended_Release_Opioid_Prescribing_ Rate  \\\n",
       "0                                            6.71   \n",
       "1                                            5.55   \n",
       "\n",
       "   2016_Opioid_Prescribing_Rate  \\\n",
       "0                          5.34   \n",
       "1                          7.47   \n",
       "\n",
       "   2016_Extended_Release_Opioid_Prescribing_ Rate  \\\n",
       "0                                            6.92   \n",
       "1                                            5.59   \n",
       "\n",
       "   2013_2016_Change in Opioid Prescribing Rate  \\\n",
       "0                                        -0.47   \n",
       "1                                        -0.28   \n",
       "\n",
       "   2013_2016_Change in Extended Release Opioid Prescribing Rate   \n",
       "0                                               0.80              \n",
       "1                                               0.35              "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Selected the opioid prescribe rate columsn for years 2013-2016 by prescriber county\n",
    "# pcr_df.head()[:2]\n",
    "psr_df.fillna(\"National\")[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State_Name</th>\n",
       "      <th>State_Abbreviation</th>\n",
       "      <th>County Name</th>\n",
       "      <th>State_FIPS</th>\n",
       "      <th>2013_Opioid_Prescribing_Rate</th>\n",
       "      <th>2013_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "      <th>2014_Opioid_Prescribing_Rate</th>\n",
       "      <th>2014_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "      <th>2015_Opioid_Prescribing_Rate</th>\n",
       "      <th>2015_Extended_Release_Opioid_Prescribing_ Rate</th>\n",
       "      <th>2016_Opioid_Prescribing_Rate</th>\n",
       "      <th>2016_Extended_Release_Opioid_Prescribing_ Rate</th>\n",
       "      <th>2013_2016_Change in Opioid Prescribing Rate</th>\n",
       "      <th>2013_2016_Change in Extended Release Opioid Prescribing Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>National</td>\n",
       "      <td>National</td>\n",
       "      <td>National</td>\n",
       "      <td>National</td>\n",
       "      <td>5.82</td>\n",
       "      <td>6.12</td>\n",
       "      <td>5.74</td>\n",
       "      <td>6.27</td>\n",
       "      <td>5.52</td>\n",
       "      <td>6.71</td>\n",
       "      <td>5.34</td>\n",
       "      <td>6.92</td>\n",
       "      <td>-0.47</td>\n",
       "      <td>0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>1001</td>\n",
       "      <td>9.8</td>\n",
       "      <td>4.78</td>\n",
       "      <td>9.72</td>\n",
       "      <td>5.85</td>\n",
       "      <td>8.07</td>\n",
       "      <td>7.24</td>\n",
       "      <td>11.47</td>\n",
       "      <td>9.39</td>\n",
       "      <td>1.67</td>\n",
       "      <td>4.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State_Name State_Abbreviation County Name State_FIPS  \\\n",
       "0   National           National    National   National   \n",
       "1    Alabama                 AL     Autauga       1001   \n",
       "\n",
       "  2013_Opioid_Prescribing_Rate 2013_Extended_Release_Opioid_Prescribing_Rate  \\\n",
       "0                         5.82                                          6.12   \n",
       "1                          9.8                                          4.78   \n",
       "\n",
       "  2014_Opioid_Prescribing_Rate 2014_Extended_Release_Opioid_Prescribing_Rate  \\\n",
       "0                         5.74                                          6.27   \n",
       "1                         9.72                                          5.85   \n",
       "\n",
       "  2015_Opioid_Prescribing_Rate 2015_Extended_Release_Opioid_Prescribing_ Rate  \\\n",
       "0                         5.52                                           6.71   \n",
       "1                         8.07                                           7.24   \n",
       "\n",
       "  2016_Opioid_Prescribing_Rate 2016_Extended_Release_Opioid_Prescribing_ Rate  \\\n",
       "0                         5.34                                           6.92   \n",
       "1                        11.47                                           9.39   \n",
       "\n",
       "  2013_2016_Change in Opioid Prescribing Rate  \\\n",
       "0                                       -0.47   \n",
       "1                                        1.67   \n",
       "\n",
       "  2013_2016_Change in Extended Release Opioid Prescribing Rate   \n",
       "0                                                0.8             \n",
       "1                                               4.61             "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pcr_df.fillna(\"National\")[:2]\n",
    "# pcr_df.head()[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State_Name</th>\n",
       "      <th>2013_2016_Change in Opioid Prescribing Rate</th>\n",
       "      <th>2013_2016_Change in Extended Release Opioid Prescribing Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>National</td>\n",
       "      <td>-0.47</td>\n",
       "      <td>0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>1.67</td>\n",
       "      <td>4.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State_Name  2013_2016_Change in Opioid Prescribing Rate  \\\n",
       "0   National                                        -0.47   \n",
       "1    Alabama                                         1.67   \n",
       "\n",
       "   2013_2016_Change in Extended Release Opioid Prescribing Rate   \n",
       "0                                               0.80              \n",
       "1                                               4.61              "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pcr_changeex_df = pcr_df[['State_Name','2013_2016_Change in Opioid Prescribing Rate', '2013_2016_Change in Extended Release Opioid Prescribing Rate ']]\n",
    "pcr_changeex_df .head()[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State_Name</th>\n",
       "      <th>State_Abbreviation</th>\n",
       "      <th>County Name</th>\n",
       "      <th>State_FIPS</th>\n",
       "      <th>2013_Part_ D_Prescribers</th>\n",
       "      <th>2013_Part_Opioid_Prescribers</th>\n",
       "      <th>2013_Opioid Claims</th>\n",
       "      <th>2013_Extended_Release_Opioid_Claims</th>\n",
       "      <th>2013_Overall_Claims</th>\n",
       "      <th>2013_Opioid_Prescribing_Rate</th>\n",
       "      <th>...</th>\n",
       "      <th>2014_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "      <th>2015_Part D Prescribers</th>\n",
       "      <th>2015_Part D Opioid Prescribers</th>\n",
       "      <th>2015_Opioid Claims</th>\n",
       "      <th>2015_Extended Release Opioid Claims</th>\n",
       "      <th>2015_Overall_Claims</th>\n",
       "      <th>2015_Opioid_Prescribing_Rate</th>\n",
       "      <th>2015_Extended_Release_Opioid_Prescribing_ Rate</th>\n",
       "      <th>2013_2015_Change_in_Opioid_Prescribing_Rate</th>\n",
       "      <th>2013_2015_Change_in_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>National</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1,037,770</td>\n",
       "      <td>491,500</td>\n",
       "      <td>78,045,683</td>\n",
       "      <td>4,773,286</td>\n",
       "      <td>1,342,096,654</td>\n",
       "      <td>5.82</td>\n",
       "      <td>...</td>\n",
       "      <td>6.27</td>\n",
       "      <td>1,090,979</td>\n",
       "      <td>496,383</td>\n",
       "      <td>78,372,855</td>\n",
       "      <td>5,257,481</td>\n",
       "      <td>1,418,618,875</td>\n",
       "      <td>5.52</td>\n",
       "      <td>6.71</td>\n",
       "      <td>-0.30</td>\n",
       "      <td>0.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>1001.0</td>\n",
       "      <td>67</td>\n",
       "      <td>44</td>\n",
       "      <td>20,482</td>\n",
       "      <td>979</td>\n",
       "      <td>208,936</td>\n",
       "      <td>9.80</td>\n",
       "      <td>...</td>\n",
       "      <td>5.85</td>\n",
       "      <td>68</td>\n",
       "      <td>43</td>\n",
       "      <td>15,998</td>\n",
       "      <td>1,159</td>\n",
       "      <td>198,347</td>\n",
       "      <td>8.07</td>\n",
       "      <td>7.24</td>\n",
       "      <td>-1.73</td>\n",
       "      <td>2.46</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  State_Name State_Abbreviation County Name  State_FIPS  \\\n",
       "0   National                NaN         NaN         NaN   \n",
       "1    Alabama                 AL     Autauga      1001.0   \n",
       "\n",
       "  2013_Part_ D_Prescribers 2013_Part_Opioid_Prescribers 2013_Opioid Claims  \\\n",
       "0                1,037,770                      491,500         78,045,683   \n",
       "1                       67                           44             20,482   \n",
       "\n",
       "  2013_Extended_Release_Opioid_Claims 2013_Overall_Claims  \\\n",
       "0                           4,773,286       1,342,096,654   \n",
       "1                                 979             208,936   \n",
       "\n",
       "   2013_Opioid_Prescribing_Rate  \\\n",
       "0                          5.82   \n",
       "1                          9.80   \n",
       "\n",
       "                               ...                               \\\n",
       "0                              ...                                \n",
       "1                              ...                                \n",
       "\n",
       "   2014_Extended_Release_Opioid_Prescribing_Rate 2015_Part D Prescribers  \\\n",
       "0                                           6.27               1,090,979   \n",
       "1                                           5.85                      68   \n",
       "\n",
       "  2015_Part D Opioid Prescribers 2015_Opioid Claims  \\\n",
       "0                        496,383         78,372,855   \n",
       "1                             43             15,998   \n",
       "\n",
       "  2015_Extended Release Opioid Claims 2015_Overall_Claims  \\\n",
       "0                           5,257,481       1,418,618,875   \n",
       "1                               1,159             198,347   \n",
       "\n",
       "   2015_Opioid_Prescribing_Rate  \\\n",
       "0                          5.52   \n",
       "1                          8.07   \n",
       "\n",
       "   2015_Extended_Release_Opioid_Prescribing_ Rate  \\\n",
       "0                                            6.71   \n",
       "1                                            7.24   \n",
       "\n",
       "  2013_2015_Change_in_Opioid_Prescribing_Rate  \\\n",
       "0                                       -0.30   \n",
       "1                                       -1.73   \n",
       "\n",
       "  2013_2015_Change_in_Extended_Release_Opioid_Prescribing_Rate   \n",
       "0                                               0.59             \n",
       "1                                               2.46             \n",
       "\n",
       "[2 rows x 27 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Prescriber_county_df.head()[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State_Name</th>\n",
       "      <th>State_Abbreviation</th>\n",
       "      <th>State_FIPS</th>\n",
       "      <th>2013_Part_ D_Prescribers</th>\n",
       "      <th>2013_Part_Opioid_Prescribers</th>\n",
       "      <th>2013_Opioid Claims</th>\n",
       "      <th>2013_Extended_Release_Opioid_Claims</th>\n",
       "      <th>2013_Overall_Claims</th>\n",
       "      <th>2013_Opioid_Prescribing_Rate</th>\n",
       "      <th>2013_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "      <th>...</th>\n",
       "      <th>2014_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "      <th>2015_Part D Prescribers</th>\n",
       "      <th>2015_Part D Opioid Prescribers</th>\n",
       "      <th>2015_Opioid Claims</th>\n",
       "      <th>2015_Extended Release Opioid Claims</th>\n",
       "      <th>2015_Overall_Claims</th>\n",
       "      <th>2015_Opioid_Prescribing_Rate</th>\n",
       "      <th>2015_Extended_Release_Opioid_Prescribing_ Rate</th>\n",
       "      <th>2013_2015_Change_in_Opioid_Prescribing_Rate</th>\n",
       "      <th>2013_2015_Change_in_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>National</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1,037,770</td>\n",
       "      <td>491,500</td>\n",
       "      <td>78,045,683</td>\n",
       "      <td>4,773,286</td>\n",
       "      <td>1,342,096,654</td>\n",
       "      <td>5.82</td>\n",
       "      <td>6.12</td>\n",
       "      <td>...</td>\n",
       "      <td>6.27</td>\n",
       "      <td>1,090,979</td>\n",
       "      <td>496,383</td>\n",
       "      <td>78,372,855</td>\n",
       "      <td>5,257,481</td>\n",
       "      <td>1,418,618,875</td>\n",
       "      <td>5.52</td>\n",
       "      <td>6.71</td>\n",
       "      <td>-0.30</td>\n",
       "      <td>0.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>35004.0</td>\n",
       "      <td>18</td>\n",
       "      <td>9</td>\n",
       "      <td>1,340</td>\n",
       "      <td>19</td>\n",
       "      <td>18,632</td>\n",
       "      <td>7.19</td>\n",
       "      <td>1.42</td>\n",
       "      <td>...</td>\n",
       "      <td>6.30</td>\n",
       "      <td>17</td>\n",
       "      <td>7</td>\n",
       "      <td>1,340</td>\n",
       "      <td>39</td>\n",
       "      <td>16,830</td>\n",
       "      <td>7.96</td>\n",
       "      <td>2.91</td>\n",
       "      <td>0.77</td>\n",
       "      <td>1.49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  State_Name State_Abbreviation  State_FIPS 2013_Part_ D_Prescribers  \\\n",
       "0   National                NaN         NaN                1,037,770   \n",
       "1    Alabama                 AL     35004.0                       18   \n",
       "\n",
       "  2013_Part_Opioid_Prescribers 2013_Opioid Claims  \\\n",
       "0                      491,500         78,045,683   \n",
       "1                            9              1,340   \n",
       "\n",
       "  2013_Extended_Release_Opioid_Claims 2013_Overall_Claims  \\\n",
       "0                           4,773,286       1,342,096,654   \n",
       "1                                  19              18,632   \n",
       "\n",
       "   2013_Opioid_Prescribing_Rate  \\\n",
       "0                          5.82   \n",
       "1                          7.19   \n",
       "\n",
       "   2013_Extended_Release_Opioid_Prescribing_Rate  \\\n",
       "0                                           6.12   \n",
       "1                                           1.42   \n",
       "\n",
       "                               ...                               \\\n",
       "0                              ...                                \n",
       "1                              ...                                \n",
       "\n",
       "  2014_Extended_Release_Opioid_Prescribing_Rate 2015_Part D Prescribers  \\\n",
       "0                                          6.27               1,090,979   \n",
       "1                                          6.30                      17   \n",
       "\n",
       "  2015_Part D Opioid Prescribers 2015_Opioid Claims  \\\n",
       "0                        496,383         78,372,855   \n",
       "1                              7              1,340   \n",
       "\n",
       "  2015_Extended Release Opioid Claims  2015_Overall_Claims  \\\n",
       "0                           5,257,481        1,418,618,875   \n",
       "1                                  39               16,830   \n",
       "\n",
       "   2015_Opioid_Prescribing_Rate  \\\n",
       "0                          5.52   \n",
       "1                          7.96   \n",
       "\n",
       "  2015_Extended_Release_Opioid_Prescribing_ Rate  \\\n",
       "0                                           6.71   \n",
       "1                                           2.91   \n",
       "\n",
       "  2013_2015_Change_in_Opioid_Prescribing_Rate  \\\n",
       "0                                       -0.30   \n",
       "1                                        0.77   \n",
       "\n",
       "  2013_2015_Change_in_Extended_Release_Opioid_Prescribing_Rate   \n",
       "0                                               0.59             \n",
       "1                                               1.49             \n",
       "\n",
       "[2 rows x 26 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Prescriber_zip_df.head()[:2] # this is the dataset for the prescriber zip which is one of the dataclassifications by cms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State_Name</th>\n",
       "      <th>State_Abbreviation</th>\n",
       "      <th>County Name</th>\n",
       "      <th>State_FIPS</th>\n",
       "      <th>2013_Opioid_Prescribing_Rate</th>\n",
       "      <th>2013_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "      <th>2014_Opioid_Prescribing_Rate</th>\n",
       "      <th>2014_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "      <th>2015_Opioid_Prescribing_Rate</th>\n",
       "      <th>2015_Extended_Release_Opioid_Prescribing_ Rate</th>\n",
       "      <th>2016_Opioid_Prescribing_Rate</th>\n",
       "      <th>2016_Extended_Release_Opioid_Prescribing_ Rate</th>\n",
       "      <th>2013_2016_Change in Opioid Prescribing Rate</th>\n",
       "      <th>2013_2016_Change in Extended Release Opioid Prescribing Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>National</td>\n",
       "      <td>National</td>\n",
       "      <td>National</td>\n",
       "      <td>National</td>\n",
       "      <td>5.82</td>\n",
       "      <td>6.12</td>\n",
       "      <td>5.74</td>\n",
       "      <td>6.27</td>\n",
       "      <td>5.52</td>\n",
       "      <td>6.71</td>\n",
       "      <td>5.34</td>\n",
       "      <td>6.92</td>\n",
       "      <td>-0.47</td>\n",
       "      <td>0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>1001</td>\n",
       "      <td>9.8</td>\n",
       "      <td>4.78</td>\n",
       "      <td>9.72</td>\n",
       "      <td>5.85</td>\n",
       "      <td>8.07</td>\n",
       "      <td>7.24</td>\n",
       "      <td>11.47</td>\n",
       "      <td>9.39</td>\n",
       "      <td>1.67</td>\n",
       "      <td>4.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State_Name State_Abbreviation County Name State_FIPS  \\\n",
       "0   National           National    National   National   \n",
       "1    Alabama                 AL     Autauga       1001   \n",
       "\n",
       "  2013_Opioid_Prescribing_Rate 2013_Extended_Release_Opioid_Prescribing_Rate  \\\n",
       "0                         5.82                                          6.12   \n",
       "1                          9.8                                          4.78   \n",
       "\n",
       "  2014_Opioid_Prescribing_Rate 2014_Extended_Release_Opioid_Prescribing_Rate  \\\n",
       "0                         5.74                                          6.27   \n",
       "1                         9.72                                          5.85   \n",
       "\n",
       "  2015_Opioid_Prescribing_Rate 2015_Extended_Release_Opioid_Prescribing_ Rate  \\\n",
       "0                         5.52                                           6.71   \n",
       "1                         8.07                                           7.24   \n",
       "\n",
       "  2016_Opioid_Prescribing_Rate 2016_Extended_Release_Opioid_Prescribing_ Rate  \\\n",
       "0                         5.34                                           6.92   \n",
       "1                        11.47                                           9.39   \n",
       "\n",
       "  2013_2016_Change in Opioid Prescribing Rate  \\\n",
       "0                                       -0.47   \n",
       "1                                        1.67   \n",
       "\n",
       "  2013_2016_Change in Extended Release Opioid Prescribing Rate   \n",
       "0                                                0.8             \n",
       "1                                               4.61             "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Selected the opioid prescribe rate columsn for years 2013-2016 by prescriber county_rate\n",
    "# pcr_df.head()[:2]\n",
    "pcr_df.fillna(\"National\")[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State_Name</th>\n",
       "      <th>2013_2016_Change in Opioid Prescribing Rate</th>\n",
       "      <th>2013_2016_Change in Extended Release Opioid Prescribing Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>National</td>\n",
       "      <td>-0.47</td>\n",
       "      <td>0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>1.67</td>\n",
       "      <td>4.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State_Name  2013_2016_Change in Opioid Prescribing Rate  \\\n",
       "0   National                                        -0.47   \n",
       "1    Alabama                                         1.67   \n",
       "\n",
       "   2013_2016_Change in Extended Release Opioid Prescribing Rate   \n",
       "0                                               0.80              \n",
       "1                                               4.61              "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pcr_changeex_df = pcr_df[['State_Name','2013_2016_Change in Opioid Prescribing Rate', '2013_2016_Change in Extended Release Opioid Prescribing Rate ']]\n",
    "pcr_changeex_df .head()[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State_Name</th>\n",
       "      <th>State_Abbreviation</th>\n",
       "      <th>State_FIPS</th>\n",
       "      <th>2013_Opioid_Prescribing_Rate</th>\n",
       "      <th>2013_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "      <th>2014_Opioid_Prescribing_Rate</th>\n",
       "      <th>2014_Extended_Release_Opioid_Prescribing_Rate</th>\n",
       "      <th>2015_Opioid_Prescribing_Rate</th>\n",
       "      <th>2015_Extended_Release_Opioid_Prescribing_ Rate</th>\n",
       "      <th>2016_Opioid Prescribing Rate</th>\n",
       "      <th>2016_Extended Release Opioid Prescribing Rate</th>\n",
       "      <th>2013_2016_Change in Opioid Prescribing Rate</th>\n",
       "      <th>2013_2016_Change in Extended Release Opioid Prescribing Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>National</td>\n",
       "      <td>National</td>\n",
       "      <td>National</td>\n",
       "      <td>5.82</td>\n",
       "      <td>6.12</td>\n",
       "      <td>5.74</td>\n",
       "      <td>6.27</td>\n",
       "      <td>5.52</td>\n",
       "      <td>6.71</td>\n",
       "      <td>5.34</td>\n",
       "      <td>6.92</td>\n",
       "      <td>-0.47</td>\n",
       "      <td>0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>35004</td>\n",
       "      <td>7.19</td>\n",
       "      <td>1.42</td>\n",
       "      <td>9.71</td>\n",
       "      <td>6.3</td>\n",
       "      <td>7.96</td>\n",
       "      <td>2.91</td>\n",
       "      <td>7.47</td>\n",
       "      <td>5.61</td>\n",
       "      <td>0.28</td>\n",
       "      <td>4.19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State_Name State_Abbreviation State_FIPS 2013_Opioid_Prescribing_Rate  \\\n",
       "0   National           National   National                         5.82   \n",
       "1    Alabama                 AL      35004                         7.19   \n",
       "\n",
       "  2013_Extended_Release_Opioid_Prescribing_Rate 2014_Opioid_Prescribing_Rate  \\\n",
       "0                                          6.12                         5.74   \n",
       "1                                          1.42                         9.71   \n",
       "\n",
       "  2014_Extended_Release_Opioid_Prescribing_Rate 2015_Opioid_Prescribing_Rate  \\\n",
       "0                                          6.27                         5.52   \n",
       "1                                           6.3                         7.96   \n",
       "\n",
       "  2015_Extended_Release_Opioid_Prescribing_ Rate 2016_Opioid Prescribing Rate  \\\n",
       "0                                           6.71                         5.34   \n",
       "1                                           2.91                         7.47   \n",
       "\n",
       "  2016_Extended Release Opioid Prescribing Rate  \\\n",
       "0                                          6.92   \n",
       "1                                          5.61   \n",
       "\n",
       "  2013_2016_Change in Opioid Prescribing Rate  \\\n",
       "0                                       -0.47   \n",
       "1                                        0.28   \n",
       "\n",
       "  2013_2016_Change in Extended Release Opioid Prescribing Rate   \n",
       "0                                                0.8             \n",
       "1                                               4.19             "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pzr_df.fillna(\"National\")[:2]\n",
    "# pzr_df.head()[:2] here the data is by the prescriber zib which is prescriber zip rate\n",
    "# flling the NaN values under the stateabbrevation and stateFips with National"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State_Name</th>\n",
       "      <th>2013_2016_Change in Opioid Prescribing Rate</th>\n",
       "      <th>2013_2016_Change in Extended Release Opioid Prescribing Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>National</td>\n",
       "      <td>-0.47</td>\n",
       "      <td>0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>0.28</td>\n",
       "      <td>4.19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State_Name  2013_2016_Change in Opioid Prescribing Rate  \\\n",
       "0   National                                        -0.47   \n",
       "1    Alabama                                         0.28   \n",
       "\n",
       "   2013_2016_Change in Extended Release Opioid Prescribing Rate   \n",
       "0                                               0.80              \n",
       "1                                               4.19              "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pzr_changeex_df = pzr_df[['State_Name','2013_2016_Change in Opioid Prescribing Rate', '2013_2016_Change in Extended Release Opioid Prescribing Rate ']]\n",
    "pzr_changeex_df .head()[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Location</th>\n",
       "      <th>FY2004__SMHA Expenditures Per Capita</th>\n",
       "      <th>FY2005__SMHA Expenditures Per Capita</th>\n",
       "      <th>FY2006__SMHA Expenditures Per Capita</th>\n",
       "      <th>FY2007__SMHA Expenditures Per Capita</th>\n",
       "      <th>FY2008__SMHA Expenditures Per Capita</th>\n",
       "      <th>FY2009__SMHA Expenditures Per Capita</th>\n",
       "      <th>FY2010__SMHA Expenditures Per Capita</th>\n",
       "      <th>FY2011__SMHA Expenditures Per Capita</th>\n",
       "      <th>FY2012__SMHA Expenditures Per Capita</th>\n",
       "      <th>FY2013__SMHA Expenditures Per Capita</th>\n",
       "      <th>Footnotes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>United States</td>\n",
       "      <td>93.04</td>\n",
       "      <td>99.55</td>\n",
       "      <td>103.53</td>\n",
       "      <td>113.27</td>\n",
       "      <td>121.13</td>\n",
       "      <td>122.90</td>\n",
       "      <td>120.56</td>\n",
       "      <td>123.93</td>\n",
       "      <td>124.99</td>\n",
       "      <td>119.62</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>58.78</td>\n",
       "      <td>60.31</td>\n",
       "      <td>64.43</td>\n",
       "      <td>74.03</td>\n",
       "      <td>79.39</td>\n",
       "      <td>77.89</td>\n",
       "      <td>78.19</td>\n",
       "      <td>78.33</td>\n",
       "      <td>76.27</td>\n",
       "      <td>72.64</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Location  FY2004__SMHA Expenditures Per Capita  \\\n",
       "0  United States                                 93.04   \n",
       "1        Alabama                                 58.78   \n",
       "\n",
       "   FY2005__SMHA Expenditures Per Capita  FY2006__SMHA Expenditures Per Capita  \\\n",
       "0                                 99.55                                103.53   \n",
       "1                                 60.31                                 64.43   \n",
       "\n",
       "   FY2007__SMHA Expenditures Per Capita  FY2008__SMHA Expenditures Per Capita  \\\n",
       "0                                113.27                                121.13   \n",
       "1                                 74.03                                 79.39   \n",
       "\n",
       "   FY2009__SMHA Expenditures Per Capita  FY2010__SMHA Expenditures Per Capita  \\\n",
       "0                                122.90                                120.56   \n",
       "1                                 77.89                                 78.19   \n",
       "\n",
       "  FY2011__SMHA Expenditures Per Capita  FY2012__SMHA Expenditures Per Capita  \\\n",
       "0                               123.93                                124.99   \n",
       "1                                78.33                                 76.27   \n",
       "\n",
       "  FY2013__SMHA Expenditures Per Capita Footnotes  \n",
       "0                               119.62       NaN  \n",
       "1                                72.64       NaN  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "KFFMentalHealthspendState_df.head()[:2]  # "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FIPS</th>\n",
       "      <th>Area Name</th>\n",
       "      <th>1900</th>\n",
       "      <th>1910</th>\n",
       "      <th>1920</th>\n",
       "      <th>1930</th>\n",
       "      <th>1940</th>\n",
       "      <th>1950</th>\n",
       "      <th>1960</th>\n",
       "      <th>1970</th>\n",
       "      <th>1980</th>\n",
       "      <th>1990</th>\n",
       "      <th>2000</th>\n",
       "      <th>2010</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>United States</td>\n",
       "      <td>39.6</td>\n",
       "      <td>45.6</td>\n",
       "      <td>51.2</td>\n",
       "      <td>56.1</td>\n",
       "      <td>56.5</td>\n",
       "      <td>64.0</td>\n",
       "      <td>69.9</td>\n",
       "      <td>73.6</td>\n",
       "      <td>73.7</td>\n",
       "      <td>75.2</td>\n",
       "      <td>79.0</td>\n",
       "      <td>80.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>11.9</td>\n",
       "      <td>17.3</td>\n",
       "      <td>21.7</td>\n",
       "      <td>28.1</td>\n",
       "      <td>30.2</td>\n",
       "      <td>43.8</td>\n",
       "      <td>54.8</td>\n",
       "      <td>58.6</td>\n",
       "      <td>60.0</td>\n",
       "      <td>60.4</td>\n",
       "      <td>55.4</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FIPS      Area Name  1900  1910  1920  1930  1940  1950  1960  1970  1980  \\\n",
       "0    0  United States  39.6  45.6  51.2  56.1  56.5  64.0  69.9  73.6  73.7   \n",
       "1    1        Alabama  11.9  17.3  21.7  28.1  30.2  43.8  54.8  58.6  60.0   \n",
       "\n",
       "   1990  2000  2010  \n",
       "0  75.2  79.0  80.7  \n",
       "1  60.4  55.4  59.0  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop_urban_df.head()[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Notes</th>\n",
       "      <th>State</th>\n",
       "      <th>State Code</th>\n",
       "      <th>Year</th>\n",
       "      <th>Year Code</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Gender Code</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Population</th>\n",
       "      <th>Crude Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1999.0</td>\n",
       "      <td>1999.0</td>\n",
       "      <td>Female</td>\n",
       "      <td>F</td>\n",
       "      <td>84.0</td>\n",
       "      <td>2293259.0</td>\n",
       "      <td>3.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1999.0</td>\n",
       "      <td>1999.0</td>\n",
       "      <td>Male</td>\n",
       "      <td>M</td>\n",
       "      <td>111.0</td>\n",
       "      <td>2136882.0</td>\n",
       "      <td>5.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Notes    State  State Code    Year  Year Code  Gender Gender Code  Deaths  \\\n",
       "0   NaN  Alabama         1.0  1999.0     1999.0  Female           F    84.0   \n",
       "1   NaN  Alabama         1.0  1999.0     1999.0    Male           M   111.0   \n",
       "\n",
       "   Population  Crude Rate  \n",
       "0   2293259.0         3.7  \n",
       "1   2136882.0         5.2  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Wonder_Drug_Death_Data1_df.head()[:2]  # looking on the first two rows of the Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Notes</th>\n",
       "      <th>State</th>\n",
       "      <th>State Code</th>\n",
       "      <th>Year</th>\n",
       "      <th>Year Code</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Gender Code</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Population</th>\n",
       "      <th>Crude Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1999.0</td>\n",
       "      <td>1999.0</td>\n",
       "      <td>Female</td>\n",
       "      <td>F</td>\n",
       "      <td>84.0</td>\n",
       "      <td>2293259.0</td>\n",
       "      <td>3.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1999.0</td>\n",
       "      <td>1999.0</td>\n",
       "      <td>Male</td>\n",
       "      <td>M</td>\n",
       "      <td>111.0</td>\n",
       "      <td>2136882.0</td>\n",
       "      <td>5.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Notes    State  State Code    Year  Year Code  Gender Gender Code  Deaths  \\\n",
       "0   NaN  Alabama         1.0  1999.0     1999.0  Female           F    84.0   \n",
       "1   NaN  Alabama         1.0  1999.0     1999.0    Male           M   111.0   \n",
       "\n",
       "   Population  Crude Rate  \n",
       "0   2293259.0         3.7  \n",
       "1   2136882.0         5.2  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Wonder_Drug_Death_Data1_df.head()[:2]"
   ]
  },
  {
   "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>Expenditure Amount (Millions)</th>\n",
       "      <th>1960</th>\n",
       "      <th>1961</th>\n",
       "      <th>1962</th>\n",
       "      <th>1963</th>\n",
       "      <th>1964</th>\n",
       "      <th>1965</th>\n",
       "      <th>1966</th>\n",
       "      <th>1967</th>\n",
       "      <th>1968</th>\n",
       "      <th>...</th>\n",
       "      <th>2007</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Total National Health Expenditures</td>\n",
       "      <td>27,214</td>\n",
       "      <td>29,138</td>\n",
       "      <td>31,842</td>\n",
       "      <td>34,595</td>\n",
       "      <td>38,394</td>\n",
       "      <td>41,852</td>\n",
       "      <td>46,081</td>\n",
       "      <td>51,565</td>\n",
       "      <td>58,402</td>\n",
       "      <td>...</td>\n",
       "      <td>2,295,307</td>\n",
       "      <td>2,399,121</td>\n",
       "      <td>2,495,414</td>\n",
       "      <td>2,598,823</td>\n",
       "      <td>2,689,349</td>\n",
       "      <td>2,797,260</td>\n",
       "      <td>2,879,008</td>\n",
       "      <td>3,026,157</td>\n",
       "      <td>3,200,815</td>\n",
       "      <td>3,337,248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Out of pocket</td>\n",
       "      <td>12,949</td>\n",
       "      <td>13,357</td>\n",
       "      <td>14,255</td>\n",
       "      <td>15,311</td>\n",
       "      <td>16,928</td>\n",
       "      <td>18,209</td>\n",
       "      <td>18,594</td>\n",
       "      <td>18,537</td>\n",
       "      <td>20,500</td>\n",
       "      <td>...</td>\n",
       "      <td>290,000</td>\n",
       "      <td>295,184</td>\n",
       "      <td>293,723</td>\n",
       "      <td>299,742</td>\n",
       "      <td>309,973</td>\n",
       "      <td>318,330</td>\n",
       "      <td>325,189</td>\n",
       "      <td>330,068</td>\n",
       "      <td>339,315</td>\n",
       "      <td>352,537</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 58 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Expenditure Amount (Millions)    1960    1961    1962    1963    1964  \\\n",
       "0  Total National Health Expenditures  27,214  29,138  31,842  34,595  38,394   \n",
       "1                       Out of pocket  12,949  13,357  14,255  15,311  16,928   \n",
       "\n",
       "     1965    1966    1967    1968    ...           2007       2008       2009  \\\n",
       "0  41,852  46,081  51,565  58,402    ...      2,295,307  2,399,121  2,495,414   \n",
       "1  18,209  18,594  18,537  20,500    ...        290,000    295,184    293,723   \n",
       "\n",
       "        2010       2011       2012       2013       2014       2015       2016  \n",
       "0  2,598,823  2,689,349  2,797,260  2,879,008  3,026,157  3,200,815  3,337,248  \n",
       "1    299,742    309,973    318,330    325,189    330,068    339,315    352,537  \n",
       "\n",
       "[2 rows x 58 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "NHE2016_df.head()[:2]   # USD in millions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>State</th>\n",
       "      <th>Population_1999</th>\n",
       "      <th>Violent Crime rate_1999</th>\n",
       "      <th>Murder and nonnegligent manslaughter rate_1999</th>\n",
       "      <th>Legacy rape rate /1_1999</th>\n",
       "      <th>Revised rape rate /2_1999</th>\n",
       "      <th>Robbery rate_1999</th>\n",
       "      <th>Aggravated assault rate_1999</th>\n",
       "      <th>Unnamed: 8_1999</th>\n",
       "      <th>Population_2014</th>\n",
       "      <th>Violent Crime rate_2014</th>\n",
       "      <th>Murder and nonnegligent manslaughter rate_2014</th>\n",
       "      <th>Legacy rape rate /1_2014</th>\n",
       "      <th>Revised rape rate /2_2014</th>\n",
       "      <th>Robbery rate_2014</th>\n",
       "      <th>Aggravated assault rate_2014</th>\n",
       "      <th>Unnamed: 8_2014</th>\n",
       "      <th>Violent Crime rate_change</th>\n",
       "      <th>Robbery rate_change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>4369862</td>\n",
       "      <td>490.2</td>\n",
       "      <td>7.9</td>\n",
       "      <td>34.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>121.2</td>\n",
       "      <td>326.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4849377</td>\n",
       "      <td>427.4</td>\n",
       "      <td>5.7</td>\n",
       "      <td>29.6</td>\n",
       "      <td>41.3</td>\n",
       "      <td>96.9</td>\n",
       "      <td>283.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-62.8</td>\n",
       "      <td>-24.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>619500</td>\n",
       "      <td>630.8</td>\n",
       "      <td>8.4</td>\n",
       "      <td>83.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>91.4</td>\n",
       "      <td>447.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>736732</td>\n",
       "      <td>635.8</td>\n",
       "      <td>5.6</td>\n",
       "      <td>75.3</td>\n",
       "      <td>104.7</td>\n",
       "      <td>85.4</td>\n",
       "      <td>440.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>-6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0    State  Population_1999  Violent Crime rate_1999  \\\n",
       "0           0  Alabama          4369862                    490.2   \n",
       "1           1   Alaska           619500                    630.8   \n",
       "\n",
       "   Murder and nonnegligent manslaughter rate_1999  Legacy rape rate /1_1999  \\\n",
       "0                                             7.9                      34.6   \n",
       "1                                             8.4                      83.5   \n",
       "\n",
       "   Revised rape rate /2_1999  Robbery rate_1999  Aggravated assault rate_1999  \\\n",
       "0                        NaN              121.2                         326.5   \n",
       "1                        NaN               91.4                         447.6   \n",
       "\n",
       "   Unnamed: 8_1999  Population_2014  Violent Crime rate_2014  \\\n",
       "0              NaN          4849377                    427.4   \n",
       "1              NaN           736732                    635.8   \n",
       "\n",
       "   Murder and nonnegligent manslaughter rate_2014  Legacy rape rate /1_2014  \\\n",
       "0                                             5.7                      29.6   \n",
       "1                                             5.6                      75.3   \n",
       "\n",
       "   Revised rape rate /2_2014  Robbery rate_2014  Aggravated assault rate_2014  \\\n",
       "0                       41.3               96.9                         283.4   \n",
       "1                      104.7               85.4                         440.2   \n",
       "\n",
       "   Unnamed: 8_2014  Violent Crime rate_change  Robbery rate_change  \n",
       "0              NaN                      -62.8                -24.3  \n",
       "1              NaN                        5.0                 -6.0  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "violentcrime_df.head()[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0', 'State', 'Population_1999', 'Violent Crime rate_1999',\n",
       "       'Murder and nonnegligent manslaughter rate_1999',\n",
       "       'Legacy rape rate /1_1999', 'Revised rape rate /2_1999',\n",
       "       'Robbery rate_1999', 'Aggravated assault rate_1999', 'Unnamed: 8_1999',\n",
       "       'Population_2014', 'Violent Crime rate_2014',\n",
       "       'Murder and nonnegligent manslaughter rate_2014',\n",
       "       'Legacy rape rate /1_2014', 'Revised rape rate /2_2014',\n",
       "       'Robbery rate_2014', 'Aggravated assault rate_2014', 'Unnamed: 8_2014',\n",
       "       'Violent Crime rate_change', 'Robbery rate_change'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "violentcrime_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "violentcrime_df.drop(['Unnamed: 0','Unnamed: 8_1999','Unnamed: 8_2014'], axis =1, inplace =True)"
   ]
  },
  {
   "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>State</th>\n",
       "      <th>Population_1999</th>\n",
       "      <th>Violent Crime rate_1999</th>\n",
       "      <th>Murder and nonnegligent manslaughter rate_1999</th>\n",
       "      <th>Legacy rape rate /1_1999</th>\n",
       "      <th>Revised rape rate /2_1999</th>\n",
       "      <th>Robbery rate_1999</th>\n",
       "      <th>Aggravated assault rate_1999</th>\n",
       "      <th>Population_2014</th>\n",
       "      <th>Violent Crime rate_2014</th>\n",
       "      <th>Murder and nonnegligent manslaughter rate_2014</th>\n",
       "      <th>Legacy rape rate /1_2014</th>\n",
       "      <th>Revised rape rate /2_2014</th>\n",
       "      <th>Robbery rate_2014</th>\n",
       "      <th>Aggravated assault rate_2014</th>\n",
       "      <th>Violent Crime rate_change</th>\n",
       "      <th>Robbery rate_change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>4369862</td>\n",
       "      <td>490.2</td>\n",
       "      <td>7.9</td>\n",
       "      <td>34.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>121.2</td>\n",
       "      <td>326.5</td>\n",
       "      <td>4849377</td>\n",
       "      <td>427.4</td>\n",
       "      <td>5.7</td>\n",
       "      <td>29.6</td>\n",
       "      <td>41.3</td>\n",
       "      <td>96.9</td>\n",
       "      <td>283.4</td>\n",
       "      <td>-62.8</td>\n",
       "      <td>-24.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>619500</td>\n",
       "      <td>630.8</td>\n",
       "      <td>8.4</td>\n",
       "      <td>83.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>91.4</td>\n",
       "      <td>447.6</td>\n",
       "      <td>736732</td>\n",
       "      <td>635.8</td>\n",
       "      <td>5.6</td>\n",
       "      <td>75.3</td>\n",
       "      <td>104.7</td>\n",
       "      <td>85.4</td>\n",
       "      <td>440.2</td>\n",
       "      <td>5.0</td>\n",
       "      <td>-6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     State  Population_1999  Violent Crime rate_1999  \\\n",
       "0  Alabama          4369862                    490.2   \n",
       "1   Alaska           619500                    630.8   \n",
       "\n",
       "   Murder and nonnegligent manslaughter rate_1999  Legacy rape rate /1_1999  \\\n",
       "0                                             7.9                      34.6   \n",
       "1                                             8.4                      83.5   \n",
       "\n",
       "   Revised rape rate /2_1999  Robbery rate_1999  Aggravated assault rate_1999  \\\n",
       "0                        NaN              121.2                         326.5   \n",
       "1                        NaN               91.4                         447.6   \n",
       "\n",
       "   Population_2014  Violent Crime rate_2014  \\\n",
       "0          4849377                    427.4   \n",
       "1           736732                    635.8   \n",
       "\n",
       "   Murder and nonnegligent manslaughter rate_2014  Legacy rape rate /1_2014  \\\n",
       "0                                             5.7                      29.6   \n",
       "1                                             5.6                      75.3   \n",
       "\n",
       "   Revised rape rate /2_2014  Robbery rate_2014  Aggravated assault rate_2014  \\\n",
       "0                       41.3               96.9                         283.4   \n",
       "1                      104.7               85.4                         440.2   \n",
       "\n",
       "   Violent Crime rate_change  Robbery rate_change  \n",
       "0                      -62.8                -24.3  \n",
       "1                        5.0                 -6.0  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "violentcrime_df.head()[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>Unnamed: 0</th>\n",
       "      <th>State</th>\n",
       "      <th>Population_1999</th>\n",
       "      <th>Property crime rate_1999</th>\n",
       "      <th>Burglary rate_1999</th>\n",
       "      <th>Larceny-theft rate_1999</th>\n",
       "      <th>Motor vehicle theft rate_1999</th>\n",
       "      <th>Unnamed: 6_1999</th>\n",
       "      <th>Population_2014</th>\n",
       "      <th>Property crime rate_2014</th>\n",
       "      <th>Burglary rate_2014</th>\n",
       "      <th>Larceny-theft rate_2014</th>\n",
       "      <th>Motor vehicle theft rate_2014</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>4369862</td>\n",
       "      <td>3922.3</td>\n",
       "      <td>884.4</td>\n",
       "      <td>2737.3</td>\n",
       "      <td>300.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4849377</td>\n",
       "      <td>3177.6</td>\n",
       "      <td>819.0</td>\n",
       "      <td>2149.5</td>\n",
       "      <td>209.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>619500</td>\n",
       "      <td>3728.7</td>\n",
       "      <td>611.3</td>\n",
       "      <td>2688.3</td>\n",
       "      <td>429.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>736732</td>\n",
       "      <td>2760.0</td>\n",
       "      <td>427.6</td>\n",
       "      <td>2096.4</td>\n",
       "      <td>236.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0    State  Population_1999  Property crime rate_1999  \\\n",
       "0           0  Alabama          4369862                    3922.3   \n",
       "1           1   Alaska           619500                    3728.7   \n",
       "\n",
       "   Burglary rate_1999  Larceny-theft rate_1999  Motor vehicle theft rate_1999  \\\n",
       "0               884.4                   2737.3                          300.6   \n",
       "1               611.3                   2688.3                          429.1   \n",
       "\n",
       "   Unnamed: 6_1999  Population_2014  Property crime rate_2014  \\\n",
       "0              NaN          4849377                    3177.6   \n",
       "1              NaN           736732                    2760.0   \n",
       "\n",
       "   Burglary rate_2014  Larceny-theft rate_2014  Motor vehicle theft rate_2014  \n",
       "0               819.0                   2149.5                          209.1  \n",
       "1               427.6                   2096.4                          236.0  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "propertycrime_df.head()[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0', 'State', 'Population_1999', 'Property crime rate_1999',\n",
       "       'Burglary rate_1999', 'Larceny-theft rate_1999',\n",
       "       'Motor vehicle theft rate_1999', 'Unnamed: 6_1999', 'Population_2014',\n",
       "       'Property crime rate_2014', 'Burglary rate_2014',\n",
       "       'Larceny-theft rate_2014', 'Motor vehicle theft rate_2014'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "propertycrime_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "propertycrime_df.drop(['Unnamed: 0','Unnamed: 6_1999'], axis =1, inplace =True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>Population_1999</th>\n",
       "      <th>Property crime rate_1999</th>\n",
       "      <th>Burglary rate_1999</th>\n",
       "      <th>Larceny-theft rate_1999</th>\n",
       "      <th>Motor vehicle theft rate_1999</th>\n",
       "      <th>Population_2014</th>\n",
       "      <th>Property crime rate_2014</th>\n",
       "      <th>Burglary rate_2014</th>\n",
       "      <th>Larceny-theft rate_2014</th>\n",
       "      <th>Motor vehicle theft rate_2014</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>4369862</td>\n",
       "      <td>3922.3</td>\n",
       "      <td>884.4</td>\n",
       "      <td>2737.3</td>\n",
       "      <td>300.6</td>\n",
       "      <td>4849377</td>\n",
       "      <td>3177.6</td>\n",
       "      <td>819.0</td>\n",
       "      <td>2149.5</td>\n",
       "      <td>209.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>619500</td>\n",
       "      <td>3728.7</td>\n",
       "      <td>611.3</td>\n",
       "      <td>2688.3</td>\n",
       "      <td>429.1</td>\n",
       "      <td>736732</td>\n",
       "      <td>2760.0</td>\n",
       "      <td>427.6</td>\n",
       "      <td>2096.4</td>\n",
       "      <td>236.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     State  Population_1999  Property crime rate_1999  Burglary rate_1999  \\\n",
       "0  Alabama          4369862                    3922.3               884.4   \n",
       "1   Alaska           619500                    3728.7               611.3   \n",
       "\n",
       "   Larceny-theft rate_1999  Motor vehicle theft rate_1999  Population_2014  \\\n",
       "0                   2737.3                          300.6          4849377   \n",
       "1                   2688.3                          429.1           736732   \n",
       "\n",
       "   Property crime rate_2014  Burglary rate_2014  Larceny-theft rate_2014  \\\n",
       "0                    3177.6               819.0                   2149.5   \n",
       "1                    2760.0               427.6                   2096.4   \n",
       "\n",
       "   Motor vehicle theft rate_2014  \n",
       "0                          209.1  \n",
       "1                          236.0  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "propertycrime_df.head()[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>Unnamed: 0</th>\n",
       "      <th>State</th>\n",
       "      <th>Population_1999_x</th>\n",
       "      <th>Violent Crime rate_1999</th>\n",
       "      <th>Murder and nonnegligent manslaughter rate_1999</th>\n",
       "      <th>Legacy rape rate /1_1999</th>\n",
       "      <th>Revised rape rate /2_1999</th>\n",
       "      <th>Robbery rate_1999</th>\n",
       "      <th>Aggravated assault rate_1999</th>\n",
       "      <th>Unnamed: 8_1999</th>\n",
       "      <th>...</th>\n",
       "      <th>Burglary rate_1999</th>\n",
       "      <th>Larceny-theft rate_1999</th>\n",
       "      <th>Motor vehicle theft rate_1999</th>\n",
       "      <th>Unnamed: 6_1999</th>\n",
       "      <th>Population_2014_y</th>\n",
       "      <th>Property crime rate_2014</th>\n",
       "      <th>Burglary rate_2014</th>\n",
       "      <th>Larceny-theft rate_2014</th>\n",
       "      <th>Motor vehicle theft rate_2014</th>\n",
       "      <th>Property crime rate_change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>4369862</td>\n",
       "      <td>490.2</td>\n",
       "      <td>7.9</td>\n",
       "      <td>34.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>121.2</td>\n",
       "      <td>326.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>884.4</td>\n",
       "      <td>2737.3</td>\n",
       "      <td>300.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4849377</td>\n",
       "      <td>3177.6</td>\n",
       "      <td>819.0</td>\n",
       "      <td>2149.5</td>\n",
       "      <td>209.1</td>\n",
       "      <td>-744.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>619500</td>\n",
       "      <td>630.8</td>\n",
       "      <td>8.4</td>\n",
       "      <td>83.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>91.4</td>\n",
       "      <td>447.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>611.3</td>\n",
       "      <td>2688.3</td>\n",
       "      <td>429.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>736732</td>\n",
       "      <td>2760.0</td>\n",
       "      <td>427.6</td>\n",
       "      <td>2096.4</td>\n",
       "      <td>236.0</td>\n",
       "      <td>-968.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0    State  Population_1999_x  Violent Crime rate_1999  \\\n",
       "0           0  Alabama            4369862                    490.2   \n",
       "1           1   Alaska             619500                    630.8   \n",
       "\n",
       "   Murder and nonnegligent manslaughter rate_1999  Legacy rape rate /1_1999  \\\n",
       "0                                             7.9                      34.6   \n",
       "1                                             8.4                      83.5   \n",
       "\n",
       "   Revised rape rate /2_1999  Robbery rate_1999  Aggravated assault rate_1999  \\\n",
       "0                        NaN              121.2                         326.5   \n",
       "1                        NaN               91.4                         447.6   \n",
       "\n",
       "   Unnamed: 8_1999             ...              Burglary rate_1999  \\\n",
       "0              NaN             ...                           884.4   \n",
       "1              NaN             ...                           611.3   \n",
       "\n",
       "   Larceny-theft rate_1999  Motor vehicle theft rate_1999  Unnamed: 6_1999  \\\n",
       "0                   2737.3                          300.6              NaN   \n",
       "1                   2688.3                          429.1              NaN   \n",
       "\n",
       "   Population_2014_y  Property crime rate_2014  Burglary rate_2014  \\\n",
       "0            4849377                    3177.6               819.0   \n",
       "1             736732                    2760.0               427.6   \n",
       "\n",
       "   Larceny-theft rate_2014  Motor vehicle theft rate_2014  \\\n",
       "0                   2149.5                          209.1   \n",
       "1                   2096.4                          236.0   \n",
       "\n",
       "   Property crime rate_change  \n",
       "0                      -744.7  \n",
       "1                      -968.7  \n",
       "\n",
       "[2 rows x 32 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tcrime_df.head()[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "tcrime_df.drop(['Unnamed: 0'], axis =1, inplace =True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>Population_1999_x</th>\n",
       "      <th>Violent Crime rate_1999</th>\n",
       "      <th>Murder and nonnegligent manslaughter rate_1999</th>\n",
       "      <th>Legacy rape rate /1_1999</th>\n",
       "      <th>Revised rape rate /2_1999</th>\n",
       "      <th>Robbery rate_1999</th>\n",
       "      <th>Aggravated assault rate_1999</th>\n",
       "      <th>Unnamed: 8_1999</th>\n",
       "      <th>Population_2014_x</th>\n",
       "      <th>...</th>\n",
       "      <th>Burglary rate_1999</th>\n",
       "      <th>Larceny-theft rate_1999</th>\n",
       "      <th>Motor vehicle theft rate_1999</th>\n",
       "      <th>Unnamed: 6_1999</th>\n",
       "      <th>Population_2014_y</th>\n",
       "      <th>Property crime rate_2014</th>\n",
       "      <th>Burglary rate_2014</th>\n",
       "      <th>Larceny-theft rate_2014</th>\n",
       "      <th>Motor vehicle theft rate_2014</th>\n",
       "      <th>Property crime rate_change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>4369862</td>\n",
       "      <td>490.2</td>\n",
       "      <td>7.9</td>\n",
       "      <td>34.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>121.2</td>\n",
       "      <td>326.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4849377</td>\n",
       "      <td>...</td>\n",
       "      <td>884.4</td>\n",
       "      <td>2737.3</td>\n",
       "      <td>300.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4849377</td>\n",
       "      <td>3177.6</td>\n",
       "      <td>819.0</td>\n",
       "      <td>2149.5</td>\n",
       "      <td>209.1</td>\n",
       "      <td>-744.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>619500</td>\n",
       "      <td>630.8</td>\n",
       "      <td>8.4</td>\n",
       "      <td>83.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>91.4</td>\n",
       "      <td>447.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>736732</td>\n",
       "      <td>...</td>\n",
       "      <td>611.3</td>\n",
       "      <td>2688.3</td>\n",
       "      <td>429.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>736732</td>\n",
       "      <td>2760.0</td>\n",
       "      <td>427.6</td>\n",
       "      <td>2096.4</td>\n",
       "      <td>236.0</td>\n",
       "      <td>-968.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     State  Population_1999_x  Violent Crime rate_1999  \\\n",
       "0  Alabama            4369862                    490.2   \n",
       "1   Alaska             619500                    630.8   \n",
       "\n",
       "   Murder and nonnegligent manslaughter rate_1999  Legacy rape rate /1_1999  \\\n",
       "0                                             7.9                      34.6   \n",
       "1                                             8.4                      83.5   \n",
       "\n",
       "   Revised rape rate /2_1999  Robbery rate_1999  Aggravated assault rate_1999  \\\n",
       "0                        NaN              121.2                         326.5   \n",
       "1                        NaN               91.4                         447.6   \n",
       "\n",
       "   Unnamed: 8_1999  Population_2014_x             ...              \\\n",
       "0              NaN            4849377             ...               \n",
       "1              NaN             736732             ...               \n",
       "\n",
       "   Burglary rate_1999  Larceny-theft rate_1999  Motor vehicle theft rate_1999  \\\n",
       "0               884.4                   2737.3                          300.6   \n",
       "1               611.3                   2688.3                          429.1   \n",
       "\n",
       "   Unnamed: 6_1999  Population_2014_y  Property crime rate_2014  \\\n",
       "0              NaN            4849377                    3177.6   \n",
       "1              NaN             736732                    2760.0   \n",
       "\n",
       "   Burglary rate_2014  Larceny-theft rate_2014  Motor vehicle theft rate_2014  \\\n",
       "0               819.0                   2149.5                          209.1   \n",
       "1               427.6                   2096.4                          236.0   \n",
       "\n",
       "   Property crime rate_change  \n",
       "0                      -744.7  \n",
       "1                      -968.7  \n",
       "\n",
       "[2 rows x 31 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tcrime_df.head()[:2]"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda env:dsi]",
   "language": "python",
   "name": "conda-env-dsi-py"
  },
  "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}