<jupyter_start><jupyter_text>Note* Instructions have been included for each segment. You do not have to follow them exactly, but they are included to help you think through the steps.<jupyter_code># Dependencies and Setup
import pandas as pd

# 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 Data Frames
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"])
school_data_complete.head()<jupyter_output><empty_output><jupyter_text>District Summary* Calculate the total number of schools* Calculate the total number of students* Calculate the total budget* Calculate the average math score * Calculate the average reading score* Calculate the overall passing rate (overall average score), i.e. (avg. math score + avg. reading score)/2* Calculate the percentage of students with a passing math score (70 or greater)* Calculate the percentage of students with a passing reading score (70 or greater)* Create a dataframe to hold the above results* Optional: give the displayed data cleaner formatting<jupyter_code>#District sumary 
school_count = len(school_data_complete["school_name"].unique())
student_count = school_data_complete["Student ID"].count()

#Calculate the total Budget
total_budget = school_data["budget"].sum()

#Average scores
average_math_score = school_data_complete["math_score"].mean()
average_reading_score = school_data_complete["reading_score"].mean()
average_passing_rate = (average_math_score + average_reading_score)/2
#average_passing_rate
#Calculate Precentage Pass Rate
passing_math_count = school_data_complete[(school_data_complete["math_score"] >=70)].count()["student_name"]
#passing_math_count
passing_math_percentage = passing_math_count / float(student_count) *100

passing_reading_count = school_data_complete[(school_data_complete["reading_score"]>=70)].count()["student_name"]
passing_reading_percentage = passing_reading_count / float(student_count) *100
#passing_reading_percentage

district_summary = pd.DataFrame({
    "Total Schools" :[school_count],
    "Total Students" : [student_count],
    "Total Budget" : [total_budget],
    "Average Math Score" : [average_math_score],
    "Average Reading Score" : [average_reading_score],
    "% Passing Math" : [passing_math_percentage],
    "% Passing Reading" : [passing_reading_percentage],
    "% Overall Passing Rate" : [average_passing_rate]
})
#district_summary
district_summary["Average Math Score"] = district_summary["Average Math Score"].map("{:,.2f}".format)
district_summary["Average Reading Score"] = district_summary["Average Reading Score"].map("{:,.2f}".format)
district_summary["% Passing Math"] = district_summary["% Passing Math"].map("{:,.2f}".format)
district_summary["% Passing Reading"] = district_summary["% Passing Reading"].map("{:,.2f}".format)
district_summary["% Overall Passing Rate"] = district_summary["% Overall Passing Rate"].map("{:,.2f}".format)
district_summary<jupyter_output><empty_output><jupyter_text>School Summary * Create an overview table that summarizes key metrics about each school, including:  * School Name  * School Type  * Total Students  * Total School Budget  * Per Student Budget  * Average Math Score  * Average Reading Score  * % Passing Math  * % Passing Reading  * Overall Passing Rate (Average of the above two)  * Create a dataframe to hold the above results<jupyter_code>school_type = school_data.set_index(["school_name"])["type"]

#Calculate the total student count
per_school_counts = school_data_complete["school_name"].value_counts()

per_school_budget = school_data_complete.groupby(["school_name"]).mean()["budget"]
per_school_capita = per_school_budget/per_school_counts

#Calculate the average test scores
per_school_math = school_data_complete.groupby(["school_name"]).mean()["math_score"]
per_school_reading = school_data_complete.groupby(["school_name"]).mean()["reading_score"]
#Calculate passing scores by filtering
school_passing_math = school_data_complete[(school_data_complete["math_score"]>=70)]
school_passing_reading = school_data_complete[(school_data_complete["reading_score"]>=70)]

per_school_passing_math = school_passing_math.groupby(["school_name"]).count()["student_name"]/per_school_counts *100
per_school_passing_reading = school_passing_reading.groupby(["school_name"]).count()["student_name"]/per_school_counts *100

overall_passing_rate = (per_school_passing_math + per_school_passing_reading)/2

