Clean_Chronic_Disease_indicators.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dependencies\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extract data from csv files\n",
    "The data was derived from the data.gov's compilation of [chronic disease indicators in the USA](https://catalog.data.gov/dataset/u-s-chronic-disease-indicators-cdi)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda3/envs/PythonData/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2785: DtypeWarning: Columns (7,10,18,19,20,21,23,30,31,32,33) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "# Load CSV file as dataframe\n",
    "    # The csv file exceeds GitHub's file size requirement. The link to the csv files are \n",
    "    # provided in the technical report\n",
    "diseasedata = pd.read_csv(\"Resources/U.S._Chronic_Disease_Indicators__CDI_.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>YearStart</th>\n",
       "      <th>YearEnd</th>\n",
       "      <th>LocationAbbr</th>\n",
       "      <th>LocationDesc</th>\n",
       "      <th>DataSource</th>\n",
       "      <th>Topic</th>\n",
       "      <th>Question</th>\n",
       "      <th>Response</th>\n",
       "      <th>DataValueUnit</th>\n",
       "      <th>DataValueType</th>\n",
       "      <th>...</th>\n",
       "      <th>LocationID</th>\n",
       "      <th>TopicID</th>\n",
       "      <th>QuestionID</th>\n",
       "      <th>DataValueTypeID</th>\n",
       "      <th>StratificationCategoryID1</th>\n",
       "      <th>StratificationID1</th>\n",
       "      <th>StratificationCategoryID2</th>\n",
       "      <th>StratificationID2</th>\n",
       "      <th>StratificationCategoryID3</th>\n",
       "      <th>StratificationID3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016</td>\n",
       "      <td>2016</td>\n",
       "      <td>US</td>\n",
       "      <td>United States</td>\n",
       "      <td>BRFSS</td>\n",
       "      <td>Alcohol</td>\n",
       "      <td>Binge drinking prevalence among adults aged &gt;=...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>...</td>\n",
       "      <td>59</td>\n",
       "      <td>ALC</td>\n",
       "      <td>ALC2_2</td>\n",
       "      <td>CRDPREV</td>\n",
       "      <td>OVERALL</td>\n",
       "      <td>OVR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016</td>\n",
       "      <td>2016</td>\n",
       "      <td>AL</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>BRFSS</td>\n",
       "      <td>Alcohol</td>\n",
       "      <td>Binge drinking prevalence among adults aged &gt;=...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>ALC</td>\n",
       "      <td>ALC2_2</td>\n",
       "      <td>CRDPREV</td>\n",
       "      <td>OVERALL</td>\n",
       "      <td>OVR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016</td>\n",
       "      <td>2016</td>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>BRFSS</td>\n",
       "      <td>Alcohol</td>\n",
       "      <td>Binge drinking prevalence among adults aged &gt;=...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>ALC</td>\n",
       "      <td>ALC2_2</td>\n",
       "      <td>CRDPREV</td>\n",
       "      <td>OVERALL</td>\n",
       "      <td>OVR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016</td>\n",
       "      <td>2016</td>\n",
       "      <td>AZ</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>BRFSS</td>\n",
       "      <td>Alcohol</td>\n",
       "      <td>Binge drinking prevalence among adults aged &gt;=...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>ALC</td>\n",
       "      <td>ALC2_2</td>\n",
       "      <td>CRDPREV</td>\n",
       "      <td>OVERALL</td>\n",
       "      <td>OVR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016</td>\n",
       "      <td>2016</td>\n",
       "      <td>AR</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>BRFSS</td>\n",
       "      <td>Alcohol</td>\n",
       "      <td>Binge drinking prevalence among adults aged &gt;=...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>ALC</td>\n",
       "      <td>ALC2_2</td>\n",
       "      <td>CRDPREV</td>\n",
       "      <td>OVERALL</td>\n",
       "      <td>OVR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   YearStart  YearEnd LocationAbbr   LocationDesc DataSource    Topic  \\\n",
       "0       2016     2016           US  United States      BRFSS  Alcohol   \n",
       "1       2016     2016           AL        Alabama      BRFSS  Alcohol   \n",
       "2       2016     2016           AK         Alaska      BRFSS  Alcohol   \n",
       "3       2016     2016           AZ        Arizona      BRFSS  Alcohol   \n",
       "4       2016     2016           AR       Arkansas      BRFSS  Alcohol   \n",
       "\n",
       "                                            Question Response DataValueUnit  \\\n",
       "0  Binge drinking prevalence among adults aged >=...      NaN             %   \n",
       "1  Binge drinking prevalence among adults aged >=...      NaN             %   \n",
       "2  Binge drinking prevalence among adults aged >=...      NaN             %   \n",
       "3  Binge drinking prevalence among adults aged >=...      NaN             %   \n",
       "4  Binge drinking prevalence among adults aged >=...      NaN             %   \n",
       "\n",
       "      DataValueType        ...        LocationID  TopicID QuestionID  \\\n",
       "0  Crude Prevalence        ...                59      ALC     ALC2_2   \n",
       "1  Crude Prevalence        ...                 1      ALC     ALC2_2   \n",
       "2  Crude Prevalence        ...                 2      ALC     ALC2_2   \n",
       "3  Crude Prevalence        ...                 4      ALC     ALC2_2   \n",
       "4  Crude Prevalence        ...                 5      ALC     ALC2_2   \n",
       "\n",
       "  DataValueTypeID  StratificationCategoryID1  StratificationID1  \\\n",
       "0         CRDPREV                    OVERALL                OVR   \n",
       "1         CRDPREV                    OVERALL                OVR   \n",
       "2         CRDPREV                    OVERALL                OVR   \n",
       "3         CRDPREV                    OVERALL                OVR   \n",
       "4         CRDPREV                    OVERALL                OVR   \n",
       "\n",
       "  StratificationCategoryID2 StratificationID2 StratificationCategoryID3  \\\n",
       "0                       NaN               NaN                       NaN   \n",
       "1                       NaN               NaN                       NaN   \n",
       "2                       NaN               NaN                       NaN   \n",
       "3                       NaN               NaN                       NaN   \n",
       "4                       NaN               NaN                       NaN   \n",
       "\n",
       "  StratificationID3  \n",
       "0               NaN  \n",
       "1               NaN  \n",
       "2               NaN  \n",
       "3               NaN  \n",
       "4               NaN  \n",
       "\n",
       "[5 rows x 34 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Preview the chronic diseases data\n",
    "diseasedata.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transform data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Choose the relevant columns\n",
    "chronic_df = diseasedata[[\"YearEnd\", \"LocationDesc\", \"Topic\", \"Question\", \n",
    "                          \"DataValueUnit\", \"DataValueType\", \"DataValue\", \n",
    "                          \"Stratification1\"]]"
   ]
  },
  {
   "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>YearEnd</th>\n",
       "      <th>LocationDesc</th>\n",
       "      <th>Topic</th>\n",
       "      <th>Question</th>\n",
       "      <th>DataValueUnit</th>\n",
       "      <th>DataValueType</th>\n",
       "      <th>DataValue</th>\n",
       "      <th>Stratification1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016</td>\n",
       "      <td>United States</td>\n",
       "      <td>Alcohol</td>\n",
       "      <td>Binge drinking prevalence among adults aged &gt;=...</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>16.9</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Alcohol</td>\n",
       "      <td>Binge drinking prevalence among adults aged &gt;=...</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>13</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>Alcohol</td>\n",
       "      <td>Binge drinking prevalence among adults aged &gt;=...</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>18.2</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>Alcohol</td>\n",
       "      <td>Binge drinking prevalence among adults aged &gt;=...</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>15.6</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>Alcohol</td>\n",
       "      <td>Binge drinking prevalence among adults aged &gt;=...</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>15</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   YearEnd   LocationDesc    Topic  \\\n",
       "0     2016  United States  Alcohol   \n",
       "1     2016        Alabama  Alcohol   \n",
       "2     2016         Alaska  Alcohol   \n",
       "3     2016        Arizona  Alcohol   \n",
       "4     2016       Arkansas  Alcohol   \n",
       "\n",
       "                                            Question DataValueUnit  \\\n",
       "0  Binge drinking prevalence among adults aged >=...             %   \n",
       "1  Binge drinking prevalence among adults aged >=...             %   \n",
       "2  Binge drinking prevalence among adults aged >=...             %   \n",
       "3  Binge drinking prevalence among adults aged >=...             %   \n",
       "4  Binge drinking prevalence among adults aged >=...             %   \n",
       "\n",
       "      DataValueType DataValue Stratification1  \n",
       "0  Crude Prevalence      16.9         Overall  \n",
       "1  Crude Prevalence        13         Overall  \n",
       "2  Crude Prevalence      18.2         Overall  \n",
       "3  Crude Prevalence      15.6         Overall  \n",
       "4  Crude Prevalence        15         Overall  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Preview the revised dataframe\n",
    "chronic_df.head()"
   ]
  },
  {
   "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>YearEnd</th>\n",
       "      <th>LocationDesc</th>\n",
       "      <th>Topic</th>\n",
       "      <th>Question</th>\n",
       "      <th>DataValueUnit</th>\n",
       "      <th>DataValueType</th>\n",
       "      <th>DataValue</th>\n",
       "      <th>Stratification1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>116756</th>\n",
       "      <td>2014</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Chronic Kidney Disease</td>\n",
       "      <td>Incidence of treated end-stage renal disease</td>\n",
       "      <td>cases per 1,000,000</td>\n",
       "      <td>Number</td>\n",
       "      <td>1749</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117030</th>\n",
       "      <td>2014</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>Chronic Kidney Disease</td>\n",
       "      <td>Incidence of treated end-stage renal disease</td>\n",
       "      <td>cases per 1,000,000</td>\n",
       "      <td>Number</td>\n",
       "      <td>2294</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117178</th>\n",
       "      <td>2014</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Chronic Kidney Disease</td>\n",
       "      <td>Incidence of treated end-stage renal disease</td>\n",
       "      <td>cases per 1,000,000</td>\n",
       "      <td>Number</td>\n",
       "      <td>10548</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117329</th>\n",
       "      <td>2014</td>\n",
       "      <td>United States</td>\n",
       "      <td>Chronic Kidney Disease</td>\n",
       "      <td>Incidence of treated end-stage renal disease</td>\n",
       "      <td>cases per 1,000,000</td>\n",
       "      <td>Number</td>\n",
       "      <td>118014</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117537</th>\n",
       "      <td>2014</td>\n",
       "      <td>Utah</td>\n",
       "      <td>Chronic Kidney Disease</td>\n",
       "      <td>Incidence of treated end-stage renal disease</td>\n",
       "      <td>cases per 1,000,000</td>\n",
       "      <td>Number</td>\n",
       "      <td>525</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        YearEnd   LocationDesc                   Topic  \\\n",
       "116756     2014       Kentucky  Chronic Kidney Disease   \n",
       "117030     2014      Louisiana  Chronic Kidney Disease   \n",
       "117178     2014          Texas  Chronic Kidney Disease   \n",
       "117329     2014  United States  Chronic Kidney Disease   \n",
       "117537     2014           Utah  Chronic Kidney Disease   \n",
       "\n",
       "                                            Question        DataValueUnit  \\\n",
       "116756  Incidence of treated end-stage renal disease  cases per 1,000,000   \n",
       "117030  Incidence of treated end-stage renal disease  cases per 1,000,000   \n",
       "117178  Incidence of treated end-stage renal disease  cases per 1,000,000   \n",
       "117329  Incidence of treated end-stage renal disease  cases per 1,000,000   \n",
       "117537  Incidence of treated end-stage renal disease  cases per 1,000,000   \n",
       "\n",
       "       DataValueType DataValue Stratification1  \n",
       "116756        Number      1749         Overall  \n",
       "117030        Number      2294         Overall  \n",
       "117178        Number     10548         Overall  \n",
       "117329        Number    118014         Overall  \n",
       "117537        Number       525         Overall  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select data for 2010, 2012, and 2014\n",
    "Years = [2010, 2012, 2014]\n",
    "chronic_df2 = chronic_df[chronic_df[\"YearEnd\"].isin(Years)]\n",
    "chronic_df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>YearEnd</th>\n",
       "      <th>LocationDesc</th>\n",
       "      <th>Topic</th>\n",
       "      <th>Question</th>\n",
       "      <th>DataValueUnit</th>\n",
       "      <th>DataValueType</th>\n",
       "      <th>DataValue</th>\n",
       "      <th>Stratification1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>122531</th>\n",
       "      <td>2014</td>\n",
       "      <td>United States</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Diabetes prevalence among women aged 18-44 years</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>5.7</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160122</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Prevalence of diagnosed diabetes among adults ...</td>\n",
       "      <td>%</td>\n",
       "      <td>Age-adjusted Prevalence</td>\n",
       "      <td>10.5</td>\n",
       "      <td>Hispanic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160139</th>\n",
       "      <td>2012</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Diabetes prevalence among women aged 18-44 years</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>6.7</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160206</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Prevalence of diagnosed diabetes among adults ...</td>\n",
       "      <td>%</td>\n",
       "      <td>Age-adjusted Prevalence</td>\n",
       "      <td>11.9</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160309</th>\n",
       "      <td>2012</td>\n",
       "      <td>Delaware</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Adults with diagnosed diabetes aged &gt;= 18 year...</td>\n",
       "      <td>%</td>\n",
       "      <td>Age-adjusted Prevalence</td>\n",
       "      <td>63.9</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        YearEnd   LocationDesc     Topic  \\\n",
       "122531     2014  United States  Diabetes   \n",
       "160122     2012        Alabama  Diabetes   \n",
       "160139     2012           Iowa  Diabetes   \n",
       "160206     2012        Alabama  Diabetes   \n",
       "160309     2012       Delaware  Diabetes   \n",
       "\n",
       "                                                 Question DataValueUnit  \\\n",
       "122531   Diabetes prevalence among women aged 18-44 years             %   \n",
       "160122  Prevalence of diagnosed diabetes among adults ...             %   \n",
       "160139   Diabetes prevalence among women aged 18-44 years             %   \n",
       "160206  Prevalence of diagnosed diabetes among adults ...             %   \n",
       "160309  Adults with diagnosed diabetes aged >= 18 year...             %   \n",
       "\n",
       "                  DataValueType DataValue Stratification1  \n",
       "122531         Crude Prevalence       5.7         Overall  \n",
       "160122  Age-adjusted Prevalence      10.5        Hispanic  \n",
       "160139         Crude Prevalence       6.7         Overall  \n",
       "160206  Age-adjusted Prevalence      11.9            Male  \n",
       "160309  Age-adjusted Prevalence      63.9          Female  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Choose data for diabetes\n",
    "chronic_df3 = chronic_df2[chronic_df2[\"Topic\"] == \"Diabetes\"]\n",
    "chronic_df3.head()"
   ]
  },
  {
   "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>YearEnd</th>\n",
       "      <th>LocationDesc</th>\n",
       "      <th>Topic</th>\n",
       "      <th>Question</th>\n",
       "      <th>DataValueUnit</th>\n",
       "      <th>DataValueType</th>\n",
       "      <th>DataValue</th>\n",
       "      <th>Stratification1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>160122</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Prevalence of diagnosed diabetes among adults ...</td>\n",
       "      <td>%</td>\n",
       "      <td>Age-adjusted Prevalence</td>\n",
       "      <td>10.5</td>\n",
       "      <td>Hispanic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160139</th>\n",
       "      <td>2012</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Diabetes prevalence among women aged 18-44 years</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>6.7</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160206</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Prevalence of diagnosed diabetes among adults ...</td>\n",
       "      <td>%</td>\n",
       "      <td>Age-adjusted Prevalence</td>\n",
       "      <td>11.9</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160309</th>\n",
       "      <td>2012</td>\n",
       "      <td>Delaware</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Adults with diagnosed diabetes aged &gt;= 18 year...</td>\n",
       "      <td>%</td>\n",
       "      <td>Age-adjusted Prevalence</td>\n",
       "      <td>63.9</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160599</th>\n",
       "      <td>2012</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Prevalence of diagnosed diabetes among adults ...</td>\n",
       "      <td>%</td>\n",
       "      <td>Age-adjusted Prevalence</td>\n",
       "      <td>12.5</td>\n",
       "      <td>Hispanic</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        YearEnd  LocationDesc     Topic  \\\n",
       "160122     2012       Alabama  Diabetes   \n",
       "160139     2012          Iowa  Diabetes   \n",
       "160206     2012       Alabama  Diabetes   \n",
       "160309     2012      Delaware  Diabetes   \n",
       "160599     2012  Pennsylvania  Diabetes   \n",
       "\n",
       "                                                 Question DataValueUnit  \\\n",
       "160122  Prevalence of diagnosed diabetes among adults ...             %   \n",
       "160139   Diabetes prevalence among women aged 18-44 years             %   \n",
       "160206  Prevalence of diagnosed diabetes among adults ...             %   \n",
       "160309  Adults with diagnosed diabetes aged >= 18 year...             %   \n",
       "160599  Prevalence of diagnosed diabetes among adults ...             %   \n",
       "\n",
       "                  DataValueType DataValue Stratification1  \n",
       "160122  Age-adjusted Prevalence      10.5        Hispanic  \n",
       "160139         Crude Prevalence       6.7         Overall  \n",
       "160206  Age-adjusted Prevalence      11.9            Male  \n",
       "160309  Age-adjusted Prevalence      63.9          Female  \n",
       "160599  Age-adjusted Prevalence      12.5        Hispanic  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remove data for the US (we need state-wise data)\n",
    "chronic_df4 = chronic_df3[chronic_df3[\"LocationDesc\"]!=\"United States\"]\n",
    "chronic_df4.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>YearEnd</th>\n",
       "      <th>LocationDesc</th>\n",
       "      <th>Topic</th>\n",
       "      <th>Question</th>\n",
       "      <th>DataValueUnit</th>\n",
       "      <th>DataValueType</th>\n",
       "      <th>DataValue</th>\n",
       "      <th>Stratification1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>160139</th>\n",
       "      <td>2012</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Diabetes prevalence among women aged 18-44 years</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>6.7</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161003</th>\n",
       "      <td>2012</td>\n",
       "      <td>Guam</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Prevalence of diagnosed diabetes among adults ...</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>9.6</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161379</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Adults with diagnosed diabetes aged &gt;= 18 year...</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>71.1</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161388</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Adults with diagnosed diabetes aged &gt;= 18 year...</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>53.7</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161396</th>\n",
       "      <td>2012</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Adults with diagnosed diabetes aged &gt;= 18 year...</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>51.1</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        YearEnd LocationDesc     Topic  \\\n",
       "160139     2012         Iowa  Diabetes   \n",
       "161003     2012         Guam  Diabetes   \n",
       "161379     2012       Alaska  Diabetes   \n",
       "161388     2012      Alabama  Diabetes   \n",
       "161396     2012     Arkansas  Diabetes   \n",
       "\n",
       "                                                 Question DataValueUnit  \\\n",
       "160139   Diabetes prevalence among women aged 18-44 years             %   \n",
       "161003  Prevalence of diagnosed diabetes among adults ...             %   \n",
       "161379  Adults with diagnosed diabetes aged >= 18 year...             %   \n",
       "161388  Adults with diagnosed diabetes aged >= 18 year...             %   \n",
       "161396  Adults with diagnosed diabetes aged >= 18 year...             %   \n",
       "\n",
       "           DataValueType DataValue Stratification1  \n",
       "160139  Crude Prevalence       6.7         Overall  \n",
       "161003  Crude Prevalence       9.6         Overall  \n",
       "161379  Crude Prevalence      71.1         Overall  \n",
       "161388  Crude Prevalence      53.7         Overall  \n",
       "161396  Crude Prevalence      51.1         Overall  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Choose the data based on the following criteria:\n",
    "# Crude Prevalence as the DataValueType\n",
    "# Overall as the Stratification\n",
    "chronic_df5 = chronic_df4.query(\"DataValueType == 'Crude Prevalence' & \\\n",
    "                                 Stratification1 == 'Overall'\")\n",
    "chronic_df5.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>YearEnd</th>\n",
       "      <th>LocationDesc</th>\n",
       "      <th>Topic</th>\n",
       "      <th>Question</th>\n",
       "      <th>DataValueUnit</th>\n",
       "      <th>DataValueType</th>\n",
       "      <th>DataValue</th>\n",
       "      <th>Stratification1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>161003</th>\n",
       "      <td>2012</td>\n",
       "      <td>Guam</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Prevalence of diagnosed diabetes among adults ...</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>9.6</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166619</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Prevalence of diagnosed diabetes among adults ...</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>7</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166627</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Prevalence of diagnosed diabetes among adults ...</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>12.2</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166635</th>\n",
       "      <td>2012</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Prevalence of diagnosed diabetes among adults ...</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>11.3</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166643</th>\n",
       "      <td>2012</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Prevalence of diagnosed diabetes among adults ...</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>10.6</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        YearEnd LocationDesc     Topic  \\\n",
       "161003     2012         Guam  Diabetes   \n",
       "166619     2012       Alaska  Diabetes   \n",
       "166627     2012      Alabama  Diabetes   \n",
       "166635     2012     Arkansas  Diabetes   \n",
       "166643     2012      Arizona  Diabetes   \n",
       "\n",
       "                                                 Question DataValueUnit  \\\n",
       "161003  Prevalence of diagnosed diabetes among adults ...             %   \n",
       "166619  Prevalence of diagnosed diabetes among adults ...             %   \n",
       "166627  Prevalence of diagnosed diabetes among adults ...             %   \n",
       "166635  Prevalence of diagnosed diabetes among adults ...             %   \n",
       "166643  Prevalence of diagnosed diabetes among adults ...             %   \n",
       "\n",
       "           DataValueType DataValue Stratification1  \n",
       "161003  Crude Prevalence       9.6         Overall  \n",
       "166619  Crude Prevalence         7         Overall  \n",
       "166627  Crude Prevalence      12.2         Overall  \n",
       "166635  Crude Prevalence      11.3         Overall  \n",
       "166643  Crude Prevalence      10.6         Overall  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Choose data with Question as \"Prevalence of diagnosed diabetes among adults aged >= 18 years\"\n",
    "chronic_df6 = chronic_df5[chronic_df5[\"Question\"] == \"Prevalence of diagnosed diabetes \\\n",
    "among adults aged >= 18 years\"]\n",
    "\n",
    "chronic_df6.head()"
   ]
  },
  {
   "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>Year</th>\n",
       "      <th>US_State</th>\n",
       "      <th>Topic</th>\n",
       "      <th>Question</th>\n",
       "      <th>DataValueUnit</th>\n",
       "      <th>DataValueType</th>\n",
       "      <th>Adult_Diabetics_Percent</th>\n",
       "      <th>Stratification1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>161003</th>\n",
       "      <td>2012</td>\n",
       "      <td>Guam</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Prevalence of diagnosed diabetes among adults ...</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>9.6</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166619</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Prevalence of diagnosed diabetes among adults ...</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>7</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166627</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Prevalence of diagnosed diabetes among adults ...</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>12.2</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166635</th>\n",
       "      <td>2012</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Prevalence of diagnosed diabetes among adults ...</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>11.3</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166643</th>\n",
       "      <td>2012</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>Diabetes</td>\n",
       "      <td>Prevalence of diagnosed diabetes among adults ...</td>\n",
       "      <td>%</td>\n",
       "      <td>Crude Prevalence</td>\n",
       "      <td>10.6</td>\n",
       "      <td>Overall</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Year  US_State     Topic  \\\n",
       "161003  2012      Guam  Diabetes   \n",
       "166619  2012    Alaska  Diabetes   \n",
       "166627  2012   Alabama  Diabetes   \n",
       "166635  2012  Arkansas  Diabetes   \n",
       "166643  2012   Arizona  Diabetes   \n",
       "\n",
       "                                                 Question DataValueUnit  \\\n",
       "161003  Prevalence of diagnosed diabetes among adults ...             %   \n",
       "166619  Prevalence of diagnosed diabetes among adults ...             %   \n",
       "166627  Prevalence of diagnosed diabetes among adults ...             %   \n",
       "166635  Prevalence of diagnosed diabetes among adults ...             %   \n",
       "166643  Prevalence of diagnosed diabetes among adults ...             %   \n",
       "\n",
       "           DataValueType Adult_Diabetics_Percent Stratification1  \n",
       "161003  Crude Prevalence                     9.6         Overall  \n",
       "166619  Crude Prevalence                       7         Overall  \n",
       "166627  Crude Prevalence                    12.2         Overall  \n",
       "166635  Crude Prevalence                    11.3         Overall  \n",
       "166643  Crude Prevalence                    10.6         Overall  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rename columns for clarity\n",
    "chronic_df6 = chronic_df6.rename(columns = {\"YearEnd\": \"Year\",\n",
    "                                            \"LocationDesc\": \"US_State\",\n",
    "                                            \"DataValue\": \"Adult_Diabetics_Percent\"})\n",
    "chronic_df6.head()"
   ]
  },
  {
   "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>Year</th>\n",
       "      <th>US_State</th>\n",
       "      <th>Adult_Diabetics_Percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>161003</th>\n",
       "      <td>2012</td>\n",
       "      <td>Guam</td>\n",
       "      <td>9.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166619</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166627</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>12.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166635</th>\n",
       "      <td>2012</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>11.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166643</th>\n",
       "      <td>2012</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>10.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166651</th>\n",
       "      <td>2012</td>\n",
       "      <td>California</td>\n",
       "      <td>9.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166659</th>\n",
       "      <td>2012</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>7.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166667</th>\n",
       "      <td>2012</td>\n",
       "      <td>Connecticut</td>\n",
       "      <td>9.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166675</th>\n",
       "      <td>2012</td>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>8.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166683</th>\n",
       "      <td>2012</td>\n",
       "      <td>Delaware</td>\n",
       "      <td>9.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166691</th>\n",
       "      <td>2012</td>\n",
       "      <td>Florida</td>\n",
       "      <td>11.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166699</th>\n",
       "      <td>2012</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>9.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166714</th>\n",
       "      <td>2012</td>\n",
       "      <td>Hawaii</td>\n",
       "      <td>7.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166722</th>\n",
       "      <td>2012</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>9.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166731</th>\n",
       "      <td>2012</td>\n",
       "      <td>Idaho</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166739</th>\n",
       "      <td>2012</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>9.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166747</th>\n",
       "      <td>2012</td>\n",
       "      <td>Indiana</td>\n",
       "      <td>10.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166755</th>\n",
       "      <td>2012</td>\n",
       "      <td>Kansas</td>\n",
       "      <td>9.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166763</th>\n",
       "      <td>2012</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>10.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166771</th>\n",
       "      <td>2012</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>12.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166779</th>\n",
       "      <td>2012</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>8.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166787</th>\n",
       "      <td>2012</td>\n",
       "      <td>Maryland</td>\n",
       "      <td>10.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166795</th>\n",
       "      <td>2012</td>\n",
       "      <td>Maine</td>\n",
       "      <td>9.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166803</th>\n",
       "      <td>2012</td>\n",
       "      <td>Michigan</td>\n",
       "      <td>10.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166811</th>\n",
       "      <td>2012</td>\n",
       "      <td>Minnesota</td>\n",
       "      <td>7.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166819</th>\n",
       "      <td>2012</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>10.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166827</th>\n",
       "      <td>2012</td>\n",
       "      <td>Mississippi</td>\n",
       "      <td>12.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166835</th>\n",
       "      <td>2012</td>\n",
       "      <td>Montana</td>\n",
       "      <td>7.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166843</th>\n",
       "      <td>2012</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>10.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166851</th>\n",
       "      <td>2012</td>\n",
       "      <td>North Dakota</td>\n",
       "      <td>8.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301017</th>\n",
       "      <td>2014</td>\n",
       "      <td>Minnesota</td>\n",
       "      <td>8.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301025</th>\n",
       "      <td>2014</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>11.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301033</th>\n",
       "      <td>2014</td>\n",
       "      <td>Mississippi</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301041</th>\n",
       "      <td>2014</td>\n",
       "      <td>Montana</td>\n",
       "      <td>8.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301049</th>\n",
       "      <td>2014</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>10.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301057</th>\n",
       "      <td>2014</td>\n",
       "      <td>North Dakota</td>\n",
       "      <td>8.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301065</th>\n",
       "      <td>2014</td>\n",
       "      <td>Nebraska</td>\n",
       "      <td>9.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301073</th>\n",
       "      <td>2014</td>\n",
       "      <td>New Hampshire</td>\n",
       "      <td>9.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301081</th>\n",
       "      <td>2014</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>9.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301089</th>\n",
       "      <td>2014</td>\n",
       "      <td>New Mexico</td>\n",
       "      <td>11.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301097</th>\n",
       "      <td>2014</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>9.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301105</th>\n",
       "      <td>2014</td>\n",
       "      <td>New York</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301113</th>\n",
       "      <td>2014</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>11.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301121</th>\n",
       "      <td>2014</td>\n",
       "      <td>Oklahoma</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301129</th>\n",
       "      <td>2014</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301137</th>\n",
       "      <td>2014</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>11.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301145</th>\n",
       "      <td>2014</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>15.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301153</th>\n",
       "      <td>2014</td>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>9.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301161</th>\n",
       "      <td>2014</td>\n",
       "      <td>South Carolina</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301169</th>\n",
       "      <td>2014</td>\n",
       "      <td>South Dakota</td>\n",
       "      <td>9.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301177</th>\n",
       "      <td>2014</td>\n",
       "      <td>Tennessee</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301185</th>\n",
       "      <td>2014</td>\n",
       "      <td>Texas</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301196</th>\n",
       "      <td>2014</td>\n",
       "      <td>Utah</td>\n",
       "      <td>7.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301204</th>\n",
       "      <td>2014</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>9.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301212</th>\n",
       "      <td>2014</td>\n",
       "      <td>Virgin Islands</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301220</th>\n",
       "      <td>2014</td>\n",
       "      <td>Vermont</td>\n",
       "      <td>7.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301228</th>\n",
       "      <td>2014</td>\n",
       "      <td>Washington</td>\n",
       "      <td>8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301236</th>\n",
       "      <td>2014</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301244</th>\n",
       "      <td>2014</td>\n",
       "      <td>West Virginia</td>\n",
       "      <td>14.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301252</th>\n",
       "      <td>2014</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>8.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>108 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Year              US_State Adult_Diabetics_Percent\n",
       "161003  2012                  Guam                     9.6\n",
       "166619  2012                Alaska                       7\n",
       "166627  2012               Alabama                    12.2\n",
       "166635  2012              Arkansas                    11.3\n",
       "166643  2012               Arizona                    10.6\n",
       "166651  2012            California                     9.8\n",
       "166659  2012              Colorado                     7.4\n",
       "166667  2012           Connecticut                     9.1\n",
       "166675  2012  District of Columbia                     8.2\n",
       "166683  2012              Delaware                     9.6\n",
       "166691  2012               Florida                    11.4\n",
       "166699  2012               Georgia                     9.9\n",
       "166714  2012                Hawaii                     7.8\n",
       "166722  2012                  Iowa                     9.7\n",
       "166731  2012                 Idaho                     8.5\n",
       "166739  2012              Illinois                     9.4\n",
       "166747  2012               Indiana                    10.9\n",
       "166755  2012                Kansas                     9.4\n",
       "166763  2012              Kentucky                    10.7\n",
       "166771  2012             Louisiana                    12.3\n",
       "166779  2012         Massachusetts                     8.3\n",
       "166787  2012              Maryland                    10.2\n",
       "166795  2012                 Maine                     9.7\n",
       "166803  2012              Michigan                    10.5\n",
       "166811  2012             Minnesota                     7.3\n",
       "166819  2012              Missouri                    10.7\n",
       "166827  2012           Mississippi                    12.5\n",
       "166835  2012               Montana                     7.2\n",
       "166843  2012        North Carolina                    10.4\n",
       "166851  2012          North Dakota                     8.6\n",
       "...      ...                   ...                     ...\n",
       "301017  2014             Minnesota                     8.1\n",
       "301025  2014              Missouri                    11.1\n",
       "301033  2014           Mississippi                      13\n",
       "301041  2014               Montana                     8.8\n",
       "301049  2014        North Carolina                    10.8\n",
       "301057  2014          North Dakota                     8.6\n",
       "301065  2014              Nebraska                     9.2\n",
       "301073  2014         New Hampshire                     9.1\n",
       "301081  2014            New Jersey                     9.7\n",
       "301089  2014            New Mexico                    11.5\n",
       "301097  2014                Nevada                     9.6\n",
       "301105  2014              New York                      10\n",
       "301113  2014                  Ohio                    11.7\n",
       "301121  2014              Oklahoma                      12\n",
       "301129  2014                Oregon                       9\n",
       "301137  2014          Pennsylvania                    11.2\n",
       "301145  2014           Puerto Rico                    15.7\n",
       "301153  2014          Rhode Island                     9.4\n",
       "301161  2014        South Carolina                      12\n",
       "301169  2014          South Dakota                     9.1\n",
       "301177  2014             Tennessee                      13\n",
       "301185  2014                 Texas                      11\n",
       "301196  2014                  Utah                     7.1\n",
       "301204  2014              Virginia                     9.7\n",
       "301212  2014        Virgin Islands                     NaN\n",
       "301220  2014               Vermont                     7.9\n",
       "301228  2014            Washington                     8.9\n",
       "301236  2014             Wisconsin                       9\n",
       "301244  2014         West Virginia                    14.1\n",
       "301252  2014               Wyoming                     8.4\n",
       "\n",
       "[108 rows x 3 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remove more columns after renaming columns\n",
    "chronic_df7 = chronic_df6[[\"Year\", \"US_State\", \"Adult_Diabetics_Percent\"]]\n",
    "chronic_df7"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load data into database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Dependencies\n",
    "from sqlalchemy import create_engine\n",
    "from config import password\n",
    "import pymysql\n",
    "pymysql.install_as_MySQLdb()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create a connection to the database\n",
    "conn = f\"root:{password}@localhost:3306/diabetes_db\" #Password is in a separate file\n",
    "engine = create_engine(f\"mysql://{conn}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda3/envs/PythonData/lib/python3.6/site-packages/pymysql/cursors.py:170: Warning: (3719, \"'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.\")\n",
      "  result = self._query(query)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['diabetes', 'obesity']"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Confirm presence of table\n",
    "engine.table_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Load the dataframe into tables\n",
    "chronic_df7.to_sql(name = \"diabetes\", con = engine, if_exists = \"replace\", index = False)"
   ]
  },
  {
   "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>Year</th>\n",
       "      <th>US_State</th>\n",
       "      <th>Adult_Diabetics_Percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2012</td>\n",
       "      <td>Guam</td>\n",
       "      <td>9.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>12.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>11.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2012</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>10.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  US_State Adult_Diabetics_Percent\n",
       "0  2012      Guam                     9.6\n",
       "1  2012    Alaska                       7\n",
       "2  2012   Alabama                    12.2\n",
       "3  2012  Arkansas                    11.3\n",
       "4  2012   Arizona                    10.6"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Read the table content (for diabetes)\n",
    "pd.read_sql_query(\"select * from diabetes\", con = engine).head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}