PyCityAnalysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PythonCity School Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1.\tSchools that spend between 585-615 dollars per student have a better percent passing reading, percent passing math, and percent overall passing rate than schools that spend more than 615 dollars per student. Also, those schools that spend less per student are smaller schools with 2,000 students or less.\n",
    "2.\tIt is also found that the schools with 2000 students or less have a higher percent passing reading, percent passing math, and percent overall passing rate than larger schools that have 2000 – 5000 students. Furthermore, the smaller the school, the less they spend per student.\n",
    "3.\tWith that being said,Charter schools have a higher percent passing reading, percent passing math, and percent overall passing rate than District schools. Thus being the top five performing schools based on percent passing rates, whereas the bottom five performing schools based on percent passing rate are District schools.\n",
    "4. In all 15 schools, the Average Reading Score is higher than the Average Math Score.\n",
    "5. Lasly, for the top 5 performing schools, the percent passing math is about 20 percent lower than the percent passing reading. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dependencies\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The path to our csv file\n",
    "schools_complete = \"Resources/schools_complete.csv\"\n",
    "students_complete = \"Resources/students_complete.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Student ID</th>\n",
       "      <th>name</th>\n",
       "      <th>gender</th>\n",
       "      <th>grade</th>\n",
       "      <th>school</th>\n",
       "      <th>reading_score</th>\n",
       "      <th>math_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Paul Bradley</td>\n",
       "      <td>M</td>\n",
       "      <td>9th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>66</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Victor Smith</td>\n",
       "      <td>M</td>\n",
       "      <td>12th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>94</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Kevin Rodriguez</td>\n",
       "      <td>M</td>\n",
       "      <td>12th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>90</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Dr. Richard Scott</td>\n",
       "      <td>M</td>\n",
       "      <td>12th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>67</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Bonnie Ray</td>\n",
       "      <td>F</td>\n",
       "      <td>9th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>97</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Student ID               name gender grade             school  \\\n",
       "0           0       Paul Bradley      M   9th  Huang High School   \n",
       "1           1       Victor Smith      M  12th  Huang High School   \n",
       "2           2    Kevin Rodriguez      M  12th  Huang High School   \n",
       "3           3  Dr. Richard Scott      M  12th  Huang High School   \n",
       "4           4         Bonnie Ray      F   9th  Huang High School   \n",
       "\n",
       "   reading_score  math_score  \n",
       "0             66          79  \n",
       "1             94          61  \n",
       "2             90          60  \n",
       "3             67          58  \n",
       "4             97          84  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create dataframe using mapping\n",
    "schools_complete_df = pd.read_csv(schools_complete)\n",
    "students_complete_df = pd.read_csv(students_complete)\n",
    "schools_complete_df.head()\n",
    "students_complete_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# District Summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>Percent Passing Math</th>\n",
       "      <th>Percent Passing Reading</th>\n",
       "      <th>Percent Overall Passing Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15</td>\n",
       "      <td>39,170</td>\n",
       "      <td>$24,649,428.00</td>\n",
       "      <td>78.985371</td>\n",
       "      <td>81.87784</td>\n",
       "      <td>72.392137</td>\n",
       "      <td>82.971662</td>\n",
       "      <td>77.6819</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Total Schools Total Students    Total Budget  Average Math Score  \\\n",
       "0             15         39,170  $24,649,428.00           78.985371   \n",
       "\n",
       "   Average Reading Score  Percent Passing Math  Percent Passing Reading  \\\n",
       "0               81.87784             72.392137                82.971662   \n",
       "\n",
       "   Percent Overall Passing Rate  \n",
       "0                       77.6819  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# finding total schools\n",
    "total_schools = len(schools_complete_df['School ID'])\n",
    "\n",
    "# The total number of students\n",
    "total_students = schools_complete_df['size'].sum()\n",
    "\n",
    "# Get the total budget\n",
    "total_budget = schools_complete_df['budget'].sum()\n",
    "\n",
    "# Get the Average Math Score\n",
    "average_math_score = round(students_complete_df['math_score'].mean(),6)\n",
    "\n",
    "# Get the Average Reading Score\n",
    "average_reading_score = round(students_complete_df['reading_score'].mean(),5)\n",
    "\n",
    "# Get the percentage of students passing math\n",
    "students_passing_math = students_complete_df.loc[students_complete_df[\"math_score\"] > 70,:]\n",
    "percent_passing_math = round(float(students_passing_math['math_score'].count()/total_students)*100,6)\n",
    "\n",
    "# Get the percentage of students passing reading\n",
    "students_passing_reading = students_complete_df.loc[students_complete_df[\"reading_score\"] >70,:]\n",
    "percent_passing_reading = round(float(students_passing_reading[\"reading_score\"].count()/total_students)*100,6)\n",
    "\n",
    "# Get the overall passing rate for math and reading\n",
    "overall_passing_rate =round((percent_passing_math + percent_passing_reading)/2,6)\n",
    "\n",
    "# Create a disctric Summary Dataframe \n",
    "summary_df = pd.DataFrame({\"Total Schools\":[total_schools],\n",
    "                          \"Total Students\":[total_students],\n",
    "                          \"Total Budget\":[total_budget],\n",
    "                          \"Average Math Score\":[average_math_score],\n",
    "                          \"Average Reading Score\":[average_reading_score],\n",
    "                          \"Percent Passing Math\":[percent_passing_math],\n",
    "                          \"Percent Passing Reading\":[percent_passing_reading],\n",
    "                          \"Percent Overall Passing Rate\":[overall_passing_rate]})\n",
    "# summary_df\n",
    "# to put it in the right format\n",
    "district_summary_df = pd.DataFrame(summary_df, columns=[\"Total Schools\",\n",
    "                                                        \"Total Students\",\n",
    "                                                        \"Total Budget\",\n",
    "                                                        \"Average Math Score\",\n",
    "                                                        \"Average Reading Score\",\n",
    "                                                        \"Percent Passing Math\",\n",
    "                                                        \"Percent Passing Reading\",\n",
    "                                                       \"Percent Overall Passing Rate\"])\n",
    "district_summary_df[\"Total Students\"] = district_summary_df[\"Total Students\"].map('{:,}'.format)\n",
    "district_summary_df[\"Total Budget\"] = district_summary_df[\"Total Budget\"].map('${:,.2f}'.format)\n",
    "\n",
    "district_summary_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# School Summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change header name from the dataframe from name to school\n",
    "schools_complete_df = schools_complete_df.rename(columns={\"name\": \"school\"})\n",
    "# schools_complete_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge the two data frames together on = school \n",
    "schools_complete_data_df = pd.merge(schools_complete_df, students_complete_df, on='school')\n",
    "#testing\n",
    "# schools_complete_data_df.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# count number of students in each school\n",
    "student_count = schools_complete_data_df['school'].value_counts()\n",
    "# testing\n",
    "# student_count.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# changing the school type into a str\n",
    "school_type = schools_complete_data_df.groupby('school')['type'].unique()\n",
    "school_type = school_type.str[0]\n",
    "#testing\n",
    "# school_type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# total budget for each school\n",
    "budget_per_school = schools_complete_data_df.groupby('school')['budget'].count()\n",
    "# budget_per_school.head()\n",
    "# testing\n",
    "# budget_per_school"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# budget for each student\n",
    "budget_per_student = schools_complete_df.set_index('school')['budget']/schools_complete_df.set_index('school')['size']\n",
    "# budget_per_student.head()\n",
    "# testing\n",
    "# budget_per_student"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Average math and readin scores for each school\n",
    "school_average_math = round(schools_complete_data_df.groupby('school')['math_score'].mean(),2)\n",
    "school_average_reading = round(schools_complete_data_df.groupby('school')['reading_score'].mean(),2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dataframe with reading and math passing only scores\n",
    "passing_df = schools_complete_data_df.loc[(schools_complete_data_df['math_score'] >=70) \n",
    "                                          & (schools_complete_data_df['reading_score'] >=70)]\n",
    "passing_math_df = schools_complete_data_df.loc[(schools_complete_data_df['math_score'] >=70)]\n",
    "passing_reading_df = schools_complete_data_df.loc[(schools_complete_data_df['reading_score'] >=70)]\n",
    "\n",
    "# testing\n",
    "# passing_math_df.head()\n",
    "# passing_reading_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# percentage for students passing math and reading \n",
    "percent_passing_math = round((passing_math_df.groupby('school')['math_score'].count()/student_count)*100,1)\n",
    "percent_passing_reading = round((passing_reading_df.groupby('school')['reading_score'].count()/student_count)*100,1)\n",
    "# percent_passing_reading\n",
    "# percent_passing_math"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# overall passing percentage\n",
    "overall_passing_percent = round((percent_passing_math + percent_passing_reading)/2, 2)\n",
    "# overall_passing_percent"
   ]
  },
  {
   "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 Type</th>\n",
       "      <th>Total Students</th>\n",
       "      <th>Total School Budget</th>\n",
       "      <th>Budget Per Student</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>Bailey High School</th>\n",
       "      <td>District</td>\n",
       "      <td>4,976</td>\n",
       "      <td>$4,976.00</td>\n",
       "      <td>$628.00</td>\n",
       "      <td>77.05</td>\n",
       "      <td>81.03</td>\n",
       "      <td>66.7</td>\n",
       "      <td>81.9</td>\n",
       "      <td>74.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cabrera High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>1,858</td>\n",
       "      <td>$1,858.00</td>\n",
       "      <td>$582.00</td>\n",
       "      <td>83.06</td>\n",
       "      <td>83.98</td>\n",
       "      <td>94.1</td>\n",
       "      <td>97.0</td>\n",
       "      <td>95.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Figueroa High School</th>\n",
       "      <td>District</td>\n",
       "      <td>2,949</td>\n",
       "      <td>$2,949.00</td>\n",
       "      <td>$639.00</td>\n",
       "      <td>76.71</td>\n",
       "      <td>81.16</td>\n",
       "      <td>66.0</td>\n",
       "      <td>80.7</td>\n",
       "      <td>73.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ford High School</th>\n",
       "      <td>District</td>\n",
       "      <td>2,739</td>\n",
       "      <td>$2,739.00</td>\n",
       "      <td>$644.00</td>\n",
       "      <td>77.10</td>\n",
       "      <td>80.75</td>\n",
       "      <td>68.3</td>\n",
       "      <td>79.3</td>\n",
       "      <td>73.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Griffin High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>1,468</td>\n",
       "      <td>$1,468.00</td>\n",
       "      <td>$625.00</td>\n",
       "      <td>83.35</td>\n",
       "      <td>83.82</td>\n",
       "      <td>93.4</td>\n",
       "      <td>97.1</td>\n",
       "      <td>95.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hernandez High School</th>\n",
       "      <td>District</td>\n",
       "      <td>4,635</td>\n",
       "      <td>$4,635.00</td>\n",
       "      <td>$652.00</td>\n",
       "      <td>77.29</td>\n",
       "      <td>80.93</td>\n",
       "      <td>66.8</td>\n",
       "      <td>80.9</td>\n",
       "      <td>73.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Holden High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>427</td>\n",
       "      <td>$427.00</td>\n",
       "      <td>$581.00</td>\n",
       "      <td>83.80</td>\n",
       "      <td>83.81</td>\n",
       "      <td>92.5</td>\n",
       "      <td>96.3</td>\n",
       "      <td>94.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Huang High School</th>\n",
       "      <td>District</td>\n",
       "      <td>2,917</td>\n",
       "      <td>$2,917.00</td>\n",
       "      <td>$655.00</td>\n",
       "      <td>76.63</td>\n",
       "      <td>81.18</td>\n",
       "      <td>65.7</td>\n",
       "      <td>81.3</td>\n",
       "      <td>73.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Johnson High School</th>\n",
       "      <td>District</td>\n",
       "      <td>4,761</td>\n",
       "      <td>$4,761.00</td>\n",
       "      <td>$650.00</td>\n",
       "      <td>77.07</td>\n",
       "      <td>80.97</td>\n",
       "      <td>66.1</td>\n",
       "      <td>81.2</td>\n",
       "      <td>73.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pena High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>962</td>\n",
       "      <td>$962.00</td>\n",
       "      <td>$609.00</td>\n",
       "      <td>83.84</td>\n",
       "      <td>84.04</td>\n",
       "      <td>94.6</td>\n",
       "      <td>95.9</td>\n",
       "      <td>95.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rodriguez High School</th>\n",
       "      <td>District</td>\n",
       "      <td>3,999</td>\n",
       "      <td>$3,999.00</td>\n",
       "      <td>$637.00</td>\n",
       "      <td>76.84</td>\n",
       "      <td>80.74</td>\n",
       "      <td>66.4</td>\n",
       "      <td>80.2</td>\n",
       "      <td>73.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shelton High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>1,761</td>\n",
       "      <td>$1,761.00</td>\n",
       "      <td>$600.00</td>\n",
       "      <td>83.36</td>\n",
       "      <td>83.73</td>\n",
       "      <td>93.9</td>\n",
       "      <td>95.9</td>\n",
       "      <td>94.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thomas High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>1,635</td>\n",
       "      <td>$1,635.00</td>\n",
       "      <td>$638.00</td>\n",
       "      <td>83.42</td>\n",
       "      <td>83.85</td>\n",
       "      <td>93.3</td>\n",
       "      <td>97.3</td>\n",
       "      <td>95.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wilson High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>2,283</td>\n",
       "      <td>$2,283.00</td>\n",
       "      <td>$578.00</td>\n",
       "      <td>83.27</td>\n",
       "      <td>83.99</td>\n",
       "      <td>93.9</td>\n",
       "      <td>96.5</td>\n",
       "      <td>95.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wright High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>1,800</td>\n",
       "      <td>$1,800.00</td>\n",
       "      <td>$583.00</td>\n",
       "      <td>83.68</td>\n",
       "      <td>83.96</td>\n",
       "      <td>93.3</td>\n",
       "      <td>96.6</td>\n",
       "      <td>94.95</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      School Type Total Students Total School Budget  \\\n",
       "Bailey High School       District          4,976           $4,976.00   \n",
       "Cabrera High School       Charter          1,858           $1,858.00   \n",
       "Figueroa High School     District          2,949           $2,949.00   \n",
       "Ford High School         District          2,739           $2,739.00   \n",
       "Griffin High School       Charter          1,468           $1,468.00   \n",
       "Hernandez High School    District          4,635           $4,635.00   \n",
       "Holden High School        Charter            427             $427.00   \n",
       "Huang High School        District          2,917           $2,917.00   \n",
       "Johnson High School      District          4,761           $4,761.00   \n",
       "Pena High School          Charter            962             $962.00   \n",
       "Rodriguez High School    District          3,999           $3,999.00   \n",
       "Shelton High School       Charter          1,761           $1,761.00   \n",
       "Thomas High School        Charter          1,635           $1,635.00   \n",
       "Wilson High School        Charter          2,283           $2,283.00   \n",
       "Wright High School        Charter          1,800           $1,800.00   \n",
       "\n",
       "                      Budget Per Student  Average Math Score  \\\n",
       "Bailey High School               $628.00               77.05   \n",
       "Cabrera High School              $582.00               83.06   \n",
       "Figueroa High School             $639.00               76.71   \n",
       "Ford High School                 $644.00               77.10   \n",
       "Griffin High School              $625.00               83.35   \n",
       "Hernandez High School            $652.00               77.29   \n",
       "Holden High School               $581.00               83.80   \n",
       "Huang High School                $655.00               76.63   \n",
       "Johnson High School              $650.00               77.07   \n",
       "Pena High School                 $609.00               83.84   \n",
       "Rodriguez High School            $637.00               76.84   \n",
       "Shelton High School              $600.00               83.36   \n",
       "Thomas High School               $638.00               83.42   \n",
       "Wilson High School               $578.00               83.27   \n",
       "Wright High School               $583.00               83.68   \n",
       "\n",
       "                       Average Reading Score  % Passing Math  \\\n",
       "Bailey High School                     81.03            66.7   \n",
       "Cabrera High School                    83.98            94.1   \n",
       "Figueroa High School                   81.16            66.0   \n",
       "Ford High School                       80.75            68.3   \n",
       "Griffin High School                    83.82            93.4   \n",
       "Hernandez High School                  80.93            66.8   \n",
       "Holden High School                     83.81            92.5   \n",
       "Huang High School                      81.18            65.7   \n",
       "Johnson High School                    80.97            66.1   \n",
       "Pena High School                       84.04            94.6   \n",
       "Rodriguez High School                  80.74            66.4   \n",
       "Shelton High School                    83.73            93.9   \n",
       "Thomas High School                     83.85            93.3   \n",
       "Wilson High School                     83.99            93.9   \n",
       "Wright High School                     83.96            93.3   \n",
       "\n",
       "                       % Passing Reading  % Overall Passing Rate  \n",
       "Bailey High School                  81.9                   74.30  \n",
       "Cabrera High School                 97.0                   95.55  \n",
       "Figueroa High School                80.7                   73.35  \n",
       "Ford High School                    79.3                   73.80  \n",
       "Griffin High School                 97.1                   95.25  \n",
       "Hernandez High School               80.9                   73.85  \n",
       "Holden High School                  96.3                   94.40  \n",
       "Huang High School                   81.3                   73.50  \n",
       "Johnson High School                 81.2                   73.65  \n",
       "Pena High School                    95.9                   95.25  \n",
       "Rodriguez High School               80.2                   73.30  \n",
       "Shelton High School                 95.9                   94.90  \n",
       "Thomas High School                  97.3                   95.30  \n",
       "Wilson High School                  96.5                   95.20  \n",
       "Wright High School                  96.6                   94.95  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# school summary dataframe\n",
    "\n",
    "school_summary_df = pd.DataFrame({\"School Type\":school_type,\n",
    "                                \"Total Students\":student_count,\n",
    "                                \"Total School Budget\":budget_per_school,\n",
    "                                \"Budget Per Student\":budget_per_student,\n",
    "                                \"Average Math Score\": school_average_math,\n",
    "                                \"Average Reading Score\": school_average_reading,\n",
    "                                \"% Passing Math\": percent_passing_math,\n",
    "                                \"% Passing Reading\": percent_passing_reading,\n",
    "                                \"% Overall Passing Rate\":overall_passing_percent},columns=[\"School Type\",\n",
    "                                                        \"Total Students\",\n",
    "                                                        \"Total School Budget\",\n",
    "                                                        \"Budget Per Student\",\n",
    "                                                        \"Average Math Score\",\n",
    "                                                        \"Average Reading Score\",\n",
    "                                                        \"% Passing Math\",\n",
    "                                                       \"% Passing Reading\",\n",
    "                                                        \"% Overall Passing Rate\"])\n",
    "#testing\n",
    "# school_summary_df.head()\n",
    "school_summary_df[\"Total Students\"] = school_summary_df[\"Total Students\"].map('{:,}'.format)\n",
    "school_summary_df[\"Total School Budget\"] = school_summary_df[\"Total School Budget\"].map('${:,.2f}'.format)\n",
    "school_summary_df[\"Budget Per Student\"] = school_summary_df[\"Budget Per Student\"].map('${:,.2f}'.format)\n",
    "school_summary_df\n",
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Top Performing Schools by Passing Rate"
   ]
  },
  {
   "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>School Type</th>\n",
       "      <th>Total Students</th>\n",
       "      <th>Total School Budget</th>\n",
       "      <th>Budget Per Student</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>Cabrera High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>1,858</td>\n",
       "      <td>$1,858.00</td>\n",
       "      <td>$582.00</td>\n",
       "      <td>83.06</td>\n",
       "      <td>83.98</td>\n",
       "      <td>94.1</td>\n",
       "      <td>97.0</td>\n",
       "      <td>95.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thomas High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>1,635</td>\n",
       "      <td>$1,635.00</td>\n",
       "      <td>$638.00</td>\n",
       "      <td>83.42</td>\n",
       "      <td>83.85</td>\n",
       "      <td>93.3</td>\n",
       "      <td>97.3</td>\n",
       "      <td>95.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Griffin High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>1,468</td>\n",
       "      <td>$1,468.00</td>\n",
       "      <td>$625.00</td>\n",
       "      <td>83.35</td>\n",
       "      <td>83.82</td>\n",
       "      <td>93.4</td>\n",
       "      <td>97.1</td>\n",
       "      <td>95.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pena High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>962</td>\n",
       "      <td>$962.00</td>\n",
       "      <td>$609.00</td>\n",
       "      <td>83.84</td>\n",
       "      <td>84.04</td>\n",
       "      <td>94.6</td>\n",
       "      <td>95.9</td>\n",
       "      <td>95.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wilson High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>2,283</td>\n",
       "      <td>$2,283.00</td>\n",
       "      <td>$578.00</td>\n",
       "      <td>83.27</td>\n",
       "      <td>83.99</td>\n",
       "      <td>93.9</td>\n",
       "      <td>96.5</td>\n",
       "      <td>95.20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    School Type Total Students Total School Budget  \\\n",
       "Cabrera High School     Charter          1,858           $1,858.00   \n",
       "Thomas High School      Charter          1,635           $1,635.00   \n",
       "Griffin High School     Charter          1,468           $1,468.00   \n",
       "Pena High School        Charter            962             $962.00   \n",
       "Wilson High School      Charter          2,283           $2,283.00   \n",
       "\n",
       "                    Budget Per Student  Average Math Score  \\\n",
       "Cabrera High School            $582.00               83.06   \n",
       "Thomas High School             $638.00               83.42   \n",
       "Griffin High School            $625.00               83.35   \n",
       "Pena High School               $609.00               83.84   \n",
       "Wilson High School             $578.00               83.27   \n",
       "\n",
       "                     Average Reading Score  % Passing Math  % Passing Reading  \\\n",
       "Cabrera High School                  83.98            94.1               97.0   \n",
       "Thomas High School                   83.85            93.3               97.3   \n",
       "Griffin High School                  83.82            93.4               97.1   \n",
       "Pena High School                     84.04            94.6               95.9   \n",
       "Wilson High School                   83.99            93.9               96.5   \n",
       "\n",
       "                     % Overall Passing Rate  \n",
       "Cabrera High School                   95.55  \n",
       "Thomas High School                    95.30  \n",
       "Griffin High School                   95.25  \n",
       "Pena High School                      95.25  \n",
       "Wilson High School                    95.20  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the top schools ( only top 5 )\n",
    "top_performing_schools= school_summary_df.sort_values(\"% Overall Passing Rate\", ascending=False, inplace=False)\n",
    "top_performing_schools.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  Bottom Performing Schools (By Passing Rate)\n"
   ]
  },
  {
   "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 Type</th>\n",
       "      <th>Total Students</th>\n",
       "      <th>Total School Budget</th>\n",
       "      <th>Budget Per Student</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>Rodriguez High School</th>\n",
       "      <td>District</td>\n",
       "      <td>3,999</td>\n",
       "      <td>$3,999.00</td>\n",
       "      <td>$637.00</td>\n",
       "      <td>76.84</td>\n",
       "      <td>80.74</td>\n",
       "      <td>66.4</td>\n",
       "      <td>80.2</td>\n",
       "      <td>73.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Figueroa High School</th>\n",
       "      <td>District</td>\n",
       "      <td>2,949</td>\n",
       "      <td>$2,949.00</td>\n",
       "      <td>$639.00</td>\n",
       "      <td>76.71</td>\n",
       "      <td>81.16</td>\n",
       "      <td>66.0</td>\n",
       "      <td>80.7</td>\n",
       "      <td>73.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Huang High School</th>\n",
       "      <td>District</td>\n",
       "      <td>2,917</td>\n",
       "      <td>$2,917.00</td>\n",
       "      <td>$655.00</td>\n",
       "      <td>76.63</td>\n",
       "      <td>81.18</td>\n",
       "      <td>65.7</td>\n",
       "      <td>81.3</td>\n",
       "      <td>73.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Johnson High School</th>\n",
       "      <td>District</td>\n",
       "      <td>4,761</td>\n",
       "      <td>$4,761.00</td>\n",
       "      <td>$650.00</td>\n",
       "      <td>77.07</td>\n",
       "      <td>80.97</td>\n",
       "      <td>66.1</td>\n",
       "      <td>81.2</td>\n",
       "      <td>73.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ford High School</th>\n",
       "      <td>District</td>\n",
       "      <td>2,739</td>\n",
       "      <td>$2,739.00</td>\n",
       "      <td>$644.00</td>\n",
       "      <td>77.10</td>\n",
       "      <td>80.75</td>\n",
       "      <td>68.3</td>\n",
       "      <td>79.3</td>\n",
       "      <td>73.80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      School Type Total Students Total School Budget  \\\n",
       "Rodriguez High School    District          3,999           $3,999.00   \n",
       "Figueroa High School     District          2,949           $2,949.00   \n",
       "Huang High School        District          2,917           $2,917.00   \n",
       "Johnson High School      District          4,761           $4,761.00   \n",
       "Ford High School         District          2,739           $2,739.00   \n",
       "\n",
       "                      Budget Per Student  Average Math Score  \\\n",
       "Rodriguez High School            $637.00               76.84   \n",
       "Figueroa High School             $639.00               76.71   \n",
       "Huang High School                $655.00               76.63   \n",
       "Johnson High School              $650.00               77.07   \n",
       "Ford High School                 $644.00               77.10   \n",
       "\n",
       "                       Average Reading Score  % Passing Math  \\\n",
       "Rodriguez High School                  80.74            66.4   \n",
       "Figueroa High School                   81.16            66.0   \n",
       "Huang High School                      81.18            65.7   \n",
       "Johnson High School                    80.97            66.1   \n",
       "Ford High School                       80.75            68.3   \n",
       "\n",
       "                       % Passing Reading  % Overall Passing Rate  \n",
       "Rodriguez High School               80.2                   73.30  \n",
       "Figueroa High School                80.7                   73.35  \n",
       "Huang High School                   81.3                   73.50  \n",
       "Johnson High School                 81.2                   73.65  \n",
       "Ford High School                    79.3                   73.80  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# only showing bottom 5\n",
    "Bottom_performing_schools= school_summary_df.sort_values(\"% Overall Passing Rate\", ascending=True, inplace=False)\n",
    "Bottom_performing_schools.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  Math Scores by Grade"
   ]
  },
  {
   "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>grade</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>Bailey High School</th>\n",
       "      <td>77.08</td>\n",
       "      <td>77.00</td>\n",
       "      <td>77.52</td>\n",
       "      <td>76.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cabrera High School</th>\n",
       "      <td>83.09</td>\n",
       "      <td>83.15</td>\n",
       "      <td>82.77</td>\n",
       "      <td>83.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Figueroa High School</th>\n",
       "      <td>76.40</td>\n",
       "      <td>76.54</td>\n",
       "      <td>76.88</td>\n",
       "      <td>77.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ford High School</th>\n",
       "      <td>77.36</td>\n",
       "      <td>77.67</td>\n",
       "      <td>76.92</td>\n",
       "      <td>76.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Griffin High School</th>\n",
       "      <td>82.04</td>\n",
       "      <td>84.23</td>\n",
       "      <td>83.84</td>\n",
       "      <td>83.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hernandez High School</th>\n",
       "      <td>77.44</td>\n",
       "      <td>77.34</td>\n",
       "      <td>77.14</td>\n",
       "      <td>77.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Holden High School</th>\n",
       "      <td>83.79</td>\n",
       "      <td>83.43</td>\n",
       "      <td>85.00</td>\n",
       "      <td>82.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Huang High School</th>\n",
       "      <td>77.03</td>\n",
       "      <td>75.91</td>\n",
       "      <td>76.45</td>\n",
       "      <td>77.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Johnson High School</th>\n",
       "      <td>77.19</td>\n",
       "      <td>76.69</td>\n",
       "      <td>77.49</td>\n",
       "      <td>76.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pena High School</th>\n",
       "      <td>83.63</td>\n",
       "      <td>83.37</td>\n",
       "      <td>84.33</td>\n",
       "      <td>84.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rodriguez High School</th>\n",
       "      <td>76.86</td>\n",
       "      <td>76.61</td>\n",
       "      <td>76.40</td>\n",
       "      <td>77.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shelton High School</th>\n",
       "      <td>83.42</td>\n",
       "      <td>82.92</td>\n",
       "      <td>83.38</td>\n",
       "      <td>83.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thomas High School</th>\n",
       "      <td>83.59</td>\n",
       "      <td>83.09</td>\n",
       "      <td>83.50</td>\n",
       "      <td>83.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wilson High School</th>\n",
       "      <td>83.09</td>\n",
       "      <td>83.72</td>\n",
       "      <td>83.20</td>\n",
       "      <td>83.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wright High School</th>\n",
       "      <td>83.26</td>\n",
       "      <td>84.01</td>\n",
       "      <td>83.84</td>\n",
       "      <td>83.64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "grade                    9th   10th   11th   12th\n",
       "Bailey High School     77.08  77.00  77.52  76.49\n",
       "Cabrera High School    83.09  83.15  82.77  83.28\n",
       "Figueroa High School   76.40  76.54  76.88  77.15\n",
       "Ford High School       77.36  77.67  76.92  76.18\n",
       "Griffin High School    82.04  84.23  83.84  83.36\n",
       "Hernandez High School  77.44  77.34  77.14  77.19\n",
       "Holden High School     83.79  83.43  85.00  82.86\n",
       "Huang High School      77.03  75.91  76.45  77.23\n",
       "Johnson High School    77.19  76.69  77.49  76.86\n",
       "Pena High School       83.63  83.37  84.33  84.12\n",
       "Rodriguez High School  76.86  76.61  76.40  77.69\n",
       "Shelton High School    83.42  82.92  83.38  83.78\n",
       "Thomas High School     83.59  83.09  83.50  83.50\n",
       "Wilson High School     83.09  83.72  83.20  83.04\n",
       "Wright High School     83.26  84.01  83.84  83.64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pandas.api.types import CategoricalDtype\n",
    "#Reset the grade order in the original students data frame \"students_complete_df\". \n",
    "students_complete_df[\"grade\"] = students_complete_df['grade'].astype(CategoricalDtype([\"9th\", \"10th\",\"11th\",\"12th\"]))\n",
    "# students_complete_df\n",
    "\n",
    "math_scores_grade = round(students_complete_df.pivot_table(index=\"school\", columns=\"grade\", values=\"math_score\"),2)\n",
    "math_scores_grade.index.name = None \n",
    "math_scores_grade"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading scores by grade"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>grade</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>Bailey High School</th>\n",
       "      <td>81.30</td>\n",
       "      <td>80.91</td>\n",
       "      <td>80.95</td>\n",
       "      <td>80.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cabrera High School</th>\n",
       "      <td>83.68</td>\n",
       "      <td>84.25</td>\n",
       "      <td>83.79</td>\n",
       "      <td>84.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Figueroa High School</th>\n",
       "      <td>81.20</td>\n",
       "      <td>81.41</td>\n",
       "      <td>80.64</td>\n",
       "      <td>81.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ford High School</th>\n",
       "      <td>80.63</td>\n",
       "      <td>81.26</td>\n",
       "      <td>80.40</td>\n",
       "      <td>80.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Griffin High School</th>\n",
       "      <td>83.37</td>\n",
       "      <td>83.71</td>\n",
       "      <td>84.29</td>\n",
       "      <td>84.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hernandez High School</th>\n",
       "      <td>80.87</td>\n",
       "      <td>80.66</td>\n",
       "      <td>81.40</td>\n",
       "      <td>80.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Holden High School</th>\n",
       "      <td>83.68</td>\n",
       "      <td>83.32</td>\n",
       "      <td>83.82</td>\n",
       "      <td>84.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Huang High School</th>\n",
       "      <td>81.29</td>\n",
       "      <td>81.51</td>\n",
       "      <td>81.42</td>\n",
       "      <td>80.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Johnson High School</th>\n",
       "      <td>81.26</td>\n",
       "      <td>80.77</td>\n",
       "      <td>80.62</td>\n",
       "      <td>81.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pena High School</th>\n",
       "      <td>83.81</td>\n",
       "      <td>83.61</td>\n",
       "      <td>84.34</td>\n",
       "      <td>84.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rodriguez High School</th>\n",
       "      <td>80.99</td>\n",
       "      <td>80.63</td>\n",
       "      <td>80.86</td>\n",
       "      <td>80.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shelton High School</th>\n",
       "      <td>84.12</td>\n",
       "      <td>83.44</td>\n",
       "      <td>84.37</td>\n",
       "      <td>82.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thomas High School</th>\n",
       "      <td>83.73</td>\n",
       "      <td>84.25</td>\n",
       "      <td>83.59</td>\n",
       "      <td>83.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wilson High School</th>\n",
       "      <td>83.94</td>\n",
       "      <td>84.02</td>\n",
       "      <td>83.76</td>\n",
       "      <td>84.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wright High School</th>\n",
       "      <td>83.83</td>\n",
       "      <td>83.81</td>\n",
       "      <td>84.16</td>\n",
       "      <td>84.07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "grade                    9th   10th   11th   12th\n",
       "Bailey High School     81.30  80.91  80.95  80.91\n",
       "Cabrera High School    83.68  84.25  83.79  84.29\n",
       "Figueroa High School   81.20  81.41  80.64  81.38\n",
       "Ford High School       80.63  81.26  80.40  80.66\n",
       "Griffin High School    83.37  83.71  84.29  84.01\n",
       "Hernandez High School  80.87  80.66  81.40  80.86\n",
       "Holden High School     83.68  83.32  83.82  84.70\n",
       "Huang High School      81.29  81.51  81.42  80.31\n",
       "Johnson High School    81.26  80.77  80.62  81.23\n",
       "Pena High School       83.81  83.61  84.34  84.59\n",
       "Rodriguez High School  80.99  80.63  80.86  80.38\n",
       "Shelton High School    84.12  83.44  84.37  82.78\n",
       "Thomas High School     83.73  84.25  83.59  83.83\n",
       "Wilson High School     83.94  84.02  83.76  84.32\n",
       "Wright High School     83.83  83.81  84.16  84.07"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Reset the grade order in the original students data frame \"students_complete_df\". \n",
    "students_complete_df['grade'] = students_complete_df['grade'].astype(CategoricalDtype([\"9th\", \"10th\",\"11th\",\"12th\"]))\n",
    "# students_complete_df\n",
    "\n",
    "reading_scores_grade = round(students_complete_df.pivot_table(index=\"school\", columns=\"grade\", values=\"reading_score\"),2)\n",
    "reading_scores_grade.index.name = None\n",
    "reading_scores_grade"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Scores by School Spending"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>% Passing Math</th>\n",
       "      <th>% Passing Reading</th>\n",
       "      <th>Average Math Score</th>\n",
       "      <th>Average Reading Score</th>\n",
       "      <th>Overall Passing Rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Spending Ranges(Per Student)</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>&lt;$585</th>\n",
       "      <td>93.450000</td>\n",
       "      <td>96.600000</td>\n",
       "      <td>83.452500</td>\n",
       "      <td>83.935000</td>\n",
       "      <td>95.025000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>$585-615</th>\n",
       "      <td>94.250000</td>\n",
       "      <td>95.900000</td>\n",
       "      <td>83.600000</td>\n",
       "      <td>83.885000</td>\n",
       "      <td>95.075000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>$615-645</th>\n",
       "      <td>75.683333</td>\n",
       "      <td>86.083333</td>\n",
       "      <td>79.078333</td>\n",
       "      <td>81.891667</td>\n",
       "      <td>80.883333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>$645-675</th>\n",
       "      <td>66.200000</td>\n",
       "      <td>81.133333</td>\n",
       "      <td>76.996667</td>\n",
       "      <td>81.026667</td>\n",
       "      <td>73.666667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              % Passing Math  % Passing Reading  \\\n",
       "Spending Ranges(Per Student)                                      \n",
       "<$585                              93.450000          96.600000   \n",
       "$585-615                           94.250000          95.900000   \n",
       "$615-645                           75.683333          86.083333   \n",
       "$645-675                           66.200000          81.133333   \n",
       "\n",
       "                              Average Math Score  Average Reading Score  \\\n",
       "Spending Ranges(Per Student)                                              \n",
       "<$585                                  83.452500              83.935000   \n",
       "$585-615                               83.600000              83.885000   \n",
       "$615-645                               79.078333              81.891667   \n",
       "$645-675                               76.996667              81.026667   \n",
       "\n",
       "                              Overall Passing Rate  \n",
       "Spending Ranges(Per Student)                        \n",
       "<$585                                    95.025000  \n",
       "$585-615                                 95.075000  \n",
       "$615-645                                 80.883333  \n",
       "$645-675                                 73.666667  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create the bins ( school spending )\n",
    "spending_bins = [0, 585, 615, 645, 675]\n",
    "\n",
    "# Names for the bins\n",
    "spending_range = [\"<$585\", \"$585-615\", \"$615-645\", \"$645-675\"]\n",
    "\n",
    "#schools_spending_df = school_summary_df\n",
    "school_summary_df[\"Spending Ranges(Per Student)\"] = pd.cut(budget_per_student, bins=spending_bins, labels=spending_range)\n",
    "school_summary_df.head()\n",
    "\n",
    "spending_math_score = school_summary_df.groupby([\"Spending Ranges(Per Student)\"])[\"Average Math Score\"].mean()\n",
    "spending_reading_score = school_summary_df.groupby([\"Spending Ranges(Per Student)\"])[\"Average Reading Score\"].mean()\n",
    "spending_passing_math = school_summary_df.groupby([\"Spending Ranges(Per Student)\"])[\"% Passing Math\"].mean()\n",
    "spending_passing_reading = school_summary_df.groupby([\"Spending Ranges(Per Student)\"])['% Passing Reading'].mean()\n",
    "overall_passing_rate = (spending_passing_math + spending_passing_reading)/2\n",
    "\n",
    "# Creating dataframe\n",
    "scores_by_spending = pd.DataFrame ({\"Average Math Score\": spending_math_score,\n",
    "    \"Average Reading Score\": spending_reading_score,\n",
    "    \"% Passing Math\": spending_passing_math,\n",
    "    \"% Passing Reading\": spending_passing_reading,\n",
    "    \"Overall Passing Rate\": overall_passing_rate})\n",
    "scores_by_spending\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Scores by Schools Size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>% Passing Math</th>\n",
       "      <th>% Passing Reading</th>\n",
       "      <th>Average Math Score</th>\n",
       "      <th>Average Reading Score</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 (&lt;1000)</th>\n",
       "      <td>93.5500</td>\n",
       "      <td>96.10</td>\n",
       "      <td>83.820</td>\n",
       "      <td>83.92500</td>\n",
       "      <td>94.82500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Medium(1000-2000)</th>\n",
       "      <td>93.6000</td>\n",
       "      <td>96.78</td>\n",
       "      <td>83.374</td>\n",
       "      <td>83.86800</td>\n",
       "      <td>95.19000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Large(2000-5000)</th>\n",
       "      <td>69.9875</td>\n",
       "      <td>82.75</td>\n",
       "      <td>77.745</td>\n",
       "      <td>81.34375</td>\n",
       "      <td>76.36875</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   % Passing Math  % Passing Reading  Average Math Score  \\\n",
       "School Size                                                                \n",
       "small (<1000)             93.5500              96.10              83.820   \n",
       "Medium(1000-2000)         93.6000              96.78              83.374   \n",
       "Large(2000-5000)          69.9875              82.75              77.745   \n",
       "\n",
       "                   Average Reading Score  Overall Passing Rate  \n",
       "School Size                                                     \n",
       "small (<1000)                   83.92500              94.82500  \n",
       "Medium(1000-2000)               83.86800              95.19000  \n",
       "Large(2000-5000)                81.34375              76.36875  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create the bins ( school size)\n",
    "size_bins = [0, 1000, 2000, 5000]\n",
    "\n",
    "# Names for the bins\n",
    "group_names = [\"small (<1000)\", \"Medium(1000-2000)\", \"Large(2000-5000)\"]\n",
    "\n",
    "school_size_df = school_summary_df\n",
    "school_size_df[\"School Size\"] = pd.cut(student_count, bins=size_bins, labels=group_names)\n",
    "school_size_df\n",
    "\n",
    "spending_math_score = school_size_df.groupby([\"School Size\"])[\"Average Math Score\"].mean()\n",
    "spending_reading_score = school_size_df.groupby([\"School Size\"])[\"Average Reading Score\"].mean()\n",
    "spending_passing_math = school_size_df.groupby([\"School Size\"])[\"% Passing Math\"].mean()\n",
    "spending_passing_reading = school_size_df.groupby([\"School Size\"])['% Passing Reading'].mean()\n",
    "overall_passing_rate = (spending_passing_math + spending_passing_reading)/2\n",
    "\n",
    "# Creating dataframe\n",
    "scores_by_size = pd.DataFrame ({\"Average Math Score\": spending_math_score,\n",
    "    \"Average Reading Score\": spending_reading_score,\n",
    "    \"% Passing Math\": spending_passing_math,\n",
    "    \"% Passing Reading\": spending_passing_reading,\n",
    "    \"Overall Passing Rate\": overall_passing_rate})\n",
    "scores_by_size\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Scores by School Type"
   ]
  },
  {
   "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>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.472500</td>\n",
       "      <td>83.897500</td>\n",
       "      <td>93.625000</td>\n",
       "      <td>96.575000</td>\n",
       "      <td>95.100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>District</th>\n",
       "      <td>76.955714</td>\n",
       "      <td>80.965714</td>\n",
       "      <td>66.571429</td>\n",
       "      <td>80.785714</td>\n",
       "      <td>73.678571</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.472500              83.897500       93.625000   \n",
       "District              76.955714              80.965714       66.571429   \n",
       "\n",
       "             % Passing Reading  % Overall Passing Rate  \n",
       "School Type                                             \n",
       "Charter              96.575000               95.100000  \n",
       "District             80.785714               73.678571  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#* Repeat the above breakdown, but this time group schools based on school type (Charter vs. District)*\n",
    "scores_school_type = school_summary_df[[\"School Type\",\"Average Math Score\",\n",
    "                                        \"Average Reading Score\", \n",
    "                                        \"% Passing Math\", \n",
    "                                        \"% Passing Reading\", \n",
    "                                        \"% Overall Passing Rate\"]]\n",
    "scores_school_type = scores_school_type.groupby('School Type').mean()\n",
    "scores_school_type"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}