PyCity_Schools_Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Observable Trends\n",
    "# Looking at all the reading scores and math scores in each table, students tend to do better in reading than in math.\n",
    "# Looking at the top performing schools, bottom performing schools, and passing rates by school type, students have a \n",
    "# much higher passing rate in charter schools (~95%) than in district schools (~74%). \n",
    "# Looking at the summary of performance by spending ranges per student, it seems that students have a higher passing \n",
    "# rate in the lower spending ranges (<$615)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Import dependencies\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# Create paths to csv files\n",
    "school_path = \"raw_data/schools_complete.csv\"\n",
    "student_path = \"raw_data/students_complete.csv\"\n",
    "\n",
    "# Read the csv files\n",
    "schools = pd.read_csv(school_path)\n",
    "schools = schools.rename(columns={\"name\": \"school\"}) # so we can merge later\n",
    "\n",
    "students = pd.read_csv(student_path)\n",
    "students = pd.DataFrame(students)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Total Schools</th>\n",
       "      <th>Total Students</th>\n",
       "      <th>Total Budget</th>\n",
       "      <th>Average Math Score</th>\n",
       "      <th>Average Reading Score</th>\n",
       "      <th>% Passing Math</th>\n",
       "      <th>% Passing Reading</th>\n",
       "      <th>% Overall Passing Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15</td>\n",
       "      <td>39170</td>\n",
       "      <td>$24,649,428.00</td>\n",
       "      <td>78.985371</td>\n",
       "      <td>81.87784</td>\n",
       "      <td>74.980853</td>\n",
       "      <td>85.805463</td>\n",
       "      <td>80.393158</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Total Schools  Total Students    Total Budget  Average Math Score  \\\n",
       "0             15           39170  $24,649,428.00           78.985371   \n",
       "\n",
       "   Average Reading Score  % Passing Math  % Passing Reading  \\\n",
       "0               81.87784       74.980853          85.805463   \n",
       "\n",
       "   % Overall Passing Rate  \n",
       "0               80.393158  "
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate total schools, students, and budget of district\n",
    "total_schools = schools['School ID'].count()\n",
    "total_students = schools['size'].sum()\n",
    "total_budget = schools['budget'].sum()\n",
    "\n",
    "# Calculate average math and reading scores\n",
    "avg_math = students['math_score'].mean()\n",
    "avg_read = students['reading_score'].mean()\n",
    "\n",
    "# Calculate percent passing rates\n",
    "pass_math = students.loc[students['math_score'] >= 70]\n",
    "per_math = len(pass_math)/ total_students * 100\n",
    "\n",
    "pass_read = students.loc[students['reading_score'] >= 70]\n",
    "per_read = len(pass_read)/ total_students * 100\n",
    "\n",
    "overall = (per_math + per_read)/2 \n",
    "\n",
    "# Create dataframe for district summary\n",
    "district_summary = pd.DataFrame({\"Total Schools\": total_schools,\n",
    "                                \"Total Students\": total_students,\n",
    "                                \"Total Budget\": total_budget,\n",
    "                                \"Average Math Score\": avg_math,\n",
    "                                \"Average Reading Score\": avg_read,\n",
    "                                \"% Passing Math\": per_math,\n",
    "                                \"% Passing Reading\": per_read,\n",
    "                                \"% Overall Passing Rate\": [overall]})\n",
    "\n",
    "#Reorganize columns with double brackets\n",
    "district_summary = district_summary[[\"Total Schools\", \"Total Students\", \"Total Budget\", \n",
    "                                     \"Average Math Score\", \"Average Reading Score\", \n",
    "                                     \"% Passing Math\", \"% Passing Reading\", \"% Overall Passing Rate\"]]\n",
    "\n",
    "# Format to \"$0,000.00\"\n",
    "district_summary['Total Budget'] = district_summary['Total Budget'].map('${:,.2f}'.format)\n",
    "\n",
    "district_summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>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>Bailey High School</th>\n",
       "      <td>District</td>\n",
       "      <td>4976</td>\n",
       "      <td>$3,124,928.00</td>\n",
       "      <td>$628.00</td>\n",
       "      <td>77.048432</td>\n",
       "      <td>81.033963</td>\n",
       "      <td>66.680064</td>\n",
       "      <td>81.933280</td>\n",
       "      <td>74.306672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cabrera High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>1858</td>\n",
       "      <td>$1,081,356.00</td>\n",
       "      <td>$582.00</td>\n",
       "      <td>83.061895</td>\n",
       "      <td>83.975780</td>\n",
       "      <td>94.133477</td>\n",
       "      <td>97.039828</td>\n",
       "      <td>95.586652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Figueroa High School</th>\n",
       "      <td>District</td>\n",
       "      <td>2949</td>\n",
       "      <td>$1,884,411.00</td>\n",
       "      <td>$639.00</td>\n",
       "      <td>76.711767</td>\n",
       "      <td>81.158020</td>\n",
       "      <td>65.988471</td>\n",
       "      <td>80.739234</td>\n",
       "      <td>73.363852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ford High School</th>\n",
       "      <td>District</td>\n",
       "      <td>2739</td>\n",
       "      <td>$1,763,916.00</td>\n",
       "      <td>$644.00</td>\n",
       "      <td>77.102592</td>\n",
       "      <td>80.746258</td>\n",
       "      <td>68.309602</td>\n",
       "      <td>79.299014</td>\n",
       "      <td>73.804308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Griffin High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>1468</td>\n",
       "      <td>$917,500.00</td>\n",
       "      <td>$625.00</td>\n",
       "      <td>83.351499</td>\n",
       "      <td>83.816757</td>\n",
       "      <td>93.392371</td>\n",
       "      <td>97.138965</td>\n",
       "      <td>95.265668</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     School Type  Total Students Total School Budget  \\\n",
       "Bailey High School      District            4976       $3,124,928.00   \n",
       "Cabrera High School      Charter            1858       $1,081,356.00   \n",
       "Figueroa High School    District            2949       $1,884,411.00   \n",
       "Ford High School        District            2739       $1,763,916.00   \n",
       "Griffin High School      Charter            1468         $917,500.00   \n",
       "\n",
       "                     Per Student Budget  Average Math Score  \\\n",
       "Bailey High School              $628.00           77.048432   \n",
       "Cabrera High School             $582.00           83.061895   \n",
       "Figueroa High School            $639.00           76.711767   \n",
       "Ford High School                $644.00           77.102592   \n",
       "Griffin High School             $625.00           83.351499   \n",
       "\n",
       "                      Average Reading Score  % Passing Math  \\\n",
       "Bailey High School                81.033963       66.680064   \n",
       "Cabrera High School               83.975780       94.133477   \n",
       "Figueroa High School              81.158020       65.988471   \n",
       "Ford High School                  80.746258       68.309602   \n",
       "Griffin High School               83.816757       93.392371   \n",
       "\n",
       "                      % Passing Reading  Overall Passing Rate  \n",
       "Bailey High School            81.933280             74.306672  \n",
       "Cabrera High School           97.039828             95.586652  \n",
       "Figueroa High School          80.739234             73.363852  \n",
       "Ford High School              79.299014             73.804308  \n",
       "Griffin High School           97.138965             95.265668  "
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge schools and students\n",
    "merge_df = pd.merge(schools, students, on=\"school\")\n",
    "merge_df = merge_df.drop(['School ID', 'Student ID'], axis=1)\n",
    "\n",
    "# Set school as index, get type, total students, total budget and per student budget\n",
    "school_index = schools.set_index('school')\n",
    "school_type = school_index['type']\n",
    "school_students = school_index['size']\n",
    "school_budget = school_index['budget']\n",
    "psb = school_budget/school_students\n",
    "\n",
    "# Set school as index and groupby school for merge_df to get average scores by school\n",
    "index = merge_df.set_index('school')\n",
    "grouped = index.groupby(['school'])\n",
    "\n",
    "# Get average math and reading score\n",
    "school_avg_math = grouped['math_score'].mean()\n",
    "school_avg_read = grouped['reading_score'].mean()\n",
    "\n",
    "# Calculate percent that passed math\n",
    "total_stu = grouped['name'].count()\n",
    "grouped_math = pass_math.groupby('school')\n",
    "school_pass_math = grouped_math['name'].count()/total_stu*100\n",
    "\n",
    "# Calculate percent that passed reading\n",
    "grouped_reading = pass_read.groupby('school')\n",
    "school_pass_read = grouped_reading['name'].count()/total_stu*100\n",
    "                             \n",
    "# Calculate overall passing rate\n",
    "overall_pass = (school_pass_math + school_pass_read)/2\n",
    "\n",
    "# Create dataframe for school summary\n",
    "school_summary = pd.DataFrame({\"School Type\": school_type,\n",
    "                              \"Total Students\": school_students,\n",
    "                              \"Total School Budget\": school_budget,\n",
    "                              \"Per Student Budget\": psb,\n",
    "                              \"Average Math Score\": school_avg_math,\n",
    "                              \"Average Reading Score\": school_avg_read,\n",
    "                              \"% Passing Math\": school_pass_math,\n",
    "                              \"% Passing Reading\": school_pass_read,\n",
    "                              \"Overall Passing Rate\": overall_pass})\n",
    "\n",
    "# Reorganize school summary columns \n",
    "school_summary = school_summary[[\"School Type\", \"Total Students\", \"Total School Budget\", \n",
    "                                 \"Per Student Budget\", \"Average Math Score\", \"Average Reading Score\", \n",
    "                                 \"% Passing Math\", \"% Passing Reading\", \"Overall Passing Rate\"]]\n",
    "\n",
    "# Format to \"$0,000.00\"\n",
    "school_summary['Total School Budget'] = school_summary['Total School Budget'].map('${:,.2f}'.format)\n",
    "school_summary['Per Student Budget'] = school_summary['Per Student Budget'].map('${:,.2f}'.format)\n",
    "\n",
    "school_summary.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>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>Cabrera High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>1858</td>\n",
       "      <td>$1,081,356.00</td>\n",
       "      <td>$582.00</td>\n",
       "      <td>83.061895</td>\n",
       "      <td>83.975780</td>\n",
       "      <td>94.133477</td>\n",
       "      <td>97.039828</td>\n",
       "      <td>95.586652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thomas High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>1635</td>\n",
       "      <td>$1,043,130.00</td>\n",
       "      <td>$638.00</td>\n",
       "      <td>83.418349</td>\n",
       "      <td>83.848930</td>\n",
       "      <td>93.272171</td>\n",
       "      <td>97.308869</td>\n",
       "      <td>95.290520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pena High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>962</td>\n",
       "      <td>$585,858.00</td>\n",
       "      <td>$609.00</td>\n",
       "      <td>83.839917</td>\n",
       "      <td>84.044699</td>\n",
       "      <td>94.594595</td>\n",
       "      <td>95.945946</td>\n",
       "      <td>95.270270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Griffin High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>1468</td>\n",
       "      <td>$917,500.00</td>\n",
       "      <td>$625.00</td>\n",
       "      <td>83.351499</td>\n",
       "      <td>83.816757</td>\n",
       "      <td>93.392371</td>\n",
       "      <td>97.138965</td>\n",
       "      <td>95.265668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wilson High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>2283</td>\n",
       "      <td>$1,319,574.00</td>\n",
       "      <td>$578.00</td>\n",
       "      <td>83.274201</td>\n",
       "      <td>83.989488</td>\n",
       "      <td>93.867718</td>\n",
       "      <td>96.539641</td>\n",
       "      <td>95.203679</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    School Type  Total Students Total School Budget  \\\n",
       "Cabrera High School     Charter            1858       $1,081,356.00   \n",
       "Thomas High School      Charter            1635       $1,043,130.00   \n",
       "Pena High School        Charter             962         $585,858.00   \n",
       "Griffin High School     Charter            1468         $917,500.00   \n",
       "Wilson High School      Charter            2283       $1,319,574.00   \n",
       "\n",
       "                    Per Student Budget  Average Math Score  \\\n",
       "Cabrera High School            $582.00           83.061895   \n",
       "Thomas High School             $638.00           83.418349   \n",
       "Pena High School               $609.00           83.839917   \n",
       "Griffin High School            $625.00           83.351499   \n",
       "Wilson High School             $578.00           83.274201   \n",
       "\n",
       "                     Average Reading Score  % Passing Math  % Passing Reading  \\\n",
       "Cabrera High School              83.975780       94.133477          97.039828   \n",
       "Thomas High School               83.848930       93.272171          97.308869   \n",
       "Pena High School                 84.044699       94.594595          95.945946   \n",
       "Griffin High School              83.816757       93.392371          97.138965   \n",
       "Wilson High School               83.989488       93.867718          96.539641   \n",
       "\n",
       "                     Overall Passing Rate  \n",
       "Cabrera High School             95.586652  \n",
       "Thomas High School              95.290520  \n",
       "Pena High School                95.270270  \n",
       "Griffin High School             95.265668  \n",
       "Wilson High School              95.203679  "
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create top performing schools summary by passing rate\n",
    "top_summary = school_summary.loc[school_summary['Overall Passing Rate'] > 90]\n",
    "top_summary.sort_values(['Overall Passing Rate'], ascending=False).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>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>Rodriguez High School</th>\n",
       "      <td>District</td>\n",
       "      <td>3999</td>\n",
       "      <td>$2,547,363.00</td>\n",
       "      <td>$637.00</td>\n",
       "      <td>76.842711</td>\n",
       "      <td>80.744686</td>\n",
       "      <td>66.366592</td>\n",
       "      <td>80.220055</td>\n",
       "      <td>73.293323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Figueroa High School</th>\n",
       "      <td>District</td>\n",
       "      <td>2949</td>\n",
       "      <td>$1,884,411.00</td>\n",
       "      <td>$639.00</td>\n",
       "      <td>76.711767</td>\n",
       "      <td>81.158020</td>\n",
       "      <td>65.988471</td>\n",
       "      <td>80.739234</td>\n",
       "      <td>73.363852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Huang High School</th>\n",
       "      <td>District</td>\n",
       "      <td>2917</td>\n",
       "      <td>$1,910,635.00</td>\n",
       "      <td>$655.00</td>\n",
       "      <td>76.629414</td>\n",
       "      <td>81.182722</td>\n",
       "      <td>65.683922</td>\n",
       "      <td>81.316421</td>\n",
       "      <td>73.500171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Johnson High School</th>\n",
       "      <td>District</td>\n",
       "      <td>4761</td>\n",
       "      <td>$3,094,650.00</td>\n",
       "      <td>$650.00</td>\n",
       "      <td>77.072464</td>\n",
       "      <td>80.966394</td>\n",
       "      <td>66.057551</td>\n",
       "      <td>81.222432</td>\n",
       "      <td>73.639992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ford High School</th>\n",
       "      <td>District</td>\n",
       "      <td>2739</td>\n",
       "      <td>$1,763,916.00</td>\n",
       "      <td>$644.00</td>\n",
       "      <td>77.102592</td>\n",
       "      <td>80.746258</td>\n",
       "      <td>68.309602</td>\n",
       "      <td>79.299014</td>\n",
       "      <td>73.804308</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      School Type  Total Students Total School Budget  \\\n",
       "Rodriguez High School    District            3999       $2,547,363.00   \n",
       "Figueroa High School     District            2949       $1,884,411.00   \n",
       "Huang High School        District            2917       $1,910,635.00   \n",
       "Johnson High School      District            4761       $3,094,650.00   \n",
       "Ford High School         District            2739       $1,763,916.00   \n",
       "\n",
       "                      Per Student Budget  Average Math Score  \\\n",
       "Rodriguez High School            $637.00           76.842711   \n",
       "Figueroa High School             $639.00           76.711767   \n",
       "Huang High School                $655.00           76.629414   \n",
       "Johnson High School              $650.00           77.072464   \n",
       "Ford High School                 $644.00           77.102592   \n",
       "\n",
       "                       Average Reading Score  % Passing Math  \\\n",
       "Rodriguez High School              80.744686       66.366592   \n",
       "Figueroa High School               81.158020       65.988471   \n",
       "Huang High School                  81.182722       65.683922   \n",
       "Johnson High School                80.966394       66.057551   \n",
       "Ford High School                   80.746258       68.309602   \n",
       "\n",
       "                       % Passing Reading  Overall Passing Rate  \n",
       "Rodriguez High School          80.220055             73.293323  \n",
       "Figueroa High School           80.739234             73.363852  \n",
       "Huang High School              81.316421             73.500171  \n",
       "Johnson High School            81.222432             73.639992  \n",
       "Ford High School               79.299014             73.804308  "
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create bottom performing schools summary by passing rate\n",
    "bottom_summary = school_summary.loc[school_summary['Overall Passing Rate'] < 75]\n",
    "bottom_summary.sort_values(['Overall Passing Rate'], ascending=True).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></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.083676</td>\n",
       "      <td>76.996772</td>\n",
       "      <td>77.515588</td>\n",
       "      <td>76.492218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cabrera High School</th>\n",
       "      <td>83.094697</td>\n",
       "      <td>83.154506</td>\n",
       "      <td>82.765560</td>\n",
       "      <td>83.277487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Figueroa High School</th>\n",
       "      <td>76.403037</td>\n",
       "      <td>76.539974</td>\n",
       "      <td>76.884344</td>\n",
       "      <td>77.151369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ford High School</th>\n",
       "      <td>77.361345</td>\n",
       "      <td>77.672316</td>\n",
       "      <td>76.918058</td>\n",
       "      <td>76.179963</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Griffin High School</th>\n",
       "      <td>82.044010</td>\n",
       "      <td>84.229064</td>\n",
       "      <td>83.842105</td>\n",
       "      <td>83.356164</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            9th       10th       11th       12th\n",
       "Bailey High School    77.083676  76.996772  77.515588  76.492218\n",
       "Cabrera High School   83.094697  83.154506  82.765560  83.277487\n",
       "Figueroa High School  76.403037  76.539974  76.884344  77.151369\n",
       "Ford High School      77.361345  77.672316  76.918058  76.179963\n",
       "Griffin High School   82.044010  84.229064  83.842105  83.356164"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Math scores by grade\n",
    "ninth = students.loc[students['grade']=='9th'].groupby(\"school\")\n",
    "ninth_math = ninth['math_score'].mean()\n",
    "tenth = students.loc[students['grade']=='10th'].groupby(\"school\")\n",
    "tenth_math = tenth['math_score'].mean()\n",
    "eleventh = students.loc[students['grade']=='11th'].groupby(\"school\")\n",
    "eleventh_math = eleventh['math_score'].mean()\n",
    "twelfth = students.loc[students['grade']=='12th'].groupby(\"school\")\n",
    "twelfth_math = twelfth['math_score'].mean()\n",
    "\n",
    "# Create dataframe for math scores summary\n",
    "math_summary = pd.DataFrame({\"9th\": ninth_math,\n",
    "                            \"10th\": tenth_math,\n",
    "                            \"11th\": eleventh_math,\n",
    "                            \"12th\": twelfth_math})\n",
    "math_summary = math_summary[[\"9th\",\"10th\",\"11th\",\"12th\"]]\n",
    "del math_summary.index.name\n",
    "\n",
    "math_summary.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></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.303155</td>\n",
       "      <td>80.907183</td>\n",
       "      <td>80.945643</td>\n",
       "      <td>80.912451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cabrera High School</th>\n",
       "      <td>83.676136</td>\n",
       "      <td>84.253219</td>\n",
       "      <td>83.788382</td>\n",
       "      <td>84.287958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Figueroa High School</th>\n",
       "      <td>81.198598</td>\n",
       "      <td>81.408912</td>\n",
       "      <td>80.640339</td>\n",
       "      <td>81.384863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ford High School</th>\n",
       "      <td>80.632653</td>\n",
       "      <td>81.262712</td>\n",
       "      <td>80.403642</td>\n",
       "      <td>80.662338</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Griffin High School</th>\n",
       "      <td>83.369193</td>\n",
       "      <td>83.706897</td>\n",
       "      <td>84.288089</td>\n",
       "      <td>84.013699</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            9th       10th       11th       12th\n",
       "Bailey High School    81.303155  80.907183  80.945643  80.912451\n",
       "Cabrera High School   83.676136  84.253219  83.788382  84.287958\n",
       "Figueroa High School  81.198598  81.408912  80.640339  81.384863\n",
       "Ford High School      80.632653  81.262712  80.403642  80.662338\n",
       "Griffin High School   83.369193  83.706897  84.288089  84.013699"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reading scores by grade\n",
    "r_ninth = students.loc[students['grade'] == '9th'].groupby(\"school\")\n",
    "ninth_read = r_ninth['reading_score'].mean()\n",
    "r_tenth = students.loc[students['grade'] == '10th'].groupby(\"school\")\n",
    "tenth_read = r_tenth['reading_score'].mean()\n",
    "r_eleventh = students.loc[students['grade'] == '11th'].groupby(\"school\")\n",
    "eleventh_read = r_eleventh['reading_score'].mean()\n",
    "r_twelfth = students.loc[students['grade'] == '12th'].groupby(\"school\")\n",
    "twelfth_read = r_twelfth['reading_score'].mean()\n",
    "\n",
    "# Create dataframe for reading scores summary\n",
    "read_summary = pd.DataFrame({\"9th\": ninth_read,\n",
    "                            \"10th\": tenth_read,\n",
    "                            \"11th\": eleventh_read,\n",
    "                            \"12th\": twelfth_read})\n",
    "read_summary = read_summary[[\"9th\",\"10th\",\"11th\",\"12th\"]]\n",
    "del read_summary.index.name\n",
    "\n",
    "read_summary.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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.455399</td>\n",
       "      <td>83.933814</td>\n",
       "      <td>93.460096</td>\n",
       "      <td>96.610877</td>\n",
       "      <td>95.035486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>$585-615</th>\n",
       "      <td>83.599686</td>\n",
       "      <td>83.885211</td>\n",
       "      <td>94.230858</td>\n",
       "      <td>95.900287</td>\n",
       "      <td>95.065572</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>$615-645</th>\n",
       "      <td>79.079225</td>\n",
       "      <td>81.891436</td>\n",
       "      <td>75.668212</td>\n",
       "      <td>86.106569</td>\n",
       "      <td>80.887391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>$645-675</th>\n",
       "      <td>76.997210</td>\n",
       "      <td>81.027843</td>\n",
       "      <td>66.164813</td>\n",
       "      <td>81.133951</td>\n",
       "      <td>73.649382</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.455399              83.933814   \n",
       "$585-615                                83.599686              83.885211   \n",
       "$615-645                                79.079225              81.891436   \n",
       "$645-675                                76.997210              81.027843   \n",
       "\n",
       "                               % Passing Math  % Passing Reading  \\\n",
       "Spending Ranges (Per Student)                                      \n",
       "<$585                               93.460096          96.610877   \n",
       "$585-615                            94.230858          95.900287   \n",
       "$615-645                            75.668212          86.106569   \n",
       "$645-675                            66.164813          81.133951   \n",
       "\n",
       "                               Overall Passing Rate  \n",
       "Spending Ranges (Per Student)                        \n",
       "<$585                                     95.035486  \n",
       "$585-615                                  95.065572  \n",
       "$615-645                                  80.887391  \n",
       "$645-675                                  73.649382  "
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create bins\n",
    "bins = [0,585,615,645,675]\n",
    "\n",
    "# Create names for the bins\n",
    "spending_range = ['<$585','$585-615','$615-645','$645-675']\n",
    "\n",
    "# Change formatting of per student budget in school_summary from string back to float so it can be binned \n",
    "school_summary['Per Student Budget'] = school_summary['Per Student Budget'].str.replace('$', '')\n",
    "school_summary['Per Student Budget'] = school_summary['Per Student Budget'].astype(float)\n",
    "                                                      \n",
    "school_summary[\"Spending Ranges (Per Student)\"] = pd.cut(school_summary[\"Per Student Budget\"], \n",
    "                                                         bins, labels=spending_range)\n",
    "spend_summary = school_summary.groupby(\"Spending Ranges (Per Student)\")\n",
    "spend_summary = spend_summary[[\"Average Math Score\", \"Average Reading Score\", \"% Passing Math\", \n",
    "                               \"% Passing Reading\", \"Overall Passing Rate\"]]\n",
    "spend_summary.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Average Math Score</th>\n",
       "      <th>Average Reading Score</th>\n",
       "      <th>% Passing Math</th>\n",
       "      <th>% Passing Reading</th>\n",
       "      <th>Overall Passing Rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>School Size</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Small</th>\n",
       "      <td>83.821598</td>\n",
       "      <td>83.929843</td>\n",
       "      <td>93.550225</td>\n",
       "      <td>96.099437</td>\n",
       "      <td>94.824831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Medium</th>\n",
       "      <td>83.374684</td>\n",
       "      <td>83.864438</td>\n",
       "      <td>93.599695</td>\n",
       "      <td>96.790680</td>\n",
       "      <td>95.195187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Large</th>\n",
       "      <td>77.746417</td>\n",
       "      <td>81.344493</td>\n",
       "      <td>69.963361</td>\n",
       "      <td>82.766634</td>\n",
       "      <td>76.364998</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Average Math Score  Average Reading Score  % Passing Math  \\\n",
       "School Size                                                              \n",
       "Small                 83.821598              83.929843       93.550225   \n",
       "Medium                83.374684              83.864438       93.599695   \n",
       "Large                 77.746417              81.344493       69.963361   \n",
       "\n",
       "             % Passing Reading  Overall Passing Rate  \n",
       "School Size                                           \n",
       "Small                96.099437             94.824831  \n",
       "Medium               96.790680             95.195187  \n",
       "Large                82.766634             76.364998  "
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create bins\n",
    "bins2 = [0, 1000, 2000, 5000]\n",
    "\n",
    "# Create names for bins\n",
    "size_range = ['Small', 'Medium', 'Large']\n",
    "\n",
    "school_summary[\"School Size\"] = pd.cut(school_summary[\"Total Students\"], \n",
    "                                                         bins2, labels=size_range)\n",
    "size_summary = school_summary.groupby(\"School Size\")\n",
    "size_summary = size_summary[[\"Average Math Score\", \"Average Reading Score\", \"% Passing Math\", \n",
    "                             \"% Passing Reading\", \"Overall Passing Rate\"]]\n",
    "size_summary.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Average Math Score</th>\n",
       "      <th>Average Reading Score</th>\n",
       "      <th>% Passing Math</th>\n",
       "      <th>% Passing Reading</th>\n",
       "      <th>Overall Passing Rate</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>School Type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Charter</th>\n",
       "      <td>83.473852</td>\n",
       "      <td>83.896421</td>\n",
       "      <td>93.620830</td>\n",
       "      <td>96.586489</td>\n",
       "      <td>95.103660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>District</th>\n",
       "      <td>76.956733</td>\n",
       "      <td>80.966636</td>\n",
       "      <td>66.548453</td>\n",
       "      <td>80.799062</td>\n",
       "      <td>73.673757</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Average Math Score  Average Reading Score  % Passing Math  \\\n",
       "School Type                                                              \n",
       "Charter               83.473852              83.896421       93.620830   \n",
       "District              76.956733              80.966636       66.548453   \n",
       "\n",
       "             % Passing Reading  Overall Passing Rate  \n",
       "School Type                                           \n",
       "Charter              96.586489             95.103660  \n",
       "District             80.799062             73.673757  "
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type_summary = school_summary.groupby(\"School Type\")\n",
    "type_summary = type_summary[[\"Average Math Score\", \"Average Reading Score\", \"% Passing Math\", \n",
    "                             \"% Passing Reading\", \"Overall Passing Rate\"]]\n",
    "type_summary.mean()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:PythonData]",
   "language": "python",
   "name": "conda-env-PythonData-py"
  },
  "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}