Photo by Jannis Lucas on Unsplash.
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
# Read in the data
schools = pd.read_csv("schools.csv")
# Preview the data
schools.head()
# Set display option to show all columns
pd.set_option('display.max_columns', None)
# Start coding here...
# Add as many cells as you like...
# Task1:
# Which NYC schools have the best math results?
## The best math results are at least 80% of the *maximum possible score of 80% for math.
best_result= 800*0.80
#print(best_result)
## Save your results in a pandas DataFrame called best_math_schools, including "school_name" and "average_math" columns, sorted by "average_math" in descending order.
filtered_schools= schools[schools['average_math']>=best_result]
best_math_schools = filtered_schools[['school_name', 'average_math']]
#print(best_math_schools)
best_math_schools=best_math_schools.sort_values('average_math', ascending=False)
#print(best_math_schools)
# Task2:
# What are the top 10 performing schools based on the combined SAT scores?
## Save your results as a pandas DataFrame called top_10_schools containing the "school_name" and a new column named "total_SAT", with results ordered by "total_SAT" in descending order ("total_SAT" being the sum of math, reading, and writing scores).
schools['total_SAT'] = schools['average_math'] + schools['average_reading'] + schools['average_writing']
top_10_schools = schools.sort_values('total_SAT', ascending=False).head(10)
top_10_schools = top_10_schools[['school_name', 'total_SAT']]
print(top_10_schools)
#Task3:
# Which single borough has the largest standard deviation in the combined SAT score?
##Save your results as a pandas DataFrame called largest_std_dev.
##The DataFrame should contain one row, with:
##"borough" - the name of the NYC borough with the largest standard deviation of "total_SAT".
##"num_schools" - the number of schools in the borough.
##"average_SAT" - the mean of "total_SAT".
##"std_SAT" - the standard deviation of "total_SAT".
## Round all numeric values to two decimal places.
task3=schools[['borough','school_name','total_SAT']]
print(task3)
std_dev_by_borough = schools.groupby('borough')['total_SAT'].std().reset_index()
print(std_dev_by_borough)
mean_by_borough = schools.groupby('borough')['total_SAT'].mean().reset_index()
print(mean_by_borough)
num_schools_by_borough = schools.groupby('borough')['school_name'].count().reset_index()
print(num_schools_by_borough)
merged = std_dev_by_borough.merge(mean_by_borough, on='borough').merge(num_schools_by_borough, on='borough')
print(merged)
merged.columns = ['borough', 'std_SAT', 'average_SAT', 'num_schools']
largest_std_dev = merged.sort_values('std_SAT', ascending=False).head(1)
largest_std_dev = largest_std_dev.round(2)
print(largest_std_dev)