Cleaning & Enriching NYC High School Data | Part 1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cleaning & Enriching NYC High School Data | Part 1/2\n",
    "\n",
    "In this notbook, we'll work on enriching and cleaning datasets in order to have them ready for further analysis. We'll be using a multiple datasets, where the main one is the SAT results dataset.\n",
    "\n",
    "List of datasets used:\n",
    "- [Sat Results](SAT_Results.csv)\n",
    "\n",
    "- [School Demographics](School_Demographics_and_Accountability_Snapshot_2006-2012.csv)\n",
    "\n",
    "- [Graduation Outcomes](Graduation_Outcomes_-_Classes_Of_2005-2010_-_School_Level.csv)\n",
    "\n",
    "- [High School Directory](DOE_High_School_Directory_2014-2015.csv)\n",
    "\n",
    "- [AP College Board](AP__College_Board__2010_School_Level_Results.csv)\n",
    "\n",
    "- [Class Size](2010-2011_Class_Size_-_School-level_detail.csv)\n",
    "\n",
    "- [School Level Data](masterfile11_gened_final.txt) # Windows-1252, tab delimiter\n",
    "\n",
    "- [District 75 Schools](masterfile11_d75_final.txt) # Windows-1252, tab delimiter"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "#### Importing data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "files = [\"School_Demographics_and_Accountability_Snapshot_2006-2012.csv\",\n",
    "        \"SAT_Results.csv\",\n",
    "        \"Graduation_Outcomes_-_Classes_Of_2005-2010_-_School_Level.csv\",\n",
    "        \"DOE_High_School_Directory_2014-2015.csv\",\n",
    "        \"AP__College_Board__2010_School_Level_Results.csv\",\n",
    "        \"2010-2011_Class_Size_-_School-level_detail.csv\",\n",
    "        \"masterfile11_gened_final.txt\",   # Windows-1252, tab delimiter\n",
    "        \"masterfile11_d75_final.txt\"]     # Windows-1252, tab delimiter\n",
    "\n",
    "\n",
    "data = dict()\n",
    "\n",
    "data[\"demographics\"] = pd.read_csv(files[0])\n",
    "data[\"sat_results\"] = pd.read_csv(files[1])\n",
    "data[\"graduation\"] = pd.read_csv(files[2])\n",
    "data[\"hs_directory\"] = pd.read_csv(files[3])\n",
    "data[\"ap_2010\"] = pd.read_csv(files[4])\n",
    "data[\"class_size\"] = pd.read_csv(files[5])\n",
    "data[\"all_survey\"] = pd.read_csv(files[6],encoding=\"Windows-1252\",delimiter='\\t')\n",
    "data[\"d75_survey\"] = pd.read_csv(files[7],encoding=\"Windows-1252\",delimiter='\\t')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "#### Exploring Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "hs_directory \n",
      "==========================\n",
      "\n",
      "      dbn                                        school_name       boro  \\\n",
      "0  17K548                Brooklyn School for Music & Theatre   Brooklyn   \n",
      "1  09X543                   High School for Violin and Dance      Bronx   \n",
      "2  09X327        Comprehensive Model School Project M.S. 327      Bronx   \n",
      "3  02M280     Manhattan Early College School for Advertising  Manhattan   \n",
      "4  28Q680  Queens Gateway to Health Sciences Secondary Sc...     Queens   \n",
      "\n",
      "  building_code    phone_number    fax_number grade_span_min  grade_span_max  \\\n",
      "0          K440    718-230-6250  718-230-6262              9              12   \n",
      "1          X400    718-842-0687  718-589-9849              9              12   \n",
      "2          X240    718-294-8111  718-294-8109              6              12   \n",
      "3          M520  718-935-3477             NaN              9              10   \n",
      "4          Q695    718-969-3155  718-969-3552              6              12   \n",
      "\n",
      "  expgrade_span_min  expgrade_span_max  \\\n",
      "0               NaN                NaN   \n",
      "1               NaN                NaN   \n",
      "2               NaN                NaN   \n",
      "3                 9               14.0   \n",
      "4               NaN                NaN   \n",
      "\n",
      "                         ...                          \\\n",
      "0                        ...                           \n",
      "1                        ...                           \n",
      "2                        ...                           \n",
      "3                        ...                           \n",
      "4                        ...                           \n",
      "\n",
      "                                          priority02  \\\n",
      "0                    Then to New York City residents   \n",
      "1  Then to New York City residents who attend an ...   \n",
      "2  Then to Bronx students or residents who attend...   \n",
      "3  Then to New York City residents who attend an ...   \n",
      "4  Then to Districts 28 and 29 students or residents   \n",
      "\n",
      "                                          priority03  \\\n",
      "0                                                NaN   \n",
      "1                Then to Bronx students or residents   \n",
      "2  Then to New York City residents who attend an ...   \n",
      "3          Then to Manhattan students or residents     \n",
      "4               Then to Queens students or residents   \n",
      "\n",
      "                            priority04                       priority05  \\\n",
      "0                                  NaN                              NaN   \n",
      "1      Then to New York City residents                              NaN   \n",
      "2  Then to Bronx students or residents  Then to New York City residents   \n",
      "3      Then to New York City residents                              NaN   \n",
      "4      Then to New York City residents                              NaN   \n",
      "\n",
      "  priority06  priority07 priority08  priority09 priority10  \\\n",
      "0        NaN         NaN        NaN         NaN        NaN   \n",
      "1        NaN         NaN        NaN         NaN        NaN   \n",
      "2        NaN         NaN        NaN         NaN        NaN   \n",
      "3        NaN         NaN        NaN         NaN        NaN   \n",
      "4        NaN         NaN        NaN         NaN        NaN   \n",
      "\n",
      "                                          Location 1  \n",
      "0  883 Classon Avenue\\nBrooklyn, NY 11225\\n(40.67...  \n",
      "1  1110 Boston Road\\nBronx, NY 10456\\n(40.8276026...  \n",
      "2  1501 Jerome Avenue\\nBronx, NY 10452\\n(40.84241...  \n",
      "3  411 Pearl Street\\nNew York, NY 10038\\n(40.7106...  \n",
      "4  160-20 Goethals Avenue\\nJamaica, NY 11432\\n(40...  \n",
      "\n",
      "[5 rows x 58 columns]\n",
      "==========================\n",
      "\n",
      "class_size \n",
      "==========================\n",
      "\n",
      "   CSD BOROUGH SCHOOL CODE                SCHOOL NAME GRADE  PROGRAM TYPE  \\\n",
      "0    1       M        M015  P.S. 015 Roberto Clemente     0K       GEN ED   \n",
      "1    1       M        M015  P.S. 015 Roberto Clemente     0K          CTT   \n",
      "2    1       M        M015  P.S. 015 Roberto Clemente     01       GEN ED   \n",
      "3    1       M        M015  P.S. 015 Roberto Clemente     01          CTT   \n",
      "4    1       M        M015  P.S. 015 Roberto Clemente     02       GEN ED   \n",
      "\n",
      "  CORE SUBJECT (MS CORE and 9-12 ONLY) CORE COURSE (MS CORE and 9-12 ONLY)  \\\n",
      "0                                    -                                   -   \n",
      "1                                    -                                   -   \n",
      "2                                    -                                   -   \n",
      "3                                    -                                   -   \n",
      "4                                    -                                   -   \n",
      "\n",
      "  SERVICE CATEGORY(K-9* ONLY)  NUMBER OF STUDENTS / SEATS FILLED  \\\n",
      "0                           -                               19.0   \n",
      "1                           -                               21.0   \n",
      "2                           -                               17.0   \n",
      "3                           -                               17.0   \n",
      "4                           -                               15.0   \n",
      "\n",
      "   NUMBER OF SECTIONS  AVERAGE CLASS SIZE  SIZE OF SMALLEST CLASS  \\\n",
      "0                 1.0                19.0                    19.0   \n",
      "1                 1.0                21.0                    21.0   \n",
      "2                 1.0                17.0                    17.0   \n",
      "3                 1.0                17.0                    17.0   \n",
      "4                 1.0                15.0                    15.0   \n",
      "\n",
      "   SIZE OF LARGEST CLASS DATA SOURCE  SCHOOLWIDE PUPIL-TEACHER RATIO  \n",
      "0                   19.0         ATS                             NaN  \n",
      "1                   21.0         ATS                             NaN  \n",
      "2                   17.0         ATS                             NaN  \n",
      "3                   17.0         ATS                             NaN  \n",
      "4                   15.0         ATS                             NaN  \n",
      "==========================\n",
      "\n",
      "ap_2010 \n",
      "==========================\n",
      "\n",
      "      DBN                             SchoolName AP Test Takers   \\\n",
      "0  01M448           UNIVERSITY NEIGHBORHOOD H.S.              39   \n",
      "1  01M450                 EAST SIDE COMMUNITY HS              19   \n",
      "2  01M515                    LOWER EASTSIDE PREP              24   \n",
      "3  01M539         NEW EXPLORATIONS SCI,TECH,MATH             255   \n",
      "4  02M296  High School of Hospitality Management               s   \n",
      "\n",
      "  Total Exams Taken Number of Exams with scores 3 4 or 5  \n",
      "0                49                                   10  \n",
      "1                21                                    s  \n",
      "2                26                                   24  \n",
      "3               377                                  191  \n",
      "4                 s                                    s  \n",
      "==========================\n",
      "\n",
      "d75_survey \n",
      "==========================\n",
      "\n",
      "      dbn    bn schoolname  d75 studentssurveyed  highschool  \\\n",
      "0  75K004  K004  P.S. K004    1              Yes         0.0   \n",
      "1  75K036  K036    P.S. 36    1              Yes         NaN   \n",
      "2  75K053  K053  P.S. K053    1              Yes         NaN   \n",
      "3  75K077  K077  P.S. K077    1              Yes         NaN   \n",
      "4  75K140  K140  P.S. K140    1              Yes         0.0   \n",
      "\n",
      "                      schooltype  rr_s  rr_t  rr_p    ...     s_q14_2  \\\n",
      "0  District 75 Special Education  38.0    90    72    ...        29.0   \n",
      "1  District 75 Special Education  70.0    69    44    ...        20.0   \n",
      "2  District 75 Special Education  94.0    97    53    ...        14.0   \n",
      "3  District 75 Special Education  95.0    65    55    ...        14.0   \n",
      "4  District 75 Special Education  77.0    70    42    ...        35.0   \n",
      "\n",
      "   s_q14_3  s_q14_4  s_q14_5  s_q14_6  s_q14_7  s_q14_8  s_q14_9  s_q14_10  \\\n",
      "0      0.0      0.0      0.0      0.0      0.0      0.0      0.0       0.0   \n",
      "1     27.0     19.0      9.0      2.0      6.0      1.0      2.0       0.0   \n",
      "2     12.0     12.0     10.0     21.0     13.0     11.0      2.0       0.0   \n",
      "3     14.0      7.0     11.0     16.0     10.0      6.0      4.0       7.0   \n",
      "4     34.0     17.0      2.0      0.0      0.0      0.0      0.0       0.0   \n",
      "\n",
      "   s_q14_11  \n",
      "0       0.0  \n",
      "1       0.0  \n",
      "2       0.0  \n",
      "3       7.0  \n",
      "4       0.0  \n",
      "\n",
      "[5 rows x 1773 columns]\n",
      "==========================\n",
      "\n",
      "sat_results \n",
      "==========================\n",
      "\n",
      "      DBN                                    SCHOOL NAME  \\\n",
      "0  01M292  HENRY STREET SCHOOL FOR INTERNATIONAL STUDIES   \n",
      "1  01M448            UNIVERSITY NEIGHBORHOOD HIGH SCHOOL   \n",
      "2  01M450                     EAST SIDE COMMUNITY SCHOOL   \n",
      "3  01M458                      FORSYTH SATELLITE ACADEMY   \n",
      "4  01M509                        MARTA VALLE HIGH SCHOOL   \n",
      "\n",
      "  Num of SAT Test Takers SAT Critical Reading Avg. Score SAT Math Avg. Score  \\\n",
      "0                     29                             355                 404   \n",
      "1                     91                             383                 423   \n",
      "2                     70                             377                 402   \n",
      "3                      7                             414                 401   \n",
      "4                     44                             390                 433   \n",
      "\n",
      "  SAT Writing Avg. Score  \n",
      "0                    363  \n",
      "1                    366  \n",
      "2                    370  \n",
      "3                    359  \n",
      "4                    384  \n",
      "==========================\n",
      "\n",
      "demographics \n",
      "==========================\n",
      "\n",
      "      DBN                       Name  schoolyear fl_percent  frl_percent  \\\n",
      "0  01M015  P.S. 015 ROBERTO CLEMENTE    20052006       89.4          NaN   \n",
      "1  01M015  P.S. 015 ROBERTO CLEMENTE    20062007       89.4          NaN   \n",
      "2  01M015  P.S. 015 ROBERTO CLEMENTE    20072008       89.4          NaN   \n",
      "3  01M015  P.S. 015 ROBERTO CLEMENTE    20082009       89.4          NaN   \n",
      "4  01M015  P.S. 015 ROBERTO CLEMENTE    20092010                    96.5   \n",
      "\n",
      "   total_enrollment prek   k grade1 grade2    ...     black_num black_per  \\\n",
      "0               281   15  36     40     33    ...            74      26.3   \n",
      "1               243   15  29     39     38    ...            68      28.0   \n",
      "2               261   18  43     39     36    ...            77      29.5   \n",
      "3               252   17  37     44     32    ...            75      29.8   \n",
      "4               208   16  40     28     32    ...            67      32.2   \n",
      "\n",
      "  hispanic_num hispanic_per white_num white_per male_num male_per female_num  \\\n",
      "0          189         67.3         5       1.8    158.0     56.2      123.0   \n",
      "1          153         63.0         4       1.6    140.0     57.6      103.0   \n",
      "2          157         60.2         7       2.7    143.0     54.8      118.0   \n",
      "3          149         59.1         7       2.8    149.0     59.1      103.0   \n",
      "4          118         56.7         6       2.9    124.0     59.6       84.0   \n",
      "\n",
      "  female_per  \n",
      "0       43.8  \n",
      "1       42.4  \n",
      "2       45.2  \n",
      "3       40.9  \n",
      "4       40.4  \n",
      "\n",
      "[5 rows x 38 columns]\n",
      "==========================\n",
      "\n",
      "graduation \n",
      "==========================\n",
      "\n",
      "    Demographic     DBN                            School Name    Cohort  \\\n",
      "0  Total Cohort  01M292  HENRY STREET SCHOOL FOR INTERNATIONAL      2003   \n",
      "1  Total Cohort  01M292  HENRY STREET SCHOOL FOR INTERNATIONAL      2004   \n",
      "2  Total Cohort  01M292  HENRY STREET SCHOOL FOR INTERNATIONAL      2005   \n",
      "3  Total Cohort  01M292  HENRY STREET SCHOOL FOR INTERNATIONAL      2006   \n",
      "4  Total Cohort  01M292  HENRY STREET SCHOOL FOR INTERNATIONAL  2006 Aug   \n",
      "\n",
      "   Total Cohort Total Grads - n Total Grads - % of cohort Total Regents - n  \\\n",
      "0             5               s                         s                 s   \n",
      "1            55              37                     67.3%                17   \n",
      "2            64              43                     67.2%                27   \n",
      "3            78              43                     55.1%                36   \n",
      "4            78              44                     56.4%                37   \n",
      "\n",
      "  Total Regents - % of cohort Total Regents - % of grads  \\\n",
      "0                           s                          s   \n",
      "1                       30.9%                      45.9%   \n",
      "2                       42.2%                      62.8%   \n",
      "3                       46.2%                      83.7%   \n",
      "4                       47.4%                      84.1%   \n",
      "\n",
      "             ...            Regents w/o Advanced - n  \\\n",
      "0            ...                                   s   \n",
      "1            ...                                  17   \n",
      "2            ...                                  27   \n",
      "3            ...                                  36   \n",
      "4            ...                                  37   \n",
      "\n",
      "  Regents w/o Advanced - % of cohort Regents w/o Advanced - % of grads  \\\n",
      "0                                  s                                 s   \n",
      "1                              30.9%                             45.9%   \n",
      "2                              42.2%                             62.8%   \n",
      "3                              46.2%                             83.7%   \n",
      "4                              47.4%                             84.1%   \n",
      "\n",
      "  Local - n Local - % of cohort   Local - % of grads Still Enrolled - n  \\\n",
      "0         s                   s                    s                  s   \n",
      "1        20               36.4%                54.1%                 15   \n",
      "2        16                 25%  37.200000000000003%                  9   \n",
      "3         7                  9%                16.3%                 16   \n",
      "4         7                  9%                15.9%                 15   \n",
      "\n",
      "  Still Enrolled - % of cohort Dropped Out - n Dropped Out - % of cohort  \n",
      "0                            s               s                         s  \n",
      "1                        27.3%               3                      5.5%  \n",
      "2                        14.1%               9                     14.1%  \n",
      "3                        20.5%              11                     14.1%  \n",
      "4                        19.2%              11                     14.1%  \n",
      "\n",
      "[5 rows x 23 columns]\n",
      "==========================\n",
      "\n",
      "all_survey \n",
      "==========================\n",
      "\n",
      "      dbn    bn                      schoolname  d75 studentssurveyed  \\\n",
      "0  01M015  M015       P.S. 015 Roberto Clemente    0               No   \n",
      "1  01M019  M019             P.S. 019 Asher Levy    0               No   \n",
      "2  01M020  M020            P.S. 020 Anna Silver    0               No   \n",
      "3  01M034  M034  P.S. 034 Franklin D. Roosevelt    0              Yes   \n",
      "4  01M063  M063       P.S. 063 William McKinley    0               No   \n",
      "\n",
      "   highschool                  schooltype  rr_s  rr_t  rr_p     ...      \\\n",
      "0         0.0           Elementary School   NaN    88    60     ...       \n",
      "1         0.0           Elementary School   NaN   100    60     ...       \n",
      "2         0.0           Elementary School   NaN    88    73     ...       \n",
      "3         0.0  Elementary / Middle School  89.0    73    50     ...       \n",
      "4         0.0           Elementary School   NaN   100    60     ...       \n",
      "\n",
      "   s_N_q14e_3  s_N_q14e_4  s_N_q14f_1  s_N_q14f_2  s_N_q14f_3  s_N_q14f_4  \\\n",
      "0         NaN         NaN         NaN         NaN         NaN         NaN   \n",
      "1         NaN         NaN         NaN         NaN         NaN         NaN   \n",
      "2         NaN         NaN         NaN         NaN         NaN         NaN   \n",
      "3        20.0        16.0        23.0        54.0        33.0        29.0   \n",
      "4         NaN         NaN         NaN         NaN         NaN         NaN   \n",
      "\n",
      "   s_N_q14g_1  s_N_q14g_2  s_N_q14g_3  s_N_q14g_4  \n",
      "0         NaN         NaN         NaN         NaN  \n",
      "1         NaN         NaN         NaN         NaN  \n",
      "2         NaN         NaN         NaN         NaN  \n",
      "3        31.0        46.0        16.0         8.0  \n",
      "4         NaN         NaN         NaN         NaN  \n",
      "\n",
      "[5 rows x 1942 columns]\n",
      "==========================\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Exploring other datasets\n",
    "\n",
    "for k in data:\n",
    "    print(k , \"\\n==========================\\n\")\n",
    "    print(data[k].head())\n",
    "    print(\"==========================\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "#### Merging Datasets: survey district 75 datasets"
   ]
  },
  {
   "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>N_p</th>\n",
       "      <th>N_s</th>\n",
       "      <th>N_t</th>\n",
       "      <th>aca_p_11</th>\n",
       "      <th>aca_s_11</th>\n",
       "      <th>aca_t_11</th>\n",
       "      <th>aca_tot_11</th>\n",
       "      <th>bn</th>\n",
       "      <th>com_p_11</th>\n",
       "      <th>com_s_11</th>\n",
       "      <th>...</th>\n",
       "      <th>t_q8c_1</th>\n",
       "      <th>t_q8c_2</th>\n",
       "      <th>t_q8c_3</th>\n",
       "      <th>t_q8c_4</th>\n",
       "      <th>t_q9</th>\n",
       "      <th>t_q9_1</th>\n",
       "      <th>t_q9_2</th>\n",
       "      <th>t_q9_3</th>\n",
       "      <th>t_q9_4</th>\n",
       "      <th>t_q9_5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>90.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22.0</td>\n",
       "      <td>7.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.9</td>\n",
       "      <td>7.9</td>\n",
       "      <td>M015</td>\n",
       "      <td>7.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>29.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>161.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.0</td>\n",
       "      <td>7.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.1</td>\n",
       "      <td>8.4</td>\n",
       "      <td>M019</td>\n",
       "      <td>7.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>74.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>367.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>42.0</td>\n",
       "      <td>8.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.5</td>\n",
       "      <td>8.0</td>\n",
       "      <td>M020</td>\n",
       "      <td>8.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>33.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>151.0</td>\n",
       "      <td>145.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>8.5</td>\n",
       "      <td>7.4</td>\n",
       "      <td>7.8</td>\n",
       "      <td>7.9</td>\n",
       "      <td>M034</td>\n",
       "      <td>8.2</td>\n",
       "      <td>5.9</td>\n",
       "      <td>...</td>\n",
       "      <td>21.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>90.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23.0</td>\n",
       "      <td>7.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.1</td>\n",
       "      <td>8.0</td>\n",
       "      <td>M063</td>\n",
       "      <td>7.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>59.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 2773 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     N_p    N_s   N_t  aca_p_11  aca_s_11  aca_t_11  aca_tot_11    bn  \\\n",
       "0   90.0    NaN  22.0       7.8       NaN       7.9         7.9  M015   \n",
       "1  161.0    NaN  34.0       7.8       NaN       9.1         8.4  M019   \n",
       "2  367.0    NaN  42.0       8.6       NaN       7.5         8.0  M020   \n",
       "3  151.0  145.0  29.0       8.5       7.4       7.8         7.9  M034   \n",
       "4   90.0    NaN  23.0       7.9       NaN       8.1         8.0  M063   \n",
       "\n",
       "   com_p_11  com_s_11   ...    t_q8c_1  t_q8c_2  t_q8c_3 t_q8c_4  t_q9  \\\n",
       "0       7.6       NaN   ...       29.0     67.0      5.0     0.0   NaN   \n",
       "1       7.6       NaN   ...       74.0     21.0      6.0     0.0   NaN   \n",
       "2       8.3       NaN   ...       33.0     35.0     20.0    13.0   NaN   \n",
       "3       8.2       5.9   ...       21.0     45.0     28.0     7.0   NaN   \n",
       "4       7.9       NaN   ...       59.0     36.0      5.0     0.0   NaN   \n",
       "\n",
       "   t_q9_1  t_q9_2  t_q9_3  t_q9_4  t_q9_5  \n",
       "0     5.0    14.0    52.0    24.0     5.0  \n",
       "1     3.0     6.0     3.0    78.0     9.0  \n",
       "2     3.0     5.0    16.0    70.0     5.0  \n",
       "3     0.0    18.0    32.0    39.0    11.0  \n",
       "4    10.0     5.0    10.0    60.0    15.0  \n",
       "\n",
       "[5 rows x 2773 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "survey = pd.concat([data[\"all_survey\"],data[\"d75_survey\"]],axis=0)\n",
    "survey.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "#### Filtering Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>DBN</th>\n",
       "      <th>rr_s</th>\n",
       "      <th>rr_t</th>\n",
       "      <th>rr_p</th>\n",
       "      <th>N_s</th>\n",
       "      <th>N_t</th>\n",
       "      <th>N_p</th>\n",
       "      <th>saf_p_11</th>\n",
       "      <th>com_p_11</th>\n",
       "      <th>eng_p_11</th>\n",
       "      <th>...</th>\n",
       "      <th>eng_t_11</th>\n",
       "      <th>aca_t_11</th>\n",
       "      <th>saf_s_11</th>\n",
       "      <th>com_s_11</th>\n",
       "      <th>eng_s_11</th>\n",
       "      <th>aca_s_11</th>\n",
       "      <th>saf_tot_11</th>\n",
       "      <th>com_tot_11</th>\n",
       "      <th>eng_tot_11</th>\n",
       "      <th>aca_tot_11</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01M015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>88</td>\n",
       "      <td>60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>8.5</td>\n",
       "      <td>7.6</td>\n",
       "      <td>7.5</td>\n",
       "      <td>...</td>\n",
       "      <td>7.6</td>\n",
       "      <td>7.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>7.7</td>\n",
       "      <td>7.5</td>\n",
       "      <td>7.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01M019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.0</td>\n",
       "      <td>161.0</td>\n",
       "      <td>8.4</td>\n",
       "      <td>7.6</td>\n",
       "      <td>7.6</td>\n",
       "      <td>...</td>\n",
       "      <td>8.9</td>\n",
       "      <td>9.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.5</td>\n",
       "      <td>8.1</td>\n",
       "      <td>8.2</td>\n",
       "      <td>8.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01M020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>88</td>\n",
       "      <td>73</td>\n",
       "      <td>NaN</td>\n",
       "      <td>42.0</td>\n",
       "      <td>367.0</td>\n",
       "      <td>8.9</td>\n",
       "      <td>8.3</td>\n",
       "      <td>8.3</td>\n",
       "      <td>...</td>\n",
       "      <td>6.8</td>\n",
       "      <td>7.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.2</td>\n",
       "      <td>7.3</td>\n",
       "      <td>7.5</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01M034</td>\n",
       "      <td>89.0</td>\n",
       "      <td>73</td>\n",
       "      <td>50</td>\n",
       "      <td>145.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>151.0</td>\n",
       "      <td>8.8</td>\n",
       "      <td>8.2</td>\n",
       "      <td>8.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.8</td>\n",
       "      <td>7.8</td>\n",
       "      <td>6.2</td>\n",
       "      <td>5.9</td>\n",
       "      <td>6.5</td>\n",
       "      <td>7.4</td>\n",
       "      <td>7.3</td>\n",
       "      <td>6.7</td>\n",
       "      <td>7.1</td>\n",
       "      <td>7.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01M063</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>8.7</td>\n",
       "      <td>7.9</td>\n",
       "      <td>8.1</td>\n",
       "      <td>...</td>\n",
       "      <td>7.8</td>\n",
       "      <td>8.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.5</td>\n",
       "      <td>7.6</td>\n",
       "      <td>7.9</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      DBN  rr_s  rr_t  rr_p    N_s   N_t    N_p  saf_p_11  com_p_11  eng_p_11  \\\n",
       "0  01M015   NaN    88    60    NaN  22.0   90.0       8.5       7.6       7.5   \n",
       "1  01M019   NaN   100    60    NaN  34.0  161.0       8.4       7.6       7.6   \n",
       "2  01M020   NaN    88    73    NaN  42.0  367.0       8.9       8.3       8.3   \n",
       "3  01M034  89.0    73    50  145.0  29.0  151.0       8.8       8.2       8.0   \n",
       "4  01M063   NaN   100    60    NaN  23.0   90.0       8.7       7.9       8.1   \n",
       "\n",
       "      ...      eng_t_11  aca_t_11  saf_s_11  com_s_11  eng_s_11  aca_s_11  \\\n",
       "0     ...           7.6       7.9       NaN       NaN       NaN       NaN   \n",
       "1     ...           8.9       9.1       NaN       NaN       NaN       NaN   \n",
       "2     ...           6.8       7.5       NaN       NaN       NaN       NaN   \n",
       "3     ...           6.8       7.8       6.2       5.9       6.5       7.4   \n",
       "4     ...           7.8       8.1       NaN       NaN       NaN       NaN   \n",
       "\n",
       "   saf_tot_11  com_tot_11  eng_tot_11  aca_tot_11  \n",
       "0         8.0         7.7         7.5         7.9  \n",
       "1         8.5         8.1         8.2         8.4  \n",
       "2         8.2         7.3         7.5         8.0  \n",
       "3         7.3         6.7         7.1         7.9  \n",
       "4         8.5         7.6         7.9         8.0  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "survey[\"DBN\"] = survey[\"dbn\"]\n",
    "\n",
    "cols = [\"DBN\", \"rr_s\", \"rr_t\", \"rr_p\", \"N_s\", \"N_t\", \"N_p\", \"saf_p_11\", \"com_p_11\", \"eng_p_11\", \"aca_p_11\", \"saf_t_11\",\n",
    "        \"com_t_11\", \"eng_t_11\", \"aca_t_11\", \"saf_s_11\", \"com_s_11\", \"eng_s_11\", \"aca_s_11\", \"saf_tot_11\", \"com_tot_11\", \n",
    "        \"eng_tot_11\", \"aca_tot_11\"]\n",
    "\n",
    "survey = survey[cols]\n",
    "\n",
    "survey.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "### Fixing column name: From lowercase to uppercase"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['DBN', 'school_name', 'boro', 'building_code', 'phone_number',\n",
       "       'fax_number', 'grade_span_min', 'grade_span_max', 'expgrade_span_min',\n",
       "       'expgrade_span_max', 'bus', 'subway', 'primary_address_line_1', 'city',\n",
       "       'state_code', 'zip', 'website', 'total_students', 'campus_name',\n",
       "       'school_type', 'overview_paragraph', 'program_highlights',\n",
       "       'language_classes', 'advancedplacement_courses', 'online_ap_courses',\n",
       "       'online_language_courses', 'extracurricular_activities',\n",
       "       'psal_sports_boys', 'psal_sports_girls', 'psal_sports_coed',\n",
       "       'school_sports', 'partner_cbo', 'partner_hospital', 'partner_highered',\n",
       "       'partner_cultural', 'partner_nonprofit', 'partner_corporate',\n",
       "       'partner_financial', 'partner_other', 'addtl_info1', 'addtl_info2',\n",
       "       'start_time', 'end_time', 'se_services', 'ell_programs',\n",
       "       'school_accessibility_description', 'number_programs', 'priority01',\n",
       "       'priority02', 'priority03', 'priority04', 'priority05', 'priority06',\n",
       "       'priority07', 'priority08', 'priority09', 'priority10', 'Location 1'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[\"hs_directory\"].rename(columns= {\"dbn\":\"DBN\"},inplace=True)\n",
    "\n",
    "data[\"hs_directory\"].columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "#### Deriving A New Column from Existing Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    01M015\n",
      "1    01M015\n",
      "Name: DBN, dtype: object\n",
      "0    1122.0\n",
      "1    1172.0\n",
      "Name: sat_score, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "def combine_pad_cols(s):\n",
    "    s = str(s)\n",
    "    if len(s) > 1:\n",
    "        return s\n",
    "    else:\n",
    "        return \"0\" + s\n",
    "    \n",
    "data[\"class_size\"][\"DBN\"] = data[\"class_size\"][\"CSD\"].apply(combine_pad_cols) + data[\"class_size\"][\"SCHOOL CODE\"]\n",
    "\n",
    "data[\"sat_results\"][\"sat_score\"] = pd.to_numeric(data[\"sat_results\"][\"SAT Math Avg. Score\"],errors=\"coerce\") + pd.to_numeric(data[\"sat_results\"][\"SAT Critical Reading Avg. Score\"],errors=\"coerce\") + pd.to_numeric(data[\"sat_results\"][\"SAT Writing Avg. Score\"],errors=\"coerce\")\n",
    "\n",
    "print(data[\"class_size\"][\"DBN\"].iloc[0:2])\n",
    "print(data[\"sat_results\"][\"sat_score\"].iloc[0:2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "#### Extracting Data: Coordinates from hs_directory dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    883 Classon Avenue\\nBrooklyn, NY 11225\\n(40.67...\n",
      "1    1110 Boston Road\\nBronx, NY 10456\\n(40.8276026...\n",
      "Name: Location 1, dtype: object\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>40.670299</td>\n",
       "      <td>-73.961648</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>40.827603</td>\n",
       "      <td>-73.904475</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         lat        lon\n",
       "0  40.670299 -73.961648\n",
       "1  40.827603 -73.904475"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import re\n",
    "\n",
    "print(data[\"hs_directory\"][\"Location 1\"].iloc[0:2])\n",
    "\n",
    "def return_coords(s,which):\n",
    "    s = re.findall(\"\\(.+, .+\\)\",s)\n",
    "    if len(s) > 0:\n",
    "        s = s[0].replace(\"(\",\"\").replace(\")\",\"\").replace(\" \",\"\")\n",
    "        if which == 'lat':\n",
    "            return float(s.split(\",\")[0])\n",
    "        else:\n",
    "            return float(s.split(\",\")[1])\n",
    "    pass\n",
    "\n",
    "data[\"hs_directory\"][\"lat\"] = data[\"hs_directory\"][\"Location 1\"].apply(lambda x: return_coords(x,'lat'))\n",
    "data[\"hs_directory\"][\"lon\"] = data[\"hs_directory\"][\"Location 1\"].apply(lambda x: return_coords(x,'lon'))\n",
    "\n",
    "data[\"hs_directory\"][[\"lat\",\"lon\"]][0:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "#### Condensing Data: Class Size, Demographics and Graduation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DBN                                  01M332\n",
      "CSD                                       1\n",
      "NUMBER OF STUDENTS / SEATS FILLED        46\n",
      "NUMBER OF SECTIONS                        2\n",
      "AVERAGE CLASS SIZE                       22\n",
      "SIZE OF SMALLEST CLASS                   21\n",
      "SIZE OF LARGEST CLASS                  23.5\n",
      "Name: 1, dtype: object \n",
      "------\n",
      "DBN                                                            01M019\n",
      "Name                 P.S. 019 ASHER LEVY                             \n",
      "schoolyear                                                   20112012\n",
      "fl_percent                                                        NaN\n",
      "frl_percent                                                      61.5\n",
      "total_enrollment                                                  328\n",
      "prek                                                               32\n",
      "k                                                                  46\n",
      "grade1                                                             52\n",
      "grade2                                                             54\n",
      "grade3                                                             52\n",
      "grade4                                                             46\n",
      "grade5                                                             46\n",
      "grade6                                                               \n",
      "grade7                                                               \n",
      "grade8                                                               \n",
      "grade9                                                               \n",
      "grade10                                                              \n",
      "grade11                                                              \n",
      "grade12                                                              \n",
      "ell_num                                                            33\n",
      "ell_percent                                                      10.1\n",
      "sped_num                                                           59\n",
      "sped_percent                                                       18\n",
      "ctt_num                                                            16\n",
      "selfcontained_num                                                  16\n",
      "asian_num                                                          51\n",
      "asian_per                                                        15.5\n",
      "black_num                                                          81\n",
      "black_per                                                        24.7\n",
      "hispanic_num                                                      158\n",
      "hispanic_per                                                     48.2\n",
      "white_num                                                          28\n",
      "white_per                                                         8.5\n",
      "male_num                                                          147\n",
      "male_per                                                         44.8\n",
      "female_num                                                        181\n",
      "female_per                                                       55.2\n",
      "Name: 13, dtype: object \n",
      "------\n",
      "Demographic                                                  Total Cohort\n",
      "DBN                                                                01M448\n",
      "School Name                           UNIVERSITY NEIGHBORHOOD HIGH SCHOOL\n",
      "Cohort                                                               2006\n",
      "Total Cohort                                                          124\n",
      "Total Grads - n                                                        53\n",
      "Total Grads - % of cohort                                           42.7%\n",
      "Total Regents - n                                                      42\n",
      "Total Regents - % of cohort                                         33.9%\n",
      "Total Regents - % of grads                                          79.2%\n",
      "Advanced Regents - n                                                    8\n",
      "Advanced Regents - % of cohort                                       6.5%\n",
      "Advanced Regents - % of grads                                       15.1%\n",
      "Regents w/o Advanced - n                                               34\n",
      "Regents w/o Advanced - % of cohort                                  27.4%\n",
      "Regents w/o Advanced - % of grads                                   64.2%\n",
      "Local - n                                                              11\n",
      "Local - % of cohort                                                  8.9%\n",
      "Local - % of grads                                                  20.8%\n",
      "Still Enrolled - n                                                     46\n",
      "Still Enrolled - % of cohort                                        37.1%\n",
      "Dropped Out - n                                                        20\n",
      "Dropped Out - % of cohort                             16.100000000000001%\n",
      "Name: 10, dtype: object\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "\n",
    "# filter\n",
    "data[\"class_size\"] = data[\"class_size\"][(data[\"class_size\"][\"GRADE \"] == \"09-12\") & (data[\"class_size\"][\"PROGRAM TYPE\"] == \"GEN ED\")]\n",
    "\n",
    "# group and aggregate\n",
    "data[\"class_size\"] = data[\"class_size\"].groupby(\"DBN\").aggregate(np.mean)\n",
    "data[\"class_size\"].reset_index(inplace=True)\n",
    "data[\"class_size\"].dropna(axis=1,inplace=True)\n",
    "\n",
    "#filter\n",
    "data[\"demographics\"] = data[\"demographics\"][data[\"demographics\"][\"schoolyear\"] == 20112012]\n",
    "\n",
    "#filter\n",
    "data[\"graduation\"] = data[\"graduation\"][(data[\"graduation\"][\"Cohort\"] == \"2006\") & (data[\"graduation\"][\"Demographic\"] == \"Total Cohort\")]\n",
    "\n",
    "\n",
    "print(data[\"class_size\"].iloc[1],'\\n------')\n",
    "print(data[\"demographics\"].iloc[1],'\\n------')\n",
    "print(data[\"graduation\"].iloc[1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "#### Converting Columns Datatype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cols = ['AP Test Takers ', 'Total Exams Taken', 'Number of Exams with scores 3 4 or 5']\n",
    "\n",
    "for col in cols:\n",
    "    data[\"ap_2010\"][col] = pd.to_numeric(data[\"ap_2010\"][col],errors=\"coerce\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "#### Merging the Datasets"
   ]
  },
  {
   "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>DBN</th>\n",
       "      <th>SCHOOL NAME</th>\n",
       "      <th>Num of SAT Test Takers</th>\n",
       "      <th>SAT Critical Reading Avg. Score</th>\n",
       "      <th>SAT Math Avg. Score</th>\n",
       "      <th>SAT Writing Avg. Score</th>\n",
       "      <th>sat_score</th>\n",
       "      <th>SchoolName</th>\n",
       "      <th>AP Test Takers</th>\n",
       "      <th>Total Exams Taken</th>\n",
       "      <th>...</th>\n",
       "      <th>priority04</th>\n",
       "      <th>priority05</th>\n",
       "      <th>priority06</th>\n",
       "      <th>priority07</th>\n",
       "      <th>priority08</th>\n",
       "      <th>priority09</th>\n",
       "      <th>priority10</th>\n",
       "      <th>Location 1</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01M292</td>\n",
       "      <td>HENRY STREET SCHOOL FOR INTERNATIONAL STUDIES</td>\n",
       "      <td>29</td>\n",
       "      <td>355</td>\n",
       "      <td>404</td>\n",
       "      <td>363</td>\n",
       "      <td>1122.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Then to Manhattan students or residents</td>\n",
       "      <td>Then to New York City residents</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>220 Henry Street\\nNew York, NY 10002\\n(40.7137...</td>\n",
       "      <td>40.713764</td>\n",
       "      <td>-73.985260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01M448</td>\n",
       "      <td>UNIVERSITY NEIGHBORHOOD HIGH SCHOOL</td>\n",
       "      <td>91</td>\n",
       "      <td>383</td>\n",
       "      <td>423</td>\n",
       "      <td>366</td>\n",
       "      <td>1172.0</td>\n",
       "      <td>UNIVERSITY NEIGHBORHOOD H.S.</td>\n",
       "      <td>39.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>200 Monroe Street\\nNew York, NY 10002\\n(40.712...</td>\n",
       "      <td>40.712332</td>\n",
       "      <td>-73.984797</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 157 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      DBN                                    SCHOOL NAME  \\\n",
       "0  01M292  HENRY STREET SCHOOL FOR INTERNATIONAL STUDIES   \n",
       "1  01M448            UNIVERSITY NEIGHBORHOOD HIGH SCHOOL   \n",
       "\n",
       "  Num of SAT Test Takers SAT Critical Reading Avg. Score SAT Math Avg. Score  \\\n",
       "0                     29                             355                 404   \n",
       "1                     91                             383                 423   \n",
       "\n",
       "  SAT Writing Avg. Score  sat_score                    SchoolName  \\\n",
       "0                    363     1122.0                           NaN   \n",
       "1                    366     1172.0  UNIVERSITY NEIGHBORHOOD H.S.   \n",
       "\n",
       "   AP Test Takers   Total Exams Taken    ...      \\\n",
       "0              NaN                NaN    ...       \n",
       "1             39.0               49.0    ...       \n",
       "\n",
       "                                priority04                       priority05  \\\n",
       "0  Then to Manhattan students or residents  Then to New York City residents   \n",
       "1                                      NaN                              NaN   \n",
       "\n",
       "  priority06 priority07  priority08 priority09 priority10  \\\n",
       "0        NaN        NaN         NaN        NaN        NaN   \n",
       "1        NaN        NaN         NaN        NaN        NaN   \n",
       "\n",
       "                                          Location 1        lat        lon  \n",
       "0  220 Henry Street\\nNew York, NY 10002\\n(40.7137...  40.713764 -73.985260  \n",
       "1  200 Monroe Street\\nNew York, NY 10002\\n(40.712...  40.712332 -73.984797  \n",
       "\n",
       "[2 rows x 157 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined = data[\"sat_results\"].merge(data[\"ap_2010\"],how=\"left\").merge(data[\"graduation\"],how=\"left\").merge(data[\"class_size\"],how=\"inner\").merge(data[\"demographics\"],how=\"inner\").merge(survey,how=\"inner\").merge(data[\"hs_directory\"],how=\"inner\")\n",
    "\n",
    "combined[0:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Fill NaNs with Mean Value"
   ]
  },
  {
   "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>DBN</th>\n",
       "      <th>SCHOOL NAME</th>\n",
       "      <th>Num of SAT Test Takers</th>\n",
       "      <th>SAT Critical Reading Avg. Score</th>\n",
       "      <th>SAT Math Avg. Score</th>\n",
       "      <th>SAT Writing Avg. Score</th>\n",
       "      <th>sat_score</th>\n",
       "      <th>SchoolName</th>\n",
       "      <th>AP Test Takers</th>\n",
       "      <th>Total Exams Taken</th>\n",
       "      <th>...</th>\n",
       "      <th>priority04</th>\n",
       "      <th>priority05</th>\n",
       "      <th>priority06</th>\n",
       "      <th>priority07</th>\n",
       "      <th>priority08</th>\n",
       "      <th>priority09</th>\n",
       "      <th>priority10</th>\n",
       "      <th>Location 1</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01M292</td>\n",
       "      <td>HENRY STREET SCHOOL FOR INTERNATIONAL STUDIES</td>\n",
       "      <td>29</td>\n",
       "      <td>355</td>\n",
       "      <td>404</td>\n",
       "      <td>363</td>\n",
       "      <td>1122.0</td>\n",
       "      <td>0</td>\n",
       "      <td>129.028846</td>\n",
       "      <td>197.038462</td>\n",
       "      <td>...</td>\n",
       "      <td>Then to Manhattan students or residents</td>\n",
       "      <td>Then to New York City residents</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>220 Henry Street\\nNew York, NY 10002\\n(40.7137...</td>\n",
       "      <td>40.713764</td>\n",
       "      <td>-73.985260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01M448</td>\n",
       "      <td>UNIVERSITY NEIGHBORHOOD HIGH SCHOOL</td>\n",
       "      <td>91</td>\n",
       "      <td>383</td>\n",
       "      <td>423</td>\n",
       "      <td>366</td>\n",
       "      <td>1172.0</td>\n",
       "      <td>UNIVERSITY NEIGHBORHOOD H.S.</td>\n",
       "      <td>39.000000</td>\n",
       "      <td>49.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>200 Monroe Street\\nNew York, NY 10002\\n(40.712...</td>\n",
       "      <td>40.712332</td>\n",
       "      <td>-73.984797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01M450</td>\n",
       "      <td>EAST SIDE COMMUNITY SCHOOL</td>\n",
       "      <td>70</td>\n",
       "      <td>377</td>\n",
       "      <td>402</td>\n",
       "      <td>370</td>\n",
       "      <td>1149.0</td>\n",
       "      <td>EAST SIDE COMMUNITY HS</td>\n",
       "      <td>19.000000</td>\n",
       "      <td>21.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>420 East 12 Street\\nNew York, NY 10009\\n(40.72...</td>\n",
       "      <td>40.729783</td>\n",
       "      <td>-73.983041</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 157 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      DBN                                    SCHOOL NAME  \\\n",
       "0  01M292  HENRY STREET SCHOOL FOR INTERNATIONAL STUDIES   \n",
       "1  01M448            UNIVERSITY NEIGHBORHOOD HIGH SCHOOL   \n",
       "2  01M450                     EAST SIDE COMMUNITY SCHOOL   \n",
       "\n",
       "  Num of SAT Test Takers SAT Critical Reading Avg. Score SAT Math Avg. Score  \\\n",
       "0                     29                             355                 404   \n",
       "1                     91                             383                 423   \n",
       "2                     70                             377                 402   \n",
       "\n",
       "  SAT Writing Avg. Score  sat_score                    SchoolName  \\\n",
       "0                    363     1122.0                             0   \n",
       "1                    366     1172.0  UNIVERSITY NEIGHBORHOOD H.S.   \n",
       "2                    370     1149.0        EAST SIDE COMMUNITY HS   \n",
       "\n",
       "   AP Test Takers   Total Exams Taken    ...      \\\n",
       "0       129.028846         197.038462    ...       \n",
       "1        39.000000          49.000000    ...       \n",
       "2        19.000000          21.000000    ...       \n",
       "\n",
       "                                priority04                       priority05  \\\n",
       "0  Then to Manhattan students or residents  Then to New York City residents   \n",
       "1                                        0                                0   \n",
       "2                                        0                                0   \n",
       "\n",
       "  priority06 priority07  priority08 priority09 priority10  \\\n",
       "0          0          0           0          0          0   \n",
       "1          0          0           0          0          0   \n",
       "2          0          0           0          0          0   \n",
       "\n",
       "                                          Location 1        lat        lon  \n",
       "0  220 Henry Street\\nNew York, NY 10002\\n(40.7137...  40.713764 -73.985260  \n",
       "1  200 Monroe Street\\nNew York, NY 10002\\n(40.712...  40.712332 -73.984797  \n",
       "2  420 East 12 Street\\nNew York, NY 10009\\n(40.72...  40.729783 -73.983041  \n",
       "\n",
       "[3 rows x 157 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "means = combined.mean()\n",
    "combined.fillna(means,inplace=True)\n",
    "combined.fillna(0,inplace=True)\n",
    "\n",
    "combined[0:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Adding Column: School District"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>DBN</th>\n",
       "      <th>SCHOOL NAME</th>\n",
       "      <th>Num of SAT Test Takers</th>\n",
       "      <th>SAT Critical Reading Avg. Score</th>\n",
       "      <th>SAT Math Avg. Score</th>\n",
       "      <th>SAT Writing Avg. Score</th>\n",
       "      <th>sat_score</th>\n",
       "      <th>SchoolName</th>\n",
       "      <th>AP Test Takers</th>\n",
       "      <th>Total Exams Taken</th>\n",
       "      <th>...</th>\n",
       "      <th>priority05</th>\n",
       "      <th>priority06</th>\n",
       "      <th>priority07</th>\n",
       "      <th>priority08</th>\n",
       "      <th>priority09</th>\n",
       "      <th>priority10</th>\n",
       "      <th>Location 1</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>school_dist</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01M292</td>\n",
       "      <td>HENRY STREET SCHOOL FOR INTERNATIONAL STUDIES</td>\n",
       "      <td>29</td>\n",
       "      <td>355</td>\n",
       "      <td>404</td>\n",
       "      <td>363</td>\n",
       "      <td>1122.0</td>\n",
       "      <td>0</td>\n",
       "      <td>129.028846</td>\n",
       "      <td>197.038462</td>\n",
       "      <td>...</td>\n",
       "      <td>Then to New York City residents</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>220 Henry Street\\nNew York, NY 10002\\n(40.7137...</td>\n",
       "      <td>40.713764</td>\n",
       "      <td>-73.98526</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 158 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      DBN                                    SCHOOL NAME  \\\n",
       "0  01M292  HENRY STREET SCHOOL FOR INTERNATIONAL STUDIES   \n",
       "\n",
       "  Num of SAT Test Takers SAT Critical Reading Avg. Score SAT Math Avg. Score  \\\n",
       "0                     29                             355                 404   \n",
       "\n",
       "  SAT Writing Avg. Score  sat_score SchoolName  AP Test Takers   \\\n",
       "0                    363     1122.0          0       129.028846   \n",
       "\n",
       "   Total Exams Taken     ...                           priority05 priority06  \\\n",
       "0         197.038462     ...      Then to New York City residents          0   \n",
       "\n",
       "  priority07 priority08  priority09 priority10  \\\n",
       "0          0          0           0          0   \n",
       "\n",
       "                                          Location 1        lat       lon  \\\n",
       "0  220 Henry Street\\nNew York, NY 10002\\n(40.7137...  40.713764 -73.98526   \n",
       "\n",
       "  school_dist  \n",
       "0          01  \n",
       "\n",
       "[1 rows x 158 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_first_two_chars(x):\n",
    "    return x[0:2]\n",
    "\n",
    "combined[\"school_dist\"] = combined[\"DBN\"].apply(get_first_two_chars)\n",
    "\n",
    "combined.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Export Data: Save into CSV File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "combined.to_csv(\"combined.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "---\n",
    "# That's it\n",
    "\n",
    "For the cleaning and enriching part. Jump to the [next notebook](Analyzing & Visualizing NYC High School Data | Part 2.ipynb) to analyze and explore!\n",
    "\n",
    "Prepared by Issam Hijazi\n",
    "\n",
    "https://ae.linkedin.com/in/ihijazi\n",
    "\n",
    "@iHijazi"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [default]",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}