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()
# Start coding here...
# Add as many cells as you like...
Which NYC schools have the best math results?
The best math results are at least 80% of the maximum possible score of 800 for math. 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.
col1 = ["school_name","average_math"]
best_math= schools[schools["average_math"]>= 640 ]
best_math_schools =best_math[col1].sort_values("average_math", ascending =False)
best_math_schools
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).
col2 = ["average_math","average_reading","average_writing"]
top_10_school=schools.copy()
top_10_school['total_SAT'] = top_10_school[col2] .sum(axis=1)
top_10_schools =top_10_school[["school_name",'total_SAT']].sort_values('total_SAT',ascending=False)
top_10_schools = top_10_schools.iloc[0:10,:]
top_10_schools
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.
schools2 = schools.copy()
col2 = ["average_math","average_reading","average_writing"]
col3 = ["borough","num_schools","average_SAT","std_SAT"]
schools2['total_SAT'] = schools2[col2].sum(axis=1)
# Calculate the average total_SAT per borough
average_SAT_per_borough = schools2.groupby("borough")["total_SAT"].transform('mean')
std_SAT_per_borough = schools2.groupby("borough")["total_SAT"].transform('std')
sum_sc_SAT_per_borough=schools2.groupby("borough")["school_name"].transform('count')
schools2["average_SAT"] = average_SAT_per_borough.round(2)
schools2["std_SAT"] = std_SAT_per_borough.round(2)
schools2["num_schools"] = sum_sc_SAT_per_borough
largest_std_dev =schools2.sort_values("std_SAT",ascending=False)[col3]
largest_std_dev= largest_std_dev.iloc[[0], :]
largest_std_dev