per_school_summary =pd.DataFrame ({
            "School_type" : school_type,
            "Total Students":per_school_counts,
            "Total School Budget":per_school_budget,
            "Per Student Budget":per_school_capita,
            "Average Math Score": per_school_math,
            "Average Reading Score":per_school_math,
            "% Passing Math": per_school_passing_math,
            "% Passing Reading" : per_school_passing_reading,
            "% Overall_passing_rate":overall_passing_rate
})
#per_school_summary
per_school_summary["Total School Budget"] = per_school_summary["Total School Budget"].map("${:,.2f}".format)
per_school_summary["Per Student Budget"] = per_school_summary["Per Student Budget"].map("${:,.2f}".format)

per_school_summary<jupyter_output><empty_output><jupyter_text>Top Performing Schools (By Passing Rate)<jupyter_code>#Sort and display the five top-performing schools
top_schools= per_school_summary.sort_values(["% Overall_passing_rate"],ascending=False)
top_schools.head(5)<jupyter_output><empty_output><jupyter_text>bottom Performing Schools (By Passing Rate)<jupyter_code>* Sort and display the five top-performing schools
bottom_schools= per_school_summary.sort_values(["% Overall_passing_rate"],ascending=True)
bottom_schools.head(5)<jupyter_output><empty_output><jupyter_text>Math Scores by Grade * Create a table that lists the average Reading Score for students of each grade level (9th, 10th, 11th, 12th) at each school.  * Create a pandas series for each grade. Hint: use a conditional statement.    * Group each series by school    * Combine the series into a dataframe    * Optional: give the displayed data cleaner formatting<jupyter_code>#Creat a date series for grade levels using conditions
ninth_grader = school_data_complete[(school_data_complete["grade"]=="9th")]
tenth_grader = school_data_complete[(school_data_complete["grade"]=="10th")]
eleventh_grader = school_data_complete[(school_data_complete["grade"]=="11th")]
twelveth_grader = school_data_complete[(school_data_complete["grade"]=="12th")]

ninth_grader_scores = ninth_grader.groupby(["school_name"]).mean()["math_score"]
tenth_grader_scores = ninth_grader.groupby(["school_name"]).mean()["math_score"]
eleventh_grader_scores = ninth_grader.groupby(["school_name"]).mean()["math_score"]
twelveth_grader_scores = ninth_grader.groupby(["school_name"]).mean()["math_score"]

scores_by_grade =pd.DataFrame({
        "9th":ninth_grader_scores,
        "10th":tenth_grader_scores,
        "11th" : eleventh_grader_scores,
        "12th" :twelveth_grader_scores
})
scores_by_grade<jupyter_output><empty_output><jupyter_text>Reading Score by Grade  * Perform the same operations as above for reading scores<jupyter_code>#Creat a date series for grade levels using conditions
ninth_grader = school_data_complete[(school_data_complete["grade"]=="9th")]
tenth_grader = school_data_complete[(school_data_complete["grade"]=="10th")]
eleventh_grader = school_data_complete[(school_data_complete["grade"]=="11th")]
twelveth_grader = school_data_complete[(school_data_complete["grade"]=="12th")]

ninth_grader_scores = ninth_grader.groupby(["school_name"]).mean()["reading_score"]
tenth_grader_scores = ninth_grader.groupby(["school_name"]).mean()["reading_score"]
eleventh_grader_scores = ninth_grader.groupby(["school_name"]).mean()["reading_score"]
twelveth_grader_scores = ninth_grader.groupby(["school_name"]).mean()["reading_score"]

