Calculate average math and reading scores

Observed Trends:

• Looking at all the reading scores and math scores in each table, students tend to do better in reading than in math.
• Looking at the top performing schools, bottom performing schools, and passing rates by school type, students have a much higher passing rate in charter schools (~95%) than in district schools (~74%).
• Looking at the summary of performance by spending ranges per student, it seems that students have a higher passing rate in the lower spending ranges (<$615).

# Import dependencies
import pandas as pd
import numpy as np

# Create paths to csv files
school_path = "raw_data/schools_complete.csv"
student_path = "raw_data/students_complete.csv"

# Read the csv files
schools = pd.read_csv(school_path)
schools = schools.rename(columns={"name": "school"}) # so we can merge later

students = pd.read_csv(student_path)
students = pd.DataFrame(students)

District Summary

# Calculate total schools, students, and budget of district
total_schools = schools['School ID'].count()
total_students = schools['size'].sum()
total_budget = schools['budget'].sum()

# Calculate average math and reading scores
avg_math = students['math_score'].mean()
avg_read = students['reading_score'].mean()

# Calculate percent passing rates
pass_math = students.loc[students['math_score'] >= 70]
per_math = len(pass_math)/ total_students * 100

pass_read = students.loc[students['reading_score'] >= 70]
per_read = len(pass_read)/ total_students * 100

overall = (per_math + per_read)/2 

# Create dataframe for district summary
district_summary = pd.DataFrame({"Total Schools": total_schools,
                                "Total Students": total_students,
                                "Total Budget": total_budget,
                                "Average Math Score": avg_math,
                                "Average Reading Score": avg_read,
                                "% Passing Math": per_math,
                                "% Passing Reading": per_read,
                                "% Overall Passing Rate": [overall]})

#Reorganize columns with double brackets
district_summary = district_summary[["Total Schools", "Total Students", "Total Budget", 
                                     "Average Math Score", "Average Reading Score", 
                                     "% Passing Math", "% Passing Reading", "% Overall Passing Rate"]]

# Format to "\$0,000.00"
district_summary['Total Budget'] = district_summary['Total Budget'].map('${:,.2f}'.format)

district_summary

School Summary

# Merge schools and students
merge_df = pd.merge(schools, students, on="school")
merge_df = merge_df.drop(['School ID', 'Student ID'], axis=1)

# Set school as index, get type, total students, total budget and per student budget
school_index = schools.set_index('school')
school_type = school_index['type']
school_students = school_index['size']
school_budget = school_index['budget']
psb = school_budget/school_students

# Set school as index and groupby school for merge_df to get average scores by school
index = merge_df.set_index('school')
grouped = index.groupby(['school'])

# Get average math and reading score
school_avg_math = grouped['math_score'].mean()
school_avg_read = grouped['reading_score'].mean()

# Calculate percent that passed math
total_stu = grouped['name'].count()
grouped_math = pass_math.groupby('school')
school_pass_math = grouped_math['name'].count()/total_stu*100

# Calculate percent that passed reading
grouped_reading = pass_read.groupby('school')
school_pass_read = grouped_reading['name'].count()/total_stu*100
                             
# Calculate overall passing rate
overall_pass = (school_pass_math + school_pass_read)/2

# Create dataframe for school summary
school_summary = pd.DataFrame({"School Type": school_type,
                              "Total Students": school_students,
                              "Total School Budget": school_budget,
                              "Per Student Budget": psb,
                              "Average Math Score": school_avg_math,
                              "Average Reading Score": school_avg_read,
                              "% Passing Math": school_pass_math,
                              "% Passing Reading": school_pass_read,
                              "Overall Passing Rate": overall_pass})

# Reorganize school summary columns 
school_summary = school_summary[["School Type", "Total Students", "Total School Budget", 
                                 "Per Student Budget", "Average Math Score", "Average Reading Score", 
                                 "% Passing Math", "% Passing Reading", "Overall Passing Rate"]]

