PyCitySchools_Challenge.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2042,
   "id": "3e1e96d2",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "E:\\anacondaFiles\\envs\\PythonData\\lib\\site-packages\\ipykernel_launcher.py:20: FutureWarning: The default value of regex will change from True to False in a future version.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Paul Bradley</td>\n",
       "      <td>M</td>\n",
       "      <td>9th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>66</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Victor Smith</td>\n",
       "      <td>M</td>\n",
       "      <td>12th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>94</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Kevin Rodriguez</td>\n",
       "      <td>M</td>\n",
       "      <td>12th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>90</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>Richard Scott</td>\n",
       "      <td>M</td>\n",
       "      <td>12th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>67</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>Bonnie Ray</td>\n",
       "      <td>F</td>\n",
       "      <td>9th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>97</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>Bryan Miranda</td>\n",
       "      <td>M</td>\n",
       "      <td>9th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>94</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>Sheena Carter</td>\n",
       "      <td>F</td>\n",
       "      <td>11th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>82</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>Nicole Baker</td>\n",
       "      <td>F</td>\n",
       "      <td>12th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>96</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>Michael Roth</td>\n",
       "      <td>M</td>\n",
       "      <td>10th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>95</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>Matthew Greene</td>\n",
       "      <td>M</td>\n",
       "      <td>10th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>96</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  Student ID     student_name gender grade        school_name  \\\n",
       "0           0           0     Paul Bradley      M   9th  Huang High School   \n",
       "1           1           1     Victor Smith      M  12th  Huang High School   \n",
       "2           2           2  Kevin Rodriguez      M  12th  Huang High School   \n",
       "3           3           3    Richard Scott      M  12th  Huang High School   \n",
       "4           4           4       Bonnie Ray      F   9th  Huang High School   \n",
       "5           5           5    Bryan Miranda      M   9th  Huang High School   \n",
       "6           6           6    Sheena Carter      F  11th  Huang High School   \n",
       "7           7           7     Nicole Baker      F  12th  Huang High School   \n",
       "8           8           8     Michael Roth      M  10th  Huang High School   \n",
       "9           9           9   Matthew Greene      M  10th  Huang High School   \n",
       "\n",
       "   reading_score  math_score  \n",
       "0             66          79  \n",
       "1             94          61  \n",
       "2             90          60  \n",
       "3             67          58  \n",
       "4             97          84  \n",
       "5             94          94  \n",
       "6             82          80  \n",
       "7             96          69  \n",
       "8             95          87  \n",
       "9             96          84  "
      ]
     },
     "execution_count": 2042,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dependencies and Setup\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "\n",
    "# File to Load (Remember to change the path if needed.)\n",
    "school_data_to_load = \"Resources/schools_complete.csv\"\n",
    "student_data_to_load = \"Resources/students_complete.csv\"\n",
    "\n",
    "# Read the School Data and Student Data and store into a Pandas DataFrame\n",
    "school_data_df = pd.read_csv(school_data_to_load)\n",
    "student_data_df = pd.read_csv(student_data_to_load)\n",
    " \n",
    "# Cleaning Student Names and Replacing Substrings in a Python String\n",
    "# Add each prefix and suffix to remove to a list.\n",
    "prefixes_suffixes = [\"Dr. \", \"Mr. \",\"Ms. \", \"Mrs. \", \"Miss \", \" MD\", \" DDS\", \" DVM\", \" PhD\"]\n",
    "\n",
    "# Iterate through the words in the \"prefixes_suffixes\" list and replace them with an empty space, \"\".\n",
    "for word in prefixes_suffixes:\n",
    "    student_data_df[\"student_name\"] = student_data_df[\"student_name\"].str.replace(word,\"\")\n",
    "\n",
    "# Check names.\n",
    "student_data_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ae9d5a8",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2043,
   "id": "3bf4689c",
   "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>Unnamed: 0</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>39160</th>\n",
       "      <td>39160</td>\n",
       "      <td>39160</td>\n",
       "      <td>Katie Weaver</td>\n",
       "      <td>F</td>\n",
       "      <td>11th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>89.0</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39161</th>\n",
       "      <td>39161</td>\n",
       "      <td>39161</td>\n",
       "      <td>April Reyes</td>\n",
       "      <td>F</td>\n",
       "      <td>10th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>70.0</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39162</th>\n",
       "      <td>39162</td>\n",
       "      <td>39162</td>\n",
       "      <td>Derek Weeks</td>\n",
       "      <td>M</td>\n",
       "      <td>12th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>94.0</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39163</th>\n",
       "      <td>39163</td>\n",
       "      <td>39163</td>\n",
       "      <td>John Reese</td>\n",
       "      <td>M</td>\n",
       "      <td>11th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>90.0</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39164</th>\n",
       "      <td>39164</td>\n",
       "      <td>39164</td>\n",
       "      <td>Joseph Anthony</td>\n",
       "      <td>M</td>\n",
       "      <td>9th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>NaN</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39165</th>\n",
       "      <td>39165</td>\n",
       "      <td>39165</td>\n",
       "      <td>Donna Howard</td>\n",
       "      <td>F</td>\n",
       "      <td>12th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>99.0</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39166</th>\n",
       "      <td>39166</td>\n",
       "      <td>39166</td>\n",
       "      <td>Dawn Bell</td>\n",
       "      <td>F</td>\n",
       "      <td>10th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>95.0</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39167</th>\n",
       "      <td>39167</td>\n",
       "      <td>39167</td>\n",
       "      <td>Rebecca Tanner</td>\n",
       "      <td>F</td>\n",
       "      <td>9th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>NaN</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39168</th>\n",
       "      <td>39168</td>\n",
       "      <td>39168</td>\n",
       "      <td>Desiree Kidd</td>\n",
       "      <td>F</td>\n",
       "      <td>10th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>99.0</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39169</th>\n",
       "      <td>39169</td>\n",
       "      <td>39169</td>\n",
       "      <td>Carolyn Jackson</td>\n",
       "      <td>F</td>\n",
       "      <td>11th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>95.0</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Unnamed: 0  Student ID     student_name gender grade  \\\n",
       "39160       39160       39160     Katie Weaver      F  11th   \n",
       "39161       39161       39161      April Reyes      F  10th   \n",
       "39162       39162       39162      Derek Weeks      M  12th   \n",
       "39163       39163       39163       John Reese      M  11th   \n",
       "39164       39164       39164   Joseph Anthony      M   9th   \n",
       "39165       39165       39165     Donna Howard      F  12th   \n",
       "39166       39166       39166        Dawn Bell      F  10th   \n",
       "39167       39167       39167   Rebecca Tanner      F   9th   \n",
       "39168       39168       39168     Desiree Kidd      F  10th   \n",
       "39169       39169       39169  Carolyn Jackson      F  11th   \n",
       "\n",
       "              school_name  reading_score  math_score  \n",
       "39160  Thomas High School           89.0          86  \n",
       "39161  Thomas High School           70.0          84  \n",
       "39162  Thomas High School           94.0          77  \n",
       "39163  Thomas High School           90.0          75  \n",
       "39164  Thomas High School            NaN          76  \n",
       "39165  Thomas High School           99.0          90  \n",
       "39166  Thomas High School           95.0          70  \n",
       "39167  Thomas High School            NaN          84  \n",
       "39168  Thomas High School           99.0          90  \n",
       "39169  Thomas High School           95.0          75  "
      ]
     },
     "execution_count": 2043,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Deliverable 1: Replace the reading and math scores.\n",
    "#Replace the 9th grade reading and math scores at Thomas High School with NaN.\n",
    "# Step 1. Imported the numpy libaries in the beginning\n",
    "\n",
    "# Step 2. Use the loc method on the student_data_df to select all the reading scores from the 9th grade at Thomas High School and replace them with NaN.\n",
    "student_data_df.loc[(student_data_df[\"grade\"]== \"9th\") & (student_data_df[\"school_name\"]== \"Thomas High School\"),\n",
    "                    \"reading_score\"]=np.nan\n",
    "\n",
    "#To check the result\n",
    "res_reading=student_data_df[(student_data_df[\"grade\"]== \"9th\") \n",
    "                            & \n",
    "                            (student_data_df[\"school_name\"]== \"Thomas High School\")]\n",
    "\n",
    "\n",
    "res_reading\n",
    "student_data_df.tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2044,
   "id": "ce26753e",
   "metadata": {
    "scrolled": true
   },
   "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>Unnamed: 0</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>37537</th>\n",
       "      <td>37537</td>\n",
       "      <td>37537</td>\n",
       "      <td>Erik Snyder</td>\n",
       "      <td>M</td>\n",
       "      <td>9th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37538</th>\n",
       "      <td>37538</td>\n",
       "      <td>37538</td>\n",
       "      <td>Tanya Martinez</td>\n",
       "      <td>F</td>\n",
       "      <td>9th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37539</th>\n",
       "      <td>37539</td>\n",
       "      <td>37539</td>\n",
       "      <td>Noah Erickson</td>\n",
       "      <td>M</td>\n",
       "      <td>9th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37540</th>\n",
       "      <td>37540</td>\n",
       "      <td>37540</td>\n",
       "      <td>Austin Meyer</td>\n",
       "      <td>M</td>\n",
       "      <td>9th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37543</th>\n",
       "      <td>37543</td>\n",
       "      <td>37543</td>\n",
       "      <td>Madison Hampton</td>\n",
       "      <td>F</td>\n",
       "      <td>9th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39152</th>\n",
       "      <td>39152</td>\n",
       "      <td>39152</td>\n",
       "      <td>Lori Moore</td>\n",
       "      <td>F</td>\n",
       "      <td>9th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39153</th>\n",
       "      <td>39153</td>\n",
       "      <td>39153</td>\n",
       "      <td>William Hubbard</td>\n",
       "      <td>M</td>\n",
       "      <td>9th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39157</th>\n",
       "      <td>39157</td>\n",
       "      <td>39157</td>\n",
       "      <td>Kristen Gonzalez</td>\n",
       "      <td>F</td>\n",
       "      <td>9th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39164</th>\n",
       "      <td>39164</td>\n",
       "      <td>39164</td>\n",
       "      <td>Joseph Anthony</td>\n",
       "      <td>M</td>\n",
       "      <td>9th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39167</th>\n",
       "      <td>39167</td>\n",
       "      <td>39167</td>\n",
       "      <td>Rebecca Tanner</td>\n",
       "      <td>F</td>\n",
       "      <td>9th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>461 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Unnamed: 0  Student ID      student_name gender grade  \\\n",
       "37537       37537       37537       Erik Snyder      M   9th   \n",
       "37538       37538       37538    Tanya Martinez      F   9th   \n",
       "37539       37539       37539     Noah Erickson      M   9th   \n",
       "37540       37540       37540      Austin Meyer      M   9th   \n",
       "37543       37543       37543   Madison Hampton      F   9th   \n",
       "...           ...         ...               ...    ...   ...   \n",
       "39152       39152       39152        Lori Moore      F   9th   \n",
       "39153       39153       39153   William Hubbard      M   9th   \n",
       "39157       39157       39157  Kristen Gonzalez      F   9th   \n",
       "39164       39164       39164    Joseph Anthony      M   9th   \n",
       "39167       39167       39167    Rebecca Tanner      F   9th   \n",
       "\n",
       "              school_name  reading_score  math_score  \n",
       "37537  Thomas High School            NaN         NaN  \n",
       "37538  Thomas High School            NaN         NaN  \n",
       "37539  Thomas High School            NaN         NaN  \n",
       "37540  Thomas High School            NaN         NaN  \n",
       "37543  Thomas High School            NaN         NaN  \n",
       "...                   ...            ...         ...  \n",
       "39152  Thomas High School            NaN         NaN  \n",
       "39153  Thomas High School            NaN         NaN  \n",
       "39157  Thomas High School            NaN         NaN  \n",
       "39164  Thomas High School            NaN         NaN  \n",
       "39167  Thomas High School            NaN         NaN  \n",
       "\n",
       "[461 rows x 8 columns]"
      ]
     },
     "execution_count": 2044,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  Step 3. Refactor the code in Step 2 to replace the math scores with NaN.\n",
    "student_data_df.loc[(student_data_df[\"grade\"]== \"9th\") & (student_data_df[\"school_name\"]== \"Thomas High School\"),\n",
    "                    \"math_score\"]=np.nan\n",
    "\n",
    "#To check the result\n",
    "res_math=student_data_df[(student_data_df[\"grade\"]== \"9th\") \n",
    "                            & \n",
    "                            (student_data_df[\"school_name\"]== \"Thomas High School\")]\n",
    "\n",
    "\n",
    "res_math\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2045,
   "id": "d501af6b",
   "metadata": {
    "scrolled": true
   },
   "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>Unnamed: 0</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>39160</th>\n",
       "      <td>39160</td>\n",
       "      <td>39160</td>\n",
       "      <td>Katie Weaver</td>\n",
       "      <td>F</td>\n",
       "      <td>11th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>89.0</td>\n",
       "      <td>86.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39161</th>\n",
       "      <td>39161</td>\n",
       "      <td>39161</td>\n",
       "      <td>April Reyes</td>\n",
       "      <td>F</td>\n",
       "      <td>10th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>70.0</td>\n",
       "      <td>84.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39162</th>\n",
       "      <td>39162</td>\n",
       "      <td>39162</td>\n",
       "      <td>Derek Weeks</td>\n",
       "      <td>M</td>\n",
       "      <td>12th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>94.0</td>\n",
       "      <td>77.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39163</th>\n",
       "      <td>39163</td>\n",
       "      <td>39163</td>\n",
       "      <td>John Reese</td>\n",
       "      <td>M</td>\n",
       "      <td>11th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>90.0</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39164</th>\n",
       "      <td>39164</td>\n",
       "      <td>39164</td>\n",
       "      <td>Joseph Anthony</td>\n",
       "      <td>M</td>\n",
       "      <td>9th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39165</th>\n",
       "      <td>39165</td>\n",
       "      <td>39165</td>\n",
       "      <td>Donna Howard</td>\n",
       "      <td>F</td>\n",
       "      <td>12th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>99.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39166</th>\n",
       "      <td>39166</td>\n",
       "      <td>39166</td>\n",
       "      <td>Dawn Bell</td>\n",
       "      <td>F</td>\n",
       "      <td>10th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>95.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39167</th>\n",
       "      <td>39167</td>\n",
       "      <td>39167</td>\n",
       "      <td>Rebecca Tanner</td>\n",
       "      <td>F</td>\n",
       "      <td>9th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39168</th>\n",
       "      <td>39168</td>\n",
       "      <td>39168</td>\n",
       "      <td>Desiree Kidd</td>\n",
       "      <td>F</td>\n",
       "      <td>10th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>99.0</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39169</th>\n",
       "      <td>39169</td>\n",
       "      <td>39169</td>\n",
       "      <td>Carolyn Jackson</td>\n",
       "      <td>F</td>\n",
       "      <td>11th</td>\n",
       "      <td>Thomas High School</td>\n",
       "      <td>95.0</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Unnamed: 0  Student ID     student_name gender grade  \\\n",
       "39160       39160       39160     Katie Weaver      F  11th   \n",
       "39161       39161       39161      April Reyes      F  10th   \n",
       "39162       39162       39162      Derek Weeks      M  12th   \n",
       "39163       39163       39163       John Reese      M  11th   \n",
       "39164       39164       39164   Joseph Anthony      M   9th   \n",
       "39165       39165       39165     Donna Howard      F  12th   \n",
       "39166       39166       39166        Dawn Bell      F  10th   \n",
       "39167       39167       39167   Rebecca Tanner      F   9th   \n",
       "39168       39168       39168     Desiree Kidd      F  10th   \n",
       "39169       39169       39169  Carolyn Jackson      F  11th   \n",
       "\n",
       "              school_name  reading_score  math_score  \n",
       "39160  Thomas High School           89.0        86.0  \n",
       "39161  Thomas High School           70.0        84.0  \n",
       "39162  Thomas High School           94.0        77.0  \n",
       "39163  Thomas High School           90.0        75.0  \n",
       "39164  Thomas High School            NaN         NaN  \n",
       "39165  Thomas High School           99.0        90.0  \n",
       "39166  Thomas High School           95.0        70.0  \n",
       "39167  Thomas High School            NaN         NaN  \n",
       "39168  Thomas High School           99.0        90.0  \n",
       "39169  Thomas High School           95.0        75.0  "
      ]
     },
     "execution_count": 2045,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  Step 4. Check the student data for NaN's. \n",
    "\n",
    "#alternately  loc() can be used for displaying data\n",
    "check_nan=student_data_df.loc[(student_data_df[\"grade\"]== \"9th\") & (student_data_df[\"school_name\"]== \"Thomas High School\")]\n",
    "\n",
    "\n",
    "student_data_df.tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2046,
   "id": "a292f049",
   "metadata": {
    "scrolled": true
   },
   "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>Unnamed: 0</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>0</td>\n",
       "      <td>Paul Bradley</td>\n",
       "      <td>M</td>\n",
       "      <td>9th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>66.0</td>\n",
       "      <td>79.0</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>1</td>\n",
       "      <td>Victor Smith</td>\n",
       "      <td>M</td>\n",
       "      <td>12th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>94.0</td>\n",
       "      <td>61.0</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>2</td>\n",
       "      <td>Kevin Rodriguez</td>\n",
       "      <td>M</td>\n",
       "      <td>12th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>90.0</td>\n",
       "      <td>60.0</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>3</td>\n",
       "      <td>Richard Scott</td>\n",
       "      <td>M</td>\n",
       "      <td>12th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>67.0</td>\n",
       "      <td>58.0</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>4</td>\n",
       "      <td>Bonnie Ray</td>\n",
       "      <td>F</td>\n",
       "      <td>9th</td>\n",
       "      <td>Huang High School</td>\n",
       "      <td>97.0</td>\n",
       "      <td>84.0</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": [
       "   Unnamed: 0  Student ID     student_name gender grade        school_name  \\\n",
       "0           0           0     Paul Bradley      M   9th  Huang High School   \n",
       "1           1           1     Victor Smith      M  12th  Huang High School   \n",
       "2           2           2  Kevin Rodriguez      M  12th  Huang High School   \n",
       "3           3           3    Richard Scott      M  12th  Huang High School   \n",
       "4           4           4       Bonnie Ray      F   9th  Huang High School   \n",
       "\n",
       "   reading_score  math_score  School ID      type  size   budget  \n",
       "0           66.0        79.0          0  District  2917  1910635  \n",
       "1           94.0        61.0          0  District  2917  1910635  \n",
       "2           90.0        60.0          0  District  2917  1910635  \n",
       "3           67.0        58.0          0  District  2917  1910635  \n",
       "4           97.0        84.0          0  District  2917  1910635  "
      ]
     },
     "execution_count": 2046,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Deliverable 2 : Repeat the school district analysis¶\n",
    "#District Summary\n",
    "\n",
    "# Combine the data into a single dataset\n",
    "school_data_complete_df = pd.merge(student_data_df, school_data_df, how=\"left\", on=[\"school_name\", \"school_name\"])\n",
    "school_data_complete_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2047,
   "id": "e2ec2197",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate the Totals (Schools and Students)\n",
    "school_count = len(school_data_complete_df[\"school_name\"].unique())\n",
    "student_count = school_data_complete_df[\"Student ID\"].count()\n",
    "#print(school_count)\n",
    "#student_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2048,
   "id": "233199a7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24649428"
      ]
     },
     "execution_count": 2048,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate the Total Budget\n",
    "total_budget = school_data_df[\"budget\"].sum()\n",
    "total_budget"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2049,
   "id": "1c8e1f4b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "81.85579580976001"
      ]
     },
     "execution_count": 2049,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate the Average Scores using the \"clean_student_data\".\n",
    "#mean for math\n",
    "average_reading_score = school_data_complete_df[\"reading_score\"].mean()\n",
    "average_reading_score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2050,
   "id": "25ff7047",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "78.93053295099331"
      ]
     },
     "execution_count": 2050,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#mean for reading\n",
    "average_math_score = school_data_complete_df[\"math_score\"].mean()\n",
    "average_math_score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2051,
   "id": "c279683b",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "# Step 1. Get the number of students that are in ninth grade at Thomas High School.\n",
    "# These students have no grades. \n",
    "ninth_grade_std = student_data_df[(student_data_df[\"grade\"]== \"9th\") \n",
    "                                     & \n",
    "                                     (student_data_df[\"school_name\"]== \"Thomas High School\")]\n",
    "\n",
    "ninth_grad_thomas_school_cnt = ninth_grade_std[\"Student ID\"].count()\n",
    "#print(\"The number of students that are in ninth grade at Thomas High School and do not have grades is \",ninth_grad_thomas_school_cnt)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2052,
   "id": "b3913f3b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "39170"
      ]
     },
     "execution_count": 2052,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the total student count \n",
    "student_count = school_data_complete_df[\"Student ID\"].count() \n",
    "student_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2053,
   "id": "4f265db9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "38709"
      ]
     },
     "execution_count": 2053,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Step 2. Subtract the number of students that are in ninth grade at \n",
    "# Thomas High School from the total student count to get the new total student count.\n",
    "new_total_std_count = student_count - ninth_grad_thomas_school_cnt\n",
    "new_total_std_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2054,
   "id": "71698127",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "passing_math_count :  28939\n",
      "passing_reading_count :  33158\n"
     ]
    }
   ],
   "source": [
    "# Calculate the passing rates using the \"clean_student_data\".\n",
    "passing_math_count = school_data_complete_df[(school_data_complete_df[\"math_score\"] >= 70)].count()[\"student_name\"]\n",
    "print(\"passing_math_count : \",passing_math_count)\n",
    "\n",
    "passing_reading_count = school_data_complete_df[(school_data_complete_df[\"reading_score\"] >= 70)].count()[\"student_name\"]\n",
    "print(\"passing_reading_count : \",passing_reading_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2055,
   "id": "8512f2a2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "passing_math_percentage :  74.76039164018704\n",
      "passing_reading_percentage :  85.6596657108166\n"
     ]
    }
   ],
   "source": [
    "# Step 3. Calculate the passing percentages with the new total student count.\n",
    "\n",
    "# Calculate the percent that passed math.\n",
    "passing_math_percentage = passing_math_count /float (new_total_std_count) * 100\n",
    "\n",
    "\n",
    "\n",
    "# Calculate the percent that passed reading.\n",
    "passing_reading_percentage = passing_reading_count /float(new_total_std_count) * 100\n",
    "\n",
    "\n",
    "print(\"passing_math_percentage : \",passing_math_percentage)\n",
    "\n",
    "print(\"passing_reading_percentage : \",passing_reading_percentage)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2056,
   "id": "4be25f8b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "overall_passing_percentage :  64.85571830840374\n"
     ]
    }
   ],
   "source": [
    "# Calculate the students who passed both reading and math.\n",
    "passing_math_reading = school_data_complete_df[(school_data_complete_df[\"math_score\"] >= 70)\n",
    "                                               & (school_data_complete_df[\"reading_score\"] >= 70)]\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# Calculate the number of students that passed both reading and math.\n",
    "overall_passing_math_reading_count = passing_math_reading[\"student_name\"].count()\n",
    "\n",
    "\n",
    "# Step 4.Calculate the overall passing percentage with new total student count.\n",
    "\n",
    "overall_passing_percentage = overall_passing_math_reading_count / float(new_total_std_count) * 100\n",
    "print(\"overall_passing_percentage : \",overall_passing_percentage)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2057,
   "id": "98d11ff8",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Total Schools</th>\n",
       "      <th>Total Students</th>\n",
       "      <th>Total Budget</th>\n",
       "      <th>Average Math Score</th>\n",
       "      <th>Average Reading Score</th>\n",
       "      <th>% Passing Math</th>\n",
       "      <th>% Passing Reading</th>\n",
       "      <th>% Overall Passing</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15</td>\n",
       "      <td>39,170</td>\n",
       "      <td>$24,649,428.00</td>\n",
       "      <td>78.9</td>\n",
       "      <td>81.9</td>\n",
       "      <td>74.8</td>\n",
       "      <td>85.7</td>\n",
       "      <td>64.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Total Schools Total Students    Total Budget Average Math Score  \\\n",
       "0             15         39,170  $24,649,428.00               78.9   \n",
       "\n",
       "  Average Reading Score % Passing Math % Passing Reading % Overall Passing  \n",
       "0                  81.9           74.8              85.7              64.9  "
      ]
     },
     "execution_count": 2057,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a DataFrame\n",
    "district_summary_df = pd.DataFrame(\n",
    "          [{\"Total Schools\": school_count, \n",
    "          \"Total Students\": student_count, \n",
    "          \"Total Budget\": total_budget,\n",
    "          \"Average Math Score\": average_math_score, \n",
    "          \"Average Reading Score\": average_reading_score,\n",
    "          \"% Passing Math\": passing_math_percentage,\n",
    "         \"% Passing Reading\": passing_reading_percentage,\n",
    "        \"% Overall Passing\": overall_passing_percentage}])\n",
    "\n",
    "\n",
    "\n",
    "# Format the \"Total Students\" to have the comma for a thousands separator.\n",
    "district_summary_df[\"Total Students\"] = district_summary_df[\"Total Students\"].map(\"{:,}\".format)\n",
    "# Format the \"Total Budget\" to have the comma for a thousands separator, a decimal separator and a \"$\".\n",
    "district_summary_df[\"Total Budget\"] = district_summary_df[\"Total Budget\"].map(\"${:,.2f}\".format)\n",
    "# Format the columns.\n",
    "district_summary_df[\"Average Math Score\"] = district_summary_df[\"Average Math Score\"].map(\"{:.1f}\".format)\n",
    "district_summary_df[\"Average Reading Score\"] = district_summary_df[\"Average Reading Score\"].map(\"{:.1f}\".format)\n",
    "district_summary_df[\"% Passing Math\"] = district_summary_df[\"% Passing Math\"].map(\"{:.1f}\".format)\n",
    "district_summary_df[\"% Passing Reading\"] = district_summary_df[\"% Passing Reading\"].map(\"{:.1f}\".format)\n",
    "district_summary_df[\"% Overall Passing\"] = district_summary_df[\"% Overall Passing\"].map(\"{:.1f}\".format)\n",
    "\n",
    "# Display the data frame\n",
    "district_summary_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2058,
   "id": "6ecf85c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#School Summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2059,
   "id": "ac159d3e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Determine the School Type\n",
    "per_school_types = school_data_df.set_index([\"school_name\"])[\"type\"]\n",
    "\n",
    "# Calculate the total student count.\n",
    "per_school_counts = school_data_complete_df[\"school_name\"].value_counts()\n",
    "\n",
    "# Calculate the total school budget and per capita spending\n",
    "per_school_budget = school_data_complete_df.groupby([\"school_name\"]).mean()[\"budget\"]\n",
    "# Calculate the per capita spending.\n",
    "per_school_capita = per_school_budget / per_school_counts\n",
    "\n",
    "# Calculate the average test scores.\n",
    "per_school_math = school_data_complete_df.groupby([\"school_name\"]).mean()[\"math_score\"]\n",
    "per_school_reading = school_data_complete_df.groupby([\"school_name\"]).mean()[\"reading_score\"]\n",
    "\n",
    "# Calculate the passing scores by creating a filtered DataFrame.\n",
    "per_school_passing_math = school_data_complete_df[(school_data_complete_df[\"math_score\"] >= 70)]\n",
    "per_school_passing_reading = school_data_complete_df[(school_data_complete_df[\"reading_score\"] >= 70)]\n",
    "\n",
    "# Calculate the number of students passing math and passing reading by school.\n",
    "per_school_passing_math = per_school_passing_math.groupby([\"school_name\"]).count()[\"student_name\"]\n",
    "per_school_passing_reading = per_school_passing_reading.groupby([\"school_name\"]).count()[\"student_name\"]\n",
    "\n",
    "# Calculate the percentage of passing math and reading scores per school.\n",
    "per_school_passing_math = per_school_passing_math / per_school_counts * 100\n",
    "per_school_passing_reading = per_school_passing_reading / per_school_counts * 100\n",
    "\n",
    "# Calculate the students who passed both reading and math.\n",
    "per_passing_math_reading = school_data_complete_df[(school_data_complete_df[\"reading_score\"] >= 70)\n",
    "                                               & (school_data_complete_df[\"math_score\"] >= 70)]\n",
    "\n",
    "# Calculate the number of students passing math and passing reading by school.\n",
    "per_passing_math_reading = per_passing_math_reading.groupby([\"school_name\"]).count()[\"student_name\"]\n",
    "\n",
    "# Calculate the percentage of passing math and reading scores per school.\n",
    "per_overall_passing_percentage = per_passing_math_reading / per_school_counts * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2060,
   "id": "c02c4bb2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>School Type</th>\n",
       "      <th>Total Students</th>\n",
       "      <th>Total School Budget</th>\n",
       "      <th>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</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>3124928.0</td>\n",
       "      <td>628.0</td>\n",
       "      <td>77.048432</td>\n",
       "      <td>81.033963</td>\n",
       "      <td>66.680064</td>\n",
       "      <td>81.933280</td>\n",
       "      <td>54.642283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cabrera High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>1858</td>\n",
       "      <td>1081356.0</td>\n",
       "      <td>582.0</td>\n",
       "      <td>83.061895</td>\n",
       "      <td>83.975780</td>\n",
       "      <td>94.133477</td>\n",
       "      <td>97.039828</td>\n",
       "      <td>91.334769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Figueroa High School</th>\n",
       "      <td>District</td>\n",
       "      <td>2949</td>\n",
       "      <td>1884411.0</td>\n",
       "      <td>639.0</td>\n",
       "      <td>76.711767</td>\n",
       "      <td>81.158020</td>\n",
       "      <td>65.988471</td>\n",
       "      <td>80.739234</td>\n",
       "      <td>53.204476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ford High School</th>\n",
       "      <td>District</td>\n",
       "      <td>2739</td>\n",
       "      <td>1763916.0</td>\n",
       "      <td>644.0</td>\n",
       "      <td>77.102592</td>\n",
       "      <td>80.746258</td>\n",
       "      <td>68.309602</td>\n",
       "      <td>79.299014</td>\n",
       "      <td>54.289887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Griffin High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>1468</td>\n",
       "      <td>917500.0</td>\n",
       "      <td>625.0</td>\n",
       "      <td>83.351499</td>\n",
       "      <td>83.816757</td>\n",
       "      <td>93.392371</td>\n",
       "      <td>97.138965</td>\n",
       "      <td>90.599455</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     School Type  Total Students  Total School Budget  \\\n",
       "Bailey High School      District            4976            3124928.0   \n",
       "Cabrera High School      Charter            1858            1081356.0   \n",
       "Figueroa High School    District            2949            1884411.0   \n",
       "Ford High School        District            2739            1763916.0   \n",
       "Griffin High School      Charter            1468             917500.0   \n",
       "\n",
       "                      Per Student Budget  Average Math Score  \\\n",
       "Bailey High School                 628.0           77.048432   \n",
       "Cabrera High School                582.0           83.061895   \n",
       "Figueroa High School               639.0           76.711767   \n",
       "Ford High School                   644.0           77.102592   \n",
       "Griffin High School                625.0           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  \n",
       "Bailey High School            81.933280          54.642283  \n",
       "Cabrera High School           97.039828          91.334769  \n",
       "Figueroa High School          80.739234          53.204476  \n",
       "Ford High School              79.299014          54.289887  \n",
       "Griffin High School           97.138965          90.599455  "
      ]
     },
     "execution_count": 2060,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create the DataFrame\n",
    "\n",
    "per_school_summary_df = pd.DataFrame({\n",
    "    \"School Type\": per_school_types,\n",
    "    \"Total Students\": per_school_counts,\n",
    "    \"Total School Budget\": per_school_budget,\n",
    "    \"Per Student Budget\": per_school_capita,\n",
    "    \"Average Math Score\": per_school_math,\n",
    "    \"Average Reading Score\": per_school_reading,\n",
    "    \"% Passing Math\": per_school_passing_math,\n",
    "    \"% Passing Reading\": per_school_passing_reading,\n",
    "    \"% Overall Passing\": per_overall_passing_percentage})\n",
    "\n",
    "per_school_summary_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2061,
   "id": "37c497c1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>School Type</th>\n",
       "      <th>Total Students</th>\n",
       "      <th>Total School Budget</th>\n",
       "      <th>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</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\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.350937</td>\n",
       "      <td>83.896082</td>\n",
       "      <td>66.911315</td>\n",
       "      <td>69.663609</td>\n",
       "      <td>65.076453</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   School Type  Total Students Total School Budget  \\\n",
       "Thomas High School     Charter            1635       $1,043,130.00   \n",
       "\n",
       "                   Per Student Budget  Average Math Score  \\\n",
       "Thomas High School            $638.00           83.350937   \n",
       "\n",
       "                    Average Reading Score  % Passing Math  % Passing Reading  \\\n",
       "Thomas High School              83.896082       66.911315          69.663609   \n",
       "\n",
       "                    % Overall Passing  \n",
       "Thomas High School          65.076453  "
      ]
     },
     "execution_count": 2061,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Format the Total School Budget and the Per Student Budget\n",
    "per_school_summary_df[\"Total School Budget\"] = per_school_summary_df[\"Total School Budget\"].map(\"${:,.2f}\".format)\n",
    "per_school_summary_df[\"Per Student Budget\"] = per_school_summary_df[\"Per Student Budget\"].map(\"${:,.2f}\".format)\n",
    "\n",
    "#Create a replica of the working dataframe.\n",
    "per_school_summary_df_copy=per_school_summary_df.copy()\n",
    "# Display the data frame\n",
    "per_school_summary_df.loc[[\"Thomas High School\"]]\n",
    "#per_school_summary_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2062,
   "id": "59e879f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Student ID    1174\n",
       "dtype: int64"
      ]
     },
     "execution_count": 2062,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Step 5.  Get the number of 10th-12th graders from Thomas High School (THS).\n",
    "\n",
    "\n",
    "ths_student_count = school_data_complete_df.loc[(school_data_complete_df[\"school_name\"] == \"Thomas High School\") & (school_data_complete_df[\"grade\"] != \"9th\"), [\"Student ID\"]].count()\n",
    "ths_student_count\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2063,
   "id": "fa18198c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1094"
      ]
     },
     "execution_count": 2063,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Step 6. Get all the students passing math from THS\n",
    "\n",
    "\n",
    "THS_math_pass_score = school_data_complete_df.loc[(school_data_complete_df[\"school_name\"]==\"Thomas High School\")&(school_data_complete_df[\"math_score\"]>=70)].count()[\"student_name\"]\n",
    "THS_math_pass_score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2064,
   "id": "9502fca8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1139"
      ]
     },
     "execution_count": 2064,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Step 7. Get all the students passing reading from THS\n",
    "THS_read_pass_score = school_data_complete_df.loc[(school_data_complete_df[\"school_name\"] == \"Thomas High School\") &(school_data_complete_df[\"reading_score\"] >= 70)].count()[\"student_name\"]\n",
    "THS_read_pass_score\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2065,
   "id": "5a22ce73",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1064"
      ]
     },
     "execution_count": 2065,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Step 8. Get all the students passing math and reading from THS\n",
    "THS_math_read_pass_score = school_data_complete_df.loc[(school_data_complete_df[\"school_name\"] == \"Thomas High School\") &(school_data_complete_df[\"reading_score\"] >= 70) & (school_data_complete_df[\"math_score\"] >= 70)].count()[\"student_name\"]\n",
    "THS_math_read_pass_score\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2066,
   "id": "d00e5e74",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "93.18568994889267"
      ]
     },
     "execution_count": 2066,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Step 9. Calculate the percentage of 10th-12th grade students passing math from Thomas High School. \n",
    "ths_math_pass_percentage = THS_math_pass_score /float (ths_student_count) * 100\n",
    "ths_math_pass_percentage\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2067,
   "id": "79ce74bc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "97.01873935264055"
      ]
     },
     "execution_count": 2067,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Step 10. Calculate the percentage of 10th-12th grade students passing reading from Thomas High School.\n",
    "ths_reading_pass_percentage = THS_read_pass_score / float(ths_student_count) * 100\n",
    "ths_reading_pass_percentage\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2068,
   "id": "16e484d5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "90.63032367972743"
      ]
     },
     "execution_count": 2068,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Step 11. Calculate the overall passing percentage of 10th-12th grade from Thomas High School. \n",
    "overall_passing_percentage = THS_math_read_pass_score /float (ths_student_count) * 100\n",
    "overall_passing_percentage\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2069,
   "id": "bc128d12",
   "metadata": {},
   "outputs": [],
   "source": [
    "#per_school_summary_df.loc[\"Thomas High School\", \"% Passing Math\"] = ths_math_pass_percentage\n",
    "per_school_summary_df.loc[[\"Thomas High School\"],[\"% Passing Math\"]]=ths_math_pass_percentage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2070,
   "id": "d59a0e82",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Step 13. Replace the passing reading percentage for Thomas High School in the per_school_summary_df.\n",
    "per_school_summary_df.loc[[\"Thomas High School\"], [\"% Passing Reading\"]] = ths_reading_pass_percentage\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2071,
   "id": "f85264aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Step 14. Replace the overall passing percentage for Thomas High School in the per_school_summary_df.\n",
    "per_school_summary_df.loc[[\"Thomas High School\"],[\"% Overall Passing\"]] = overall_passing_percentage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2072,
   "id": "7b1455a3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>School Type</th>\n",
       "      <th>Total Students</th>\n",
       "      <th>Total School Budget</th>\n",
       "      <th>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</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\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.350937</td>\n",
       "      <td>83.896082</td>\n",
       "      <td>93.18569</td>\n",
       "      <td>97.018739</td>\n",
       "      <td>90.630324</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   School Type  Total Students Total School Budget  \\\n",
       "Thomas High School     Charter            1635       $1,043,130.00   \n",
       "\n",
       "                   Per Student Budget  Average Math Score  \\\n",
       "Thomas High School            $638.00           83.350937   \n",
       "\n",
       "                    Average Reading Score  % Passing Math  % Passing Reading  \\\n",
       "Thomas High School              83.896082        93.18569          97.018739   \n",
       "\n",
       "                    % Overall Passing  \n",
       "Thomas High School          90.630324  "
      ]
     },
     "execution_count": 2072,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "per_school_summary_df.loc[[\"Thomas High School\"]]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2073,
   "id": "b858675f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>School Type</th>\n",
       "      <th>Total Students</th>\n",
       "      <th>Total School Budget</th>\n",
       "      <th>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</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>91.334769</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.350937</td>\n",
       "      <td>83.896082</td>\n",
       "      <td>93.185690</td>\n",
       "      <td>97.018739</td>\n",
       "      <td>90.630324</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>90.599455</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>90.582567</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>90.540541</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",
       "Griffin High School     Charter            1468         $917,500.00   \n",
       "Wilson High School      Charter            2283       $1,319,574.00   \n",
       "Pena High School        Charter             962         $585,858.00   \n",
       "\n",
       "                    Per Student Budget  Average Math Score  \\\n",
       "Cabrera High School            $582.00           83.061895   \n",
       "Thomas High School             $638.00           83.350937   \n",
       "Griffin High School            $625.00           83.351499   \n",
       "Wilson High School             $578.00           83.274201   \n",
       "Pena High School               $609.00           83.839917   \n",
       "\n",
       "                     Average Reading Score  % Passing Math  % Passing Reading  \\\n",
       "Cabrera High School              83.975780       94.133477          97.039828   \n",
       "Thomas High School               83.896082       93.185690          97.018739   \n",
       "Griffin High School              83.816757       93.392371          97.138965   \n",
       "Wilson High School               83.989488       93.867718          96.539641   \n",
       "Pena High School                 84.044699       94.594595          95.945946   \n",
       "\n",
       "                     % Overall Passing  \n",
       "Cabrera High School          91.334769  \n",
       "Thomas High School           90.630324  \n",
       "Griffin High School          90.599455  \n",
       "Wilson High School           90.582567  \n",
       "Pena High School             90.540541  "
      ]
     },
     "execution_count": 2073,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#High and Low Performing Schools calculating code follows:\n",
    "\n",
    "# Sort and show top five schools.\n",
    "top_schools = per_school_summary_df.sort_values([\"% Overall Passing\"],ascending=False)\n",
    "top_schools.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2074,
   "id": "88b2024c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>School Type</th>\n",
       "      <th>Total Students</th>\n",
       "      <th>Total School Budget</th>\n",
       "      <th>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</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>52.988247</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>53.204476</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>53.513884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hernandez High School</th>\n",
       "      <td>District</td>\n",
       "      <td>4635</td>\n",
       "      <td>$3,022,020.00</td>\n",
       "      <td>$652.00</td>\n",
       "      <td>77.289752</td>\n",
       "      <td>80.934412</td>\n",
       "      <td>66.752967</td>\n",
       "      <td>80.862999</td>\n",
       "      <td>53.527508</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>53.539172</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",
       "Hernandez High School    District            4635       $3,022,020.00   \n",
       "Johnson High School      District            4761       $3,094,650.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",
       "Hernandez High School            $652.00           77.289752   \n",
       "Johnson High School              $650.00           77.072464   \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",
       "Hernandez High School              80.934412       66.752967   \n",
       "Johnson High School                80.966394       66.057551   \n",
       "\n",
       "                       % Passing Reading  % Overall Passing  \n",
       "Rodriguez High School          80.220055          52.988247  \n",
       "Figueroa High School           80.739234          53.204476  \n",
       "Huang High School              81.316421          53.513884  \n",
       "Hernandez High School          80.862999          53.527508  \n",
       "Johnson High School            81.222432          53.539172  "
      ]
     },
     "execution_count": 2074,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sort and show bottom five schools.\n",
    "bottom_schools = per_school_summary_df.sort_values([\"% Overall Passing\"],ascending=True)\n",
    "bottom_schools.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2075,
   "id": "7571b4d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Math and Reading Scores by Grade\n",
    "\n",
    "\n",
    "# Create a Series of scores by grade levels using conditionals.\n",
    "\n",
    "ninth_graders = school_data_complete_df[(school_data_complete_df[\"grade\"] == \"9th\")]\n",
    "\n",
    "tenth_graders = school_data_complete_df[(school_data_complete_df[\"grade\"] == \"10th\")]\n",
    "\n",
    "eleventh_graders = school_data_complete_df[(school_data_complete_df[\"grade\"] == \"11th\")]\n",
    "\n",
    "twelfth_graders = school_data_complete_df[(school_data_complete_df[\"grade\"] == \"12th\")]\n",
    "\n",
    "\n",
    "\n",
    "# Group each school Series by the school name for the average math score.\n",
    "\n",
    "ninth_grade_math_scores = ninth_graders.groupby([\"school_name\"]).mean()[\"math_score\"]\n",
    "\n",
    "tenth_grade_math_scores = tenth_graders.groupby([\"school_name\"]).mean()[\"math_score\"]\n",
    "\n",
    "eleventh_grade_math_scores = eleventh_graders.groupby([\"school_name\"]).mean()[\"math_score\"]\n",
    "\n",
    "twelfth_grade_math_scores = twelfth_graders.groupby([\"school_name\"]).mean()[\"math_score\"]\n",
    "\n",
    "\n",
    "# Group each school Series by the school name for the average reading score.\n",
    "\n",
    "ninth_grade_reading_scores = ninth_graders.groupby([\"school_name\"]).mean()[\"reading_score\"]\n",
    "\n",
    "tenth_grade_reading_scores = tenth_graders.groupby([\"school_name\"]).mean()[\"reading_score\"]\n",
    "\n",
    "eleventh_grade_reading_scores = eleventh_graders.groupby([\"school_name\"]).mean()[\"reading_score\"]\n",
    "\n",
    "twelfth_grade_reading_scores = twelfth_graders.groupby([\"school_name\"]).mean()[\"reading_score\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2076,
   "id": "bc021318",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Combine each Series for average math scores by school into single data frame.\n",
    "\n",
    "math_scores_by_grade = pd.DataFrame({\n",
    "              \"9th\": ninth_grade_math_scores,\n",
    "              \"10th\": tenth_grade_math_scores,\n",
    "              \"11th\": eleventh_grade_math_scores,\n",
    "              \"12th\": twelfth_grade_math_scores})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2077,
   "id": "5284a8bc",
   "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>9th</th>\n",
       "      <th>10th</th>\n",
       "      <th>11th</th>\n",
       "      <th>12th</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>school_name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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",
       "school_name                                                     \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": 2077,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Combine each Series for average reading scores by school into single data frame.\n",
    "\n",
    "reading_scores_by_grade = pd.DataFrame({\n",
    "              \"9th\": ninth_grade_reading_scores,\n",
    "              \"10th\": tenth_grade_reading_scores,\n",
    "              \"11th\": eleventh_grade_reading_scores,\n",
    "              \"12th\": twelfth_grade_reading_scores})\n",
    "\n",
    "reading_scores_by_grade.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2078,
   "id": "e20c93b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Format each grade column.\n",
    "math_scores_by_grade[\"9th\"] = math_scores_by_grade[\"9th\"].map(\"{: .1f}\".format)\n",
    "\n",
    "math_scores_by_grade[\"10th\"] = math_scores_by_grade[\"10th\"].map(\"{: .1f}\".format)\n",
    "\n",
    "math_scores_by_grade[\"11th\"] = math_scores_by_grade[\"11th\"].map(\"{: .1f}\".format)\n",
    "\n",
    "math_scores_by_grade[\"12th\"] = math_scores_by_grade[\"12th\"].map(\"{: .1f}\".format)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2079,
   "id": "d9a362d1",
   "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>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.1</td>\n",
       "      <td>77.0</td>\n",
       "      <td>77.5</td>\n",
       "      <td>76.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cabrera High School</th>\n",
       "      <td>83.1</td>\n",
       "      <td>83.2</td>\n",
       "      <td>82.8</td>\n",
       "      <td>83.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Figueroa High School</th>\n",
       "      <td>76.4</td>\n",
       "      <td>76.5</td>\n",
       "      <td>76.9</td>\n",
       "      <td>77.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ford High School</th>\n",
       "      <td>77.4</td>\n",
       "      <td>77.7</td>\n",
       "      <td>76.9</td>\n",
       "      <td>76.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Griffin High School</th>\n",
       "      <td>82.0</td>\n",
       "      <td>84.2</td>\n",
       "      <td>83.8</td>\n",
       "      <td>83.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hernandez High School</th>\n",
       "      <td>77.4</td>\n",
       "      <td>77.3</td>\n",
       "      <td>77.1</td>\n",
       "      <td>77.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Holden High School</th>\n",
       "      <td>83.8</td>\n",
       "      <td>83.4</td>\n",
       "      <td>85.0</td>\n",
       "      <td>82.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Huang High School</th>\n",
       "      <td>77.0</td>\n",
       "      <td>75.9</td>\n",
       "      <td>76.4</td>\n",
       "      <td>77.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Johnson High School</th>\n",
       "      <td>77.2</td>\n",
       "      <td>76.7</td>\n",
       "      <td>77.5</td>\n",
       "      <td>76.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pena High School</th>\n",
       "      <td>83.6</td>\n",
       "      <td>83.4</td>\n",
       "      <td>84.3</td>\n",
       "      <td>84.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rodriguez High School</th>\n",
       "      <td>76.9</td>\n",
       "      <td>76.6</td>\n",
       "      <td>76.4</td>\n",
       "      <td>77.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shelton High School</th>\n",
       "      <td>83.4</td>\n",
       "      <td>82.9</td>\n",
       "      <td>83.4</td>\n",
       "      <td>83.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thomas High School</th>\n",
       "      <td>nan</td>\n",
       "      <td>83.1</td>\n",
       "      <td>83.5</td>\n",
       "      <td>83.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wilson High School</th>\n",
       "      <td>83.1</td>\n",
       "      <td>83.7</td>\n",
       "      <td>83.2</td>\n",
       "      <td>83.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wright High School</th>\n",
       "      <td>83.3</td>\n",
       "      <td>84.0</td>\n",
       "      <td>83.8</td>\n",
       "      <td>83.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         9th   10th   11th   12th\n",
       "Bailey High School      77.1   77.0   77.5   76.5\n",
       "Cabrera High School     83.1   83.2   82.8   83.3\n",
       "Figueroa High School    76.4   76.5   76.9   77.2\n",
       "Ford High School        77.4   77.7   76.9   76.2\n",
       "Griffin High School     82.0   84.2   83.8   83.4\n",
       "Hernandez High School   77.4   77.3   77.1   77.2\n",
       "Holden High School      83.8   83.4   85.0   82.9\n",
       "Huang High School       77.0   75.9   76.4   77.2\n",
       "Johnson High School     77.2   76.7   77.5   76.9\n",
       "Pena High School        83.6   83.4   84.3   84.1\n",
       "Rodriguez High School   76.9   76.6   76.4   77.7\n",
       "Shelton High School     83.4   82.9   83.4   83.8\n",
       "Thomas High School       nan   83.1   83.5   83.5\n",
       "Wilson High School      83.1   83.7   83.2   83.0\n",
       "Wright High School      83.3   84.0   83.8   83.6"
      ]
     },
     "execution_count": 2079,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remove the index.\n",
    "\n",
    "math_scores_by_grade.index.name = None\n",
    "# Display the data frame\n",
    "math_scores_by_grade"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2080,
   "id": "66e3dde3",
   "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>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.3</td>\n",
       "      <td>80.9</td>\n",
       "      <td>80.9</td>\n",
       "      <td>80.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cabrera High School</th>\n",
       "      <td>83.7</td>\n",
       "      <td>84.3</td>\n",
       "      <td>83.8</td>\n",
       "      <td>84.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Figueroa High School</th>\n",
       "      <td>81.2</td>\n",
       "      <td>81.4</td>\n",
       "      <td>80.6</td>\n",
       "      <td>81.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ford High School</th>\n",
       "      <td>80.6</td>\n",
       "      <td>81.3</td>\n",
       "      <td>80.4</td>\n",
       "      <td>80.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Griffin High School</th>\n",
       "      <td>83.4</td>\n",
       "      <td>83.7</td>\n",
       "      <td>84.3</td>\n",
       "      <td>84.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hernandez High School</th>\n",
       "      <td>80.9</td>\n",
       "      <td>80.7</td>\n",
       "      <td>81.4</td>\n",
       "      <td>80.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Holden High School</th>\n",
       "      <td>83.7</td>\n",
       "      <td>83.3</td>\n",
       "      <td>83.8</td>\n",
       "      <td>84.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Huang High School</th>\n",
       "      <td>81.3</td>\n",
       "      <td>81.5</td>\n",
       "      <td>81.4</td>\n",
       "      <td>80.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Johnson High School</th>\n",
       "      <td>81.3</td>\n",
       "      <td>80.8</td>\n",
       "      <td>80.6</td>\n",
       "      <td>81.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pena High School</th>\n",
       "      <td>83.8</td>\n",
       "      <td>83.6</td>\n",
       "      <td>84.3</td>\n",
       "      <td>84.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rodriguez High School</th>\n",
       "      <td>81.0</td>\n",
       "      <td>80.6</td>\n",
       "      <td>80.9</td>\n",
       "      <td>80.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shelton High School</th>\n",
       "      <td>84.1</td>\n",
       "      <td>83.4</td>\n",
       "      <td>84.4</td>\n",
       "      <td>82.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thomas High School</th>\n",
       "      <td>nan</td>\n",
       "      <td>84.3</td>\n",
       "      <td>83.6</td>\n",
       "      <td>83.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wilson High School</th>\n",
       "      <td>83.9</td>\n",
       "      <td>84.0</td>\n",
       "      <td>83.8</td>\n",
       "      <td>84.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wright High School</th>\n",
       "      <td>83.8</td>\n",
       "      <td>83.8</td>\n",
       "      <td>84.2</td>\n",
       "      <td>84.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         9th   10th   11th   12th\n",
       "Bailey High School      81.3   80.9   80.9   80.9\n",
       "Cabrera High School     83.7   84.3   83.8   84.3\n",
       "Figueroa High School    81.2   81.4   80.6   81.4\n",
       "Ford High School        80.6   81.3   80.4   80.7\n",
       "Griffin High School     83.4   83.7   84.3   84.0\n",
       "Hernandez High School   80.9   80.7   81.4   80.9\n",
       "Holden High School      83.7   83.3   83.8   84.7\n",
       "Huang High School       81.3   81.5   81.4   80.3\n",
       "Johnson High School     81.3   80.8   80.6   81.2\n",
       "Pena High School        83.8   83.6   84.3   84.6\n",
       "Rodriguez High School   81.0   80.6   80.9   80.4\n",
       "Shelton High School     84.1   83.4   84.4   82.8\n",
       "Thomas High School       nan   84.3   83.6   83.8\n",
       "Wilson High School      83.9   84.0   83.8   84.3\n",
       "Wright High School      83.8   83.8   84.2   84.1"
      ]
     },
     "execution_count": 2080,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Format each grade column.\n",
    "\n",
    "reading_scores_by_grade[\"9th\"] = reading_scores_by_grade[\"9th\"].map(\"{: .1f}\".format)\n",
    "\n",
    "reading_scores_by_grade[\"10th\"] = reading_scores_by_grade[\"10th\"].map(\"{: .1f}\".format)\n",
    "\n",
    "reading_scores_by_grade[\"11th\"] = reading_scores_by_grade[\"11th\"].map(\"{: .1f}\".format)\n",
    "\n",
    "reading_scores_by_grade[\"12th\"] = reading_scores_by_grade[\"12th\"].map(\"{: .1f}\".format)\n",
    "\n",
    "# Remove the index.\n",
    "reading_scores_by_grade.index.name = None\n",
    "\n",
    "# Display the data frame\n",
    "reading_scores_by_grade\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2081,
   "id": "1072762b",
   "metadata": {},
   "outputs": [],
   "source": [
    "#per_school_capita.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2082,
   "id": "473ac9f7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>School Type</th>\n",
       "      <th>Total Students</th>\n",
       "      <th>Total School Budget</th>\n",
       "      <th>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</th>\n",
       "      <th>Spending Ranges (Per Student)</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>54.642283</td>\n",
       "      <td>$586-630</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>91.334769</td>\n",
       "      <td>&lt;$586</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>53.204476</td>\n",
       "      <td>$631-645</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>54.289887</td>\n",
       "      <td>$631-645</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>90.599455</td>\n",
       "      <td>$586-630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hernandez High School</th>\n",
       "      <td>District</td>\n",
       "      <td>4635</td>\n",
       "      <td>$3,022,020.00</td>\n",
       "      <td>$652.00</td>\n",
       "      <td>77.289752</td>\n",
       "      <td>80.934412</td>\n",
       "      <td>66.752967</td>\n",
       "      <td>80.862999</td>\n",
       "      <td>53.527508</td>\n",
       "      <td>$646-675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Holden High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>427</td>\n",
       "      <td>$248,087.00</td>\n",
       "      <td>$581.00</td>\n",
       "      <td>83.803279</td>\n",
       "      <td>83.814988</td>\n",
       "      <td>92.505855</td>\n",
       "      <td>96.252927</td>\n",
       "      <td>89.227166</td>\n",
       "      <td>&lt;$586</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>53.513884</td>\n",
       "      <td>$646-675</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>53.539172</td>\n",
       "      <td>$646-675</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>90.540541</td>\n",
       "      <td>$586-630</td>\n",
       "    </tr>\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>52.988247</td>\n",
       "      <td>$631-645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shelton High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>1761</td>\n",
       "      <td>$1,056,600.00</td>\n",
       "      <td>$600.00</td>\n",
       "      <td>83.359455</td>\n",
       "      <td>83.725724</td>\n",
       "      <td>93.867121</td>\n",
       "      <td>95.854628</td>\n",
       "      <td>89.892107</td>\n",
       "      <td>$586-630</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.350937</td>\n",
       "      <td>83.896082</td>\n",
       "      <td>66.911315</td>\n",
       "      <td>69.663609</td>\n",
       "      <td>65.076453</td>\n",
       "      <td>$631-645</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>90.582567</td>\n",
       "      <td>&lt;$586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wright High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>1800</td>\n",
       "      <td>$1,049,400.00</td>\n",
       "      <td>$583.00</td>\n",
       "      <td>83.682222</td>\n",
       "      <td>83.955000</td>\n",
       "      <td>93.333333</td>\n",
       "      <td>96.611111</td>\n",
       "      <td>90.333333</td>\n",
       "      <td>&lt;$586</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",
       "Hernandez High School    District            4635       $3,022,020.00   \n",
       "Holden High School        Charter             427         $248,087.00   \n",
       "Huang High School        District            2917       $1,910,635.00   \n",
       "Johnson High School      District            4761       $3,094,650.00   \n",
       "Pena High School          Charter             962         $585,858.00   \n",
       "Rodriguez High School    District            3999       $2,547,363.00   \n",
       "Shelton High School       Charter            1761       $1,056,600.00   \n",
       "Thomas High School        Charter            1635       $1,043,130.00   \n",
       "Wilson High School        Charter            2283       $1,319,574.00   \n",
       "Wright High School        Charter            1800       $1,049,400.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",
       "Hernandez High School            $652.00           77.289752   \n",
       "Holden High School               $581.00           83.803279   \n",
       "Huang High School                $655.00           76.629414   \n",
       "Johnson High School              $650.00           77.072464   \n",
       "Pena High School                 $609.00           83.839917   \n",
       "Rodriguez High School            $637.00           76.842711   \n",
       "Shelton High School              $600.00           83.359455   \n",
       "Thomas High School               $638.00           83.350937   \n",
       "Wilson High School               $578.00           83.274201   \n",
       "Wright High School               $583.00           83.682222   \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",
       "Hernandez High School              80.934412       66.752967   \n",
       "Holden High School                 83.814988       92.505855   \n",
       "Huang High School                  81.182722       65.683922   \n",
       "Johnson High School                80.966394       66.057551   \n",
       "Pena High School                   84.044699       94.594595   \n",
       "Rodriguez High School              80.744686       66.366592   \n",
       "Shelton High School                83.725724       93.867121   \n",
       "Thomas High School                 83.896082       66.911315   \n",
       "Wilson High School                 83.989488       93.867718   \n",
       "Wright High School                 83.955000       93.333333   \n",
       "\n",
       "                       % Passing Reading  % Overall Passing  \\\n",
       "Bailey High School             81.933280          54.642283   \n",
       "Cabrera High School            97.039828          91.334769   \n",
       "Figueroa High School           80.739234          53.204476   \n",
       "Ford High School               79.299014          54.289887   \n",
       "Griffin High School            97.138965          90.599455   \n",
       "Hernandez High School          80.862999          53.527508   \n",
       "Holden High School             96.252927          89.227166   \n",
       "Huang High School              81.316421          53.513884   \n",
       "Johnson High School            81.222432          53.539172   \n",
       "Pena High School               95.945946          90.540541   \n",
       "Rodriguez High School          80.220055          52.988247   \n",
       "Shelton High School            95.854628          89.892107   \n",
       "Thomas High School             69.663609          65.076453   \n",
       "Wilson High School             96.539641          90.582567   \n",
       "Wright High School             96.611111          90.333333   \n",
       "\n",
       "                      Spending Ranges (Per Student)  \n",
       "Bailey High School                         $586-630  \n",
       "Cabrera High School                           <$586  \n",
       "Figueroa High School                       $631-645  \n",
       "Ford High School                           $631-645  \n",
       "Griffin High School                        $586-630  \n",
       "Hernandez High School                      $646-675  \n",
       "Holden High School                            <$586  \n",
       "Huang High School                          $646-675  \n",
       "Johnson High School                        $646-675  \n",
       "Pena High School                           $586-630  \n",
       "Rodriguez High School                      $631-645  \n",
       "Shelton High School                        $586-630  \n",
       "Thomas High School                         $631-645  \n",
       "Wilson High School                            <$586  \n",
       "Wright High School                            <$586  "
      ]
     },
     "execution_count": 2082,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Scores by School Spending code follows:\n",
    "# Establish the spending bins and group names.\n",
    "\n",
    "spending_bins = [0, 585, 630, 645, 675]\n",
    "group_names = [\"<$586\", \"$586-630\", \"$631-645\", \"$646-675\"]\n",
    "\n",
    "# Categorize spending based on the bins.\n",
    "per_school_summary_df_copy[\"Spending Ranges (Per Student)\"] = pd.cut(per_school_capita, spending_bins, labels=group_names)\n",
    "\n",
    "per_school_summary_df_copy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2083,
   "id": "09500bd0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate averages for the desired columns.\n",
    "\n",
    "spending_math_scores = per_school_summary_df_copy.groupby([\"Spending Ranges (Per Student)\"]).mean()[\"Average Math Score\"]\n",
    "\n",
    "spending_reading_scores = per_school_summary_df_copy.groupby([\"Spending Ranges (Per Student)\"]).mean()[\"Average Reading Score\"]\n",
    "\n",
    "spending_passing_math = per_school_summary_df_copy.groupby([\"Spending Ranges (Per Student)\"]).mean()[\"% Passing Math\"]\n",
    "\n",
    "spending_passing_reading = per_school_summary_df_copy.groupby([\"Spending Ranges (Per Student)\"]).mean()[\"% Passing Reading\"]\n",
    "\n",
    "overall_passing_spending = per_school_summary_df_copy.groupby([\"Spending Ranges (Per Student)\"]).mean()[\"% Overall Passing\"]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2084,
   "id": "4a63cc39",
   "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</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;$586</th>\n",
       "      <td>83.455399</td>\n",
       "      <td>83.933814</td>\n",
       "      <td>93.460096</td>\n",
       "      <td>96.610877</td>\n",
       "      <td>90.369459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>$586-630</th>\n",
       "      <td>81.899826</td>\n",
       "      <td>83.155286</td>\n",
       "      <td>87.133538</td>\n",
       "      <td>92.718205</td>\n",
       "      <td>81.418596</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>$631-645</th>\n",
       "      <td>78.502002</td>\n",
       "      <td>81.636261</td>\n",
       "      <td>66.893995</td>\n",
       "      <td>77.480478</td>\n",
       "      <td>56.389766</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>$646-675</th>\n",
       "      <td>76.997210</td>\n",
       "      <td>81.027843</td>\n",
       "      <td>66.164813</td>\n",
       "      <td>81.133951</td>\n",
       "      <td>53.526855</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               Average Math Score  Average Reading Score  \\\n",
       "Spending Ranges (Per Student)                                              \n",
       "<$586                                   83.455399              83.933814   \n",
       "$586-630                                81.899826              83.155286   \n",
       "$631-645                                78.502002              81.636261   \n",
       "$646-675                                76.997210              81.027843   \n",
       "\n",
       "                               % Passing Math  % Passing Reading  \\\n",
       "Spending Ranges (Per Student)                                      \n",
       "<$586                               93.460096          96.610877   \n",
       "$586-630                            87.133538          92.718205   \n",
       "$631-645                            66.893995          77.480478   \n",
       "$646-675                            66.164813          81.133951   \n",
       "\n",
       "                               % Overall Passing  \n",
       "Spending Ranges (Per Student)                     \n",
       "<$586                                  90.369459  \n",
       "$586-630                               81.418596  \n",
       "$631-645                               56.389766  \n",
       "$646-675                               53.526855  "
      ]
     },
     "execution_count": 2084,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create the DataFrame\n",
    "spending_summary_df = pd.DataFrame({\n",
    "          \"Average Math Score\" : spending_math_scores,\n",
    "          \"Average Reading Score\": spending_reading_scores,\n",
    "          \"% Passing Math\": spending_passing_math,\n",
    "          \"% Passing Reading\": spending_passing_reading,\n",
    "          \"% Overall Passing\": overall_passing_spending})\n",
    "spending_summary_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2085,
   "id": "db661406",
   "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</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;$586</th>\n",
       "      <td>83.5</td>\n",
       "      <td>83.9</td>\n",
       "      <td>93</td>\n",
       "      <td>97</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>$586-630</th>\n",
       "      <td>81.9</td>\n",
       "      <td>83.2</td>\n",
       "      <td>87</td>\n",
       "      <td>93</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>$631-645</th>\n",
       "      <td>78.5</td>\n",
       "      <td>81.6</td>\n",
       "      <td>67</td>\n",
       "      <td>77</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>$646-675</th>\n",
       "      <td>77.0</td>\n",
       "      <td>81.0</td>\n",
       "      <td>66</td>\n",
       "      <td>81</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Average Math Score Average Reading Score  \\\n",
       "Spending Ranges (Per Student)                                            \n",
       "<$586                                       83.5                  83.9   \n",
       "$586-630                                    81.9                  83.2   \n",
       "$631-645                                    78.5                  81.6   \n",
       "$646-675                                    77.0                  81.0   \n",
       "\n",
       "                              % Passing Math % Passing Reading  \\\n",
       "Spending Ranges (Per Student)                                    \n",
       "<$586                                     93                97   \n",
       "$586-630                                  87                93   \n",
       "$631-645                                  67                77   \n",
       "$646-675                                  66                81   \n",
       "\n",
       "                              % Overall Passing  \n",
       "Spending Ranges (Per Student)                    \n",
       "<$586                                        90  \n",
       "$586-630                                     81  \n",
       "$631-645                                     56  \n",
       "$646-675                                     54  "
      ]
     },
     "execution_count": 2085,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Format the DataFrame \n",
    "\n",
    "spending_summary_df[\"Average Math Score\"] = spending_summary_df[\"Average Math Score\"].map(\"{:.1f}\".format)\n",
    "\n",
    "spending_summary_df[\"Average Reading Score\"] = spending_summary_df[\"Average Reading Score\"].map(\"{:.1f}\".format)\n",
    "\n",
    "spending_summary_df[\"% Passing Math\"] = spending_summary_df[\"% Passing Math\"].map(\"{:.0f}\".format)\n",
    "\n",
    "spending_summary_df[\"% Passing Reading\"] = spending_summary_df[\"% Passing Reading\"].map(\"{:.0f}\".format)\n",
    "\n",
    "spending_summary_df[\"% Overall Passing\"] = spending_summary_df[\"% Overall Passing\"].map(\"{:.0f}\".format)\n",
    "\n",
    "spending_summary_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2086,
   "id": "08498316",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>School Type</th>\n",
       "      <th>Total Students</th>\n",
       "      <th>Total School Budget</th>\n",
       "      <th>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</th>\n",
       "      <th>Spending Ranges (Per Student)</th>\n",
       "      <th>School Size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Wright High School</th>\n",
       "      <td>Charter</td>\n",
       "      <td>1800</td>\n",
       "      <td>$1,049,400.00</td>\n",
       "      <td>$583.00</td>\n",
       "      <td>83.682222</td>\n",
       "      <td>83.955</td>\n",
       "      <td>93.333333</td>\n",
       "      <td>96.611111</td>\n",
       "      <td>90.333333</td>\n",
       "      <td>&lt;$586</td>\n",
       "      <td>Medium (1000-2000)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   School Type  Total Students Total School Budget  \\\n",
       "Wright High School     Charter            1800       $1,049,400.00   \n",
       "\n",
       "                   Per Student Budget  Average Math Score  \\\n",
       "Wright High School            $583.00           83.682222   \n",
       "\n",
       "                    Average Reading Score  % Passing Math  % Passing Reading  \\\n",
       "Wright High School                 83.955       93.333333          96.611111   \n",
       "\n",
       "                    % Overall Passing Spending Ranges (Per Student)  \\\n",
       "Wright High School          90.333333                         <$586   \n",
       "\n",
       "                           School Size  \n",
       "Wright High School  Medium (1000-2000)  "
      ]
     },
     "execution_count": 2086,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Scores by School Size\n",
    "\n",
    "# Establish the bins.\n",
    "size_bins = [0, 1000, 2000, 5000]\n",
    "group_names = [\"Small (<1000)\", \"Medium (1000-2000)\", \"Large (2000-5000)\"]\n",
    "# Categorize spending based on the bins.\n",
    "per_school_summary_df_copy[\"School Size\"] = pd.cut(per_school_summary_df[\"Total Students\"], size_bins, labels=group_names)\n",
    "per_school_summary_df_copy.tail(1)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2087,
   "id": "a46456bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate averages for the desired columns. \n",
    "\n",
    "size_math_scores = per_school_summary_df_copy.groupby([\"School Size\"]).mean()[\"Average Math Score\"]\n",
    "\n",
    "size_reading_scores = per_school_summary_df_copy.groupby([\"School Size\"]).mean()[\"Average Reading Score\"]\n",
    "\n",
    "size_passing_math = per_school_summary_df_copy.groupby([\"School Size\"]).mean()[\"% Passing Math\"]\n",
    "\n",
    "size_passing_reading = per_school_summary_df_copy.groupby([\"School Size\"]).mean()[\"% Passing Reading\"]\n",
    "\n",
    "size_overall_passing = per_school_summary_df_copy.groupby([\"School Size\"]).mean()[\"% Overall Passing\"]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2088,
   "id": "f588129a",
   "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</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.821598</td>\n",
       "      <td>83.929843</td>\n",
       "      <td>93.550225</td>\n",
       "      <td>96.099437</td>\n",
       "      <td>89.883853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Medium (1000-2000)</th>\n",
       "      <td>83.361201</td>\n",
       "      <td>83.873869</td>\n",
       "      <td>88.327523</td>\n",
       "      <td>91.261628</td>\n",
       "      <td>85.447223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Large (2000-5000)</th>\n",
       "      <td>77.746417</td>\n",
       "      <td>81.344493</td>\n",
       "      <td>69.963361</td>\n",
       "      <td>82.766634</td>\n",
       "      <td>58.286003</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.821598              83.929843       93.550225   \n",
       "Medium (1000-2000)           83.361201              83.873869       88.327523   \n",
       "Large (2000-5000)            77.746417              81.344493       69.963361   \n",
       "\n",
       "                    % Passing Reading  % Overall Passing  \n",
       "School Size                                               \n",
       "Small (<1000)               96.099437          89.883853  \n",
       "Medium (1000-2000)          91.261628          85.447223  \n",
       "Large (2000-5000)           82.766634          58.286003  "
      ]
     },
     "execution_count": 2088,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Assemble into DataFrame. \n",
    "\n",
    "size_summary_df = pd.DataFrame({\n",
    "          \"Average Math Score\" : size_math_scores,\n",
    "          \"Average Reading Score\": size_reading_scores,\n",
    "          \"% Passing Math\": size_passing_math,\n",
    "          \"% Passing Reading\": size_passing_reading,\n",
    "          \"% Overall Passing\": size_overall_passing})\n",
    "\n",
    "size_summary_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2089,
   "id": "ef3e88d9",
   "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</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.8</td>\n",
       "      <td>83.9</td>\n",
       "      <td>94</td>\n",
       "      <td>96</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Medium (1000-2000)</th>\n",
       "      <td>83.4</td>\n",
       "      <td>83.9</td>\n",
       "      <td>88</td>\n",
       "      <td>91</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Large (2000-5000)</th>\n",
       "      <td>77.7</td>\n",
       "      <td>81.3</td>\n",
       "      <td>70</td>\n",
       "      <td>83</td>\n",
       "      <td>58</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.8                  83.9             94   \n",
       "Medium (1000-2000)               83.4                  83.9             88   \n",
       "Large (2000-5000)                77.7                  81.3             70   \n",
       "\n",
       "                   % Passing Reading % Overall Passing  \n",
       "School Size                                             \n",
       "Small (<1000)                     96                90  \n",
       "Medium (1000-2000)                91                85  \n",
       "Large (2000-5000)                 83                58  "
      ]
     },
     "execution_count": 2089,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Format the DataFrame  \n",
    "\n",
    "size_summary_df[\"Average Math Score\"] = size_summary_df[\"Average Math Score\"].map(\"{:.1f}\".format)\n",
    "\n",
    "size_summary_df[\"Average Reading Score\"] = size_summary_df[\"Average Reading Score\"].map(\"{:.1f}\".format)\n",
    "\n",
    "size_summary_df[\"% Passing Math\"] = size_summary_df[\"% Passing Math\"].map(\"{:.0f}\".format)\n",
    "\n",
    "size_summary_df[\"% Passing Reading\"] = size_summary_df[\"% Passing Reading\"].map(\"{:.0f}\".format)\n",
    "\n",
    "size_summary_df[\"% Overall Passing\"] = size_summary_df[\"% Overall Passing\"].map(\"{:.0f}\".format)\n",
    "\n",
    "size_summary_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2090,
   "id": "e9566255",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Scores by School Type\n",
    "\n",
    "# Calculate averages for the desired columns. \n",
    "size_math_scores = per_school_summary_df_copy.groupby([\"School Size\"]).mean()[\"Average Math Score\"]\n",
    "\n",
    "size_reading_scores = per_school_summary_df_copy.groupby([\"School Size\"]).mean()[\"Average Reading Score\"]\n",
    "\n",
    "size_passing_math = per_school_summary_df_copy.groupby([\"School Size\"]).mean()[\"% Passing Math\"]\n",
    "\n",
    "size_passing_reading = per_school_summary_df_copy.groupby([\"School Size\"]).mean()[\"% Passing Reading\"]\n",
    "\n",
    "size_overall_passing = per_school_summary_df_copy.groupby([\"School Size\"]).mean()[\"% Overall Passing\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2091,
   "id": "e26e345c",
   "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</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.821598</td>\n",
       "      <td>83.929843</td>\n",
       "      <td>93.550225</td>\n",
       "      <td>96.099437</td>\n",
       "      <td>89.883853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Medium (1000-2000)</th>\n",
       "      <td>83.361201</td>\n",
       "      <td>83.873869</td>\n",
       "      <td>88.327523</td>\n",
       "      <td>91.261628</td>\n",
       "      <td>85.447223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Large (2000-5000)</th>\n",
       "      <td>77.746417</td>\n",
       "      <td>81.344493</td>\n",
       "      <td>69.963361</td>\n",
       "      <td>82.766634</td>\n",
       "      <td>58.286003</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.821598              83.929843       93.550225   \n",
       "Medium (1000-2000)           83.361201              83.873869       88.327523   \n",
       "Large (2000-5000)            77.746417              81.344493       69.963361   \n",
       "\n",
       "                    % Passing Reading  % Overall Passing  \n",
       "School Size                                               \n",
       "Small (<1000)               96.099437          89.883853  \n",
       "Medium (1000-2000)          91.261628          85.447223  \n",
       "Large (2000-5000)           82.766634          58.286003  "
      ]
     },
     "execution_count": 2091,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Assemble into DataFrame. \n",
    "\n",
    "size_summary_df = pd.DataFrame({\n",
    "          \"Average Math Score\" : size_math_scores,\n",
    "          \"Average Reading Score\": size_reading_scores,\n",
    "          \"% Passing Math\": size_passing_math,\n",
    "          \"% Passing Reading\": size_passing_reading,\n",
    "          \"% Overall Passing\": size_overall_passing})\n",
    "\n",
    "size_summary_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2092,
   "id": "bf24c4c3",
   "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</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.8</td>\n",
       "      <td>83.9</td>\n",
       "      <td>94</td>\n",
       "      <td>96</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Medium (1000-2000)</th>\n",
       "      <td>83.4</td>\n",
       "      <td>83.9</td>\n",
       "      <td>88</td>\n",
       "      <td>91</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Large (2000-5000)</th>\n",
       "      <td>77.7</td>\n",
       "      <td>81.3</td>\n",
       "      <td>70</td>\n",
       "      <td>83</td>\n",
       "      <td>58</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.8                  83.9             94   \n",
       "Medium (1000-2000)               83.4                  83.9             88   \n",
       "Large (2000-5000)                77.7                  81.3             70   \n",
       "\n",
       "                   % Passing Reading % Overall Passing  \n",
       "School Size                                             \n",
       "Small (<1000)                     96                90  \n",
       "Medium (1000-2000)                91                85  \n",
       "Large (2000-5000)                 83                58  "
      ]
     },
     "execution_count": 2092,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# # Format the DataFrame \n",
    "\n",
    "\n",
    "size_summary_df[\"Average Math Score\"] = size_summary_df[\"Average Math Score\"].map(\"{:.1f}\".format)\n",
    "\n",
    "size_summary_df[\"Average Reading Score\"] = size_summary_df[\"Average Reading Score\"].map(\"{:.1f}\".format)\n",
    "\n",
    "size_summary_df[\"% Passing Math\"] = size_summary_df[\"% Passing Math\"].map(\"{:.0f}\".format)\n",
    "\n",
    "size_summary_df[\"% Passing Reading\"] = size_summary_df[\"% Passing Reading\"].map(\"{:.0f}\".format)\n",
    "\n",
    "size_summary_df[\"% Overall Passing\"] = size_summary_df[\"% Overall Passing\"].map(\"{:.0f}\".format)\n",
    "\n",
    "size_summary_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2093,
   "id": "4b761ba2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate averages for the desired columns. \n",
    "type_math_scores = per_school_summary_df_copy.groupby([\"School Type\"]).mean()[\"Average Math Score\"]\n",
    "\n",
    "type_reading_scores = per_school_summary_df_copy.groupby([\"School Type\"]).mean()[\"Average Reading Score\"]\n",
    "\n",
    "type_passing_math = per_school_summary_df_copy.groupby([\"School Type\"]).mean()[\"% Passing Math\"]\n",
    "\n",
    "type_passing_reading = per_school_summary_df_copy.groupby([\"School Type\"]).mean()[\"% Passing Reading\"]\n",
    "\n",
    "type_overall_passing = per_school_summary_df_copy.groupby([\"School Type\"]).mean()[\"% Overall Passing\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2094,
   "id": "a446c971",
   "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</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.465425</td>\n",
       "      <td>83.902315</td>\n",
       "      <td>90.325723</td>\n",
       "      <td>93.130832</td>\n",
       "      <td>87.198299</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>53.672208</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.465425              83.902315       90.325723   \n",
       "District              76.956733              80.966636       66.548453   \n",
       "\n",
       "             % Passing Reading  % Overall Passing  \n",
       "School Type                                        \n",
       "Charter              93.130832          87.198299  \n",
       "District             80.799062          53.672208  "
      ]
     },
     "execution_count": 2094,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Assemble into DataFrame. \n",
    "type_summary_df = pd.DataFrame({\n",
    "          \"Average Math Score\" : type_math_scores,\n",
    "          \"Average Reading Score\": type_reading_scores,\n",
    "          \"% Passing Math\": type_passing_math,\n",
    "          \"% Passing Reading\": type_passing_reading,\n",
    "          \"% Overall Passing\": type_overall_passing})\n",
    "\n",
    "type_summary_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2095,
   "id": "61fb0d91",
   "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</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.5</td>\n",
       "      <td>83.9</td>\n",
       "      <td>90</td>\n",
       "      <td>93</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>District</th>\n",
       "      <td>77.0</td>\n",
       "      <td>81.0</td>\n",
       "      <td>67</td>\n",
       "      <td>81</td>\n",
       "      <td>54</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.5                  83.9             90   \n",
       "District                  77.0                  81.0             67   \n",
       "\n",
       "            % Passing Reading % Overall Passing  \n",
       "School Type                                      \n",
       "Charter                    93                87  \n",
       "District                   81                54  "
      ]
     },
     "execution_count": 2095,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# # Format the DataFrame \n",
    "type_summary_df[\"Average Math Score\"] = type_summary_df[\"Average Math Score\"].map(\"{:.1f}\".format)\n",
    "\n",
    "type_summary_df[\"Average Reading Score\"] = type_summary_df[\"Average Reading Score\"].map(\"{:.1f}\".format)\n",
    "\n",
    "type_summary_df[\"% Passing Math\"] = type_summary_df[\"% Passing Math\"].map(\"{:.0f}\".format)\n",
    "\n",
    "type_summary_df[\"% Passing Reading\"] = type_summary_df[\"% Passing Reading\"].map(\"{:.0f}\".format)\n",
    "\n",
    "type_summary_df[\"% Overall Passing\"] = type_summary_df[\"% Overall Passing\"].map(\"{:.0f}\".format)\n",
    "\n",
    "type_summary_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2096,
   "id": "dc00fd12",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PythonData",
   "language": "python",
   "name": "pythondata"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}