<jupyter_start><jupyter_text>PyCitySchools Welcome homework grader(s)!* This report provides statistics and summaries across all schools in a single school grouping that includes both district and charter schools for math and reading scores.  Notations and comments will be provided before individual summaries if applicable.<jupyter_code># Dependencies and Setup
import pandas as pd
import numpy as np

# File to Load (Remember to Change These)
school_data_to_load = "Resources/schools_complete.csv"
student_data_to_load = "Resources/students_complete.csv"

# Read School and Student Data File and store into Pandas DataFrames
school_data = pd.read_csv(school_data_to_load)
student_data = pd.read_csv(student_data_to_load)

# Combine the data into a single dataset.  
school_data_complete = pd.merge(student_data, school_data, how="left", on=["school_name", "school_name"])
# set variable for general stats
num_schools = len(school_data_complete["school_name"].unique())
num_students = school_data_complete["Student ID"].count()
total_budget = school_data["budget"].sum()
avg_math_score = school_data_complete["math_score"].mean()
avg_read_score = school_data_complete["reading_score"].mean()
# Create bin for pass/fail. Then calc pass/fail rates for each math, reading, and both
pass_bin = [0,69,100]
pass_name = ["Fail","Pass"]

# Math, reading, and both pass rate
school_data_complete["Math_PF"] = pd.cut(school_data_complete["math_score"],pass_bin,labels=pass_name)
count_passing_math = school_data_complete.loc[school_data_complete["Math_PF"] == "Pass"]["Student ID"].count()
pct_passing_math = count_passing_math / num_students * 100

school_data_complete["Read_PF"] = pd.cut(school_data_complete["reading_score"],pass_bin,labels=pass_name)
count_passing_read = school_data_complete.loc[school_data_complete["Read_PF"] == "Pass"]["Student ID"].count()
pct_passing_read = count_passing_read / num_students * 100

count_passing_both = school_data_complete.loc[(school_data_complete["Math_PF"] == "Pass") & (school_data_complete["Read_PF"] == "Pass")]["Student ID"].count()
count_passing_both
pct_passing_both = count_passing_both / num_students *100
# District Summary
district_summary_data = [{"Total Schools": num_schools, "Total Students" : num_students, "Total Budget" : total_budget, "Average Math Score": avg_math_score , "Average Reading Score" : avg_read_score, "% Passing Math": pct_passing_math, "% Passing Reading" : pct_passing_read, "% Overall Passing" : pct_passing_both }]
district_summary_df = pd.DataFrame(district_summary_data)
district_summary_df['Total Budget'] = district_summary_df['Total Budget'].astype(float).map("${:,.2f}".format)
district_summary_df['Total Students'] = district_summary_df['Total Students'].astype(float).map("{:,.0f}".format)<jupyter_output><empty_output><jupyter_text>District Summary* This is a snapshot of the math and reading scores across the entire district, including the pass rates.  Note that a passing grade is 70 or above.<jupyter_code>district_summary_df
# make listing of schools
name_of_school = school_data_complete.groupby(['school_name'])['school_name'].first()
type_of_school = school_data_complete.groupby(['school_name'])['type'].first()

# stats per school
school_total_students = school_data_complete.groupby(['school_name'])['Student ID'].count()
school_total_budget = school_data_complete.groupby(['school_name'])['budget'].mean()
school_avg_math = school_data_complete.groupby(['school_name'])['math_score'].mean()
school_avg_read = school_data_complete.groupby(['school_name'])['reading_score'].mean()

school_mathP_count = school_data_complete[school_data_complete['Math_PF'] == "Pass"].groupby(['school_name']).size()
school_readP_count = school_data_complete[school_data_complete['Read_PF'] == "Pass"].groupby(['school_name']).size()
school_bothP_count = school_data_complete[(school_data_complete['Math_PF'] == "Pass") & (school_data_complete['Read_PF'] == "Pass")].groupby(['school_name']).size()

# make school summary data
school_summary_data_df = pd.DataFrame({"School Type": type_of_school, "Total Students": school_total_students,  "Total School Budget": school_total_budget, "Average Math Score": school_avg_math, "Average Reading Score": school_avg_read})

# calc additional stat fields
per_student_budget = school_total_budget / school_total_students
pct_passing_math = school_mathP_count / school_total_students * 100
pct_passing_read = school_readP_count / school_total_students * 100
pct_passing_both = school_bothP_count / school_total_students * 100