# Format to "\$0,000.00"
school_summary['Total School Budget'] = school_summary['Total School Budget'].map('${:,.2f}'.format)
school_summary['Per Student Budget'] = school_summary['Per Student Budget'].map('${:,.2f}'.format)

school_summary.head()

Top Performing Schools (By Passing Rate)

# Create top performing schools summary by passing rate
top_summary = school_summary.loc[school_summary['Overall Passing Rate'] > 90]
top_summary.sort_values(['Overall Passing Rate'], ascending=False).head()

Bottom Performing Schools (By Passing Rate)

# Create bottom performing schools summary by passing rate
bottom_summary = school_summary.loc[school_summary['Overall Passing Rate'] < 75]
bottom_summary.sort_values(['Overall Passing Rate'], ascending=True).head()

Math Scores by Grade

# Math scores by grade
ninth = students.loc[students['grade']=='9th'].groupby("school")
ninth_math = ninth['math_score'].mean()
tenth = students.loc[students['grade']=='10th'].groupby("school")
tenth_math = tenth['math_score'].mean()
eleventh = students.loc[students['grade']=='11th'].groupby("school")
eleventh_math = eleventh['math_score'].mean()
twelfth = students.loc[students['grade']=='12th'].groupby("school")
twelfth_math = twelfth['math_score'].mean()

# Create dataframe for math scores summary
math_summary = pd.DataFrame({"9th": ninth_math,
                            "10th": tenth_math,
                            "11th": eleventh_math,
                            "12th": twelfth_math})
math_summary = math_summary[["9th","10th","11th","12th"]]
del math_summary.index.name

math_summary.head()

Reading Scores by Grade

# Reading scores by grade
r_ninth = students.loc[students['grade'] == '9th'].groupby("school")
ninth_read = r_ninth['reading_score'].mean()
r_tenth = students.loc[students['grade'] == '10th'].groupby("school")
tenth_read = r_tenth['reading_score'].mean()
r_eleventh = students.loc[students['grade'] == '11th'].groupby("school")
eleventh_read = r_eleventh['reading_score'].mean()
r_twelfth = students.loc[students['grade'] == '12th'].groupby("school")
twelfth_read = r_twelfth['reading_score'].mean()

# Create dataframe for reading scores summary
read_summary = pd.DataFrame({"9th": ninth_read,
                            "10th": tenth_read,
                            "11th": eleventh_read,
                            "12th": twelfth_read})
read_summary = read_summary[["9th","10th","11th","12th"]]
del read_summary.index.name

read_summary.head()

Scores by School Spending

# Create bins
bins = [0,585,615,645,675]

# Create names for the bins
spending_range = ['<\$585','\$585-615','\$615-645','\$645-675']

# Change formatting of per student budget in school_summary from string back to float so it can be binned 
school_summary['Per Student Budget'] = school_summary['Per Student Budget'].str.replace('$', '')
school_summary['Per Student Budget'] = school_summary['Per Student Budget'].astype(float)
                                                      
school_summary["Spending Ranges (Per Student)"] = pd.cut(school_summary["Per Student Budget"], 
                                                         bins, labels=spending_range)
spend_summary = school_summary.groupby("Spending Ranges (Per Student)")
spend_summary = spend_summary[["Average Math Score", "Average Reading Score", "% Passing Math", 
                               "% Passing Reading", "Overall Passing Rate"]]
spend_summary.mean()

Scores by School Size

# Create bins
bins2 = [0, 1000, 2000, 5000]

# Create names for bins
size_range = ['Small', 'Medium', 'Large']

school_summary["School Size"] = pd.cut(school_summary["Total Students"], 
                                                         bins2, labels=size_range)
size_summary = school_summary.groupby("School Size")
size_summary = size_summary[["Average Math Score", "Average Reading Score", "% Passing Math", 
                             "% Passing Reading", "Overall Passing Rate"]]
size_summary.mean()

Scores by School Type

type_summary = school_summary.groupby("School Type")
type_summary = type_summary[["Average Math Score", "Average Reading Score", "% Passing Math", 
                             "% Passing Reading", "Overall Passing Rate"]]
type_summary.mean()