Project: Exploring NYC Public School Test Result Scores

Every year, American high school students take SATs, which are standardized tests intended to measure literacy, numeracy, and writing skills. There are three sections - reading, math, and writing, each with a maximum score of 800 points. These tests are extremely important for students and colleges, as they play a pivotal role in the admissions process.

Analyzing the performance of schools is important for a variety of stakeholders, including policy and education professionals, researchers, government, and even parents considering which school their children should attend.

You have been provided with a dataset called schools.csv, which is previewed below.

You have been tasked with answering three key questions about New York City (NYC) public school SAT performance.

# Re-run this cell 
import pandas as pd
import numpy as np

# Read in the data
schools = pd.read_csv("schools.csv")

# Preview the data
print(schools.sort_values("average_math",ascending=False).average_math.head(15))

# Start coding here...
# Add as many cells as you like...

88     754
170    714
93     711
365    701
68     683
280    682
333    680
174    669
0      657
45     641
5      634
213    633
204    631
237    625
3      613
Name: average_math, dtype: int64

schools.columns

Index(['school_name', 'borough', 'building_code', 'average_math',
       'average_reading', 'average_writing', 'percent_tested'],
      dtype='object')

Task 1

Create a pandas DataFrame called best_math_schools containing the “school_name” and “average_math” score for all schools where the results are at least 80% of the maximum possible score, sorted by “average_math” in descending order.

math_scores = schools[["school_name","average_math"]]
math_scores = math_scores[math_scores["average_math"]>=(800*.8)]
print(math_scores.shape)
best_math_schools = math_scores.sort_values("average_math",ascending=False)
print(best_math_schools)

(10, 2)
                                           school_name  average_math
88                              Stuyvesant High School           754
170                       Bronx High School of Science           714
93                 Staten Island Technical High School           711
365  Queens High School for the Sciences at York Co...           701
68   High School for Mathematics, Science, and Engi...           683
280                     Brooklyn Technical High School           682
333                        Townsend Harris High School           680
174  High School of American Studies at Lehman College           669
0    New Explorations into Science, Technology and ...           657
45                       Eleanor Roosevelt High School           641

Task 2

Identify the top 10 performing schools based on scores across the three SAT sections, storing as a pandas DataFrame called top_10_schools containing the school name and a column named “total_SAT”, with results sorted by total_SAT in descending order.

schools['total_SAT'] = schools.loc[:,["average_math","average_reading","average_writing"]].sum(axis=1)
schools.head()

	school_name	borough	building_code	average_math	average_reading	average_writing	percent_tested	total_SAT
0	New Explorations into Science, Technology and ...	Manhattan	M022	657	601	601	NaN	1859
1	Essex Street Academy	Manhattan	M445	395	411	387	78.9	1193
2	Lower Manhattan Arts Academy	Manhattan	M445	418	428	415	65.1	1261
3	High School for Dual Language and Asian Studies	Manhattan	M445	613	453	463	95.9	1529
4	Henry Street School for International Studies	Manhattan	M056	410	406	381	59.7	1197

top_10_schools = schools.sort_values("total_SAT",ascending=False)
top_10_schools = top_10_schools.iloc[0:10][["school_name","total_SAT"]]
top_10_schools.shape

(10, 2)

Task 3

Locate the NYC borough with the largest standard deviation for “total_SAT”, storing as a DataFrame called largest_std_dev with “borough” as the index and three columns: “num_schools” for the number of schools in the borough, “average_SAT” for the mean of “total_SAT”, and “std_SAT” for the standard deviation of “total_SAT”. Round all numeric values to two decimal places.

schools.borough.unique()

array(['Manhattan', 'Staten Island', 'Bronx', 'Queens', 'Brooklyn'],
      dtype=object)


schools_std_dev = schools.groupby("borough")["total_SAT"].agg(['count','mean','std']).round(2).rename(columns={"count":"num_schools","mean":"average_SAT","std":"std_SAT"})

schools_std_dev

	num_schools	average_SAT	std_SAT
borough
Bronx	98	1202.72	150.39
Brooklyn	109	1230.26	154.87
Manhattan	89	1340.13	230.29
Queens	69	1345.48	195.25
Staten Island	10	1439.00	222.30

largest_std_dev = schools_std_dev.sort_values("std_SAT",ascending=False).iloc[[0]]

largest_std_dev

	num_schools	average_SAT	std_SAT
borough
Manhattan	89	1340.13	230.29