# make school summary output table
school_summary_df = pd.DataFrame({"School Type": type_of_school, "Total Students": school_total_students,  "Total School Budget": school_total_budget, "Per Student Budget": per_student_budget, "Average Math Score": school_avg_math, "Average Reading Score": school_avg_read, "% Passing Math": pct_passing_math, "% Passing Reading": pct_passing_read, "% Overall Passing": pct_passing_both})

# format fields
school_summary_df['Total School Budget'] = school_summary_df['Total School Budget'].astype(float).map("${:,.2f}".format)
school_summary_df['Per Student Budget'] = school_summary_df['Per Student Budget'].astype(float).map("${:,.2f}".format)<jupyter_output><empty_output><jupyter_text>School Summary* This summary provides the math and reading scores for each school, including the pass rates. Items to note:  (e.g. trends) School sizes range from 427 to 4,976 students and the budget per student has a 13% range depending on school.  The smallest per student budget is  ranges from 578 to 655 (US dollars) per student.<jupyter_code>school_summary_df
school_top_5 = school_summary_df.sort_values("% Overall Passing", ascending = False)<jupyter_output><empty_output><jupyter_text>Top Performing Schools (By % Overall Passing)  Note that the top 5 schools based on percentage of students passing both reading and math are a charter schools.  Regardless of the budget per student, each of these schools has similar math and reading scores and high pass rates for both educational subjects.<jupyter_code>school_top_5.head()<jupyter_output><empty_output><jupyter_text>Bottom Performing Schools (By % Overall Passing)  Note that the bottom 5 schools based on percentage of students passing both reading and math are a district schools.  Additionally, these are all schools within the higher brackets for budget per student.  However, what is not known is what the budget is spent on<jupyter_code>school_bottom_5 = school_summary_df.sort_values("% Overall Passing")
school_bottom_5.head()
# make listing of schools
avg_math_9 = school_data_complete[school_data_complete['grade'] == "9th"].groupby(['school_name'])['math_score'].mean()
avg_math_10 = school_data_complete[school_data_complete['grade'] == "10th"].groupby(['school_name'])['math_score'].mean()
avg_math_11 = school_data_complete[school_data_complete['grade'] == "11th"].groupby(['school_name'])['math_score'].mean()
avg_math_12 = school_data_complete[school_data_complete['grade'] == "12th"].groupby(['school_name'])['math_score'].mean()

math_summary_by_grade_df = pd.DataFrame({"9th" : avg_math_9, "10th": avg_math_10, "11th": avg_math_11,"12th": avg_math_12})
math_summary_by_grade_df['9th'] = math_summary_by_grade_df['9th'].round(1)
math_summary_by_grade_df['10th'] = math_summary_by_grade_df['10th'].round(1)
math_summary_by_grade_df['11th'] = math_summary_by_grade_df['11th'].round(1)
math_summary_by_grade_df['12th'] = math_summary_by_grade_df['12th'].round(1)<jupyter_output><empty_output><jupyter_text>Math Scores by Grade  Within each school, the percentage of students passing math does not fluctuate much based on their grade level.<jupyter_code>math_summary_by_grade_df
# make listing of schools
avg_read_9 = school_data_complete[school_data_complete['grade'] == "9th"].groupby(['school_name'])['reading_score'].mean()
avg_read_10 = school_data_complete[school_data_complete['grade'] == "10th"].groupby(['school_name'])['reading_score'].mean()
avg_read_11 = school_data_complete[school_data_complete['grade'] == "11th"].groupby(['school_name'])['reading_score'].mean()
avg_read_12 = school_data_complete[school_data_complete['grade'] == "12th"].groupby(['school_name'])['reading_score'].mean()

