Clean_Nutrition_Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extract data from csv files\n",
    "The data on weight status for children aged 3 months to 4 years old was derived from from Women, Infant, and Children Participant and Program Characteristics (WIC-PC). The data is organised by state (USA) the csv file was downloaded from [data.gov](https://catalog.data.gov/dataset/nutrition-physical-activity-and-obesity-women-infant-and-child-dfe5d/resource/415dca15-b90a-46c3-8d13-70322ee4628e)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dependencies\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>YearStart</th>\n",
       "      <th>YearEnd</th>\n",
       "      <th>LocationAbbr</th>\n",
       "      <th>LocationDesc</th>\n",
       "      <th>Datasource</th>\n",
       "      <th>Class</th>\n",
       "      <th>Topic</th>\n",
       "      <th>Question</th>\n",
       "      <th>Data_Value_Unit</th>\n",
       "      <th>Data_Value_Type</th>\n",
       "      <th>...</th>\n",
       "      <th>GeoLocation</th>\n",
       "      <th>ClassID</th>\n",
       "      <th>TopicID</th>\n",
       "      <th>QuestionID</th>\n",
       "      <th>DataValueTypeID</th>\n",
       "      <th>LocationID</th>\n",
       "      <th>StratificationCategory1</th>\n",
       "      <th>Stratification1</th>\n",
       "      <th>StratificationCategoryId1</th>\n",
       "      <th>StratificationID1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2008</td>\n",
       "      <td>2008</td>\n",
       "      <td>AL</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Women, Infants, and Children Participant and P...</td>\n",
       "      <td>Obesity / Weight Status</td>\n",
       "      <td>Obesity / Weight Status</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Value</td>\n",
       "      <td>...</td>\n",
       "      <td>(32.84057112200048, -86.63186076199969)</td>\n",
       "      <td>OWS</td>\n",
       "      <td>OWS1</td>\n",
       "      <td>Q040</td>\n",
       "      <td>VALUE</td>\n",
       "      <td>1</td>\n",
       "      <td>Total</td>\n",
       "      <td>Total</td>\n",
       "      <td>OVR</td>\n",
       "      <td>OVERALL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2008</td>\n",
       "      <td>2008</td>\n",
       "      <td>AL</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Women, Infants, and Children Participant and P...</td>\n",
       "      <td>Obesity / Weight Status</td>\n",
       "      <td>Obesity / Weight Status</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Value</td>\n",
       "      <td>...</td>\n",
       "      <td>(32.84057112200048, -86.63186076199969)</td>\n",
       "      <td>OWS</td>\n",
       "      <td>OWS1</td>\n",
       "      <td>Q040</td>\n",
       "      <td>VALUE</td>\n",
       "      <td>1</td>\n",
       "      <td>Gender</td>\n",
       "      <td>Male</td>\n",
       "      <td>GEN</td>\n",
       "      <td>MALE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2008</td>\n",
       "      <td>2008</td>\n",
       "      <td>AL</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Women, Infants, and Children Participant and P...</td>\n",
       "      <td>Obesity / Weight Status</td>\n",
       "      <td>Obesity / Weight Status</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Value</td>\n",
       "      <td>...</td>\n",
       "      <td>(32.84057112200048, -86.63186076199969)</td>\n",
       "      <td>OWS</td>\n",
       "      <td>OWS1</td>\n",
       "      <td>Q040</td>\n",
       "      <td>VALUE</td>\n",
       "      <td>1</td>\n",
       "      <td>Gender</td>\n",
       "      <td>Female</td>\n",
       "      <td>GEN</td>\n",
       "      <td>FEMALE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2008</td>\n",
       "      <td>2008</td>\n",
       "      <td>AL</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Women, Infants, and Children Participant and P...</td>\n",
       "      <td>Obesity / Weight Status</td>\n",
       "      <td>Obesity / Weight Status</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Value</td>\n",
       "      <td>...</td>\n",
       "      <td>(32.84057112200048, -86.63186076199969)</td>\n",
       "      <td>OWS</td>\n",
       "      <td>OWS1</td>\n",
       "      <td>Q040</td>\n",
       "      <td>VALUE</td>\n",
       "      <td>1</td>\n",
       "      <td>Age (months)</td>\n",
       "      <td>24 - 35</td>\n",
       "      <td>AGEMO</td>\n",
       "      <td>AGEMO2435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2008</td>\n",
       "      <td>2008</td>\n",
       "      <td>AL</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Women, Infants, and Children Participant and P...</td>\n",
       "      <td>Obesity / Weight Status</td>\n",
       "      <td>Obesity / Weight Status</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Value</td>\n",
       "      <td>...</td>\n",
       "      <td>(32.84057112200048, -86.63186076199969)</td>\n",
       "      <td>OWS</td>\n",
       "      <td>OWS1</td>\n",
       "      <td>Q040</td>\n",
       "      <td>VALUE</td>\n",
       "      <td>1</td>\n",
       "      <td>Age (months)</td>\n",
       "      <td>36 - 47</td>\n",
       "      <td>AGEMO</td>\n",
       "      <td>AGEMO3647</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   YearStart  YearEnd LocationAbbr LocationDesc  \\\n",
       "0       2008     2008           AL      Alabama   \n",
       "1       2008     2008           AL      Alabama   \n",
       "2       2008     2008           AL      Alabama   \n",
       "3       2008     2008           AL      Alabama   \n",
       "4       2008     2008           AL      Alabama   \n",
       "\n",
       "                                          Datasource                    Class  \\\n",
       "0  Women, Infants, and Children Participant and P...  Obesity / Weight Status   \n",
       "1  Women, Infants, and Children Participant and P...  Obesity / Weight Status   \n",
       "2  Women, Infants, and Children Participant and P...  Obesity / Weight Status   \n",
       "3  Women, Infants, and Children Participant and P...  Obesity / Weight Status   \n",
       "4  Women, Infants, and Children Participant and P...  Obesity / Weight Status   \n",
       "\n",
       "                     Topic                                           Question  \\\n",
       "0  Obesity / Weight Status  Percent of WIC children aged 2 to 4 years who ...   \n",
       "1  Obesity / Weight Status  Percent of WIC children aged 2 to 4 years who ...   \n",
       "2  Obesity / Weight Status  Percent of WIC children aged 2 to 4 years who ...   \n",
       "3  Obesity / Weight Status  Percent of WIC children aged 2 to 4 years who ...   \n",
       "4  Obesity / Weight Status  Percent of WIC children aged 2 to 4 years who ...   \n",
       "\n",
       "   Data_Value_Unit Data_Value_Type        ...         \\\n",
       "0              NaN           Value        ...          \n",
       "1              NaN           Value        ...          \n",
       "2              NaN           Value        ...          \n",
       "3              NaN           Value        ...          \n",
       "4              NaN           Value        ...          \n",
       "\n",
       "                               GeoLocation  ClassID TopicID QuestionID  \\\n",
       "0  (32.84057112200048, -86.63186076199969)      OWS    OWS1       Q040   \n",
       "1  (32.84057112200048, -86.63186076199969)      OWS    OWS1       Q040   \n",
       "2  (32.84057112200048, -86.63186076199969)      OWS    OWS1       Q040   \n",
       "3  (32.84057112200048, -86.63186076199969)      OWS    OWS1       Q040   \n",
       "4  (32.84057112200048, -86.63186076199969)      OWS    OWS1       Q040   \n",
       "\n",
       "   DataValueTypeID  LocationID  StratificationCategory1 Stratification1  \\\n",
       "0            VALUE           1                    Total           Total   \n",
       "1            VALUE           1                   Gender            Male   \n",
       "2            VALUE           1                   Gender          Female   \n",
       "3            VALUE           1             Age (months)         24 - 35   \n",
       "4            VALUE           1             Age (months)         36 - 47   \n",
       "\n",
       "  StratificationCategoryId1 StratificationID1  \n",
       "0                       OVR           OVERALL  \n",
       "1                       GEN              MALE  \n",
       "2                       GEN            FEMALE  \n",
       "3                     AGEMO         AGEMO2435  \n",
       "4                     AGEMO         AGEMO3647  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load CSV file as dataframe\n",
    "    # The Resources folder cannot be loaded because of it exceeds the size limit of \n",
    "    # GitHub repositories. The link to the Google Drive is found in the technical report\n",
    "nutr_df = pd.read_csv(\"Resources/Nutrition__Physical_Activity__and_Obesity_-_Women__Infant__and_Child.csv\")\n",
    "nutr_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transform data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['YearStart',\n",
       " 'YearEnd',\n",
       " 'LocationAbbr',\n",
       " 'LocationDesc',\n",
       " 'Datasource',\n",
       " 'Class',\n",
       " 'Topic',\n",
       " 'Question',\n",
       " 'Data_Value_Unit',\n",
       " 'Data_Value_Type',\n",
       " 'Data_Value',\n",
       " 'Data_Value_Alt',\n",
       " 'Data_Value_Footnote_Symbol',\n",
       " 'Data_Value_Footnote',\n",
       " 'Low_Confidence_Limit',\n",
       " 'High_Confidence_Limit ',\n",
       " 'Sample_Size',\n",
       " 'Total',\n",
       " 'Age(months)',\n",
       " 'Gender',\n",
       " 'Race/Ethnicity',\n",
       " 'GeoLocation',\n",
       " 'ClassID',\n",
       " 'TopicID',\n",
       " 'QuestionID',\n",
       " 'DataValueTypeID',\n",
       " 'LocationID',\n",
       " 'StratificationCategory1',\n",
       " 'Stratification1',\n",
       " 'StratificationCategoryId1',\n",
       " 'StratificationID1']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get a list of column headers\n",
    "list(nutr_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>YearEnd</th>\n",
       "      <th>LocationDesc</th>\n",
       "      <th>Question</th>\n",
       "      <th>Data_Value</th>\n",
       "      <th>StratificationID1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2008</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>15.4</td>\n",
       "      <td>OVERALL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2008</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>15.5</td>\n",
       "      <td>MALE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2008</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>15.3</td>\n",
       "      <td>FEMALE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2008</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>15.3</td>\n",
       "      <td>AGEMO2435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2008</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>14.9</td>\n",
       "      <td>AGEMO3647</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   YearEnd LocationDesc                                           Question  \\\n",
       "0     2008      Alabama  Percent of WIC children aged 2 to 4 years who ...   \n",
       "1     2008      Alabama  Percent of WIC children aged 2 to 4 years who ...   \n",
       "2     2008      Alabama  Percent of WIC children aged 2 to 4 years who ...   \n",
       "3     2008      Alabama  Percent of WIC children aged 2 to 4 years who ...   \n",
       "4     2008      Alabama  Percent of WIC children aged 2 to 4 years who ...   \n",
       "\n",
       "   Data_Value StratificationID1  \n",
       "0        15.4           OVERALL  \n",
       "1        15.5              MALE  \n",
       "2        15.3            FEMALE  \n",
       "3        15.3         AGEMO2435  \n",
       "4        14.9         AGEMO3647  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the relevant columns\n",
    "nutr_df2 = nutr_df[[\"YearEnd\", \"LocationDesc\", \"Question\", \"Data_Value\", \n",
    "                    \"StratificationID1\"]]\n",
    "nutr_df2.head()"
   ]
  },
  {
   "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>Question</th>\n",
       "      <th>Data_Value</th>\n",
       "      <th>StratificationID1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2010</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>16.0</td>\n",
       "      <td>OVERALL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2010</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>16.3</td>\n",
       "      <td>MALE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2010</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>15.8</td>\n",
       "      <td>FEMALE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2010</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>15.5</td>\n",
       "      <td>AGEMO2435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2010</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>16.3</td>\n",
       "      <td>AGEMO3647</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    YearEnd LocationDesc                                           Question  \\\n",
       "11     2010      Alabama  Percent of WIC children aged 2 to 4 years who ...   \n",
       "12     2010      Alabama  Percent of WIC children aged 2 to 4 years who ...   \n",
       "13     2010      Alabama  Percent of WIC children aged 2 to 4 years who ...   \n",
       "14     2010      Alabama  Percent of WIC children aged 2 to 4 years who ...   \n",
       "15     2010      Alabama  Percent of WIC children aged 2 to 4 years who ...   \n",
       "\n",
       "    Data_Value StratificationID1  \n",
       "11        16.0           OVERALL  \n",
       "12        16.3              MALE  \n",
       "13        15.8            FEMALE  \n",
       "14        15.5         AGEMO2435  \n",
       "15        16.3         AGEMO3647  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Choose data for 2010, 2012, and 2014\n",
    "Years = [2010, 2012, 2014]\n",
    "\n",
    "nutr_df3 = nutr_df2[nutr_df2[\"YearEnd\"].isin(Years)]\n",
    "nutr_df3.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>Question</th>\n",
       "      <th>Data_Value</th>\n",
       "      <th>StratificationID1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>2010</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>15.8</td>\n",
       "      <td>OVERALL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>15.6</td>\n",
       "      <td>OVERALL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>2014</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>16.3</td>\n",
       "      <td>OVERALL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>2010</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>21.2</td>\n",
       "      <td>OVERALL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>Percent of WIC children aged 2 to 4 years who ...</td>\n",
       "      <td>20.6</td>\n",
       "      <td>OVERALL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     YearEnd LocationDesc                                           Question  \\\n",
       "58      2010      Alabama  Percent of WIC children aged 2 to 4 years who ...   \n",
       "66      2012      Alabama  Percent of WIC children aged 2 to 4 years who ...   \n",
       "82      2014      Alabama  Percent of WIC children aged 2 to 4 years who ...   \n",
       "155     2010       Alaska  Percent of WIC children aged 2 to 4 years who ...   \n",
       "166     2012       Alaska  Percent of WIC children aged 2 to 4 years who ...   \n",
       "\n",
       "     Data_Value StratificationID1  \n",
       "58         15.8           OVERALL  \n",
       "66         15.6           OVERALL  \n",
       "82         16.3           OVERALL  \n",
       "155        21.2           OVERALL  \n",
       "166        20.6           OVERALL  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Choose data with no stratification (stratification ID1 = \"overall\") \n",
    "# and choose values for obese children 2-4 yo\n",
    "nutr_df4 = nutr_df3.query(\"StratificationID1 == 'OVERALL'& \\\n",
    "                          Question == 'Percent of WIC children aged 2 to 4 years who have obesity'\")\n",
    "nutr_df4.head()"
   ]
  },
  {
   "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>Year</th>\n",
       "      <th>US_State</th>\n",
       "      <th>Obese_Children_Percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>2010</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>15.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>15.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>2014</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>16.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>2010</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>21.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>20.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Year US_State  Obese_Children_Percent\n",
       "58   2010  Alabama                    15.8\n",
       "66   2012  Alabama                    15.6\n",
       "82   2014  Alabama                    16.3\n",
       "155  2010   Alaska                    21.2\n",
       "166  2012   Alaska                    20.6"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Further clean the dataset\n",
    "nutr_df4 = nutr_df4[[\"YearEnd\", \"LocationDesc\", \"Data_Value\"]]\n",
    "\n",
    "# Rename the remaining columns\n",
    "nutr_df4 = nutr_df4.rename(columns = {\"YearEnd\": \"Year\",\n",
    "                                      \"LocationDesc\": \"US_State\",\n",
    "                                      \"Data_Value\": \"Obese_Children_Percent\"})\n",
    "\n",
    "# Preview the dataframe\n",
    "nutr_df4.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load data into database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dependencies\n",
    "from sqlalchemy import create_engine\n",
    "from config import password"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a connection to the database\n",
    "conn = \"root:{0}@localhost:3306/diabetes_db\".format(password) # Password is in a separate file\n",
    "engine = create_engine(f\"mysql://{conn}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda3/envs/PythonData/lib/python3.6/site-packages/sqlalchemy/engine/default.py:507: 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",
      "  cursor.execute(statement, parameters)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['diabetes', 'obesity']"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Confirm presence of tables\n",
    "engine.table_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load dataframes into tables\n",
    "nutr_df4.to_sql(name = \"obesity\", con = engine, if_exists = \"replace\", index = False)"
   ]
  },
  {
   "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>Obese_Children_Percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>15.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>15.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2014</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>16.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2010</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>21.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2012</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>20.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year US_State  Obese_Children_Percent\n",
       "0  2010  Alabama                    15.8\n",
       "1  2012  Alabama                    15.6\n",
       "2  2014  Alabama                    16.3\n",
       "3  2010   Alaska                    21.2\n",
       "4  2012   Alaska                    20.6"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read the table contents (for obesity)\n",
    "pd.read_sql_query(\"select * from obesity\", 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
}