<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"])<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 Summary
school_count = len(school_data_complete["school_name"].unique())
student_count = school_data_complete["Student ID"].count()

#Calculate 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()
overall_passing_rate = (average_math_score + average_reading_score)/2

#Calculate Percentage pass rate

passing_math_count = school_data_complete[(school_data_complete["math_score"]>=70)].count()["student_name"]
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


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": [overall_passing_rate]
})

district_summary["Total Students"]= district_summary["Total Students"].map("${:,}".format)
district_summary["Total Budget"]= district_summary["Total Budget"].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  Top Performing Schools (By Passing Rate) * Sort and display the top five schools in overall passing rate<jupyter_code>#school summary
school_type = school_data.set_index(["school_name"])["type"]

#Calculate the total student count

per_school_counts = school_data_complete["school_name"].value_counts()

#calculate total school budget per capita spending

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

#calculate 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_reading,
    "% Passing Math":per_school_passing_math,
    "% Passing Reading": per_school_passing_reading,
    "% Overall Passing Rate": overall_passing_rate
})


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>Bottom Performing Schools (By Passing Rate) * Sort and display the five worst-performing schools<jupyter_code>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>#Create a data table for grade levels using conditionals

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"]
twelfth_grader = school_data_complete[(school_data_complete["grade"]) == "12th"]

ninth_grader_scores = ninth_grader.groupby(["school_name"]).mean()["math_score"]
tenth_grader_scores = tenth_grader.groupby(["school_name"]).mean()["math_score"]
eleventh_grader_scores = eleventh_grader.groupby(["school_name"]).mean()["math_score"]
twelfth_grader_scores = twelfth_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":twelfth_grader_scores})

scores_by_grade.index.name = None
scores_by_grade<jupyter_output><empty_output><jupyter_text>Reading Score by Grade  * Perform the same operations as above for reading scores<jupyter_code>#Create a data table for grade levels using conditionals

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"]
twelfth_grader = school_data_complete[(school_data_complete["grade"]) == "12th"]

ninth_grader_scores = ninth_grader.groupby(["school_name"]).mean()["reading_score"]
tenth_grader_scores = tenth_grader.groupby(["school_name"]).mean()["reading_score"]
eleventh_grader_scores = eleventh_grader.groupby(["school_name"]).mean()["reading_score"]
twelfth_grader_scores = twelfth_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":twelfth_grader_scores})

scores_by_grade.index.name = None
scores_by_grade

#Create a data table for grade levels using conditionals

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"]
twelfth_grader = school_data_complete[(school_data_complete["grade"]) == "12th"]

ninth_grader_scores = ninth_grader.groupby(["school_name"]).mean()["math_score"]
tenth_grader_scores = tenth_grader.groupby(["school_name"]).mean()["math_score"]
eleventh_grader_scores = eleventh_grader.groupby(["school_name"]).mean()["math_score"]
twelfth_grader_scores = twelfth_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":twelfth_grader_scores})

scores_by_grade.index.name = None
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"]
 #Categorize 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_math,
    " % 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.11598797041372023, 'fraction_numerical': 0.012273429244899619, 'mean_word_length': 7.935366739288308, 'pattern_counts': {'":': 44, '<': 39, '<?xml version=': 0, '>': 41, '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': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '18133', 'license': 'MIT', 'n_tokens_mistral': 4129, 'n_tokens_neox': 3816, 'n_words': 849, 'path': 'PyCitySchools.ipynb', 'repo_name': 'nazim806/pandas-challenge', 'size': '12303'}