read_summary_by_grade_df = pd.DataFrame({"9th" : avg_read_9, "10th": avg_read_10, "11th": avg_read_11,"12th": avg_read_12})
read_summary_by_grade_df['9th'] = read_summary_by_grade_df['9th'].round(1)
read_summary_by_grade_df['10th'] = read_summary_by_grade_df['10th'].round(1)
read_summary_by_grade_df['11th'] = read_summary_by_grade_df['11th'].round(1)
read_summary_by_grade_df['12th'] = read_summary_by_grade_df['12th'].round(1)<jupyter_output><empty_output><jupyter_text>Reading Score by Grade  Similar to the the math scores by grade, within each school, the percentage of students passing reading does not fluctuate much based on their grade level.<jupyter_code>read_summary_by_grade_df
# there's got to be a better way to do this, but nothing is working to convert per student budget to a float.  So I'm recreating the table
# make school summary output table
school_data_df = pd.DataFrame({"School Type": type_of_school, "Total Students": school_total_students,  "Total School Budget": school_total_budget, "Per Student Budget": per_student_budget, "Average Math Score": school_avg_math, "Average Reading Score": school_avg_read, "% Passing Math": pct_passing_math, "% Passing Reading": pct_passing_read, "% Overall Passing": pct_passing_both})
# merge per/student data with per/school data to add in spending/school for each student (including the Dr.)
school_spending_df = pd.merge(school_data_complete, school_data_df, how = "left", on=["school_name", "school_name"])

# clean up column noise
school_spending_df = school_spending_df.drop('Average Math Score',1)
school_spending_df = school_spending_df.drop('Average Reading Score',1)
school_spending_df = school_spending_df.drop('% Passing Math',1)
school_spending_df = school_spending_df.drop('% Passing Reading',1)
school_spending_df = school_spending_df.drop('% Overall Passing',1)


# apply spending categories
spending_bin = [-100,585,630,645,10000]
spending_name = ["<$585","$585-630", "$630-645","$645-680"]
school_spending_df["spending_category"] = pd.cut(school_spending_df['Per Student Budget'],spending_bin, labels = spending_name)

# basic stats on df
spending_count_percategory = school_spending_df.groupby(['spending_category'])['Student ID'].count()
spending_avg_math = school_spending_df.groupby(['spending_category'])['math_score'].mean()
spending_avg_read = school_spending_df.groupby(['spending_category'])['reading_score'].mean()

# pct passing
spending_student_count = school_spending_df.groupby(['spending_category'])['Student ID'].count()
spending_mathP_count = school_spending_df[school_spending_df['Math_PF'] == "Pass"].groupby(['spending_category']).size()
spending_readP_count = school_spending_df[school_spending_df['Read_PF'] == "Pass"].groupby(['spending_category']).size()
spending_bothP_count = school_spending_df[(school_spending_df['Math_PF'] == "Pass") & (school_spending_df['Read_PF'] == "Pass")].groupby(['spending_category']).size()

spending_passing_math = spending_mathP_count / spending_student_count * 100
spending_passing_read = spending_readP_count / spending_student_count * 100
spending_passing_both = spending_bothP_count / spending_student_count * 100

# make out table
spending_summary_df = pd.DataFrame({"Average Math Score": spending_avg_math, "Average Reading Score": spending_avg_read, "% Passing Math": spending_passing_math, "% Passing Reading": spending_passing_read, "% Overall Passing": spending_passing_both})

# format fields
spending_summary_df['Average Math Score'] = spending_summary_df['Average Math Score'].round(2)
spending_summary_df['Average Reading Score'] = spending_summary_df['Average Reading Score'].round(2)
spending_summary_df['% Passing Math'] = spending_summary_df['% Passing Math'].round(2)
spending_summary_df['% Passing Reading'] = spending_summary_df['% Passing Reading'].round(2)
spending_summary_df['% Overall Passing'] = spending_summary_df['% Overall Passing'].round(2)
# neither of the below is working to rename the spending category title
spending_summary_renamed_df = spending_summary_df.rename(columns={ "Spending Ranges (Per Student)" : "spending_category"})
spending_summary_df.rename_axis("Spending Ranges (Per Student)")<jupyter_output><empty_output><jupyter_text>Scores by School Spending Counterintuitive to those with deep pockets, “throwing money at it” will not increase the passing rates in reading and math comprehensions as seen from the below data.  Those schools with the smallest per student budget appear to be the most successful in  students scoring passing grades.<jupyter_code>spending_summary_df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11740890688259109, 'fraction_numerical': 0.015571473061351603, 'mean_word_length': 8.873174481168332, 'pattern_counts': {'":': 37, '<': 34, '<?xml version=': 0, '>': 33, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '623039', 'license': 'ADSL', 'n_tokens_mistral': 4176, 'n_tokens_neox': 4001, 'n_words': 921, 'path': 'PyCitySchools/PyCitySchools_starter.ipynb', 'repo_name': 'JGarvert/pandas-challenge', 'size': '12844'}