Husky enrollment by town.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Husky Enrollment by town\n",
    "\n",
    "Analysis started Friday, March 18, 2016.\n",
    "\n",
    "## 0 Data import\n",
    "\n",
    "### 0.A Import Husky sheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "town                170\n",
       "population          170\n",
       "husky_enrollment    170\n",
       "husky_pct           170\n",
       "dtype: int64"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "## Load HUSKY A tab, skipping header lines\n",
    "husky = pd.read_excel(\"HUSKYA and Medicaid by Town with %.xls\",\n",
    "                           sheetname=0,\n",
    "                           skiprows=6, \n",
    "                           names=[\"town\",\"population\",\"husky_enrollment\",\"husky_pct_enrollment\"])\n",
    "\n",
    "## (Uncomment below) Check that their calculated enrollment percent matches my own (should equal zero)\n",
    "#husky[\"calculated_pct\"] = husky[\"enrollment\"] / husky[\"population\"]\n",
    "#husky[\"calculated_pct\"].sum() - husky[\"pct_enrollment\"].sum()\n",
    "\n",
    "\n",
    "## Add a column for a human-friendly percent\n",
    "husky[\"husky_pct\"] = husky[\"husky_pct_enrollment\"] * 100\n",
    "\n",
    "## Drop origin percent col\n",
    "husky = husky.drop([\"husky_pct_enrollment\"], 1)\n",
    "\n",
    "## (Uncomment below) Describe the dataset\n",
    "#husky.describe()\n",
    "\n",
    "## (Uncomment below) Count rows\n",
    "husky.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 0.b Import Medicaid sheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "town              170\n",
       "population        170\n",
       "med_enrollment    170\n",
       "med_pct           170\n",
       "dtype: int64"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Load Sheet 2\n",
    "medicaid = pd.read_excel(\"HUSKYA and Medicaid by Town with %.xls\",\n",
    "                         sheetname=\"Medicaid Enrollment by Town\",\n",
    "                         skiprows=4,\n",
    "                         names=[\"town\",\"population\",\"med_enrollment\",\"drop\",\"med_pct\"])\n",
    "\n",
    "## fourth column is empty\n",
    "medicaid = medicaid.drop(\"drop\",1)\n",
    "\n",
    "## drop null rows\n",
    "medicaid = medicaid[medicaid[\"population\"].notnull()]\n",
    "\n",
    "\n",
    "## Check that their percentages are correct\n",
    "#medicaid[\"calculated_pct\"] = medicaid[\"med_enrollment\"] / medicaid[\"population\"]\n",
    "#medicaid[\"calculated_pct\"].sum() - medicaid[\"med_pct\"].sum()\n",
    "\n",
    "## Add a human-readable percent column\n",
    "medicaid[\"med_pct\"] = medicaid[\"med_pct\"] * 100\n",
    "\n",
    "## Drop original percent column\n",
    "#medicaid = medicaid.drop(\"med_pct\", 1)\n",
    "\n",
    "## (Uncomment below) Count rows\n",
    "medicaid.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge sheets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "town                169\n",
       "population          169\n",
       "med_enrollment      169\n",
       "med_pct             169\n",
       "husky_enrollment    169\n",
       "husky_pct           169\n",
       "dtype: int64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined = medicaid.merge(husky,on=[\"town\",\"population\"])\n",
    "\n",
    "## Count rows. Should have the same # no of rows as medicaid and husky\n",
    "combined.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "## Something went wrong. Let's find the row that didn't synch\n",
    "\n",
    "## Rows in husky but not in combined\n",
    "#husky[~husky[\"town\"].isin(combined[\"town\"])]\n",
    "\n",
    "## Rows in medicaid but not in combined\n",
    "#medicaid[~medicaid[\"town\"].isin(combined[\"town\"])]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "town                170\n",
       "population          170\n",
       "med_enrollment      170\n",
       "med_pct             170\n",
       "husky_enrollment    170\n",
       "husky_pct           170\n",
       "dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## North stonington is the problem. It's entered both as \"No. Stonington\" and \"North Stonington\"\n",
    "medicaid = medicaid.replace(\"No. Stonington\", \"North Stonington\")\n",
    "\n",
    "\n",
    "## Try merge again\n",
    "combined = medicaid.merge(husky,on=[\"town\",\"population\"])\n",
    "\n",
    "## Count rows. Should have the same # no of rows as medicaid and husky\n",
    "combined.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "## Good! Moving on..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1 Towns with the highest enrollment\n",
    "\n",
    "Canaan has a population of 1,195, so the percentage each resident accounts for is significantly higher than the other places on the list. We'll look just at towns with populations over 5,000\n",
    "\n",
    "### 1.a Top medicaid enrollment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Canaan has a population of 1,195, so the percentage each resident accounts for is significantly higher than the other places on the list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>town</th>\n",
       "      <th>population</th>\n",
       "      <th>med_enrollment</th>\n",
       "      <th>med_pct</th>\n",
       "      <th>husky_enrollment</th>\n",
       "      <th>husky_pct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>Hartford</td>\n",
       "      <td>124705</td>\n",
       "      <td>71755</td>\n",
       "      <td>57.539794</td>\n",
       "      <td>39917</td>\n",
       "      <td>32.009142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>Waterbury</td>\n",
       "      <td>109307</td>\n",
       "      <td>55828</td>\n",
       "      <td>51.074497</td>\n",
       "      <td>33099</td>\n",
       "      <td>30.280769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>New Britain</td>\n",
       "      <td>72878</td>\n",
       "      <td>34049</td>\n",
       "      <td>46.720547</td>\n",
       "      <td>20398</td>\n",
       "      <td>27.989242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Bridgeport</td>\n",
       "      <td>147612</td>\n",
       "      <td>66854</td>\n",
       "      <td>45.290356</td>\n",
       "      <td>40457</td>\n",
       "      <td>27.407663</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>New Haven</td>\n",
       "      <td>130282</td>\n",
       "      <td>56711</td>\n",
       "      <td>43.529421</td>\n",
       "      <td>31913</td>\n",
       "      <td>24.495326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>New London</td>\n",
       "      <td>27374</td>\n",
       "      <td>11716</td>\n",
       "      <td>42.799737</td>\n",
       "      <td>6830</td>\n",
       "      <td>24.950683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>Windham</td>\n",
       "      <td>25005</td>\n",
       "      <td>10163</td>\n",
       "      <td>40.643871</td>\n",
       "      <td>5682</td>\n",
       "      <td>22.723455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>Norwich</td>\n",
       "      <td>40178</td>\n",
       "      <td>15467</td>\n",
       "      <td>38.496192</td>\n",
       "      <td>9106</td>\n",
       "      <td>22.664145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>East Hartford</td>\n",
       "      <td>51033</td>\n",
       "      <td>19261</td>\n",
       "      <td>37.742245</td>\n",
       "      <td>11487</td>\n",
       "      <td>22.508965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>Meriden</td>\n",
       "      <td>60293</td>\n",
       "      <td>22091</td>\n",
       "      <td>36.639411</td>\n",
       "      <td>13079</td>\n",
       "      <td>21.692402</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              town  population  med_enrollment    med_pct  husky_enrollment  \\\n",
       "63        Hartford      124705           71755  57.539794             39917   \n",
       "150      Waterbury      109307           55828  51.074497             33099   \n",
       "88     New Britain       72878           34049  46.720547             20398   \n",
       "14      Bridgeport      147612           66854  45.290356             40457   \n",
       "92       New Haven      130282           56711  43.529421             31913   \n",
       "93      New London       27374           11716  42.799737              6830   \n",
       "162        Windham       25005           10163  40.643871              5682   \n",
       "103        Norwich       40178           15467  38.496192              9106   \n",
       "41   East Hartford       51033           19261  37.742245             11487   \n",
       "79         Meriden       60293           22091  36.639411             13079   \n",
       "\n",
       "     husky_pct  \n",
       "63   32.009142  \n",
       "150  30.280769  \n",
       "88   27.989242  \n",
       "14   27.407663  \n",
       "92   24.495326  \n",
       "93   24.950683  \n",
       "162  22.723455  \n",
       "103  22.664145  \n",
       "41   22.508965  \n",
       "79   21.692402  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined[combined[\"population\"] > 5000].sort_values(\"med_pct\", ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.b Top husky enrollment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>town</th>\n",
       "      <th>population</th>\n",
       "      <th>med_enrollment</th>\n",
       "      <th>med_pct</th>\n",
       "      <th>husky_enrollment</th>\n",
       "      <th>husky_pct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>Hartford</td>\n",
       "      <td>124705</td>\n",
       "      <td>71755</td>\n",
       "      <td>57.539794</td>\n",
       "      <td>39917</td>\n",
       "      <td>32.009142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>Waterbury</td>\n",
       "      <td>109307</td>\n",
       "      <td>55828</td>\n",
       "      <td>51.074497</td>\n",
       "      <td>33099</td>\n",
       "      <td>30.280769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>New Britain</td>\n",
       "      <td>72878</td>\n",
       "      <td>34049</td>\n",
       "      <td>46.720547</td>\n",
       "      <td>20398</td>\n",
       "      <td>27.989242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Bridgeport</td>\n",
       "      <td>147612</td>\n",
       "      <td>66854</td>\n",
       "      <td>45.290356</td>\n",
       "      <td>40457</td>\n",
       "      <td>27.407663</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>New London</td>\n",
       "      <td>27374</td>\n",
       "      <td>11716</td>\n",
       "      <td>42.799737</td>\n",
       "      <td>6830</td>\n",
       "      <td>24.950683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>New Haven</td>\n",
       "      <td>130282</td>\n",
       "      <td>56711</td>\n",
       "      <td>43.529421</td>\n",
       "      <td>31913</td>\n",
       "      <td>24.495326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>Windham</td>\n",
       "      <td>25005</td>\n",
       "      <td>10163</td>\n",
       "      <td>40.643871</td>\n",
       "      <td>5682</td>\n",
       "      <td>22.723455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>Norwich</td>\n",
       "      <td>40178</td>\n",
       "      <td>15467</td>\n",
       "      <td>38.496192</td>\n",
       "      <td>9106</td>\n",
       "      <td>22.664145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>East Hartford</td>\n",
       "      <td>51033</td>\n",
       "      <td>19261</td>\n",
       "      <td>37.742245</td>\n",
       "      <td>11487</td>\n",
       "      <td>22.508965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>Meriden</td>\n",
       "      <td>60293</td>\n",
       "      <td>22091</td>\n",
       "      <td>36.639411</td>\n",
       "      <td>13079</td>\n",
       "      <td>21.692402</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              town  population  med_enrollment    med_pct  husky_enrollment  \\\n",
       "63        Hartford      124705           71755  57.539794             39917   \n",
       "150      Waterbury      109307           55828  51.074497             33099   \n",
       "88     New Britain       72878           34049  46.720547             20398   \n",
       "14      Bridgeport      147612           66854  45.290356             40457   \n",
       "93      New London       27374           11716  42.799737              6830   \n",
       "92       New Haven      130282           56711  43.529421             31913   \n",
       "162        Windham       25005           10163  40.643871              5682   \n",
       "103        Norwich       40178           15467  38.496192              9106   \n",
       "41   East Hartford       51033           19261  37.742245             11487   \n",
       "79         Meriden       60293           22091  36.639411             13079   \n",
       "\n",
       "     husky_pct  \n",
       "63   32.009142  \n",
       "150  30.280769  \n",
       "88   27.989242  \n",
       "14   27.407663  \n",
       "93   24.950683  \n",
       "92   24.495326  \n",
       "162  22.723455  \n",
       "103  22.664145  \n",
       "41   22.508965  \n",
       "79   21.692402  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined[combined[\"population\"] > 5000].sort_values(\"husky_pct\", ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>population</th>\n",
       "      <th>med_enrollment</th>\n",
       "      <th>med_pct</th>\n",
       "      <th>husky_enrollment</th>\n",
       "      <th>husky_pct</th>\n",
       "      <th>husky_share</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>170.000000</td>\n",
       "      <td>170.000000</td>\n",
       "      <td>170.000000</td>\n",
       "      <td>170.000000</td>\n",
       "      <td>170.000000</td>\n",
       "      <td>170.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>42313.847059</td>\n",
       "      <td>9690.200000</td>\n",
       "      <td>16.583130</td>\n",
       "      <td>5483.376471</td>\n",
       "      <td>9.188307</td>\n",
       "      <td>54.339931</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>275407.148728</td>\n",
       "      <td>63679.145467</td>\n",
       "      <td>10.220578</td>\n",
       "      <td>36057.610465</td>\n",
       "      <td>6.085087</td>\n",
       "      <td>6.209408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>846.000000</td>\n",
       "      <td>64.000000</td>\n",
       "      <td>3.715826</td>\n",
       "      <td>34.000000</td>\n",
       "      <td>1.905090</td>\n",
       "      <td>28.302676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>5473.250000</td>\n",
       "      <td>683.250000</td>\n",
       "      <td>9.694484</td>\n",
       "      <td>347.500000</td>\n",
       "      <td>5.097916</td>\n",
       "      <td>50.191141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>12821.000000</td>\n",
       "      <td>1477.000000</td>\n",
       "      <td>13.902793</td>\n",
       "      <td>756.000000</td>\n",
       "      <td>7.337486</td>\n",
       "      <td>54.551823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>25938.500000</td>\n",
       "      <td>3960.500000</td>\n",
       "      <td>19.562850</td>\n",
       "      <td>2113.500000</td>\n",
       "      <td>11.331514</td>\n",
       "      <td>58.729329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>3596677.000000</td>\n",
       "      <td>823867.000000</td>\n",
       "      <td>63.598326</td>\n",
       "      <td>466087.000000</td>\n",
       "      <td>32.887029</td>\n",
       "      <td>69.834711</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           population  med_enrollment     med_pct  husky_enrollment  \\\n",
       "count      170.000000      170.000000  170.000000        170.000000   \n",
       "mean     42313.847059     9690.200000   16.583130       5483.376471   \n",
       "std     275407.148728    63679.145467   10.220578      36057.610465   \n",
       "min        846.000000       64.000000    3.715826         34.000000   \n",
       "25%       5473.250000      683.250000    9.694484        347.500000   \n",
       "50%      12821.000000     1477.000000   13.902793        756.000000   \n",
       "75%      25938.500000     3960.500000   19.562850       2113.500000   \n",
       "max    3596677.000000   823867.000000   63.598326     466087.000000   \n",
       "\n",
       "        husky_pct  husky_share  \n",
       "count  170.000000   170.000000  \n",
       "mean     9.188307    54.339931  \n",
       "std      6.085087     6.209408  \n",
       "min      1.905090    28.302676  \n",
       "25%      5.097916    50.191141  \n",
       "50%      7.337486    54.551823  \n",
       "75%     11.331514    58.729329  \n",
       "max     32.887029    69.834711  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add a column for the share of the husky program \n",
    "combined[\"husky_share\"] = combined[\"husky_enrollment\"] * 100 / combined[\"med_enrollment\"]\n",
    "\n",
    "combined.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.c Town's with the largest share of Husky A enrollment vs. overall medicaid program"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>town</th>\n",
       "      <th>husky_share</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Danbury</td>\n",
       "      <td>64.918573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>Redding</td>\n",
       "      <td>64.123711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>Ledyard</td>\n",
       "      <td>64.096715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>Woodstock</td>\n",
       "      <td>63.465160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Canterbury</td>\n",
       "      <td>63.010753</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Burlington</td>\n",
       "      <td>63.008130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>Griswold</td>\n",
       "      <td>62.915007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ansonia</td>\n",
       "      <td>62.469257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>Naugatuck</td>\n",
       "      <td>62.215772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>North Stonington</td>\n",
       "      <td>61.983471</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 town  husky_share\n",
       "33            Danbury    64.918573\n",
       "116           Redding    64.123711\n",
       "71            Ledyard    64.096715\n",
       "168         Woodstock    63.465160\n",
       "21         Canterbury    63.010753\n",
       "19         Burlington    63.008130\n",
       "57           Griswold    62.915007\n",
       "1             Ansonia    62.469257\n",
       "87          Naugatuck    62.215772\n",
       "101  North Stonington    61.983471"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined[combined[\"population\"] > 5000].sort_values(\"husky_share\", ascending=False).head(10)[[\"town\",\"husky_share\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.c Town's with the smallest share of Husky A enrollment vs. overall medicaid program\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>town</th>\n",
       "      <th>husky_share</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>Southbury</td>\n",
       "      <td>28.302676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>Wilton</td>\n",
       "      <td>39.492754</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Avon</td>\n",
       "      <td>42.384106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>Westbrook</td>\n",
       "      <td>42.469598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Bethany</td>\n",
       "      <td>43.198091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Cheshire</td>\n",
       "      <td>43.450479</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Farmington</td>\n",
       "      <td>43.763756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>Rocky Hill</td>\n",
       "      <td>43.889316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>Prospect</td>\n",
       "      <td>44.585987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Bloomfield</td>\n",
       "      <td>44.646506</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           town  husky_share\n",
       "130   Southbury    28.302676\n",
       "160      Wilton    39.492754\n",
       "3          Avon    42.384106\n",
       "155   Westbrook    42.469598\n",
       "7       Bethany    43.198091\n",
       "24     Cheshire    43.450479\n",
       "51   Farmington    43.763756\n",
       "118  Rocky Hill    43.889316\n",
       "114    Prospect    44.585987\n",
       "10   Bloomfield    44.646506"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined[combined[\"population\"] > 5000].sort_values(\"husky_share\", ascending=True).head(10)[[\"town\",\"husky_share\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.d Section conclusions\n",
    "\n",
    "1. The lists with the highest medicaid enrollment and the highest husky A enrollment are virtually identical, with only New London and New Haven switching positions.\n",
    "2. At first glance, the towns with the largest and smallest share of husky enrollment versus total medicaid enrollment seems random. Maybe it is just a proxy for the age of the population. Anecdotally, I think Southbury has a large elderly population."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2 Comparing age of town populations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#combined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>town</th>\n",
       "      <th>under_18</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bethel</td>\n",
       "      <td>22.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bridgeport</td>\n",
       "      <td>25.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Brookfield</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Danbury</td>\n",
       "      <td>21.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Darien</td>\n",
       "      <td>33.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         town  under_18\n",
       "1      Bethel      22.8\n",
       "2  Bridgeport      25.2\n",
       "3  Brookfield      23.0\n",
       "4     Danbury      21.2\n",
       "5      Darien      33.7"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "age = pd.read_csv(\"ACS_14_5YR_S0101_with_ann.csv\",\n",
    "                 skiprows=1)\n",
    "age = age[~age[\"Geography\"].str.contains(\"County subdivisions not defined\")]\n",
    "\n",
    "age.dtypes\n",
    "\n",
    "import pprint  \n",
    "total_cols = [col for col in age.columns if 'Total; Estimate' in col and 'AGE' in col]\n",
    "#pprint.pprint(total_cols)\n",
    "#age_totals = age[total_cols]\n",
    "#age_totals.dtypes\n",
    "\n",
    "age_medians = age[[\"Geography\",\"Total; Estimate; SUMMARY INDICATORS - Median age (years)\"]]\n",
    "\n",
    "age_groups = age[[\"Total; Estimate; AGE - Under 5 years\",\n",
    "     \"Total; Estimate; SELECTED AGE CATEGORIES - 5 to 14 years\",\n",
    "     \"Total; Estimate; SELECTED AGE CATEGORIES - 15 to 17 years\"\n",
    "    ]]\n",
    "\n",
    "age_groups.loc[:,\n",
    "               \"under_5\"] = pd.to_numeric(age_groups[\"Total; Estimate; AGE - Under 5 years\"])\n",
    "age_groups.loc[:,\n",
    "               \"5_to_14\"] = pd.to_numeric(age_groups[\"Total; Estimate; SELECTED AGE CATEGORIES - 5 to 14 years\"])\n",
    "age_groups.loc[:,\n",
    "               \"15_to_17\"] = pd.to_numeric(age_groups[\"Total; Estimate; SELECTED AGE CATEGORIES - 15 to 17 years\"])\n",
    "age_groups.loc[:,\n",
    "             \"under_18\"] = age_groups[\"under_5\"] + age_groups[\"5_to_14\"] + age_groups[\"15_to_17\"]\n",
    "\n",
    "age_groups.loc[age[\"Geography\"].notnull(),\n",
    "                \"town\"] = age[\"Geography\"].apply(lambda x: x[0:x.find(\" town, \")])\n",
    "\n",
    "age_under_18 = age_groups[[\"town\", \"under_18\"]]\n",
    "\n",
    "age_under_18.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#combined.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>median_age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>169.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>43.550888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>4.877208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>21.100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>41.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>43.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>46.100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>58.600000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       median_age\n",
       "count  169.000000\n",
       "mean    43.550888\n",
       "std      4.877208\n",
       "min     21.100000\n",
       "25%     41.500000\n",
       "50%     43.800000\n",
       "75%     46.100000\n",
       "max     58.600000"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "\n",
    "age_medians.loc[age_medians[\"Geography\"].notnull(),\"town\"] = age_medians[\"Geography\"].apply(lambda x: x[0:x.find(\" town, \")])\n",
    "age_medians.loc[np.isreal(age_medians[\"Total; Estimate; SUMMARY INDICATORS - Median age (years)\"]),\n",
    "                                     \"median_age\"] = pd.to_numeric(age_medians[\"Total; Estimate; SUMMARY INDICATORS - Median age (years)\"])\n",
    "age_medians_clean = age_medians[[\"town\",\"median_age\"]]\n",
    "\n",
    "#age_medians.sort_values(by=\"median_age\", ascending=False)[[\"town\",\"median_age\"]]\n",
    "age_medians_clean.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#combined = combined.merge(age_medians_clean,on=\"town\")\n",
    "combined = combined.merge(age_under_18,on=\"town\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>population</th>\n",
       "      <th>med_enrollment</th>\n",
       "      <th>med_pct</th>\n",
       "      <th>husky_enrollment</th>\n",
       "      <th>husky_pct</th>\n",
       "      <th>husky_share</th>\n",
       "      <th>median_age</th>\n",
       "      <th>under_18</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>population</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.891665</td>\n",
       "      <td>0.524308</td>\n",
       "      <td>0.890324</td>\n",
       "      <td>0.525807</td>\n",
       "      <td>0.086894</td>\n",
       "      <td>-0.580746</td>\n",
       "      <td>0.108752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>med_enrollment</th>\n",
       "      <td>0.891665</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.695984</td>\n",
       "      <td>0.998616</td>\n",
       "      <td>0.699091</td>\n",
       "      <td>0.171036</td>\n",
       "      <td>-0.559031</td>\n",
       "      <td>0.069922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>med_pct</th>\n",
       "      <td>0.524308</td>\n",
       "      <td>0.695984</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.696863</td>\n",
       "      <td>0.987432</td>\n",
       "      <td>0.278470</td>\n",
       "      <td>-0.473650</td>\n",
       "      <td>-0.203857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>husky_enrollment</th>\n",
       "      <td>0.890324</td>\n",
       "      <td>0.998616</td>\n",
       "      <td>0.696863</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.704848</td>\n",
       "      <td>0.195524</td>\n",
       "      <td>-0.561162</td>\n",
       "      <td>0.074034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>husky_pct</th>\n",
       "      <td>0.525807</td>\n",
       "      <td>0.699091</td>\n",
       "      <td>0.987432</td>\n",
       "      <td>0.704848</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.410273</td>\n",
       "      <td>-0.511964</td>\n",
       "      <td>-0.160799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>husky_share</th>\n",
       "      <td>0.086894</td>\n",
       "      <td>0.171036</td>\n",
       "      <td>0.278470</td>\n",
       "      <td>0.195524</td>\n",
       "      <td>0.410273</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.312398</td>\n",
       "      <td>0.073939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>median_age</th>\n",
       "      <td>-0.580746</td>\n",
       "      <td>-0.559031</td>\n",
       "      <td>-0.473650</td>\n",
       "      <td>-0.561162</td>\n",
       "      <td>-0.511964</td>\n",
       "      <td>-0.312398</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.183240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>under_18</th>\n",
       "      <td>0.108752</td>\n",
       "      <td>0.069922</td>\n",
       "      <td>-0.203857</td>\n",
       "      <td>0.074034</td>\n",
       "      <td>-0.160799</td>\n",
       "      <td>0.073939</td>\n",
       "      <td>-0.183240</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  population  med_enrollment   med_pct  husky_enrollment  \\\n",
       "population          1.000000        0.891665  0.524308          0.890324   \n",
       "med_enrollment      0.891665        1.000000  0.695984          0.998616   \n",
       "med_pct             0.524308        0.695984  1.000000          0.696863   \n",
       "husky_enrollment    0.890324        0.998616  0.696863          1.000000   \n",
       "husky_pct           0.525807        0.699091  0.987432          0.704848   \n",
       "husky_share         0.086894        0.171036  0.278470          0.195524   \n",
       "median_age         -0.580746       -0.559031 -0.473650         -0.561162   \n",
       "under_18            0.108752        0.069922 -0.203857          0.074034   \n",
       "\n",
       "                  husky_pct  husky_share  median_age  under_18  \n",
       "population         0.525807     0.086894   -0.580746  0.108752  \n",
       "med_enrollment     0.699091     0.171036   -0.559031  0.069922  \n",
       "med_pct            0.987432     0.278470   -0.473650 -0.203857  \n",
       "husky_enrollment   0.704848     0.195524   -0.561162  0.074034  \n",
       "husky_pct          1.000000     0.410273   -0.511964 -0.160799  \n",
       "husky_share        0.410273     1.000000   -0.312398  0.073939  \n",
       "median_age        -0.511964    -0.312398    1.000000 -0.183240  \n",
       "under_18          -0.160799     0.073939   -0.183240  1.000000  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined.corr()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>town</th>\n",
       "      <th>husky_share</th>\n",
       "      <th>median_age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Danbury</td>\n",
       "      <td>64.918573</td>\n",
       "      <td>36.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>Redding</td>\n",
       "      <td>64.123711</td>\n",
       "      <td>48.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>Ledyard</td>\n",
       "      <td>64.096715</td>\n",
       "      <td>41.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>Woodstock</td>\n",
       "      <td>63.465160</td>\n",
       "      <td>44.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Canterbury</td>\n",
       "      <td>63.010753</td>\n",
       "      <td>43.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Burlington</td>\n",
       "      <td>63.008130</td>\n",
       "      <td>42.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>Griswold</td>\n",
       "      <td>62.915007</td>\n",
       "      <td>37.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ansonia</td>\n",
       "      <td>62.469257</td>\n",
       "      <td>40.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>Naugatuck</td>\n",
       "      <td>62.215772</td>\n",
       "      <td>38.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>North Stonington</td>\n",
       "      <td>61.983471</td>\n",
       "      <td>46.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 town  husky_share  median_age\n",
       "33            Danbury    64.918573        36.8\n",
       "116           Redding    64.123711        48.8\n",
       "71            Ledyard    64.096715        41.4\n",
       "167         Woodstock    63.465160        44.5\n",
       "21         Canterbury    63.010753        43.4\n",
       "19         Burlington    63.008130        42.4\n",
       "57           Griswold    62.915007        37.3\n",
       "1             Ansonia    62.469257        40.8\n",
       "87          Naugatuck    62.215772        38.7\n",
       "101  North Stonington    61.983471        46.9"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined[combined[\"population\"] > 5000].sort_values(\"husky_share\", ascending=False).head(10)[[\"town\",\"husky_share\",\"median_age\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>town</th>\n",
       "      <th>husky_share</th>\n",
       "      <th>median_age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>Southbury</td>\n",
       "      <td>28.302676</td>\n",
       "      <td>50.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>Wilton</td>\n",
       "      <td>39.492754</td>\n",
       "      <td>42.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Avon</td>\n",
       "      <td>42.384106</td>\n",
       "      <td>45.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>Westbrook</td>\n",
       "      <td>42.469598</td>\n",
       "      <td>48.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Bethany</td>\n",
       "      <td>43.198091</td>\n",
       "      <td>44.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Cheshire</td>\n",
       "      <td>43.450479</td>\n",
       "      <td>42.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Farmington</td>\n",
       "      <td>43.763756</td>\n",
       "      <td>43.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>Rocky Hill</td>\n",
       "      <td>43.889316</td>\n",
       "      <td>43.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>Prospect</td>\n",
       "      <td>44.585987</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Bloomfield</td>\n",
       "      <td>44.646506</td>\n",
       "      <td>48.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           town  husky_share  median_age\n",
       "130   Southbury    28.302676        50.1\n",
       "160      Wilton    39.492754        42.2\n",
       "3          Avon    42.384106        45.8\n",
       "155   Westbrook    42.469598        48.9\n",
       "7       Bethany    43.198091        44.6\n",
       "24     Cheshire    43.450479        42.9\n",
       "51   Farmington    43.763756        43.4\n",
       "118  Rocky Hill    43.889316        43.4\n",
       "114    Prospect    44.585987        45.0\n",
       "10   Bloomfield    44.646506        48.2"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined[combined[\"population\"] > 5000].sort_values(\"husky_share\", ascending=True).head(10)[[\"town\",\"husky_share\",\"median_age\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.a Section conclusions\n",
    "\n",
    "1. There is no strong correlation between a town's median age and the share of Husky A enrollments compared with overall Medicaid enrollments."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3 Output for chart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "combined.to_csv(\"output.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>town</th>\n",
       "      <th>population</th>\n",
       "      <th>med_enrollment</th>\n",
       "      <th>med_pct</th>\n",
       "      <th>husky_enrollment</th>\n",
       "      <th>husky_pct</th>\n",
       "      <th>husky_share</th>\n",
       "      <th>median_age</th>\n",
       "      <th>under_18</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andover</td>\n",
       "      <td>3272</td>\n",
       "      <td>395</td>\n",
       "      <td>12.072127</td>\n",
       "      <td>226</td>\n",
       "      <td>6.907090</td>\n",
       "      <td>57.215190</td>\n",
       "      <td>43.0</td>\n",
       "      <td>23.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ansonia</td>\n",
       "      <td>18959</td>\n",
       "      <td>6099</td>\n",
       "      <td>32.169418</td>\n",
       "      <td>3810</td>\n",
       "      <td>20.095997</td>\n",
       "      <td>62.469257</td>\n",
       "      <td>40.8</td>\n",
       "      <td>22.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ashford</td>\n",
       "      <td>4259</td>\n",
       "      <td>888</td>\n",
       "      <td>20.849965</td>\n",
       "      <td>562</td>\n",
       "      <td>13.195586</td>\n",
       "      <td>63.288288</td>\n",
       "      <td>41.1</td>\n",
       "      <td>20.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Avon</td>\n",
       "      <td>18421</td>\n",
       "      <td>1208</td>\n",
       "      <td>6.557733</td>\n",
       "      <td>512</td>\n",
       "      <td>2.779437</td>\n",
       "      <td>42.384106</td>\n",
       "      <td>45.8</td>\n",
       "      <td>25.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Barkhamsted</td>\n",
       "      <td>3705</td>\n",
       "      <td>483</td>\n",
       "      <td>13.036437</td>\n",
       "      <td>283</td>\n",
       "      <td>7.638327</td>\n",
       "      <td>58.592133</td>\n",
       "      <td>44.7</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Beacon Falls</td>\n",
       "      <td>6055</td>\n",
       "      <td>805</td>\n",
       "      <td>13.294798</td>\n",
       "      <td>431</td>\n",
       "      <td>7.118084</td>\n",
       "      <td>53.540373</td>\n",
       "      <td>43.0</td>\n",
       "      <td>22.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Berlin</td>\n",
       "      <td>20610</td>\n",
       "      <td>2363</td>\n",
       "      <td>11.465308</td>\n",
       "      <td>1123</td>\n",
       "      <td>5.448811</td>\n",
       "      <td>47.524333</td>\n",
       "      <td>45.7</td>\n",
       "      <td>21.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Bethany</td>\n",
       "      <td>5531</td>\n",
       "      <td>419</td>\n",
       "      <td>7.575484</td>\n",
       "      <td>181</td>\n",
       "      <td>3.272464</td>\n",
       "      <td>43.198091</td>\n",
       "      <td>44.6</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Bethel</td>\n",
       "      <td>19372</td>\n",
       "      <td>2731</td>\n",
       "      <td>14.097667</td>\n",
       "      <td>1596</td>\n",
       "      <td>8.238695</td>\n",
       "      <td>58.440132</td>\n",
       "      <td>42.3</td>\n",
       "      <td>22.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Bethlehem</td>\n",
       "      <td>3501</td>\n",
       "      <td>481</td>\n",
       "      <td>13.738932</td>\n",
       "      <td>278</td>\n",
       "      <td>7.940588</td>\n",
       "      <td>57.796258</td>\n",
       "      <td>48.9</td>\n",
       "      <td>20.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Bloomfield</td>\n",
       "      <td>20819</td>\n",
       "      <td>4894</td>\n",
       "      <td>23.507373</td>\n",
       "      <td>2185</td>\n",
       "      <td>10.495221</td>\n",
       "      <td>44.646506</td>\n",
       "      <td>48.2</td>\n",
       "      <td>14.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Bolton</td>\n",
       "      <td>4952</td>\n",
       "      <td>532</td>\n",
       "      <td>10.743134</td>\n",
       "      <td>300</td>\n",
       "      <td>6.058158</td>\n",
       "      <td>56.390977</td>\n",
       "      <td>47.2</td>\n",
       "      <td>20.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Bozrah</td>\n",
       "      <td>2622</td>\n",
       "      <td>441</td>\n",
       "      <td>16.819222</td>\n",
       "      <td>225</td>\n",
       "      <td>8.581236</td>\n",
       "      <td>51.020408</td>\n",
       "      <td>46.5</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Branford</td>\n",
       "      <td>28225</td>\n",
       "      <td>4522</td>\n",
       "      <td>16.021258</td>\n",
       "      <td>2132</td>\n",
       "      <td>7.553587</td>\n",
       "      <td>47.147280</td>\n",
       "      <td>48.0</td>\n",
       "      <td>17.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Bridgeport</td>\n",
       "      <td>147612</td>\n",
       "      <td>66854</td>\n",
       "      <td>45.290356</td>\n",
       "      <td>40457</td>\n",
       "      <td>27.407663</td>\n",
       "      <td>60.515452</td>\n",
       "      <td>32.3</td>\n",
       "      <td>25.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Bridgewater</td>\n",
       "      <td>1675</td>\n",
       "      <td>121</td>\n",
       "      <td>7.223881</td>\n",
       "      <td>59</td>\n",
       "      <td>3.522388</td>\n",
       "      <td>48.760331</td>\n",
       "      <td>52.3</td>\n",
       "      <td>19.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Bristol</td>\n",
       "      <td>60570</td>\n",
       "      <td>16295</td>\n",
       "      <td>26.902757</td>\n",
       "      <td>9528</td>\n",
       "      <td>15.730560</td>\n",
       "      <td>58.471924</td>\n",
       "      <td>41.4</td>\n",
       "      <td>20.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Brookfield</td>\n",
       "      <td>17055</td>\n",
       "      <td>1728</td>\n",
       "      <td>10.131926</td>\n",
       "      <td>984</td>\n",
       "      <td>5.769569</td>\n",
       "      <td>56.944444</td>\n",
       "      <td>44.6</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>8254</td>\n",
       "      <td>1763</td>\n",
       "      <td>21.359341</td>\n",
       "      <td>934</td>\n",
       "      <td>11.315726</td>\n",
       "      <td>52.977879</td>\n",
       "      <td>42.3</td>\n",
       "      <td>22.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Burlington</td>\n",
       "      <td>9576</td>\n",
       "      <td>738</td>\n",
       "      <td>7.706767</td>\n",
       "      <td>465</td>\n",
       "      <td>4.855890</td>\n",
       "      <td>63.008130</td>\n",
       "      <td>42.4</td>\n",
       "      <td>26.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Canaan</td>\n",
       "      <td>1195</td>\n",
       "      <td>760</td>\n",
       "      <td>63.598326</td>\n",
       "      <td>393</td>\n",
       "      <td>32.887029</td>\n",
       "      <td>51.710526</td>\n",
       "      <td>51.4</td>\n",
       "      <td>16.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Canterbury</td>\n",
       "      <td>5088</td>\n",
       "      <td>930</td>\n",
       "      <td>18.278302</td>\n",
       "      <td>586</td>\n",
       "      <td>11.517296</td>\n",
       "      <td>63.010753</td>\n",
       "      <td>43.4</td>\n",
       "      <td>23.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Canton</td>\n",
       "      <td>10345</td>\n",
       "      <td>992</td>\n",
       "      <td>9.589174</td>\n",
       "      <td>471</td>\n",
       "      <td>4.552924</td>\n",
       "      <td>47.479839</td>\n",
       "      <td>45.2</td>\n",
       "      <td>22.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Chaplin</td>\n",
       "      <td>2262</td>\n",
       "      <td>478</td>\n",
       "      <td>21.131742</td>\n",
       "      <td>276</td>\n",
       "      <td>12.201592</td>\n",
       "      <td>57.740586</td>\n",
       "      <td>42.8</td>\n",
       "      <td>20.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Cheshire</td>\n",
       "      <td>29250</td>\n",
       "      <td>2504</td>\n",
       "      <td>8.560684</td>\n",
       "      <td>1088</td>\n",
       "      <td>3.719658</td>\n",
       "      <td>43.450479</td>\n",
       "      <td>42.9</td>\n",
       "      <td>21.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Chester</td>\n",
       "      <td>4316</td>\n",
       "      <td>599</td>\n",
       "      <td>13.878591</td>\n",
       "      <td>245</td>\n",
       "      <td>5.676552</td>\n",
       "      <td>40.901503</td>\n",
       "      <td>50.9</td>\n",
       "      <td>19.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Clinton</td>\n",
       "      <td>13129</td>\n",
       "      <td>2170</td>\n",
       "      <td>16.528296</td>\n",
       "      <td>1188</td>\n",
       "      <td>9.048671</td>\n",
       "      <td>54.746544</td>\n",
       "      <td>44.2</td>\n",
       "      <td>21.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Colchester</td>\n",
       "      <td>16192</td>\n",
       "      <td>2543</td>\n",
       "      <td>15.705287</td>\n",
       "      <td>1399</td>\n",
       "      <td>8.640069</td>\n",
       "      <td>55.013763</td>\n",
       "      <td>39.2</td>\n",
       "      <td>25.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Colebrook</td>\n",
       "      <td>1445</td>\n",
       "      <td>88</td>\n",
       "      <td>6.089965</td>\n",
       "      <td>51</td>\n",
       "      <td>3.529412</td>\n",
       "      <td>57.954545</td>\n",
       "      <td>47.5</td>\n",
       "      <td>18.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Columbia</td>\n",
       "      <td>5454</td>\n",
       "      <td>643</td>\n",
       "      <td>11.789512</td>\n",
       "      <td>337</td>\n",
       "      <td>6.178951</td>\n",
       "      <td>52.410575</td>\n",
       "      <td>48.0</td>\n",
       "      <td>20.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>Suffield</td>\n",
       "      <td>15814</td>\n",
       "      <td>1221</td>\n",
       "      <td>7.721007</td>\n",
       "      <td>627</td>\n",
       "      <td>3.964841</td>\n",
       "      <td>51.351351</td>\n",
       "      <td>41.5</td>\n",
       "      <td>19.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>Thomaston</td>\n",
       "      <td>7683</td>\n",
       "      <td>1315</td>\n",
       "      <td>17.115710</td>\n",
       "      <td>687</td>\n",
       "      <td>8.941820</td>\n",
       "      <td>52.243346</td>\n",
       "      <td>43.7</td>\n",
       "      <td>20.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>Thompson</td>\n",
       "      <td>9308</td>\n",
       "      <td>1766</td>\n",
       "      <td>18.972927</td>\n",
       "      <td>1023</td>\n",
       "      <td>10.990546</td>\n",
       "      <td>57.927520</td>\n",
       "      <td>43.0</td>\n",
       "      <td>19.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>Tolland</td>\n",
       "      <td>14872</td>\n",
       "      <td>1256</td>\n",
       "      <td>8.445401</td>\n",
       "      <td>691</td>\n",
       "      <td>4.646315</td>\n",
       "      <td>55.015924</td>\n",
       "      <td>43.1</td>\n",
       "      <td>25.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>Torrington</td>\n",
       "      <td>35190</td>\n",
       "      <td>11219</td>\n",
       "      <td>31.881216</td>\n",
       "      <td>6037</td>\n",
       "      <td>17.155442</td>\n",
       "      <td>53.810500</td>\n",
       "      <td>44.3</td>\n",
       "      <td>19.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>Trumbull</td>\n",
       "      <td>36578</td>\n",
       "      <td>3908</td>\n",
       "      <td>10.684018</td>\n",
       "      <td>1936</td>\n",
       "      <td>5.292799</td>\n",
       "      <td>49.539406</td>\n",
       "      <td>43.4</td>\n",
       "      <td>25.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>Union</td>\n",
       "      <td>846</td>\n",
       "      <td>64</td>\n",
       "      <td>7.565012</td>\n",
       "      <td>34</td>\n",
       "      <td>4.018913</td>\n",
       "      <td>53.125000</td>\n",
       "      <td>42.2</td>\n",
       "      <td>22.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>Vernon</td>\n",
       "      <td>29098</td>\n",
       "      <td>7105</td>\n",
       "      <td>24.417486</td>\n",
       "      <td>3853</td>\n",
       "      <td>13.241460</td>\n",
       "      <td>54.229416</td>\n",
       "      <td>40.4</td>\n",
       "      <td>17.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>Voluntown</td>\n",
       "      <td>2593</td>\n",
       "      <td>502</td>\n",
       "      <td>19.359815</td>\n",
       "      <td>327</td>\n",
       "      <td>12.610875</td>\n",
       "      <td>65.139442</td>\n",
       "      <td>44.4</td>\n",
       "      <td>20.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>Wallingford</td>\n",
       "      <td>45074</td>\n",
       "      <td>7216</td>\n",
       "      <td>16.009229</td>\n",
       "      <td>3695</td>\n",
       "      <td>8.197631</td>\n",
       "      <td>51.205654</td>\n",
       "      <td>43.7</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>Warren</td>\n",
       "      <td>1427</td>\n",
       "      <td>117</td>\n",
       "      <td>8.199019</td>\n",
       "      <td>73</td>\n",
       "      <td>5.115627</td>\n",
       "      <td>62.393162</td>\n",
       "      <td>50.3</td>\n",
       "      <td>19.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>Washington</td>\n",
       "      <td>3487</td>\n",
       "      <td>391</td>\n",
       "      <td>11.213077</td>\n",
       "      <td>227</td>\n",
       "      <td>6.509894</td>\n",
       "      <td>58.056266</td>\n",
       "      <td>52.0</td>\n",
       "      <td>14.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>Waterbury</td>\n",
       "      <td>109307</td>\n",
       "      <td>55828</td>\n",
       "      <td>51.074497</td>\n",
       "      <td>33099</td>\n",
       "      <td>30.280769</td>\n",
       "      <td>59.287454</td>\n",
       "      <td>35.1</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>Waterford</td>\n",
       "      <td>19427</td>\n",
       "      <td>3193</td>\n",
       "      <td>16.435888</td>\n",
       "      <td>1577</td>\n",
       "      <td>8.117568</td>\n",
       "      <td>49.389289</td>\n",
       "      <td>47.0</td>\n",
       "      <td>19.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>Watertown</td>\n",
       "      <td>22046</td>\n",
       "      <td>3804</td>\n",
       "      <td>17.254831</td>\n",
       "      <td>1939</td>\n",
       "      <td>8.795246</td>\n",
       "      <td>50.972660</td>\n",
       "      <td>44.5</td>\n",
       "      <td>21.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>West Hartford</td>\n",
       "      <td>63324</td>\n",
       "      <td>9697</td>\n",
       "      <td>15.313309</td>\n",
       "      <td>4785</td>\n",
       "      <td>7.556377</td>\n",
       "      <td>49.345158</td>\n",
       "      <td>41.5</td>\n",
       "      <td>22.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>West Haven</td>\n",
       "      <td>54905</td>\n",
       "      <td>16622</td>\n",
       "      <td>30.274110</td>\n",
       "      <td>10085</td>\n",
       "      <td>18.368090</td>\n",
       "      <td>60.672603</td>\n",
       "      <td>36.3</td>\n",
       "      <td>21.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>Westbrook</td>\n",
       "      <td>6902</td>\n",
       "      <td>1069</td>\n",
       "      <td>15.488264</td>\n",
       "      <td>454</td>\n",
       "      <td>6.577804</td>\n",
       "      <td>42.469598</td>\n",
       "      <td>48.9</td>\n",
       "      <td>15.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>Weston</td>\n",
       "      <td>10388</td>\n",
       "      <td>386</td>\n",
       "      <td>3.715826</td>\n",
       "      <td>229</td>\n",
       "      <td>2.204467</td>\n",
       "      <td>59.326425</td>\n",
       "      <td>43.3</td>\n",
       "      <td>33.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>Westport</td>\n",
       "      <td>27561</td>\n",
       "      <td>1428</td>\n",
       "      <td>5.181234</td>\n",
       "      <td>659</td>\n",
       "      <td>2.391060</td>\n",
       "      <td>46.148459</td>\n",
       "      <td>45.3</td>\n",
       "      <td>28.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>Wethersfield</td>\n",
       "      <td>26446</td>\n",
       "      <td>4039</td>\n",
       "      <td>15.272631</td>\n",
       "      <td>2058</td>\n",
       "      <td>7.781895</td>\n",
       "      <td>50.953206</td>\n",
       "      <td>45.3</td>\n",
       "      <td>19.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>Willington</td>\n",
       "      <td>5934</td>\n",
       "      <td>733</td>\n",
       "      <td>12.352545</td>\n",
       "      <td>417</td>\n",
       "      <td>7.027300</td>\n",
       "      <td>56.889495</td>\n",
       "      <td>36.4</td>\n",
       "      <td>18.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>Wilton</td>\n",
       "      <td>18692</td>\n",
       "      <td>1104</td>\n",
       "      <td>5.906270</td>\n",
       "      <td>436</td>\n",
       "      <td>2.332549</td>\n",
       "      <td>39.492754</td>\n",
       "      <td>42.2</td>\n",
       "      <td>31.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>Windham</td>\n",
       "      <td>25005</td>\n",
       "      <td>10163</td>\n",
       "      <td>40.643871</td>\n",
       "      <td>5682</td>\n",
       "      <td>22.723455</td>\n",
       "      <td>55.908688</td>\n",
       "      <td>30.7</td>\n",
       "      <td>19.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>Windsor</td>\n",
       "      <td>29069</td>\n",
       "      <td>5638</td>\n",
       "      <td>19.395232</td>\n",
       "      <td>2978</td>\n",
       "      <td>10.244590</td>\n",
       "      <td>52.820149</td>\n",
       "      <td>42.5</td>\n",
       "      <td>21.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>Windsor Locks</td>\n",
       "      <td>12565</td>\n",
       "      <td>2545</td>\n",
       "      <td>20.254676</td>\n",
       "      <td>1557</td>\n",
       "      <td>12.391564</td>\n",
       "      <td>61.178782</td>\n",
       "      <td>42.1</td>\n",
       "      <td>17.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>Wolcott</td>\n",
       "      <td>16716</td>\n",
       "      <td>2606</td>\n",
       "      <td>15.589854</td>\n",
       "      <td>1298</td>\n",
       "      <td>7.765016</td>\n",
       "      <td>49.808135</td>\n",
       "      <td>44.7</td>\n",
       "      <td>21.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>Woodbridge</td>\n",
       "      <td>8925</td>\n",
       "      <td>714</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>343</td>\n",
       "      <td>3.843137</td>\n",
       "      <td>48.039216</td>\n",
       "      <td>47.4</td>\n",
       "      <td>23.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>Woodbury</td>\n",
       "      <td>9719</td>\n",
       "      <td>1021</td>\n",
       "      <td>10.505196</td>\n",
       "      <td>511</td>\n",
       "      <td>5.257743</td>\n",
       "      <td>50.048972</td>\n",
       "      <td>47.6</td>\n",
       "      <td>21.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>Woodstock</td>\n",
       "      <td>7860</td>\n",
       "      <td>1062</td>\n",
       "      <td>13.511450</td>\n",
       "      <td>674</td>\n",
       "      <td>8.575064</td>\n",
       "      <td>63.465160</td>\n",
       "      <td>44.5</td>\n",
       "      <td>23.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>168 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              town  population  med_enrollment    med_pct  husky_enrollment  \\\n",
       "0          Andover        3272             395  12.072127               226   \n",
       "1          Ansonia       18959            6099  32.169418              3810   \n",
       "2          Ashford        4259             888  20.849965               562   \n",
       "3             Avon       18421            1208   6.557733               512   \n",
       "4      Barkhamsted        3705             483  13.036437               283   \n",
       "5     Beacon Falls        6055             805  13.294798               431   \n",
       "6           Berlin       20610            2363  11.465308              1123   \n",
       "7          Bethany        5531             419   7.575484               181   \n",
       "8           Bethel       19372            2731  14.097667              1596   \n",
       "9        Bethlehem        3501             481  13.738932               278   \n",
       "10      Bloomfield       20819            4894  23.507373              2185   \n",
       "11          Bolton        4952             532  10.743134               300   \n",
       "12          Bozrah        2622             441  16.819222               225   \n",
       "13        Branford       28225            4522  16.021258              2132   \n",
       "14      Bridgeport      147612           66854  45.290356             40457   \n",
       "15     Bridgewater        1675             121   7.223881                59   \n",
       "16         Bristol       60570           16295  26.902757              9528   \n",
       "17      Brookfield       17055            1728  10.131926               984   \n",
       "18        Brooklyn        8254            1763  21.359341               934   \n",
       "19      Burlington        9576             738   7.706767               465   \n",
       "20          Canaan        1195             760  63.598326               393   \n",
       "21      Canterbury        5088             930  18.278302               586   \n",
       "22          Canton       10345             992   9.589174               471   \n",
       "23         Chaplin        2262             478  21.131742               276   \n",
       "24        Cheshire       29250            2504   8.560684              1088   \n",
       "25         Chester        4316             599  13.878591               245   \n",
       "26         Clinton       13129            2170  16.528296              1188   \n",
       "27      Colchester       16192            2543  15.705287              1399   \n",
       "28       Colebrook        1445              88   6.089965                51   \n",
       "29        Columbia        5454             643  11.789512               337   \n",
       "..             ...         ...             ...        ...               ...   \n",
       "138       Suffield       15814            1221   7.721007               627   \n",
       "139      Thomaston        7683            1315  17.115710               687   \n",
       "140       Thompson        9308            1766  18.972927              1023   \n",
       "141        Tolland       14872            1256   8.445401               691   \n",
       "142     Torrington       35190           11219  31.881216              6037   \n",
       "143       Trumbull       36578            3908  10.684018              1936   \n",
       "144          Union         846              64   7.565012                34   \n",
       "145         Vernon       29098            7105  24.417486              3853   \n",
       "146      Voluntown        2593             502  19.359815               327   \n",
       "147    Wallingford       45074            7216  16.009229              3695   \n",
       "148         Warren        1427             117   8.199019                73   \n",
       "149     Washington        3487             391  11.213077               227   \n",
       "150      Waterbury      109307           55828  51.074497             33099   \n",
       "151      Waterford       19427            3193  16.435888              1577   \n",
       "152      Watertown       22046            3804  17.254831              1939   \n",
       "153  West Hartford       63324            9697  15.313309              4785   \n",
       "154     West Haven       54905           16622  30.274110             10085   \n",
       "155      Westbrook        6902            1069  15.488264               454   \n",
       "156         Weston       10388             386   3.715826               229   \n",
       "157       Westport       27561            1428   5.181234               659   \n",
       "158   Wethersfield       26446            4039  15.272631              2058   \n",
       "159     Willington        5934             733  12.352545               417   \n",
       "160         Wilton       18692            1104   5.906270               436   \n",
       "161        Windham       25005           10163  40.643871              5682   \n",
       "162        Windsor       29069            5638  19.395232              2978   \n",
       "163  Windsor Locks       12565            2545  20.254676              1557   \n",
       "164        Wolcott       16716            2606  15.589854              1298   \n",
       "165     Woodbridge        8925             714   8.000000               343   \n",
       "166       Woodbury        9719            1021  10.505196               511   \n",
       "167      Woodstock        7860            1062  13.511450               674   \n",
       "\n",
       "     husky_pct  husky_share  median_age  under_18  \n",
       "0     6.907090    57.215190        43.0      23.3  \n",
       "1    20.095997    62.469257        40.8      22.8  \n",
       "2    13.195586    63.288288        41.1      20.7  \n",
       "3     2.779437    42.384106        45.8      25.1  \n",
       "4     7.638327    58.592133        44.7      22.0  \n",
       "5     7.118084    53.540373        43.0      22.1  \n",
       "6     5.448811    47.524333        45.7      21.2  \n",
       "7     3.272464    43.198091        44.6      24.0  \n",
       "8     8.238695    58.440132        42.3      22.8  \n",
       "9     7.940588    57.796258        48.9      20.8  \n",
       "10   10.495221    44.646506        48.2      14.6  \n",
       "11    6.058158    56.390977        47.2      20.5  \n",
       "12    8.581236    51.020408        46.5      20.0  \n",
       "13    7.553587    47.147280        48.0      17.5  \n",
       "14   27.407663    60.515452        32.3      25.2  \n",
       "15    3.522388    48.760331        52.3      19.5  \n",
       "16   15.730560    58.471924        41.4      20.7  \n",
       "17    5.769569    56.944444        44.6      23.0  \n",
       "18   11.315726    52.977879        42.3      22.7  \n",
       "19    4.855890    63.008130        42.4      26.6  \n",
       "20   32.887029    51.710526        51.4      16.2  \n",
       "21   11.517296    63.010753        43.4      23.1  \n",
       "22    4.552924    47.479839        45.2      22.7  \n",
       "23   12.201592    57.740586        42.8      20.1  \n",
       "24    3.719658    43.450479        42.9      21.6  \n",
       "25    5.676552    40.901503        50.9      19.2  \n",
       "26    9.048671    54.746544        44.2      21.1  \n",
       "27    8.640069    55.013763        39.2      25.3  \n",
       "28    3.529412    57.954545        47.5      18.1  \n",
       "29    6.178951    52.410575        48.0      20.7  \n",
       "..         ...          ...         ...       ...  \n",
       "138   3.964841    51.351351        41.5      19.8  \n",
       "139   8.941820    52.243346        43.7      20.3  \n",
       "140  10.990546    57.927520        43.0      19.7  \n",
       "141   4.646315    55.015924        43.1      25.6  \n",
       "142  17.155442    53.810500        44.3      19.3  \n",
       "143   5.292799    49.539406        43.4      25.1  \n",
       "144   4.018913    53.125000        42.2      22.8  \n",
       "145  13.241460    54.229416        40.4      17.5  \n",
       "146  12.610875    65.139442        44.4      20.6  \n",
       "147   8.197631    51.205654        43.7      20.0  \n",
       "148   5.115627    62.393162        50.3      19.1  \n",
       "149   6.509894    58.056266        52.0      14.5  \n",
       "150  30.280769    59.287454        35.1      25.0  \n",
       "151   8.117568    49.389289        47.0      19.8  \n",
       "152   8.795246    50.972660        44.5      21.4  \n",
       "153   7.556377    49.345158        41.5      22.6  \n",
       "154  18.368090    60.672603        36.3      21.5  \n",
       "155   6.577804    42.469598        48.9      15.7  \n",
       "156   2.204467    59.326425        43.3      33.4  \n",
       "157   2.391060    46.148459        45.3      28.6  \n",
       "158   7.781895    50.953206        45.3      19.9  \n",
       "159   7.027300    56.889495        36.4      18.9  \n",
       "160   2.332549    39.492754        42.2      31.4  \n",
       "161  22.723455    55.908688        30.7      19.1  \n",
       "162  10.244590    52.820149        42.5      21.7  \n",
       "163  12.391564    61.178782        42.1      17.9  \n",
       "164   7.765016    49.808135        44.7      21.4  \n",
       "165   3.843137    48.039216        47.4      23.8  \n",
       "166   5.257743    50.048972        47.6      21.3  \n",
       "167   8.575064    63.465160        44.5      23.7  \n",
       "\n",
       "[168 rows x 9 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "def hashed_json(dataset,index):\n",
    "    dataset=json.loads(dataset.to_json(orient=\"records\"))\n",
    "    ret = {}\n",
    "    for record in dataset:\n",
    "        ret[record[index]] = record\n",
    "        del (ret[record[index]][index])\n",
    "    return ret\n",
    "\n",
    "outfh = open (\"medicaid.json\",\"w\")\n",
    "\n",
    "outfh.write(json.dumps(hashed_json(combined,\"town\")))\n",
    "\n",
    "outfh.close()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}