scores_by_grade =pd.DataFrame({
        "9th":ninth_grader_scores,
        "10th":tenth_grader_scores,
        "11th" : eleventh_grader_scores,
        "12th" :twelveth_grader_scores
})
scores_by_grade<jupyter_output><empty_output><jupyter_text>Scores by School Spending * Create a table that breaks down school performances based on average Spending Ranges (Per Student). Use 4 reasonable bins to group school spending. Include in the table each of the following:  * Average Math Score  * Average Reading Score  * % Passing Math  * % Passing Reading  * Overall Passing Rate (Average of the above two)<jupyter_code># Sample bins. Feel free to create your own bins.
spending_bins = [0, 585, 615, 645, 675]
group_names = ["<$585", "$585-615", "$615-645", "$645-675"]
#Categorized the Spending based on bins
per_school_summary["Spending Ranges(Per Student)"] = pd.cut(per_school_capita,spending_bins,labels= group_names)

spending_math_scores =per_school_summary.groupby(["Spending Ranges(Per Student)"]).mean()["Average Math Score"]
spending_reading_scores =per_school_summary.groupby(["Spending Ranges(Per Student)"]).mean()["Average Reading Score"]
spending_passing_math =per_school_summary.groupby(["Spending Ranges(Per Student)"]).mean()["% Passing Math"]
spending_passing_reading =per_school_summary.groupby(["Spending Ranges(Per Student)"]).mean()["% Passing Reading"]
overall_passing_rate = (spending_passing_math+spending_passing_reading)/2

spending_summary = pd.DataFrame({
        "Average Math Score": spending_math_scores,
        "Average Reading Score" :spending_reading_scores,
        "% Passing Math": spending_passing_reading,
        "% Passing Reading" : spending_passing_reading,
        "% overall_passing_rate":overall_passing_rate
})

spending_summary<jupyter_output><empty_output><jupyter_text>Scores by School Size * Perform the same operations as above, based on school size.<jupyter_code># Sample bins. Feel free to create your own bins.
size_bins = [0, 1000, 2000, 5000]
group_names = ["Small (<1000)", "Medium (1000-2000)", "Large (2000-5000)"]
per_school_summary["School Size"] = pd.cut(per_school_summary["Total Students"],size_bins,labels=group_names)

size_math_scores = per_school_summary.groupby(["School Size"]).mean()["Average Math Score"]
size_reading_scores = per_school_summary.groupby(["School Size"]).mean()["Average Reading Score"]
size_passing_math = per_school_summary.groupby(["School Size"]).mean()["% Passing Math"]
size_passing_reading = per_school_summary.groupby(["School Size"]).mean()["% Passing Reading"]
overall_passing_rate = (size_passing_math + size_passing_reading)/2

size_summary = pd.DataFrame({
    "Average Math Score" : size_math_scores,
    "average Reading Score" : size_reading_scores,
    "% Passing Math" : size_passing_math,
    "% Passing Reading" : size_passing_reading,
    " % overall_passing_rate" : overall_passing_rate
})

size_summary<jupyter_output><empty_output><jupyter_text>Scores by School Type * Perform the same operations as above, based on school type.<jupyter_code>type_math_scores = per_school_summary.groupby(["School_type"]).mean()["Average Math Score"]
type_reading_scores = per_school_summary.groupby(["School_type"]).mean()["Average Reading Score"]
type_passing_math = per_school_summary.groupby(["School_type"]).mean()["% Passing Math"]
type_passing_reading = per_school_summary.groupby(["School_type"]).mean()["% Passing Reading"]
overall_passing_rate = (type_passing_math + type_passing_reading)/ 2

type_summary =pd.DataFrame({
    "Average Math Score" : type_math_scores,
    "average Reading Score" : type_reading_scores,
    "% Passing Math" : type_passing_math,
    "% Passing Reading" : type_passing_reading,
    " % overall_passing_rate" : overall_passing_rate
    
})
type_summary<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.115, 'fraction_numerical': 0.011833333333333333, 'mean_word_length': 7.282263630089717, 'pattern_counts': {'":': 14, '<': 43, '<?xml version=': 0, '>': 45, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '319629', 'license': 'MIT', 'n_tokens_mistral': 3993, 'n_tokens_neox': 3719, 'n_words': 814, 'path': 'PyCitySchools_starter.ipynb', 'repo_name': 'kyawlynn001/Higch_School_Success_Pandas', 'size': '12000'}