SAT Scores Analysis-2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_data(year=\"2015-16\"):\n",
    "    \n",
    "    ret = pd.read_csv(\"data/CTSchoolDaySAT\" + year + \".csv\",skiprows=3,header=[0,1])\n",
    "    \n",
    "    ret.columns = ['District',\n",
    "                   'District Code',\n",
    "                   'Subject',\n",
    "                   'Total Number of Students',\n",
    "                   'Total Number Tested',\n",
    "                   'Connecticut School Day SAT Participation Rate',\n",
    "                   'Total Numberwith Scored Tests',\n",
    "                   'Level 1 Not Met Count',\n",
    "                   'Level 1 Not Met Count %',\n",
    "                   'Level 2 Approaching Count',\n",
    "                   'Level 2 Approaching %',\n",
    "                   'Level 3 Met Count',\n",
    "                   'Level 3 Met Count %',\n",
    "                   'Level 4 Exceeded Count',\n",
    "                   'Level 4 Exceeded Count %',\n",
    "                   'Level 3&4 Met or Exceeded Count',\n",
    "                   'Level 3&4 Met or Exceeded %',\n",
    "                   'Average Score']\n",
    "    \n",
    "    ret[\"year\"] = year\n",
    "    \n",
    "    \n",
    "    ret = ret.set_index([\"District\",\"year\",\"Subject\"])\n",
    "    \n",
    "    return ret\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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></th>\n",
       "      <th></th>\n",
       "      <th>District Code</th>\n",
       "      <th>Total Number of Students</th>\n",
       "      <th>Total Number Tested</th>\n",
       "      <th>Connecticut School Day SAT Participation Rate</th>\n",
       "      <th>Total Numberwith Scored Tests</th>\n",
       "      <th>Level 1 Not Met Count</th>\n",
       "      <th>Level 1 Not Met Count %</th>\n",
       "      <th>Level 2 Approaching Count</th>\n",
       "      <th>Level 2 Approaching %</th>\n",
       "      <th>Level 3 Met Count</th>\n",
       "      <th>Level 3 Met Count %</th>\n",
       "      <th>Level 4 Exceeded Count</th>\n",
       "      <th>Level 4 Exceeded Count %</th>\n",
       "      <th>Level 3&amp;4 Met or Exceeded Count</th>\n",
       "      <th>Level 3&amp;4 Met or Exceeded %</th>\n",
       "      <th>Average Score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>District</th>\n",
       "      <th>year</th>\n",
       "      <th>Subject</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Achievement First Hartford Academy Inc. District</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">2016-17</th>\n",
       "      <th>ELA</th>\n",
       "      <td>=\"2880013\"</td>\n",
       "      <td>39</td>\n",
       "      <td>39</td>\n",
       "      <td>100.0</td>\n",
       "      <td>39</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>10</td>\n",
       "      <td>25.6</td>\n",
       "      <td>22</td>\n",
       "      <td>56.4</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Math</th>\n",
       "      <td>=\"2880013\"</td>\n",
       "      <td>39</td>\n",
       "      <td>39</td>\n",
       "      <td>100.0</td>\n",
       "      <td>38</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>20</td>\n",
       "      <td>52.6</td>\n",
       "      <td>13</td>\n",
       "      <td>34.2</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Amistad Academy District</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">2016-17</th>\n",
       "      <th>ELA</th>\n",
       "      <td>=\"2790013\"</td>\n",
       "      <td>50</td>\n",
       "      <td>48</td>\n",
       "      <td>96.0</td>\n",
       "      <td>47</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>9</td>\n",
       "      <td>19.1</td>\n",
       "      <td>33</td>\n",
       "      <td>70.2</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>511</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Math</th>\n",
       "      <td>=\"2790013\"</td>\n",
       "      <td>50</td>\n",
       "      <td>48</td>\n",
       "      <td>96.0</td>\n",
       "      <td>47</td>\n",
       "      <td>10</td>\n",
       "      <td>21.3</td>\n",
       "      <td>28</td>\n",
       "      <td>59.6</td>\n",
       "      <td>9</td>\n",
       "      <td>19.1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>19.1</td>\n",
       "      <td>467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Ansonia School District</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">2016-17</th>\n",
       "      <th>ELA</th>\n",
       "      <td>=\"0020011\"</td>\n",
       "      <td>134</td>\n",
       "      <td>127</td>\n",
       "      <td>94.8</td>\n",
       "      <td>127</td>\n",
       "      <td>38</td>\n",
       "      <td>29.9</td>\n",
       "      <td>26</td>\n",
       "      <td>20.5</td>\n",
       "      <td>52</td>\n",
       "      <td>40.9</td>\n",
       "      <td>11</td>\n",
       "      <td>8.7</td>\n",
       "      <td>63</td>\n",
       "      <td>49.6</td>\n",
       "      <td>478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Math</th>\n",
       "      <td>=\"0020011\"</td>\n",
       "      <td>134</td>\n",
       "      <td>127</td>\n",
       "      <td>94.8</td>\n",
       "      <td>126</td>\n",
       "      <td>57</td>\n",
       "      <td>45.2</td>\n",
       "      <td>48</td>\n",
       "      <td>38.1</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>21</td>\n",
       "      <td>16.7</td>\n",
       "      <td>444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Area Cooperative Educational Services</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">2016-17</th>\n",
       "      <th>ELA</th>\n",
       "      <td>=\"2440014\"</td>\n",
       "      <td>18</td>\n",
       "      <td>15</td>\n",
       "      <td>*</td>\n",
       "      <td>15</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",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Math</th>\n",
       "      <td>=\"2440014\"</td>\n",
       "      <td>18</td>\n",
       "      <td>15</td>\n",
       "      <td>*</td>\n",
       "      <td>13</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",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Avon School District</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">2016-17</th>\n",
       "      <th>ELA</th>\n",
       "      <td>=\"0040011\"</td>\n",
       "      <td>285</td>\n",
       "      <td>282</td>\n",
       "      <td>98.9</td>\n",
       "      <td>282</td>\n",
       "      <td>14</td>\n",
       "      <td>5.0</td>\n",
       "      <td>19</td>\n",
       "      <td>6.7</td>\n",
       "      <td>120</td>\n",
       "      <td>42.6</td>\n",
       "      <td>129</td>\n",
       "      <td>45.7</td>\n",
       "      <td>249</td>\n",
       "      <td>88.3</td>\n",
       "      <td>603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Math</th>\n",
       "      <td>=\"0040011\"</td>\n",
       "      <td>285</td>\n",
       "      <td>282</td>\n",
       "      <td>98.9</td>\n",
       "      <td>282</td>\n",
       "      <td>13</td>\n",
       "      <td>4.6</td>\n",
       "      <td>46</td>\n",
       "      <td>16.3</td>\n",
       "      <td>104</td>\n",
       "      <td>36.9</td>\n",
       "      <td>119</td>\n",
       "      <td>42.2</td>\n",
       "      <td>223</td>\n",
       "      <td>79.1</td>\n",
       "      <td>611</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                 District Code  \\\n",
       "District                                         year    Subject                 \n",
       "Achievement First Hartford Academy Inc. District 2016-17 ELA        =\"2880013\"   \n",
       "                                                         Math       =\"2880013\"   \n",
       "Amistad Academy District                         2016-17 ELA        =\"2790013\"   \n",
       "                                                         Math       =\"2790013\"   \n",
       "Ansonia School District                          2016-17 ELA        =\"0020011\"   \n",
       "                                                         Math       =\"0020011\"   \n",
       "Area Cooperative Educational Services            2016-17 ELA        =\"2440014\"   \n",
       "                                                         Math       =\"2440014\"   \n",
       "Avon School District                             2016-17 ELA        =\"0040011\"   \n",
       "                                                         Math       =\"0040011\"   \n",
       "\n",
       "                                                                 Total Number of Students  \\\n",
       "District                                         year    Subject                            \n",
       "Achievement First Hartford Academy Inc. District 2016-17 ELA                           39   \n",
       "                                                         Math                          39   \n",
       "Amistad Academy District                         2016-17 ELA                           50   \n",
       "                                                         Math                          50   \n",
       "Ansonia School District                          2016-17 ELA                          134   \n",
       "                                                         Math                         134   \n",
       "Area Cooperative Educational Services            2016-17 ELA                           18   \n",
       "                                                         Math                          18   \n",
       "Avon School District                             2016-17 ELA                          285   \n",
       "                                                         Math                         285   \n",
       "\n",
       "                                                                 Total Number Tested  \\\n",
       "District                                         year    Subject                       \n",
       "Achievement First Hartford Academy Inc. District 2016-17 ELA                      39   \n",
       "                                                         Math                     39   \n",
       "Amistad Academy District                         2016-17 ELA                      48   \n",
       "                                                         Math                     48   \n",
       "Ansonia School District                          2016-17 ELA                     127   \n",
       "                                                         Math                    127   \n",
       "Area Cooperative Educational Services            2016-17 ELA                      15   \n",
       "                                                         Math                     15   \n",
       "Avon School District                             2016-17 ELA                     282   \n",
       "                                                         Math                    282   \n",
       "\n",
       "                                                                 Connecticut School Day SAT Participation Rate  \\\n",
       "District                                         year    Subject                                                 \n",
       "Achievement First Hartford Academy Inc. District 2016-17 ELA                                             100.0   \n",
       "                                                         Math                                            100.0   \n",
       "Amistad Academy District                         2016-17 ELA                                              96.0   \n",
       "                                                         Math                                             96.0   \n",
       "Ansonia School District                          2016-17 ELA                                              94.8   \n",
       "                                                         Math                                             94.8   \n",
       "Area Cooperative Educational Services            2016-17 ELA                                                 *   \n",
       "                                                         Math                                                *   \n",
       "Avon School District                             2016-17 ELA                                              98.9   \n",
       "                                                         Math                                             98.9   \n",
       "\n",
       "                                                                 Total Numberwith Scored Tests  \\\n",
       "District                                         year    Subject                                 \n",
       "Achievement First Hartford Academy Inc. District 2016-17 ELA                                39   \n",
       "                                                         Math                               38   \n",
       "Amistad Academy District                         2016-17 ELA                                47   \n",
       "                                                         Math                               47   \n",
       "Ansonia School District                          2016-17 ELA                               127   \n",
       "                                                         Math                              126   \n",
       "Area Cooperative Educational Services            2016-17 ELA                                15   \n",
       "                                                         Math                               13   \n",
       "Avon School District                             2016-17 ELA                               282   \n",
       "                                                         Math                              282   \n",
       "\n",
       "                                                                 Level 1 Not Met Count  \\\n",
       "District                                         year    Subject                         \n",
       "Achievement First Hartford Academy Inc. District 2016-17 ELA                         *   \n",
       "                                                         Math                        *   \n",
       "Amistad Academy District                         2016-17 ELA                         *   \n",
       "                                                         Math                       10   \n",
       "Ansonia School District                          2016-17 ELA                        38   \n",
       "                                                         Math                       57   \n",
       "Area Cooperative Educational Services            2016-17 ELA                         *   \n",
       "                                                         Math                        *   \n",
       "Avon School District                             2016-17 ELA                        14   \n",
       "                                                         Math                       13   \n",
       "\n",
       "                                                                 Level 1 Not Met Count %  \\\n",
       "District                                         year    Subject                           \n",
       "Achievement First Hartford Academy Inc. District 2016-17 ELA                           *   \n",
       "                                                         Math                          *   \n",
       "Amistad Academy District                         2016-17 ELA                           *   \n",
       "                                                         Math                       21.3   \n",
       "Ansonia School District                          2016-17 ELA                        29.9   \n",
       "                                                         Math                       45.2   \n",
       "Area Cooperative Educational Services            2016-17 ELA                           *   \n",
       "                                                         Math                          *   \n",
       "Avon School District                             2016-17 ELA                         5.0   \n",
       "                                                         Math                        4.6   \n",
       "\n",
       "                                                                 Level 2 Approaching Count  \\\n",
       "District                                         year    Subject                             \n",
       "Achievement First Hartford Academy Inc. District 2016-17 ELA                            10   \n",
       "                                                         Math                           20   \n",
       "Amistad Academy District                         2016-17 ELA                             9   \n",
       "                                                         Math                           28   \n",
       "Ansonia School District                          2016-17 ELA                            26   \n",
       "                                                         Math                           48   \n",
       "Area Cooperative Educational Services            2016-17 ELA                             *   \n",
       "                                                         Math                            *   \n",
       "Avon School District                             2016-17 ELA                            19   \n",
       "                                                         Math                           46   \n",
       "\n",
       "                                                                 Level 2 Approaching %  \\\n",
       "District                                         year    Subject                         \n",
       "Achievement First Hartford Academy Inc. District 2016-17 ELA                      25.6   \n",
       "                                                         Math                     52.6   \n",
       "Amistad Academy District                         2016-17 ELA                      19.1   \n",
       "                                                         Math                     59.6   \n",
       "Ansonia School District                          2016-17 ELA                      20.5   \n",
       "                                                         Math                     38.1   \n",
       "Area Cooperative Educational Services            2016-17 ELA                         *   \n",
       "                                                         Math                        *   \n",
       "Avon School District                             2016-17 ELA                       6.7   \n",
       "                                                         Math                     16.3   \n",
       "\n",
       "                                                                 Level 3 Met Count  \\\n",
       "District                                         year    Subject                     \n",
       "Achievement First Hartford Academy Inc. District 2016-17 ELA                    22   \n",
       "                                                         Math                   13   \n",
       "Amistad Academy District                         2016-17 ELA                    33   \n",
       "                                                         Math                    9   \n",
       "Ansonia School District                          2016-17 ELA                    52   \n",
       "                                                         Math                    *   \n",
       "Area Cooperative Educational Services            2016-17 ELA                     *   \n",
       "                                                         Math                    *   \n",
       "Avon School District                             2016-17 ELA                   120   \n",
       "                                                         Math                  104   \n",
       "\n",
       "                                                                 Level 3 Met Count %  \\\n",
       "District                                         year    Subject                       \n",
       "Achievement First Hartford Academy Inc. District 2016-17 ELA                    56.4   \n",
       "                                                         Math                   34.2   \n",
       "Amistad Academy District                         2016-17 ELA                    70.2   \n",
       "                                                         Math                   19.1   \n",
       "Ansonia School District                          2016-17 ELA                    40.9   \n",
       "                                                         Math                      *   \n",
       "Area Cooperative Educational Services            2016-17 ELA                       *   \n",
       "                                                         Math                      *   \n",
       "Avon School District                             2016-17 ELA                    42.6   \n",
       "                                                         Math                   36.9   \n",
       "\n",
       "                                                                 Level 4 Exceeded Count  \\\n",
       "District                                         year    Subject                          \n",
       "Achievement First Hartford Academy Inc. District 2016-17 ELA                          *   \n",
       "                                                         Math                         *   \n",
       "Amistad Academy District                         2016-17 ELA                          *   \n",
       "                                                         Math                         0   \n",
       "Ansonia School District                          2016-17 ELA                         11   \n",
       "                                                         Math                         *   \n",
       "Area Cooperative Educational Services            2016-17 ELA                          *   \n",
       "                                                         Math                         *   \n",
       "Avon School District                             2016-17 ELA                        129   \n",
       "                                                         Math                       119   \n",
       "\n",
       "                                                                 Level 4 Exceeded Count %  \\\n",
       "District                                         year    Subject                            \n",
       "Achievement First Hartford Academy Inc. District 2016-17 ELA                            *   \n",
       "                                                         Math                           *   \n",
       "Amistad Academy District                         2016-17 ELA                            *   \n",
       "                                                         Math                         0.0   \n",
       "Ansonia School District                          2016-17 ELA                          8.7   \n",
       "                                                         Math                           *   \n",
       "Area Cooperative Educational Services            2016-17 ELA                            *   \n",
       "                                                         Math                           *   \n",
       "Avon School District                             2016-17 ELA                         45.7   \n",
       "                                                         Math                        42.2   \n",
       "\n",
       "                                                                 Level 3&4 Met or Exceeded Count  \\\n",
       "District                                         year    Subject                                   \n",
       "Achievement First Hartford Academy Inc. District 2016-17 ELA                                   *   \n",
       "                                                         Math                                  *   \n",
       "Amistad Academy District                         2016-17 ELA                                   *   \n",
       "                                                         Math                                  9   \n",
       "Ansonia School District                          2016-17 ELA                                  63   \n",
       "                                                         Math                                 21   \n",
       "Area Cooperative Educational Services            2016-17 ELA                                   *   \n",
       "                                                         Math                                  *   \n",
       "Avon School District                             2016-17 ELA                                 249   \n",
       "                                                         Math                                223   \n",
       "\n",
       "                                                                 Level 3&4 Met or Exceeded %  \\\n",
       "District                                         year    Subject                               \n",
       "Achievement First Hartford Academy Inc. District 2016-17 ELA                               *   \n",
       "                                                         Math                              *   \n",
       "Amistad Academy District                         2016-17 ELA                               *   \n",
       "                                                         Math                           19.1   \n",
       "Ansonia School District                          2016-17 ELA                            49.6   \n",
       "                                                         Math                           16.7   \n",
       "Area Cooperative Educational Services            2016-17 ELA                               *   \n",
       "                                                         Math                              *   \n",
       "Avon School District                             2016-17 ELA                            88.3   \n",
       "                                                         Math                           79.1   \n",
       "\n",
       "                                                                 Average Score  \n",
       "District                                         year    Subject                \n",
       "Achievement First Hartford Academy Inc. District 2016-17 ELA               488  \n",
       "                                                         Math              494  \n",
       "Amistad Academy District                         2016-17 ELA               511  \n",
       "                                                         Math              467  \n",
       "Ansonia School District                          2016-17 ELA               478  \n",
       "                                                         Math              444  \n",
       "Area Cooperative Educational Services            2016-17 ELA                 *  \n",
       "                                                         Math                *  \n",
       "Avon School District                             2016-17 ELA               603  \n",
       "                                                         Math              611  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scores_17 = get_data(year=\"2016-17\")\n",
    "scores_16 = get_data(year=\"2015-16\")\n",
    "scores_17.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ True,  True,  True,  True,  True,  True,  True,  True,  True,\n",
       "        True,  True,  True,  True,  True,  True,  True], dtype=bool)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# test that both sheets have the same column names\n",
    "scores_17.columns == scores_16.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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></th>\n",
       "      <th></th>\n",
       "      <th>District Code</th>\n",
       "      <th>Total Number of Students</th>\n",
       "      <th>Total Number Tested</th>\n",
       "      <th>Connecticut School Day SAT Participation Rate</th>\n",
       "      <th>Total Numberwith Scored Tests</th>\n",
       "      <th>Level 1 Not Met Count</th>\n",
       "      <th>Level 1 Not Met Count %</th>\n",
       "      <th>Level 2 Approaching Count</th>\n",
       "      <th>Level 2 Approaching %</th>\n",
       "      <th>Level 3 Met Count</th>\n",
       "      <th>Level 3 Met Count %</th>\n",
       "      <th>Level 4 Exceeded Count</th>\n",
       "      <th>Level 4 Exceeded Count %</th>\n",
       "      <th>Level 3&amp;4 Met or Exceeded Count</th>\n",
       "      <th>Level 3&amp;4 Met or Exceeded %</th>\n",
       "      <th>Average Score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>District</th>\n",
       "      <th>year</th>\n",
       "      <th>Subject</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Achievement First Hartford Academy Inc. District</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">2015-16</th>\n",
       "      <th>ELA</th>\n",
       "      <td>=\"2880013\"</td>\n",
       "      <td>32</td>\n",
       "      <td>30</td>\n",
       "      <td>93.8</td>\n",
       "      <td>30</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>7</td>\n",
       "      <td>23.3</td>\n",
       "      <td>17</td>\n",
       "      <td>56.7</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Math</th>\n",
       "      <td>=\"2880013\"</td>\n",
       "      <td>32</td>\n",
       "      <td>30</td>\n",
       "      <td>93.8</td>\n",
       "      <td>30</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>13</td>\n",
       "      <td>43.3</td>\n",
       "      <td>14</td>\n",
       "      <td>46.7</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Amistad Academy District</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">2015-16</th>\n",
       "      <th>ELA</th>\n",
       "      <td>=\"2790013\"</td>\n",
       "      <td>45</td>\n",
       "      <td>44</td>\n",
       "      <td>97.8</td>\n",
       "      <td>44</td>\n",
       "      <td>6</td>\n",
       "      <td>13.6</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>28</td>\n",
       "      <td>63.6</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Math</th>\n",
       "      <td>=\"2790013\"</td>\n",
       "      <td>45</td>\n",
       "      <td>44</td>\n",
       "      <td>97.8</td>\n",
       "      <td>44</td>\n",
       "      <td>11</td>\n",
       "      <td>25.0</td>\n",
       "      <td>23</td>\n",
       "      <td>52.3</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>10</td>\n",
       "      <td>22.7</td>\n",
       "      <td>470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ansonia School District</th>\n",
       "      <th>2015-16</th>\n",
       "      <th>ELA</th>\n",
       "      <td>=\"0020011\"</td>\n",
       "      <td>151</td>\n",
       "      <td>138</td>\n",
       "      <td>91.4</td>\n",
       "      <td>138</td>\n",
       "      <td>37</td>\n",
       "      <td>26.8</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>62</td>\n",
       "      <td>44.9</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>*</td>\n",
       "      <td>476</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                 District Code  \\\n",
       "District                                         year    Subject                 \n",
       "Achievement First Hartford Academy Inc. District 2015-16 ELA        =\"2880013\"   \n",
       "                                                         Math       =\"2880013\"   \n",
       "Amistad Academy District                         2015-16 ELA        =\"2790013\"   \n",
       "                                                         Math       =\"2790013\"   \n",
       "Ansonia School District                          2015-16 ELA        =\"0020011\"   \n",
       "\n",
       "                                                                 Total Number of Students  \\\n",
       "District                                         year    Subject                            \n",
       "Achievement First Hartford Academy Inc. District 2015-16 ELA                           32   \n",
       "                                                         Math                          32   \n",
       "Amistad Academy District                         2015-16 ELA                           45   \n",
       "                                                         Math                          45   \n",
       "Ansonia School District                          2015-16 ELA                          151   \n",
       "\n",
       "                                                                 Total Number Tested  \\\n",
       "District                                         year    Subject                       \n",
       "Achievement First Hartford Academy Inc. District 2015-16 ELA                      30   \n",
       "                                                         Math                     30   \n",
       "Amistad Academy District                         2015-16 ELA                      44   \n",
       "                                                         Math                     44   \n",
       "Ansonia School District                          2015-16 ELA                     138   \n",
       "\n",
       "                                                                 Connecticut School Day SAT Participation Rate  \\\n",
       "District                                         year    Subject                                                 \n",
       "Achievement First Hartford Academy Inc. District 2015-16 ELA                                              93.8   \n",
       "                                                         Math                                             93.8   \n",
       "Amistad Academy District                         2015-16 ELA                                              97.8   \n",
       "                                                         Math                                             97.8   \n",
       "Ansonia School District                          2015-16 ELA                                              91.4   \n",
       "\n",
       "                                                                 Total Numberwith Scored Tests  \\\n",
       "District                                         year    Subject                                 \n",
       "Achievement First Hartford Academy Inc. District 2015-16 ELA                                30   \n",
       "                                                         Math                               30   \n",
       "Amistad Academy District                         2015-16 ELA                                44   \n",
       "                                                         Math                               44   \n",
       "Ansonia School District                          2015-16 ELA                               138   \n",
       "\n",
       "                                                                 Level 1 Not Met Count  \\\n",
       "District                                         year    Subject                         \n",
       "Achievement First Hartford Academy Inc. District 2015-16 ELA                         *   \n",
       "                                                         Math                        *   \n",
       "Amistad Academy District                         2015-16 ELA                         6   \n",
       "                                                         Math                       11   \n",
       "Ansonia School District                          2015-16 ELA                        37   \n",
       "\n",
       "                                                                 Level 1 Not Met Count %  \\\n",
       "District                                         year    Subject                           \n",
       "Achievement First Hartford Academy Inc. District 2015-16 ELA                           *   \n",
       "                                                         Math                          *   \n",
       "Amistad Academy District                         2015-16 ELA                        13.6   \n",
       "                                                         Math                       25.0   \n",
       "Ansonia School District                          2015-16 ELA                        26.8   \n",
       "\n",
       "                                                                 Level 2 Approaching Count  \\\n",
       "District                                         year    Subject                             \n",
       "Achievement First Hartford Academy Inc. District 2015-16 ELA                             7   \n",
       "                                                         Math                           13   \n",
       "Amistad Academy District                         2015-16 ELA                             *   \n",
       "                                                         Math                           23   \n",
       "Ansonia School District                          2015-16 ELA                             *   \n",
       "\n",
       "                                                                 Level 2 Approaching %  \\\n",
       "District                                         year    Subject                         \n",
       "Achievement First Hartford Academy Inc. District 2015-16 ELA                      23.3   \n",
       "                                                         Math                     43.3   \n",
       "Amistad Academy District                         2015-16 ELA                         *   \n",
       "                                                         Math                     52.3   \n",
       "Ansonia School District                          2015-16 ELA                         *   \n",
       "\n",
       "                                                                 Level 3 Met Count  \\\n",
       "District                                         year    Subject                     \n",
       "Achievement First Hartford Academy Inc. District 2015-16 ELA                    17   \n",
       "                                                         Math                   14   \n",
       "Amistad Academy District                         2015-16 ELA                    28   \n",
       "                                                         Math                    *   \n",
       "Ansonia School District                          2015-16 ELA                    62   \n",
       "\n",
       "                                                                 Level 3 Met Count %  \\\n",
       "District                                         year    Subject                       \n",
       "Achievement First Hartford Academy Inc. District 2015-16 ELA                    56.7   \n",
       "                                                         Math                   46.7   \n",
       "Amistad Academy District                         2015-16 ELA                    63.6   \n",
       "                                                         Math                      *   \n",
       "Ansonia School District                          2015-16 ELA                    44.9   \n",
       "\n",
       "                                                                 Level 4 Exceeded Count  \\\n",
       "District                                         year    Subject                          \n",
       "Achievement First Hartford Academy Inc. District 2015-16 ELA                          *   \n",
       "                                                         Math                         *   \n",
       "Amistad Academy District                         2015-16 ELA                          *   \n",
       "                                                         Math                         *   \n",
       "Ansonia School District                          2015-16 ELA                          *   \n",
       "\n",
       "                                                                 Level 4 Exceeded Count %  \\\n",
       "District                                         year    Subject                            \n",
       "Achievement First Hartford Academy Inc. District 2015-16 ELA                            *   \n",
       "                                                         Math                           *   \n",
       "Amistad Academy District                         2015-16 ELA                            *   \n",
       "                                                         Math                           *   \n",
       "Ansonia School District                          2015-16 ELA                            *   \n",
       "\n",
       "                                                                 Level 3&4 Met or Exceeded Count  \\\n",
       "District                                         year    Subject                                   \n",
       "Achievement First Hartford Academy Inc. District 2015-16 ELA                                   *   \n",
       "                                                         Math                                  *   \n",
       "Amistad Academy District                         2015-16 ELA                                   *   \n",
       "                                                         Math                                 10   \n",
       "Ansonia School District                          2015-16 ELA                                   *   \n",
       "\n",
       "                                                                 Level 3&4 Met or Exceeded %  \\\n",
       "District                                         year    Subject                               \n",
       "Achievement First Hartford Academy Inc. District 2015-16 ELA                               *   \n",
       "                                                         Math                              *   \n",
       "Amistad Academy District                         2015-16 ELA                               *   \n",
       "                                                         Math                           22.7   \n",
       "Ansonia School District                          2015-16 ELA                               *   \n",
       "\n",
       "                                                                 Average Score  \n",
       "District                                         year    Subject                \n",
       "Achievement First Hartford Academy Inc. District 2015-16 ELA               529  \n",
       "                                                         Math              537  \n",
       "Amistad Academy District                         2015-16 ELA               515  \n",
       "                                                         Math              470  \n",
       "Ansonia School District                          2015-16 ELA               476  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scores_16.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# combine the two sheets\n",
    "scores_both = pd.concat([scores_16,scores_17])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "District Code                                    =\"2880013\"\n",
       "Total Number of Students                                 32\n",
       "Total Number Tested                                      30\n",
       "Connecticut School Day SAT Participation Rate          93.8\n",
       "Total Numberwith Scored Tests                            30\n",
       "Level 1 Not Met Count                                     *\n",
       "Level 1 Not Met Count %                                   *\n",
       "Level 2 Approaching Count                                13\n",
       "Level 2 Approaching %                                  43.3\n",
       "Level 3 Met Count                                        14\n",
       "Level 3 Met Count %                                    46.7\n",
       "Level 4 Exceeded Count                                    *\n",
       "Level 4 Exceeded Count %                                  *\n",
       "Level 3&4 Met or Exceeded Count                           *\n",
       "Level 3&4 Met or Exceeded %                               *\n",
       "Average Score                                           537\n",
       "Name: (Achievement First Hartford Academy Inc. District, 2015-16, Math), dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# this is how you select one row\n",
    "scores_both.loc[\"Achievement First Hartford Academy Inc. District\",\"2015-16\",\"Math\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Achievement First Hartford Academy Inc. District', 'Amistad Academy District', 'Ansonia School District', 'Area Cooperative Educational Services', 'Avon School District', 'Berlin School District', 'Bethel School District', 'Bloomfield School District', 'Bolton School District', 'Branford School District'] ...\n"
     ]
    }
   ],
   "source": [
    "# make a list of school districts\n",
    "districts = []\n",
    "for index, row in scores_both.iterrows():\n",
    "    if index[0] not in districts:\n",
    "        districts.append(index[0])\n",
    "        \n",
    "print districts[:10],\"...\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}