PyCitySchools_starter.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PyCity Schools Analysis\n",
    "\n",
    "* As a whole, schools with higher budgets, did not yield better test results. By contrast, schools with higher spending per student actually (\\$645-675) underperformed compared to schools with smaller budgets (<\\$585 per student).\n",
    "\n",
    "* As a whole, smaller and medium sized schools dramatically out-performed large sized schools on passing math performances (89-91% passing vs 67%).\n",
    "\n",
    "* As a whole, charter schools out-performed the public district schools across all metrics. However, more analysis will be required to glean if the effect is due to school practices or the fact that charter schools tend to serve smaller student populations per school. \n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note\n",
    "* Instructions have been included for each segment. You do not have to follow them exactly, but they are included to help you think through the steps."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dependencies and Setup\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# File to Load (Remember to Change These)\n",
    "school_data_to_load = \"schools_complete.csv\"\n",
    "student_data_to_load = \"students_complete.csv\"\n",
    "\n",
    "# Read School and Student Data File and store into Pandas Data Frames\n",
    "school_data = pd.read_csv(school_data_to_load)\n",
    "student_data = pd.read_csv(student_data_to_load)\n",
    "\n",
    "# Combine the data into a single dataset\n",
    "school_data_complete = pd.merge(student_data, school_data, how=\"left\", on=[\"school_name\", \"school_name\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>student_name</th>\n",
       "      <th>gender</th>\n",
       "      <th>grade</th>\n",
       "      <th>school_name</th>\n",
       "      <th>reading_score</th>\n",
       "      <th>math_score</th>\n",
       "      <th>School ID</th>\n",
       "      <th>type</th>\n",
       "      <th>size</th>\n",
       "      <th>budget</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",
       "      <td>0</td>\n",
       "      <td>District</td>\n",
       "      <td>2917</td>\n",
       "      <td>1910635</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",
       "      <td>0</td>\n",
       "      <td>District</td>\n",
       "      <td>2917</td>\n",
       "      <td>1910635</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",
       "      <td>0</td>\n",
       "      <td>District</td>\n",
       "      <td>2917</td>\n",
       "      <td>1910635</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",
       "      <td>0</td>\n",
       "      <td>District</td>\n",
       "      <td>2917</td>\n",
       "      <td>1910635</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",
       "      <td>0</td>\n",
       "      <td>District</td>\n",
       "      <td>2917</td>\n",
       "      <td>1910635</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Student ID       student_name gender grade        school_name  \\\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  School ID      type  size   budget  \n",
       "0             66          79          0  District  2917  1910635  \n",
       "1             94          61          0  District  2917  1910635  \n",
       "2             90          60          0  District  2917  1910635  \n",
       "3             67          58          0  District  2917  1910635  \n",
       "4             97          84          0  District  2917  1910635  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "school_data_complete.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## District Summary\n",
    "\n",
    "* Calculate the total number of schools\n",
    "\n",
    "* Calculate the total number of students\n",
    "\n",
    "* Calculate the total budget\n",
    "\n",
    "* Calculate the average math score \n",
    "\n",
    "* Calculate the average reading score\n",
    "\n",
    "* Calculate the overall passing rate (overall average score), i.e. (avg. math score + avg. reading score)/2\n",
    "\n",
    "* Calculate the percentage of students with a passing math score (70 or greater)\n",
    "\n",
    "* Calculate the percentage of students with a passing reading score (70 or greater)\n",
    "\n",
    "* Create a dataframe to hold the above results\n",
    "\n",
    "* Optional: give the displayed data cleaner formatting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#total_schools = school_data_complete.groupby('school_name')['School ID'].nunique()\n",
    "total_schools = school_data.groupby('school_name')['School ID'].nunique().count()\n",
    "total_students = student_data.groupby('Student ID').nunique().count()\n",
    "total_students = total_students[0]\n",
    "total_budget =  school_data['budget'].sum()\n",
    "total_math_score =  float(format(student_data['math_score'].mean(), '.2f'))\n",
    "total_read_score =  float(format(student_data['reading_score'].mean(), '.2f'))\n",
    "overal_passing_rate = float(format(((total_math_score + total_read_score) / 2), '.2f'))\n",
    "students_math_70 = student_data[student_data.math_score >= 70].count()\n",
    "students_math_70 = float(format(((students_math_70[0] * 100) / total_students), '.2f'))\n",
    "students_read_70 = student_data[student_data.reading_score >= 70].count()\n",
    "students_read_70 = float(format(((students_read_70[0] * 100) / total_students), '.2f'))\n",
    "#df = df.groupby('domain')['ID'].nunique()\n",
    "#print(f'Total Schools: {total_schools}')\n",
    "#print(f'Total Students: {total_students}')\n",
    "#print(f'Total Budget: {total_budget}')\n",
    "#print(f'Math Score Average: {total_math_score}')\n",
    "#print(f'Reading Score Average: {total_read_score}')\n",
    "#print(f'Overal Passing Rate: {overal_passing_rate}')\n",
    "#print(f'Students Math >= 70: {students_math_70}')\n",
    "#print(f'Students Reading >= 70: {students_read_70}')\n",
    "\n",
    "df_resumen = pd.DataFrame({\n",
    "                'Total Schools' : [total_schools],\n",
    "                'Total Students' : [total_students],\n",
    "                'Total Budget' : [total_budget],\n",
    "                'Math Score Average' : [total_math_score],\n",
    "                'Reading Score Average' : [total_read_score],\n",
    "                'Overal Passing Rate' : [overal_passing_rate],\n",
    "                'Students Math >= 70' : [students_math_70],    \n",
    "                'Students Reading >= 70' : [students_read_70]\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Math Score Average</th>\n",
       "      <th>Reading Score Average</th>\n",
       "      <th>Overal Passing Rate</th>\n",
       "      <th>Students Math &gt;= 70</th>\n",
       "      <th>Students Reading &gt;= 70</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.99</td>\n",
       "      <td>81.88</td>\n",
       "      <td>80.44</td>\n",
       "      <td>74.98</td>\n",
       "      <td>85.81</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Total Schools  Total Students  Total Budget  Math Score Average  \\\n",
       "0             15           39170      24649428               78.99   \n",
       "\n",
       "   Reading Score Average  Overal Passing Rate  Students Math >= 70  \\\n",
       "0                  81.88                80.44                74.98   \n",
       "\n",
       "   Students Reading >= 70  \n",
       "0                   85.81  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_resumen.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## School Summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Create an overview table that summarizes key metrics about each school, including:\n",
    "  * School Name\n",
    "  * School Type\n",
    "  * Total Students\n",
    "  * Total School Budget\n",
    "  * Per Student Budget\n",
    "  * Average Math Score\n",
    "  * Average Reading Score\n",
    "  * % Passing Math\n",
    "  * % Passing Reading\n",
    "  * Overall Passing Rate (Average of the above two)\n",
    "  \n",
    "* Create a dataframe to hold the above results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#school_data_complete.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#school_data_complete = pd.merge(student_data, school_data, how=\"left\", on=[\"school_name\", \"school_name\"])\n",
    "#school_data.type\n",
    "#df_tmp = pd.DataFrame(columns=['Sn', 'Ty'])\n",
    "df_tmp = pd.DataFrame()\n",
    "df_r = pd.DataFrame()\n",
    "cols = ['School Name', 'School Type', 'Total Students', 'Total School Budget', 'Per Student Budget', 'Average Math Score', 'Average Reading Score', '% Passing Math', '% Passing Reading', 'Overall Passing Rate']\n",
    "for index, school in school_data.iterrows():\n",
    "    sn = school['school_name']\n",
    "    st = school['type']\n",
    "        #student_data[student_data.math_score >= 70].count()\n",
    "    ts = student_data[student_data.school_name == sn].count()[0]\n",
    "    tsb = school['budget']\n",
    "    tbps = school['budget'] / ts \n",
    "    ams = float(format(student_data[student_data.school_name == sn]['math_score'].mean(),'.2f'))\n",
    "    ars = float(format(student_data[student_data.school_name == sn]['reading_score'].mean(),'.2f'))\n",
    "    ppm = float(format(((student_data[(student_data.school_name == sn) & (student_data.math_score >= 70)].count()[0] * 100 ) / ts),'.2f'))\n",
    "    ppr = float(format(((student_data[(student_data.school_name == sn) & (student_data.reading_score >= 70)].count()[0] * 100 ) / ts),'.2f'))\n",
    "    opr = float(format(((ppm + ppr) / 2), '.2f'))\n",
    "    #print(f'{sn} - {st} - {ts} - {tsb} - {tbps} - {ams} - {ars} - {ppm} - {ppr} - {opr}')\n",
    "    \n",
    "    df_tmp = [(sn, st, ts, tsb, tbps, ams, ars, ppm, ppr, opr)]\n",
    "    df_r = df_r.append(pd.DataFrame(df_tmp, columns=cols, index=[index]))    \n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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 Name</th>\n",
       "      <th>School Type</th>\n",
       "      <th>Total Students</th>\n",
       "      <th>Total School 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>Huang High School</td>\n",
       "      <td>District</td>\n",
       "      <td>2917</td>\n",
       "      <td>1910635</td>\n",
       "      <td>655.0</td>\n",
       "      <td>76.63</td>\n",
       "      <td>81.18</td>\n",
       "      <td>65.68</td>\n",
       "      <td>81.32</td>\n",
       "      <td>73.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\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.71</td>\n",
       "      <td>81.16</td>\n",
       "      <td>65.99</td>\n",
       "      <td>80.74</td>\n",
       "      <td>73.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\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.36</td>\n",
       "      <td>83.73</td>\n",
       "      <td>93.87</td>\n",
       "      <td>95.85</td>\n",
       "      <td>94.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\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.29</td>\n",
       "      <td>80.93</td>\n",
       "      <td>66.75</td>\n",
       "      <td>80.86</td>\n",
       "      <td>73.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\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.35</td>\n",
       "      <td>83.82</td>\n",
       "      <td>93.39</td>\n",
       "      <td>97.14</td>\n",
       "      <td>95.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\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.27</td>\n",
       "      <td>83.99</td>\n",
       "      <td>93.87</td>\n",
       "      <td>96.54</td>\n",
       "      <td>95.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\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.06</td>\n",
       "      <td>83.98</td>\n",
       "      <td>94.13</td>\n",
       "      <td>97.04</td>\n",
       "      <td>95.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\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.05</td>\n",
       "      <td>81.03</td>\n",
       "      <td>66.68</td>\n",
       "      <td>81.93</td>\n",
       "      <td>74.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\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.80</td>\n",
       "      <td>83.81</td>\n",
       "      <td>92.51</td>\n",
       "      <td>96.25</td>\n",
       "      <td>94.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\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.84</td>\n",
       "      <td>84.04</td>\n",
       "      <td>94.59</td>\n",
       "      <td>95.95</td>\n",
       "      <td>95.27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             School Name School Type  Total Students  Total School Budget  \\\n",
       "0      Huang High School    District            2917              1910635   \n",
       "1   Figueroa High School    District            2949              1884411   \n",
       "2    Shelton High School     Charter            1761              1056600   \n",
       "3  Hernandez High School    District            4635              3022020   \n",
       "4    Griffin High School     Charter            1468               917500   \n",
       "5     Wilson High School     Charter            2283              1319574   \n",
       "6    Cabrera High School     Charter            1858              1081356   \n",
       "7     Bailey High School    District            4976              3124928   \n",
       "8     Holden High School     Charter             427               248087   \n",
       "9       Pena High School     Charter             962               585858   \n",
       "\n",
       "   Per Student Budget  Average Math Score  Average Reading Score  \\\n",
       "0               655.0               76.63                  81.18   \n",
       "1               639.0               76.71                  81.16   \n",
       "2               600.0               83.36                  83.73   \n",
       "3               652.0               77.29                  80.93   \n",
       "4               625.0               83.35                  83.82   \n",
       "5               578.0               83.27                  83.99   \n",
       "6               582.0               83.06                  83.98   \n",
       "7               628.0               77.05                  81.03   \n",
       "8               581.0               83.80                  83.81   \n",
       "9               609.0               83.84                  84.04   \n",
       "\n",
       "   % Passing Math  % Passing Reading  Overall Passing Rate  \n",
       "0           65.68              81.32                 73.50  \n",
       "1           65.99              80.74                 73.36  \n",
       "2           93.87              95.85                 94.86  \n",
       "3           66.75              80.86                 73.81  \n",
       "4           93.39              97.14                 95.27  \n",
       "5           93.87              96.54                 95.21  \n",
       "6           94.13              97.04                 95.59  \n",
       "7           66.68              81.93                 74.31  \n",
       "8           92.51              96.25                 94.38  \n",
       "9           94.59              95.95                 95.27  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_r.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sorted = df_r.sort_values('Overall Passing Rate', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Top Performing Schools (By Passing Rate)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Sort and display the top five schools in overall passing rate"
   ]
  },
  {
   "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>School Name</th>\n",
       "      <th>School Type</th>\n",
       "      <th>Total Students</th>\n",
       "      <th>Total School 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>6</th>\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.06</td>\n",
       "      <td>83.98</td>\n",
       "      <td>94.13</td>\n",
       "      <td>97.04</td>\n",
       "      <td>95.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\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.42</td>\n",
       "      <td>83.85</td>\n",
       "      <td>93.27</td>\n",
       "      <td>97.31</td>\n",
       "      <td>95.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\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.35</td>\n",
       "      <td>83.82</td>\n",
       "      <td>93.39</td>\n",
       "      <td>97.14</td>\n",
       "      <td>95.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\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.84</td>\n",
       "      <td>84.04</td>\n",
       "      <td>94.59</td>\n",
       "      <td>95.95</td>\n",
       "      <td>95.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\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.27</td>\n",
       "      <td>83.99</td>\n",
       "      <td>93.87</td>\n",
       "      <td>96.54</td>\n",
       "      <td>95.21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            School Name School Type  Total Students  Total School Budget  \\\n",
       "6   Cabrera High School     Charter            1858              1081356   \n",
       "14   Thomas High School     Charter            1635              1043130   \n",
       "4   Griffin High School     Charter            1468               917500   \n",
       "9      Pena High School     Charter             962               585858   \n",
       "5    Wilson High School     Charter            2283              1319574   \n",
       "\n",
       "    Per Student Budget  Average Math Score  Average Reading Score  \\\n",
       "6                582.0               83.06                  83.98   \n",
       "14               638.0               83.42                  83.85   \n",
       "4                625.0               83.35                  83.82   \n",
       "9                609.0               83.84                  84.04   \n",
       "5                578.0               83.27                  83.99   \n",
       "\n",
       "    % Passing Math  % Passing Reading  Overall Passing Rate  \n",
       "6            94.13              97.04                 95.59  \n",
       "14           93.27              97.31                 95.29  \n",
       "4            93.39              97.14                 95.27  \n",
       "9            94.59              95.95                 95.27  \n",
       "5            93.87              96.54                 95.21  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sorted.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bottom Performing Schools (By Passing Rate)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Sort and display the five worst-performing schools"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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 Name</th>\n",
       "      <th>School Type</th>\n",
       "      <th>Total Students</th>\n",
       "      <th>Total School 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>11</th>\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.84</td>\n",
       "      <td>80.74</td>\n",
       "      <td>66.37</td>\n",
       "      <td>80.22</td>\n",
       "      <td>73.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\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.71</td>\n",
       "      <td>81.16</td>\n",
       "      <td>65.99</td>\n",
       "      <td>80.74</td>\n",
       "      <td>73.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\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.63</td>\n",
       "      <td>81.18</td>\n",
       "      <td>65.68</td>\n",
       "      <td>81.32</td>\n",
       "      <td>73.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\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.07</td>\n",
       "      <td>80.97</td>\n",
       "      <td>66.06</td>\n",
       "      <td>81.22</td>\n",
       "      <td>73.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\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.29</td>\n",
       "      <td>80.93</td>\n",
       "      <td>66.75</td>\n",
       "      <td>80.86</td>\n",
       "      <td>73.81</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              School Name School Type  Total Students  Total School Budget  \\\n",
       "11  Rodriguez High School    District            3999              2547363   \n",
       "1    Figueroa High School    District            2949              1884411   \n",
       "0       Huang High School    District            2917              1910635   \n",
       "12    Johnson High School    District            4761              3094650   \n",
       "3   Hernandez High School    District            4635              3022020   \n",
       "\n",
       "    Per Student Budget  Average Math Score  Average Reading Score  \\\n",
       "11               637.0               76.84                  80.74   \n",
       "1                639.0               76.71                  81.16   \n",
       "0                655.0               76.63                  81.18   \n",
       "12               650.0               77.07                  80.97   \n",
       "3                652.0               77.29                  80.93   \n",
       "\n",
       "    % Passing Math  % Passing Reading  Overall Passing Rate  \n",
       "11           66.37              80.22                 73.30  \n",
       "1            65.99              80.74                 73.36  \n",
       "0            65.68              81.32                 73.50  \n",
       "12           66.06              81.22                 73.64  \n",
       "3            66.75              80.86                 73.81  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sorted = df_r.sort_values('Overall Passing Rate', ascending=True)\n",
    "df_sorted.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Math Scores by Grade"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Create a table that lists the average Reading Score for students of each grade level (9th, 10th, 11th, 12th) at each school.\n",
    "\n",
    "  * Create a pandas series for each grade. Hint: use a conditional statement.\n",
    "  \n",
    "  * Group each series by school\n",
    "  \n",
    "  * Combine the series into a dataframe\n",
    "  \n",
    "  * Optional: give the displayed data cleaner formatting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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 Name</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>Huang High School</td>\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>1</th>\n",
       "      <td>Figueroa High School</td>\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>2</th>\n",
       "      <td>Shelton High School</td>\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>3</th>\n",
       "      <td>Hernandez High School</td>\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>4</th>\n",
       "      <td>Griffin High School</td>\n",
       "      <td>82.04</td>\n",
       "      <td>84.23</td>\n",
       "      <td>83.84</td>\n",
       "      <td>83.36</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             School Name    9th   10th   11th   12th\n",
       "0      Huang High School  77.03  75.91  76.45  77.23\n",
       "1   Figueroa High School  76.40  76.54  76.88  77.15\n",
       "2    Shelton High School  83.42  82.92  83.38  83.78\n",
       "3  Hernandez High School  77.44  77.34  77.14  77.19\n",
       "4    Griffin High School  82.04  84.23  83.84  83.36"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tmp = pd.DataFrame()\n",
    "df_r = pd.DataFrame()\n",
    "cols = ['School Name', '9th', '10th', '11th', '12th']\n",
    "for index, school in school_data.iterrows():\n",
    "    sn = school['school_name']\n",
    "    rs9  = format(student_data[(student_data.school_name == sn) & (student_data.grade == '9th')]['math_score'].mean(), '.2f')\n",
    "    rs10 = format(student_data[(student_data.school_name == sn) & (student_data.grade == '10th')]['math_score'].mean(), '.2f')\n",
    "    rs11 = format(student_data[(student_data.school_name == sn) & (student_data.grade == '11th')]['math_score'].mean(), '.2f')\n",
    "    rs12 = format(student_data[(student_data.school_name == sn) & (student_data.grade == '12th')]['math_score'].mean(), '.2f')\n",
    "    #rs10 = float(format(((student_data[(student_data.school_name == sn) & (student_data.reading_score >= 70)].count()[0] * 100 ) / ts),'.2f'))     \n",
    "    #print(f'{sn} - {rs9} - ')\n",
    "    \n",
    "    df_tmp = [(sn, rs9, rs10, rs11, rs12)]\n",
    "    df_r = df_r.append(pd.DataFrame(df_tmp, columns=cols, index=[index]))  \n",
    "    \n",
    "df_r.head()    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading Score by Grade "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Perform the same operations as above for reading scores"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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 Name</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>Huang High School</td>\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>1</th>\n",
       "      <td>Figueroa High School</td>\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>2</th>\n",
       "      <td>Shelton High School</td>\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>3</th>\n",
       "      <td>Hernandez High School</td>\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>4</th>\n",
       "      <td>Griffin High School</td>\n",
       "      <td>83.37</td>\n",
       "      <td>83.71</td>\n",
       "      <td>84.29</td>\n",
       "      <td>84.01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             School Name    9th   10th   11th   12th\n",
       "0      Huang High School  81.29  81.51  81.42  80.31\n",
       "1   Figueroa High School  81.20  81.41  80.64  81.38\n",
       "2    Shelton High School  84.12  83.44  84.37  82.78\n",
       "3  Hernandez High School  80.87  80.66  81.40  80.86\n",
       "4    Griffin High School  83.37  83.71  84.29  84.01"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tmp = pd.DataFrame()\n",
    "df_r = pd.DataFrame()\n",
    "cols = ['School Name', '9th', '10th', '11th', '12th']\n",
    "for index, school in school_data.iterrows():\n",
    "    sn = school['school_name']\n",
    "    rs9  = format(student_data[(student_data.school_name == sn) & (student_data.grade == '9th')]['reading_score'].mean(), '.2f')\n",
    "    rs10 = format(student_data[(student_data.school_name == sn) & (student_data.grade == '10th')]['reading_score'].mean(), '.2f')\n",
    "    rs11 = format(student_data[(student_data.school_name == sn) & (student_data.grade == '11th')]['reading_score'].mean(), '.2f')\n",
    "    rs12 = format(student_data[(student_data.school_name == sn) & (student_data.grade == '12th')]['reading_score'].mean(), '.2f')\n",
    "    \n",
    "    df_tmp = [(sn, rs9, rs10, rs11, rs12)]\n",
    "    df_r = df_r.append(pd.DataFrame(df_tmp, columns=cols, index=[index]))  \n",
    "    \n",
    "df_r.head()  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Scores by School Spending"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Create a table that breaks down school performances based on average Spending Ranges (Per Student). Use 4 reasonable bins to group school spending. Include in the table each of the following:\n",
    "  * Average Math Score\n",
    "  * Average Reading Score\n",
    "  * % Passing Math\n",
    "  * % Passing Reading\n",
    "  * Overall Passing Rate (Average of the above two)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sample bins. Feel free to create your own bins.\n",
    "spending_bins = [0, 585, 615, 645, 675]\n",
    "group_names = [\"<$585\", \"$585-615\", \"$615-645\", \"$645-675\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sorted['Spending Ranges (Per Student)'] = pd.cut(df_sorted['Per Student Budget'],spending_bins, labels=group_names)\n",
    "#df_sorted.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>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>83.452500</td>\n",
       "      <td>83.932500</td>\n",
       "      <td>93.460000</td>\n",
       "      <td>96.610000</td>\n",
       "      <td>95.0375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>$585-615</th>\n",
       "      <td>83.600000</td>\n",
       "      <td>83.885000</td>\n",
       "      <td>94.230000</td>\n",
       "      <td>95.900000</td>\n",
       "      <td>95.0650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>$615-645</th>\n",
       "      <td>79.078333</td>\n",
       "      <td>81.891667</td>\n",
       "      <td>75.668333</td>\n",
       "      <td>86.106667</td>\n",
       "      <td>80.8900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>$645-675</th>\n",
       "      <td>76.996667</td>\n",
       "      <td>81.026667</td>\n",
       "      <td>66.163333</td>\n",
       "      <td>81.133333</td>\n",
       "      <td>73.6500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               Average Math Score  Average Reading Score  \\\n",
       "Spending Ranges (Per Student)                                              \n",
       "<$585                                   83.452500              83.932500   \n",
       "$585-615                                83.600000              83.885000   \n",
       "$615-645                                79.078333              81.891667   \n",
       "$645-675                                76.996667              81.026667   \n",
       "\n",
       "                               % Passing Math  % Passing Reading  \\\n",
       "Spending Ranges (Per Student)                                      \n",
       "<$585                               93.460000          96.610000   \n",
       "$585-615                            94.230000          95.900000   \n",
       "$615-645                            75.668333          86.106667   \n",
       "$645-675                            66.163333          81.133333   \n",
       "\n",
       "                               Overall Passing Rate  \n",
       "Spending Ranges (Per Student)                        \n",
       "<$585                                       95.0375  \n",
       "$585-615                                    95.0650  \n",
       "$615-645                                    80.8900  \n",
       "$645-675                                    73.6500  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_grouped = df_sorted.groupby(['Spending Ranges (Per Student)']).mean()\n",
    "df_grouped = df_grouped[['Average Math Score', 'Average Reading Score','% Passing Math','% Passing Reading','Overall Passing Rate']]\n",
    "df_grouped.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Scores by School Size"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Perform the same operations as above, based on school size."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sample bins. Feel free to create your own bins.\n",
    "size_bins = [0, 1000, 2000, 5000]\n",
    "group_names = [\"Small (<1000)\", \"Medium (1000-2000)\", \"Large (2000-5000)\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sorted['School Size'] = pd.cut(df_sorted['Total Students'],size_bins, labels=group_names)"
   ]
  },
  {
   "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>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 (&lt;1000)</th>\n",
       "      <td>83.820</td>\n",
       "      <td>83.92500</td>\n",
       "      <td>93.55000</td>\n",
       "      <td>96.10000</td>\n",
       "      <td>94.8250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Medium (1000-2000)</th>\n",
       "      <td>83.374</td>\n",
       "      <td>83.86600</td>\n",
       "      <td>93.59800</td>\n",
       "      <td>96.79000</td>\n",
       "      <td>95.1960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Large (2000-5000)</th>\n",
       "      <td>77.745</td>\n",
       "      <td>81.34375</td>\n",
       "      <td>69.96375</td>\n",
       "      <td>82.76625</td>\n",
       "      <td>76.3675</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Average Math Score  Average Reading Score  % Passing Math  \\\n",
       "School Size                                                                     \n",
       "Small (<1000)                   83.820               83.92500        93.55000   \n",
       "Medium (1000-2000)              83.374               83.86600        93.59800   \n",
       "Large (2000-5000)               77.745               81.34375        69.96375   \n",
       "\n",
       "                    % Passing Reading  Overall Passing Rate  \n",
       "School Size                                                  \n",
       "Small (<1000)                96.10000               94.8250  \n",
       "Medium (1000-2000)           96.79000               95.1960  \n",
       "Large (2000-5000)            82.76625               76.3675  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_grouped = df_sorted.groupby(['School Size']).mean()\n",
    "df_grouped = df_grouped[['Average Math Score', 'Average Reading Score','% Passing Math','% Passing Reading','Overall Passing Rate']]\n",
    "df_grouped.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Scores by School Type"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Perform the same operations as above, based on school type."
   ]
  },
  {
   "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>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.896250</td>\n",
       "      <td>93.620000</td>\n",
       "      <td>96.586250</td>\n",
       "      <td>95.105000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>District</th>\n",
       "      <td>76.955714</td>\n",
       "      <td>80.965714</td>\n",
       "      <td>66.548571</td>\n",
       "      <td>80.798571</td>\n",
       "      <td>73.675714</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.896250       93.620000   \n",
       "District              76.955714              80.965714       66.548571   \n",
       "\n",
       "             % Passing Reading  Overall Passing Rate  \n",
       "School Type                                           \n",
       "Charter              96.586250             95.105000  \n",
       "District             80.798571             73.675714  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_grouped = df_sorted.groupby(['School Type']).mean()\n",
    "df_grouped = df_grouped[['Average Math Score', 'Average Reading Score','% Passing Math','% Passing Reading','Overall Passing Rate']]\n",
    "df_grouped.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernel_info": {
   "name": "python3"
  },
  "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.5"
  },
  "nteract": {
   "version": "0.8.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}