School District Dataset.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import dependencies\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read our schools data into pandas\n",
    "csv_path1 = \"data/schools_complete.csv\"\n",
    "schools_df = pd.read_csv(csv_path1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the students data into pandas\n",
    "csv_path2 = \"data/students_complete.csv\"\n",
    "students_df = pd.read_csv(csv_path2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find the total number of schools\n",
    "total_schools = schools_df['School ID'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sum down the columns to reach total students and total budget\n",
    "total_students = schools_df['size'].sum()\n",
    "total_budget = schools_df['budget'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate the average Math and Reading scores\n",
    "average_math_score = (students_df['math_score'].sum())/(total_students)\n",
    "average_reading_score = (students_df['reading_score'].sum())/(total_students)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate the percent passing math and percent passing reading scores\n",
    "passing_math = students_df.loc[(students_df['math_score'] >= 70)]\n",
    "number_passing_math = passing_math['math_score'].count()\n",
    "percent_passing_math = (number_passing_math/total_students)*100\n",
    "\n",
    "passing_reading = students_df.loc[(students_df['reading_score'] >= 70)]\n",
    "number_passing_reading = passing_reading['reading_score'].count()\n",
    "percent_passing_reading = (number_passing_reading/total_students)*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate the Overall Passing Rate as the average of the above two\n",
    "overall_passing = (percent_passing_math + percent_passing_reading)/2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Total Schools</th>\n",
       "      <th>Total Students</th>\n",
       "      <th>Total Budget</th>\n",
       "      <th>Average Math Score</th>\n",
       "      <th>Average Reading Score</th>\n",
       "      <th>% Passing Math</th>\n",
       "      <th>% Passing Reading</th>\n",
       "      <th>Overall Passing Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15</td>\n",
       "      <td>39170</td>\n",
       "      <td>24649428</td>\n",
       "      <td>78.985371</td>\n",
       "      <td>81.87784</td>\n",
       "      <td>74.980853</td>\n",
       "      <td>85.805463</td>\n",
       "      <td>80.393158</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Total Schools  Total Students  Total Budget  Average Math Score  \\\n",
       "0             15           39170      24649428           78.985371   \n",
       "\n",
       "   Average Reading Score  % Passing Math  % Passing Reading  \\\n",
       "0               81.87784       74.980853          85.805463   \n",
       "\n",
       "   Overall Passing Rate  \n",
       "0             80.393158  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create the District Summary Table\n",
    "district_summary = pd.DataFrame({\"Total Schools\": [total_schools], \"Total Students\": [total_students], \n",
    "                                    \"Total Budget\": [total_budget], \"Average Math Score\": [average_math_score], \n",
    "                                    \"Average Reading Score\":[average_reading_score], \"% Passing Math\": [percent_passing_math],\n",
    "                                   \"% Passing Reading\": [percent_passing_reading], \"Overall Passing Rate\": [overall_passing]})\n",
    "district_summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TABLE: District Summary. Shows key statistics for the district."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ANALYSIS: The district as a whole, if resources are available, needs to invest more in its students. While some of the charter\n",
    "schools are doing well as is seen in the other tables, the overall passing rate for the district is only incrementally over\n",
    "80%, and the percentage passing math is at 75%. Clearly more must be done for the students."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now, produce the School Summary table. First, find the average math and reading scores\n",
    "sorted_students_df = students_df.groupby(\"school\")\n",
    "average_math_school = sorted_students_df[\"math_score\"].mean().to_frame()\n",
    "average_reading_school = sorted_students_df[\"reading_score\"].mean().to_frame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate the number passing math and number passing reading. From this, we will find out the percent passing each\n",
    "math_passing = students_df[students_df[\"math_score\"] > 70].groupby('school').count()[\"math_score\"].to_frame()\n",
    "reading_passing = students_df[students_df[\"reading_score\"] > 70].groupby('school').count()[\"reading_score\"].to_frame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Count the total number of students per school and save as DataFrame\n",
    "students_in_school = sorted_students_df[\"school\"].count().to_frame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a table that lists all of the scores for each school and save as DataFrame\n",
    "from functools import reduce\n",
    "\n",
    "mergeVars = [average_math_school, average_reading_school, math_passing, reading_passing, students_in_school]\n",
    "scores_school = reduce(lambda left, right: pd.merge(left, right, left_index = True, right_index = True), mergeVars)\n",
    "scores_school.columns = [\"Average Math Score\", \"Average Reading Score\", \"n Passed Math\", \"n Passed Reading\", \"# Students\"]\n",
    "scores_school[\"% Passing Math\"] = (scores_school[\"n Passed Math\"] / scores_school[\"# Students\"] * 100)\n",
    "scores_school[\"% Passing Reading\"] = (scores_school[\"n Passed Reading\"] / scores_school[\"# Students\"] * 100)\n",
    "scores_school[\"Overall Passing Rate\"] = (scores_school[\"% Passing Math\"]+scores_school[\"% Passing Reading\"])/2\n",
    "scores_school[\"Total Students\"] = scores_school[\"# Students\"]\n",
    "scores_school = scores_school[[\"Total Students\", \"Average Math Score\", \"Average Reading Score\", \"% Passing Math\", \"% Passing Reading\", \"Overall Passing Rate\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now create an overview table summarizing key statistics about each school. First calculate the Per Student Budget\n",
    "schools_df[\"Per student budget\"] = schools_df['budget']/schools_df['size']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rename the columns in the schools DataFrame\n",
    "schools_df = schools_df.rename(columns = {\"name\": \"school\", \"type\": \"School Type\", \"size\": \"Total Students\", \"budget\": \"Total budget\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge the two DataFrames(schools data frame and school scores data frame) into one table\n",
    "school_summary = pd.merge(schools_df, scores_school, on = \"Total Students\", how = \"outer\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>School ID</th>\n",
       "      <th>school</th>\n",
       "      <th>School Type</th>\n",
       "      <th>Total Students</th>\n",
       "      <th>Total budget</th>\n",
       "      <th>Per student budget</th>\n",
       "      <th>Average Math Score</th>\n",
       "      <th>Average Reading Score</th>\n",
       "      <th>% Passing Math</th>\n",
       "      <th>% Passing Reading</th>\n",
       "      <th>Overall Passing Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>District</td>\n",
       "      <td>2917</td>\n",
       "      <td>1910635</td>\n",
       "      <td>655.0</td>\n",
       "      <td>76.629414</td>\n",
       "      <td>81.182722</td>\n",
       "      <td>63.318478</td>\n",
       "      <td>78.813850</td>\n",
       "      <td>71.066164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Figueroa High School</td>\n",
       "      <td>District</td>\n",
       "      <td>2949</td>\n",
       "      <td>1884411</td>\n",
       "      <td>639.0</td>\n",
       "      <td>76.711767</td>\n",
       "      <td>81.158020</td>\n",
       "      <td>63.750424</td>\n",
       "      <td>78.433367</td>\n",
       "      <td>71.091896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Shelton High School</td>\n",
       "      <td>Charter</td>\n",
       "      <td>1761</td>\n",
       "      <td>1056600</td>\n",
       "      <td>600.0</td>\n",
       "      <td>83.359455</td>\n",
       "      <td>83.725724</td>\n",
       "      <td>89.892107</td>\n",
       "      <td>92.617831</td>\n",
       "      <td>91.254969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Hernandez High School</td>\n",
       "      <td>District</td>\n",
       "      <td>4635</td>\n",
       "      <td>3022020</td>\n",
       "      <td>652.0</td>\n",
       "      <td>77.289752</td>\n",
       "      <td>80.934412</td>\n",
       "      <td>64.746494</td>\n",
       "      <td>78.187702</td>\n",
       "      <td>71.467098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Griffin High School</td>\n",
       "      <td>Charter</td>\n",
       "      <td>1468</td>\n",
       "      <td>917500</td>\n",
       "      <td>625.0</td>\n",
       "      <td>83.351499</td>\n",
       "      <td>83.816757</td>\n",
       "      <td>89.713896</td>\n",
       "      <td>93.392371</td>\n",
       "      <td>91.553134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>Wilson High School</td>\n",
       "      <td>Charter</td>\n",
       "      <td>2283</td>\n",
       "      <td>1319574</td>\n",
       "      <td>578.0</td>\n",
       "      <td>83.274201</td>\n",
       "      <td>83.989488</td>\n",
       "      <td>90.932983</td>\n",
       "      <td>93.254490</td>\n",
       "      <td>92.093736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>Cabrera High School</td>\n",
       "      <td>Charter</td>\n",
       "      <td>1858</td>\n",
       "      <td>1081356</td>\n",
       "      <td>582.0</td>\n",
       "      <td>83.061895</td>\n",
       "      <td>83.975780</td>\n",
       "      <td>89.558665</td>\n",
       "      <td>93.864370</td>\n",
       "      <td>91.711518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>Bailey High School</td>\n",
       "      <td>District</td>\n",
       "      <td>4976</td>\n",
       "      <td>3124928</td>\n",
       "      <td>628.0</td>\n",
       "      <td>77.048432</td>\n",
       "      <td>81.033963</td>\n",
       "      <td>64.630225</td>\n",
       "      <td>79.300643</td>\n",
       "      <td>71.965434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>Holden High School</td>\n",
       "      <td>Charter</td>\n",
       "      <td>427</td>\n",
       "      <td>248087</td>\n",
       "      <td>581.0</td>\n",
       "      <td>83.803279</td>\n",
       "      <td>83.814988</td>\n",
       "      <td>90.632319</td>\n",
       "      <td>92.740047</td>\n",
       "      <td>91.686183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>Pena High School</td>\n",
       "      <td>Charter</td>\n",
       "      <td>962</td>\n",
       "      <td>585858</td>\n",
       "      <td>609.0</td>\n",
       "      <td>83.839917</td>\n",
       "      <td>84.044699</td>\n",
       "      <td>91.683992</td>\n",
       "      <td>92.203742</td>\n",
       "      <td>91.943867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>Wright High School</td>\n",
       "      <td>Charter</td>\n",
       "      <td>1800</td>\n",
       "      <td>1049400</td>\n",
       "      <td>583.0</td>\n",
       "      <td>83.682222</td>\n",
       "      <td>83.955000</td>\n",
       "      <td>90.277778</td>\n",
       "      <td>93.444444</td>\n",
       "      <td>91.861111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>Rodriguez High School</td>\n",
       "      <td>District</td>\n",
       "      <td>3999</td>\n",
       "      <td>2547363</td>\n",
       "      <td>637.0</td>\n",
       "      <td>76.842711</td>\n",
       "      <td>80.744686</td>\n",
       "      <td>64.066017</td>\n",
       "      <td>77.744436</td>\n",
       "      <td>70.905226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>Johnson High School</td>\n",
       "      <td>District</td>\n",
       "      <td>4761</td>\n",
       "      <td>3094650</td>\n",
       "      <td>650.0</td>\n",
       "      <td>77.072464</td>\n",
       "      <td>80.966394</td>\n",
       "      <td>63.852132</td>\n",
       "      <td>78.281874</td>\n",
       "      <td>71.067003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>Ford High School</td>\n",
       "      <td>District</td>\n",
       "      <td>2739</td>\n",
       "      <td>1763916</td>\n",
       "      <td>644.0</td>\n",
       "      <td>77.102592</td>\n",
       "      <td>80.746258</td>\n",
       "      <td>65.753925</td>\n",
       "      <td>77.510040</td>\n",
       "      <td>71.631982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>Charter</td>\n",
       "      <td>1635</td>\n",
       "      <td>1043130</td>\n",
       "      <td>638.0</td>\n",
       "      <td>83.418349</td>\n",
       "      <td>83.848930</td>\n",
       "      <td>90.214067</td>\n",
       "      <td>92.905199</td>\n",
       "      <td>91.559633</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    School ID                 school School Type  Total Students  \\\n",
       "0           0      Huang High School    District            2917   \n",
       "1           1   Figueroa High School    District            2949   \n",
       "2           2    Shelton High School     Charter            1761   \n",
       "3           3  Hernandez High School    District            4635   \n",
       "4           4    Griffin High School     Charter            1468   \n",
       "5           5     Wilson High School     Charter            2283   \n",
       "6           6    Cabrera High School     Charter            1858   \n",
       "7           7     Bailey High School    District            4976   \n",
       "8           8     Holden High School     Charter             427   \n",
       "9           9       Pena High School     Charter             962   \n",
       "10         10     Wright High School     Charter            1800   \n",
       "11         11  Rodriguez High School    District            3999   \n",
       "12         12    Johnson High School    District            4761   \n",
       "13         13       Ford High School    District            2739   \n",
       "14         14     Thomas High School     Charter            1635   \n",
       "\n",
       "    Total budget  Per student budget  Average Math Score  \\\n",
       "0        1910635               655.0           76.629414   \n",
       "1        1884411               639.0           76.711767   \n",
       "2        1056600               600.0           83.359455   \n",
       "3        3022020               652.0           77.289752   \n",
       "4         917500               625.0           83.351499   \n",
       "5        1319574               578.0           83.274201   \n",
       "6        1081356               582.0           83.061895   \n",
       "7        3124928               628.0           77.048432   \n",
       "8         248087               581.0           83.803279   \n",
       "9         585858               609.0           83.839917   \n",
       "10       1049400               583.0           83.682222   \n",
       "11       2547363               637.0           76.842711   \n",
       "12       3094650               650.0           77.072464   \n",
       "13       1763916               644.0           77.102592   \n",
       "14       1043130               638.0           83.418349   \n",
       "\n",
       "    Average Reading Score  % Passing Math  % Passing Reading  \\\n",
       "0               81.182722       63.318478          78.813850   \n",
       "1               81.158020       63.750424          78.433367   \n",
       "2               83.725724       89.892107          92.617831   \n",
       "3               80.934412       64.746494          78.187702   \n",
       "4               83.816757       89.713896          93.392371   \n",
       "5               83.989488       90.932983          93.254490   \n",
       "6               83.975780       89.558665          93.864370   \n",
       "7               81.033963       64.630225          79.300643   \n",
       "8               83.814988       90.632319          92.740047   \n",
       "9               84.044699       91.683992          92.203742   \n",
       "10              83.955000       90.277778          93.444444   \n",
       "11              80.744686       64.066017          77.744436   \n",
       "12              80.966394       63.852132          78.281874   \n",
       "13              80.746258       65.753925          77.510040   \n",
       "14              83.848930       90.214067          92.905199   \n",
       "\n",
       "    Overall Passing Rate  \n",
       "0              71.066164  \n",
       "1              71.091896  \n",
       "2              91.254969  \n",
       "3              71.467098  \n",
       "4              91.553134  \n",
       "5              92.093736  \n",
       "6              91.711518  \n",
       "7              71.965434  \n",
       "8              91.686183  \n",
       "9              91.943867  \n",
       "10             91.861111  \n",
       "11             70.905226  \n",
       "12             71.067003  \n",
       "13             71.631982  \n",
       "14             91.559633  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Finalize our summary table that includes all of the information from each school\n",
    "school_summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TABLE: School Summary. This table summarizes the key details and statistics for each school."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ANALYSIS: Note that the district schools as a group are larger, have a higher per student budget, and have lower scores and passing rates. This is a general trend."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>School ID</th>\n",
       "      <th>school</th>\n",
       "      <th>School Type</th>\n",
       "      <th>Total Students</th>\n",
       "      <th>Total budget</th>\n",
       "      <th>Per student budget</th>\n",
       "      <th>Average Math Score</th>\n",
       "      <th>Average Reading Score</th>\n",
       "      <th>% Passing Math</th>\n",
       "      <th>% Passing Reading</th>\n",
       "      <th>Overall Passing Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>Wilson High School</td>\n",
       "      <td>Charter</td>\n",
       "      <td>2283</td>\n",
       "      <td>1319574</td>\n",
       "      <td>578.0</td>\n",
       "      <td>83.274201</td>\n",
       "      <td>83.989488</td>\n",
       "      <td>90.932983</td>\n",
       "      <td>93.254490</td>\n",
       "      <td>92.093736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>Pena High School</td>\n",
       "      <td>Charter</td>\n",
       "      <td>962</td>\n",
       "      <td>585858</td>\n",
       "      <td>609.0</td>\n",
       "      <td>83.839917</td>\n",
       "      <td>84.044699</td>\n",
       "      <td>91.683992</td>\n",
       "      <td>92.203742</td>\n",
       "      <td>91.943867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>Wright High School</td>\n",
       "      <td>Charter</td>\n",
       "      <td>1800</td>\n",
       "      <td>1049400</td>\n",
       "      <td>583.0</td>\n",
       "      <td>83.682222</td>\n",
       "      <td>83.955000</td>\n",
       "      <td>90.277778</td>\n",
       "      <td>93.444444</td>\n",
       "      <td>91.861111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>Cabrera High School</td>\n",
       "      <td>Charter</td>\n",
       "      <td>1858</td>\n",
       "      <td>1081356</td>\n",
       "      <td>582.0</td>\n",
       "      <td>83.061895</td>\n",
       "      <td>83.975780</td>\n",
       "      <td>89.558665</td>\n",
       "      <td>93.864370</td>\n",
       "      <td>91.711518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>Holden High School</td>\n",
       "      <td>Charter</td>\n",
       "      <td>427</td>\n",
       "      <td>248087</td>\n",
       "      <td>581.0</td>\n",
       "      <td>83.803279</td>\n",
       "      <td>83.814988</td>\n",
       "      <td>90.632319</td>\n",
       "      <td>92.740047</td>\n",
       "      <td>91.686183</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  School ID               school School Type  Total Students  \\\n",
       "0      5          5   Wilson High School     Charter            2283   \n",
       "1      9          9     Pena High School     Charter             962   \n",
       "2     10         10   Wright High School     Charter            1800   \n",
       "3      6          6  Cabrera High School     Charter            1858   \n",
       "4      8          8   Holden High School     Charter             427   \n",
       "\n",
       "   Total budget  Per student budget  Average Math Score  \\\n",
       "0       1319574               578.0           83.274201   \n",
       "1        585858               609.0           83.839917   \n",
       "2       1049400               583.0           83.682222   \n",
       "3       1081356               582.0           83.061895   \n",
       "4        248087               581.0           83.803279   \n",
       "\n",
       "   Average Reading Score  % Passing Math  % Passing Reading  \\\n",
       "0              83.989488       90.932983          93.254490   \n",
       "1              84.044699       91.683992          92.203742   \n",
       "2              83.955000       90.277778          93.444444   \n",
       "3              83.975780       89.558665          93.864370   \n",
       "4              83.814988       90.632319          92.740047   \n",
       "\n",
       "   Overall Passing Rate  \n",
       "0             92.093736  \n",
       "1             91.943867  \n",
       "2             91.861111  \n",
       "3             91.711518  \n",
       "4             91.686183  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now, create a table of the five top performing schools based on Overall Passing Rate\n",
    "\n",
    "topSchools = school_summary.sort_values(\"Overall Passing Rate\", ascending = False).reset_index()\n",
    "topSchools = topSchools.loc[0:4,:]\n",
    "topSchools"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TABLE: Top Performing Schools. This table summarizes the key details and statistics for the five schools with the highest overall passing rate."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ANALYSIS: All of the top five performing schools are Charter schools."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>School ID</th>\n",
       "      <th>school</th>\n",
       "      <th>School Type</th>\n",
       "      <th>Total Students</th>\n",
       "      <th>Total budget</th>\n",
       "      <th>Per student budget</th>\n",
       "      <th>Average Math Score</th>\n",
       "      <th>Average Reading Score</th>\n",
       "      <th>% Passing Math</th>\n",
       "      <th>% Passing Reading</th>\n",
       "      <th>Overall Passing Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>Rodriguez High School</td>\n",
       "      <td>District</td>\n",
       "      <td>3999</td>\n",
       "      <td>2547363</td>\n",
       "      <td>637.0</td>\n",
       "      <td>76.842711</td>\n",
       "      <td>80.744686</td>\n",
       "      <td>64.066017</td>\n",
       "      <td>77.744436</td>\n",
       "      <td>70.905226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>District</td>\n",
       "      <td>2917</td>\n",
       "      <td>1910635</td>\n",
       "      <td>655.0</td>\n",
       "      <td>76.629414</td>\n",
       "      <td>81.182722</td>\n",
       "      <td>63.318478</td>\n",
       "      <td>78.813850</td>\n",
       "      <td>71.066164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>Johnson High School</td>\n",
       "      <td>District</td>\n",
       "      <td>4761</td>\n",
       "      <td>3094650</td>\n",
       "      <td>650.0</td>\n",
       "      <td>77.072464</td>\n",
       "      <td>80.966394</td>\n",
       "      <td>63.852132</td>\n",
       "      <td>78.281874</td>\n",
       "      <td>71.067003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Figueroa High School</td>\n",
       "      <td>District</td>\n",
       "      <td>2949</td>\n",
       "      <td>1884411</td>\n",
       "      <td>639.0</td>\n",
       "      <td>76.711767</td>\n",
       "      <td>81.158020</td>\n",
       "      <td>63.750424</td>\n",
       "      <td>78.433367</td>\n",
       "      <td>71.091896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>Hernandez High School</td>\n",
       "      <td>District</td>\n",
       "      <td>4635</td>\n",
       "      <td>3022020</td>\n",
       "      <td>652.0</td>\n",
       "      <td>77.289752</td>\n",
       "      <td>80.934412</td>\n",
       "      <td>64.746494</td>\n",
       "      <td>78.187702</td>\n",
       "      <td>71.467098</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  School ID                 school School Type  Total Students  \\\n",
       "0     11         11  Rodriguez High School    District            3999   \n",
       "1      0          0      Huang High School    District            2917   \n",
       "2     12         12    Johnson High School    District            4761   \n",
       "3      1          1   Figueroa High School    District            2949   \n",
       "4      3          3  Hernandez High School    District            4635   \n",
       "\n",
       "   Total budget  Per student budget  Average Math Score  \\\n",
       "0       2547363               637.0           76.842711   \n",
       "1       1910635               655.0           76.629414   \n",
       "2       3094650               650.0           77.072464   \n",
       "3       1884411               639.0           76.711767   \n",
       "4       3022020               652.0           77.289752   \n",
       "\n",
       "   Average Reading Score  % Passing Math  % Passing Reading  \\\n",
       "0              80.744686       64.066017          77.744436   \n",
       "1              81.182722       63.318478          78.813850   \n",
       "2              80.966394       63.852132          78.281874   \n",
       "3              81.158020       63.750424          78.433367   \n",
       "4              80.934412       64.746494          78.187702   \n",
       "\n",
       "   Overall Passing Rate  \n",
       "0             70.905226  \n",
       "1             71.066164  \n",
       "2             71.067003  \n",
       "3             71.091896  \n",
       "4             71.467098  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now, create a table of the bottom five schools based on the same\n",
    "\n",
    "bottomSchools = school_summary.sort_values(\"Overall Passing Rate\", ascending = True).reset_index()\n",
    "bottomSchools = bottomSchools.loc[0:4,:]\n",
    "bottomSchools"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TABLE: Bottom Performing Schools. This table summarizes the key details and statistics for the five schools with the lowest overall passing rate."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ANALYSIS: All of the bottom five performing schools are Charter schools. This sends an obvious message about the disparity between the quality of education in district versus charter schools."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now, work on the Math and Reading Scores by Grade. First, sort students by grade as how in the School Summary problem they are sorted by school\n",
    "sorted_students_df = students_df.groupby(\"school\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Math Scores by Grades\n",
    "\n",
    "# 9th grade\n",
    "math9 = students_df[students_df[\"grade\"] == \"9th\"].groupby('school')[\"math_score\"].mean().to_frame()\n",
    "math9 = math9.rename(columns = {\"math_score\": \"9th\"})\n",
    "\n",
    "# 10th grade\n",
    "math10 = students_df[students_df[\"grade\"] == \"10th\"].groupby('school')[\"math_score\"].mean().to_frame()\n",
    "math10 = math10.rename(columns = {\"math_score\": \"10th\"})\n",
    "\n",
    "# 11th grade\n",
    "math11 = students_df[students_df[\"grade\"] == \"11th\"].groupby('school')[\"math_score\"].mean().to_frame()\n",
    "math11 = math11.rename(columns = {\"math_score\": \"11th\"})\n",
    "\n",
    "# 12th grade\n",
    "math12 = students_df[students_df[\"grade\"] == \"12th\"].groupby('school')[\"math_score\"].mean().to_frame()\n",
    "math12 = math12.rename(columns = {\"math_score\": \"12th\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>school</th>\n",
       "      <th>9th</th>\n",
       "      <th>10th</th>\n",
       "      <th>11th</th>\n",
       "      <th>12th</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bailey High School</td>\n",
       "      <td>77.083676</td>\n",
       "      <td>76.996772</td>\n",
       "      <td>77.515588</td>\n",
       "      <td>76.492218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Cabrera High School</td>\n",
       "      <td>83.094697</td>\n",
       "      <td>83.154506</td>\n",
       "      <td>82.765560</td>\n",
       "      <td>83.277487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Figueroa High School</td>\n",
       "      <td>76.403037</td>\n",
       "      <td>76.539974</td>\n",
       "      <td>76.884344</td>\n",
       "      <td>77.151369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ford High School</td>\n",
       "      <td>77.361345</td>\n",
       "      <td>77.672316</td>\n",
       "      <td>76.918058</td>\n",
       "      <td>76.179963</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Griffin High School</td>\n",
       "      <td>82.044010</td>\n",
       "      <td>84.229064</td>\n",
       "      <td>83.842105</td>\n",
       "      <td>83.356164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Hernandez High School</td>\n",
       "      <td>77.438495</td>\n",
       "      <td>77.337408</td>\n",
       "      <td>77.136029</td>\n",
       "      <td>77.186567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Holden High School</td>\n",
       "      <td>83.787402</td>\n",
       "      <td>83.429825</td>\n",
       "      <td>85.000000</td>\n",
       "      <td>82.855422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Huang High School</td>\n",
       "      <td>77.027251</td>\n",
       "      <td>75.908735</td>\n",
       "      <td>76.446602</td>\n",
       "      <td>77.225641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Johnson High School</td>\n",
       "      <td>77.187857</td>\n",
       "      <td>76.691117</td>\n",
       "      <td>77.491653</td>\n",
       "      <td>76.863248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Pena High School</td>\n",
       "      <td>83.625455</td>\n",
       "      <td>83.372000</td>\n",
       "      <td>84.328125</td>\n",
       "      <td>84.121547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Rodriguez High School</td>\n",
       "      <td>76.859966</td>\n",
       "      <td>76.612500</td>\n",
       "      <td>76.395626</td>\n",
       "      <td>77.690748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Shelton High School</td>\n",
       "      <td>83.420755</td>\n",
       "      <td>82.917411</td>\n",
       "      <td>83.383495</td>\n",
       "      <td>83.778976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>83.590022</td>\n",
       "      <td>83.087886</td>\n",
       "      <td>83.498795</td>\n",
       "      <td>83.497041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Wilson High School</td>\n",
       "      <td>83.085578</td>\n",
       "      <td>83.724422</td>\n",
       "      <td>83.195326</td>\n",
       "      <td>83.035794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Wright High School</td>\n",
       "      <td>83.264706</td>\n",
       "      <td>84.010288</td>\n",
       "      <td>83.836782</td>\n",
       "      <td>83.644986</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   school        9th       10th       11th       12th\n",
       "0      Bailey High School  77.083676  76.996772  77.515588  76.492218\n",
       "1     Cabrera High School  83.094697  83.154506  82.765560  83.277487\n",
       "2    Figueroa High School  76.403037  76.539974  76.884344  77.151369\n",
       "3        Ford High School  77.361345  77.672316  76.918058  76.179963\n",
       "4     Griffin High School  82.044010  84.229064  83.842105  83.356164\n",
       "5   Hernandez High School  77.438495  77.337408  77.136029  77.186567\n",
       "6      Holden High School  83.787402  83.429825  85.000000  82.855422\n",
       "7       Huang High School  77.027251  75.908735  76.446602  77.225641\n",
       "8     Johnson High School  77.187857  76.691117  77.491653  76.863248\n",
       "9        Pena High School  83.625455  83.372000  84.328125  84.121547\n",
       "10  Rodriguez High School  76.859966  76.612500  76.395626  77.690748\n",
       "11    Shelton High School  83.420755  82.917411  83.383495  83.778976\n",
       "12     Thomas High School  83.590022  83.087886  83.498795  83.497041\n",
       "13     Wilson High School  83.085578  83.724422  83.195326  83.035794\n",
       "14     Wright High School  83.264706  84.010288  83.836782  83.644986"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge grade dfs together\n",
    "from functools import reduce\n",
    "\n",
    "mergemath = [math9, math10, math11, math12]\n",
    "math_Grades = reduce(lambda left, right: pd.merge(left, right, left_index = True, right_index = True), mergemath)\n",
    "math_Grades.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TABLE: Math Scores By Grade. This table shows the average math scores for students in grades 9, 10, 11, and 12 by school."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ANALYSIS: The scores across grades are pretty consistent. Schools tend to score either in the 77% area or the 83% area with little variation among grades. This performance still leaves much to be desired, however."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reading scores by grades\n",
    "\n",
    "# 9th grade\n",
    "reading9 = students_df[students_df[\"grade\"] == \"9th\"].groupby('school')[\"reading_score\"].mean().to_frame()\n",
    "reading9 = reading9.rename(columns = {\"reading_score\": \"9th\"})\n",
    "\n",
    "# 10th grade\n",
    "reading10 = students_df[students_df[\"grade\"] == \"10th\"].groupby('school')[\"reading_score\"].mean().to_frame()\n",
    "reading10 = reading10.rename(columns = {\"reading_score\": \"10th\"})\n",
    "\n",
    "# 11th grade\n",
    "reading11 = students_df[students_df[\"grade\"] == \"11th\"].groupby('school')[\"reading_score\"].mean().to_frame()\n",
    "reading11 = reading11.rename(columns = {\"reading_score\": \"11th\"})\n",
    "\n",
    "# 12th grade\n",
    "reading12 = students_df[students_df[\"grade\"] == \"12th\"].groupby('school')[\"reading_score\"].mean().to_frame()\n",
    "reading12 = reading12.rename(columns = {\"reading_score\": \"12th\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>school</th>\n",
       "      <th>9th</th>\n",
       "      <th>10th</th>\n",
       "      <th>11th</th>\n",
       "      <th>12th</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bailey High School</td>\n",
       "      <td>81.303155</td>\n",
       "      <td>80.907183</td>\n",
       "      <td>80.945643</td>\n",
       "      <td>80.912451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Cabrera High School</td>\n",
       "      <td>83.676136</td>\n",
       "      <td>84.253219</td>\n",
       "      <td>83.788382</td>\n",
       "      <td>84.287958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Figueroa High School</td>\n",
       "      <td>81.198598</td>\n",
       "      <td>81.408912</td>\n",
       "      <td>80.640339</td>\n",
       "      <td>81.384863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ford High School</td>\n",
       "      <td>80.632653</td>\n",
       "      <td>81.262712</td>\n",
       "      <td>80.403642</td>\n",
       "      <td>80.662338</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Griffin High School</td>\n",
       "      <td>83.369193</td>\n",
       "      <td>83.706897</td>\n",
       "      <td>84.288089</td>\n",
       "      <td>84.013699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Hernandez High School</td>\n",
       "      <td>80.866860</td>\n",
       "      <td>80.660147</td>\n",
       "      <td>81.396140</td>\n",
       "      <td>80.857143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Holden High School</td>\n",
       "      <td>83.677165</td>\n",
       "      <td>83.324561</td>\n",
       "      <td>83.815534</td>\n",
       "      <td>84.698795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Huang High School</td>\n",
       "      <td>81.290284</td>\n",
       "      <td>81.512386</td>\n",
       "      <td>81.417476</td>\n",
       "      <td>80.305983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Johnson High School</td>\n",
       "      <td>81.260714</td>\n",
       "      <td>80.773431</td>\n",
       "      <td>80.616027</td>\n",
       "      <td>81.227564</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Pena High School</td>\n",
       "      <td>83.807273</td>\n",
       "      <td>83.612000</td>\n",
       "      <td>84.335938</td>\n",
       "      <td>84.591160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Rodriguez High School</td>\n",
       "      <td>80.993127</td>\n",
       "      <td>80.629808</td>\n",
       "      <td>80.864811</td>\n",
       "      <td>80.376426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Shelton High School</td>\n",
       "      <td>84.122642</td>\n",
       "      <td>83.441964</td>\n",
       "      <td>84.373786</td>\n",
       "      <td>82.781671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>83.728850</td>\n",
       "      <td>84.254157</td>\n",
       "      <td>83.585542</td>\n",
       "      <td>83.831361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Wilson High School</td>\n",
       "      <td>83.939778</td>\n",
       "      <td>84.021452</td>\n",
       "      <td>83.764608</td>\n",
       "      <td>84.317673</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Wright High School</td>\n",
       "      <td>83.833333</td>\n",
       "      <td>83.812757</td>\n",
       "      <td>84.156322</td>\n",
       "      <td>84.073171</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   school        9th       10th       11th       12th\n",
       "0      Bailey High School  81.303155  80.907183  80.945643  80.912451\n",
       "1     Cabrera High School  83.676136  84.253219  83.788382  84.287958\n",
       "2    Figueroa High School  81.198598  81.408912  80.640339  81.384863\n",
       "3        Ford High School  80.632653  81.262712  80.403642  80.662338\n",
       "4     Griffin High School  83.369193  83.706897  84.288089  84.013699\n",
       "5   Hernandez High School  80.866860  80.660147  81.396140  80.857143\n",
       "6      Holden High School  83.677165  83.324561  83.815534  84.698795\n",
       "7       Huang High School  81.290284  81.512386  81.417476  80.305983\n",
       "8     Johnson High School  81.260714  80.773431  80.616027  81.227564\n",
       "9        Pena High School  83.807273  83.612000  84.335938  84.591160\n",
       "10  Rodriguez High School  80.993127  80.629808  80.864811  80.376426\n",
       "11    Shelton High School  84.122642  83.441964  84.373786  82.781671\n",
       "12     Thomas High School  83.728850  84.254157  83.585542  83.831361\n",
       "13     Wilson High School  83.939778  84.021452  83.764608  84.317673\n",
       "14     Wright High School  83.833333  83.812757  84.156322  84.073171"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge grade dfs together\n",
    "from functools import reduce\n",
    "\n",
    "mergereading = [reading9, reading10, reading11, reading12]\n",
    "reading_Grades = reduce(lambda left, right: pd.merge(left, right, left_index = True, right_index = True), mergereading)\n",
    "reading_Grades.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TABLE: Reading Scores By Grade. This table shows the average reading scores for students in grades 9, 10, 11, and 12 by school."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ANALYSIS: When scanning the math and reading scores by grade tables, there is a clear difference between math and reading scores: reading scores tend to be 4 to 5 percentage points higher than math scores. This difference suggests that an improvement in curriculum should be made. More time or resources should be spent on math so that students score equally well in both subjects."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now, work on creating a data frame for scores by school spending\n",
    "# Create the basic skeleton\n",
    "school_budget = school_summary[['Per student budget',\n",
    "                             'Average Math Score',\n",
    "                             'Average Reading Score',\n",
    "                             '% Passing Math',\n",
    "                             '% Passing Reading',\n",
    "                             'Overall Passing Rate']]       \\\n",
    "                .reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create reasonable bins with which to group school spending into\n",
    "bins = [575, 595, 615, 635, 655]\n",
    "\n",
    "# Create labels for these bins\n",
    "group_labels = [\"575 to 595\", \"595 to 615\", \"615 to 635\", \"635 to 655\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Slice the data and place it into bins\n",
    "school_budget[\"Per student budget group\"] = pd.cut(school_budget[\"Per student budget\"], bins, labels=group_labels)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Per student budget group\n",
      "575 to 595    4\n",
      "595 to 615    2\n",
      "615 to 635    2\n",
      "635 to 655    7\n",
      "Name: index, dtype: int64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Average Math Score</th>\n",
       "      <th>Average Reading Score</th>\n",
       "      <th>% Passing Math</th>\n",
       "      <th>% Passing Reading</th>\n",
       "      <th>Overall Passing Rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Per student budget group</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>575 to 595</th>\n",
       "      <td>83.455399</td>\n",
       "      <td>83.933814</td>\n",
       "      <td>90.350436</td>\n",
       "      <td>93.325838</td>\n",
       "      <td>91.838137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>595 to 615</th>\n",
       "      <td>83.599686</td>\n",
       "      <td>83.885211</td>\n",
       "      <td>90.788049</td>\n",
       "      <td>92.410786</td>\n",
       "      <td>91.599418</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>615 to 635</th>\n",
       "      <td>80.199966</td>\n",
       "      <td>82.425360</td>\n",
       "      <td>77.172061</td>\n",
       "      <td>86.346507</td>\n",
       "      <td>81.759284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>635 to 655</th>\n",
       "      <td>77.866721</td>\n",
       "      <td>81.368774</td>\n",
       "      <td>67.957362</td>\n",
       "      <td>80.268067</td>\n",
       "      <td>74.112715</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Average Math Score  Average Reading Score  \\\n",
       "Per student budget group                                              \n",
       "575 to 595                         83.455399              83.933814   \n",
       "595 to 615                         83.599686              83.885211   \n",
       "615 to 635                         80.199966              82.425360   \n",
       "635 to 655                         77.866721              81.368774   \n",
       "\n",
       "                          % Passing Math  % Passing Reading  \\\n",
       "Per student budget group                                      \n",
       "575 to 595                     90.350436          93.325838   \n",
       "595 to 615                     90.788049          92.410786   \n",
       "615 to 635                     77.172061          86.346507   \n",
       "635 to 655                     67.957362          80.268067   \n",
       "\n",
       "                          Overall Passing Rate  \n",
       "Per student budget group                        \n",
       "575 to 595                           91.838137  \n",
       "595 to 615                           91.599418  \n",
       "615 to 635                           81.759284  \n",
       "635 to 655                           74.112715  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Final commands to generate a scores by spending report\n",
    "school_budget = school_budget.groupby(\"Per student budget group\")\n",
    "print(school_budget[\"index\"].count())\n",
    "school_budget[[\"Average Math Score\", \"Average Reading Score\", \"% Passing Math\", \"% Passing Reading\", \"Overall Passing Rate\"]].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TABLE: Scores by School Spending. This table shows the passing rate for different school spending groups."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ANALYSIS: Surprisingly, the overall passing rate is lowest for the students in the highest per student budget group. However, we will also see that the district schools have a much lower passing rate than the charter schools - so it seems that district schools spend more resources per student and are also less efficient (ie. perform worse)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now, we want to create a DataFrame conveying the school scores by size of school.\n",
    "# Repeat the process of the above section, but include Total Students rather than Per student budget:\n",
    "school_size = school_summary[['Total Students',\n",
    "                             'Average Math Score',\n",
    "                             'Average Reading Score',\n",
    "                             '% Passing Math',\n",
    "                             '% Passing Reading',\n",
    "                             'Overall Passing Rate']]       \\\n",
    "                .reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create reasonable bins with which to group school spending into\n",
    "bins2 = [400, 2000, 3600, 5200]\n",
    "\n",
    "# Create labels for these bins\n",
    "group_labels2 = [\"Small\", \"Medium\", \"Large\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Slice the data and place it into bins\n",
    "pd.cut(school_size[\"Total Students\"], bins2, labels=group_labels2)\n",
    "school_size[\"School Size\"] = pd.cut(school_size[\"Total Students\"], bins2, labels=group_labels2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "School Size\n",
      "Small     7\n",
      "Medium    4\n",
      "Large     4\n",
      "Name: index, dtype: int64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Average Math Score</th>\n",
       "      <th>Average Reading Score</th>\n",
       "      <th>% Passing Math</th>\n",
       "      <th>% Passing Reading</th>\n",
       "      <th>Overall Passing Rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>School Size</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>Small</th>\n",
       "      <td>83.502373</td>\n",
       "      <td>83.883125</td>\n",
       "      <td>90.281832</td>\n",
       "      <td>93.024001</td>\n",
       "      <td>91.652916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Medium</th>\n",
       "      <td>78.429493</td>\n",
       "      <td>81.769122</td>\n",
       "      <td>70.938952</td>\n",
       "      <td>82.002937</td>\n",
       "      <td>76.470945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Large</th>\n",
       "      <td>77.063340</td>\n",
       "      <td>80.919864</td>\n",
       "      <td>64.323717</td>\n",
       "      <td>78.378664</td>\n",
       "      <td>71.351190</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Average Math Score  Average Reading Score  % Passing Math  \\\n",
       "School Size                                                              \n",
       "Small                 83.502373              83.883125       90.281832   \n",
       "Medium                78.429493              81.769122       70.938952   \n",
       "Large                 77.063340              80.919864       64.323717   \n",
       "\n",
       "             % Passing Reading  Overall Passing Rate  \n",
       "School Size                                           \n",
       "Small                93.024001             91.652916  \n",
       "Medium               82.002937             76.470945  \n",
       "Large                78.378664             71.351190  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Finalize a scores by school size report\n",
    "school_size = school_size.groupby(\"School Size\")\n",
    "print(school_size[\"index\"].count())\n",
    "school_size[[\"Average Math Score\", \"Average Reading Score\", \"% Passing Math\", \"% Passing Reading\",\n",
    "                \"Overall Passing Rate\"]].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TABLE: Scores by School Size. This table shows the scores and passing rates for schools of different sizes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ANALYSIS: Here we see that the larger schools perform the worst and the medium schools also perform poorly. The small schools, which happen to be most of the charter schools, have the highest overall passing rate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now, we are producing a similar table based on school type (district or charter).\n",
    "\n",
    "school_type = school_summary[['School Type',\n",
    "                             'Average Math Score',\n",
    "                             'Average Reading Score',\n",
    "                             '% Passing Math',\n",
    "                             '% Passing Reading',\n",
    "                             'Overall Passing Rate']]       \\\n",
    "                .reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Average Math Score</th>\n",
       "      <th>Average Reading Score</th>\n",
       "      <th>% Passing Math</th>\n",
       "      <th>% Passing Reading</th>\n",
       "      <th>Overall Passing Rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>School Type</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>Charter</th>\n",
       "      <td>83.473852</td>\n",
       "      <td>83.896421</td>\n",
       "      <td>90.363226</td>\n",
       "      <td>93.052812</td>\n",
       "      <td>91.708019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>District</th>\n",
       "      <td>76.956733</td>\n",
       "      <td>80.966636</td>\n",
       "      <td>64.302528</td>\n",
       "      <td>78.324559</td>\n",
       "      <td>71.313543</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Average Math Score  Average Reading Score  % Passing Math  \\\n",
       "School Type                                                              \n",
       "Charter               83.473852              83.896421       90.363226   \n",
       "District              76.956733              80.966636       64.302528   \n",
       "\n",
       "             % Passing Reading  Overall Passing Rate  \n",
       "School Type                                           \n",
       "Charter              93.052812             91.708019  \n",
       "District             78.324559             71.313543  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Produce the final table.\n",
    "school_type = school_type.groupby(\"School Type\")\n",
    "school_type[[\"Average Math Score\", \"Average Reading Score\", \"% Passing Math\", \"% Passing Reading\",\n",
    "                \"Overall Passing Rate\"]].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TABLE: Scores by School Type. This scores and passing rates for district versus charter schools."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ANALYSIS: This is the most striking analysis table. There is a huge difference in performance between the district and charter schools. It is clear that the administration needs to look into and improve the district schools' performance."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}