<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 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"])
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 percentage of students with a passing math score (70 or greater)* Calculate the percentage of students with a passing reading score (70 or greater)* Calculate the percentage of students who passed math **and** reading (% Overall Passing)* Create a dataframe to hold the above results* Optional: give the displayed data cleaner formatting<jupyter_code># Calculate the total number of schools
district_school_count = school_data_complete["School ID"].nunique()

# Calculate the total number of students
district_student_count = school_data_complete["Student ID"].nunique()

# Calculate the total budget
district_budget_total = school_data["budget"].sum()
district_budget_total = "${0:,.2f}".format(district_budget_total)

# Calculate the average math score
district_avg_math_score = school_data_complete["math_score"].mean()

# Calculate the average reading score
district_avg_rdg_score = school_data_complete["reading_score"].mean()

# Calculate the percentage of students with a passing math score (70 or greater)
district_percent_passing_math = (school_data_complete.loc[school_data_complete["math_score"] >= 70]["math_score"].count()/district_student_count)*100

# Calculate the percentage of students with a passing reading score (70 or greater)
district_percent_passing_rdg = (school_data_complete.loc[school_data_complete["reading_score"] >= 70]["reading_score"].count()/district_student_count)*100

# Calculate the percentage of students who passed math and reading (% Overall Passing)
district_percent_passing_overall = school_data_complete[(school_data_complete["math_score"] >= 70) & (school_data_complete["reading_score"] >= 70)]["Student ID"].count()/district_student_count*100


# Create a dataframe to hold the above results
district_summary = pd.DataFrame({"Total Schools": [district_school_count], 
                                 "Total Students": [district_student_count],
                                 "Total Budget": [district_budget_total], 
                                 "Average Math Scores": [district_avg_math_score], 
                                 "Average Reading Scores": [district_avg_rdg_score], 
                                 "% Passing Math": [district_percent_passing_math], 
                                 "% Passing Reading": [district_percent_passing_rdg], 
                                 "% Passing Overall": [district_percent_passing_overall]})
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 (The percentage of students that passed math **and** reading.)  * Create a dataframe to hold the above results<jupyter_code># Create an overview table that summarizes key metrics about each school, including:
# School Name
# School Type
school_type = school_data_complete.groupby("school_name").first()["type"]

# Total Students
school_students_total = school_data_complete.groupby("school_name").nunique()["Student ID"]

# Total School Budget
school_budget = school_data_complete.groupby("school_name").first()["budget"]

# Per Student Budget
school_budget_student = school_budget/students_total

# Average Math Score
school_avg_math_score = school_data_complete.groupby("school_name").mean()["math_score"]

# Average Reading Score
school_avg_rdg_score = school_data_complete.groupby("school_name").mean()["reading_score"]

# % Passing Math
school_passing_math = school_data_complete[(school_data_complete["math_score"] >= 70)]
school_total_passing_math = school_passing_math.groupby("school_name").count()["Student ID"]
school_percent_passing_math = (school_total_passing_math/students_total)*100

# % Passing Reading
school_passing_rdg = school_data_complete[(school_data_complete["reading_score"] >= 70)]
school_total_passing_rdg = school_passing_rdg.groupby("school_name").count()["Student ID"]
school_percent_passing_rdg = (school_total_passing_rdg/students_total)*100

# % Overall Passing (The percentage of students that passed math and reading.)
school_percent_passing_overall = school_data_complete[(school_data_complete["reading_score"] >= 70) & (school_data_complete["math_score"] >= 70)].groupby("school_name")["Student ID"].count()/school_students_total*100


# Create a dataframe to hold the above results
school_summary = pd.DataFrame({"School Type": school_type,
                               "Total Students": students_total,
                               "Total Budget": school_budget,
                               "Budget per Student": school_budget_student,
                               "Average Math Scores": school_avg_math_score,
                               "Average Reading Scores": school_avg_rdg_score,
                               "% Passing Math": school_percent_passing_math,
                               "% Passing Reading": school_percent_passing_rdg,
                               "% Passing Overall": school_percent_passing_overall})

school_summary["Total Budget"] = school_summary["Total Budget"].map("${:,.2f}".format)
school_summary["Budget per Student"] = school_summary["Budget per Student"].map("${:,.2f}".format)

school_summary<jupyter_output><empty_output><jupyter_text>Top Performing Schools (By % Overall Passing) * Sort and display the top five performing schools by % overall passing.<jupyter_code>five_best_schools = school_summary.sort_values(by="% Passing Overall", ascending=False)
five_best_schools.head()<jupyter_output><empty_output><jupyter_text>Bottom Performing Schools (By % Overall Passing) * Sort and display the five worst-performing schools by % overall passing.<jupyter_code>five_worst_schools = school_summary.sort_values(by="% Passing Overall")
five_worst_schools.head()<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 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
math_scores_9th = school_data_complete.loc[school_data_complete["grade"] == "9th"].groupby("school_name")["math_score"].mean()             
math_scores_10th = school_data_complete.loc[school_data_complete["grade"] == "10th"].groupby("school_name")["math_score"].mean()             
math_scores_11th = school_data_complete.loc[school_data_complete["grade"] == "11th"].groupby("school_name")["math_score"].mean()             
math_scores_12th = school_data_complete.loc[school_data_complete["grade"] == "12th"].groupby("school_name")["math_score"].mean()             


# Combine the series into a dataframe
math_scores_by_grade = pd.DataFrame({"9th": math_scores_9th,
                                   "10th": math_scores_10th,
                                   "11th": math_scores_11th,
                                   "12th": math_scores_12th})
math_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 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
rdg_scores_9th = school_data_complete.loc[school_data_complete["grade"] == "9th"].groupby("school_name")["reading_score"].mean()             
rdg_scores_10th = school_data_complete.loc[school_data_complete["grade"] == "10th"].groupby("school_name")["reading_score"].mean()             
rdg_scores_11th = school_data_complete.loc[school_data_complete["grade"] == "11th"].groupby("school_name")["reading_score"].mean()             
rdg_scores_12th = school_data_complete.loc[school_data_complete["grade"] == "12th"].groupby("school_name")["reading_score"].mean()             


# Combine the series into a dataframe
rdg_scores_by_grade = pd.DataFrame({"9th": rdg_scores_9th,
                                   "10th": rdg_scores_10th,
                                   "11th": rdg_scores_11th,
                                   "12th": rdg_scores_12th})
rdg_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># Create a table that breaks down school performances based on average Spending Ranges (Per Student).
# Use 4 reasonable bins to group school spending. 
spend_bins = [0, 584.99, 629.99, 644.99, 680]
spend_group_names = ["<$585", "$585-630", "$630-645", "$645-680"]

school_data_complete["Spending Ranges (Per Student)"] = pd.cut(school_data_complete["budget"]/school_data_complete["size"], spend_bins, labels = spend_group_names)
spending = school_data_complete.groupby("Spending Ranges (Per Student)")

# Average Math Score
spend_avg_math_score = round(spending["math_score"].mean(),2)

# Average Reading Score
spend_avg_rdg_score = round(spending["reading_score"].mean(),2)

# % Passing Math
spend_percent_passing_math = round(school_data_complete[school_data_complete["math_score"] >= 70].groupby("Spending Ranges (Per Student)")["Student ID"].count()/spending["Student ID"].count()*100,2)

# % Passing Reading
spend_percent_passing_rdg = round(school_data_complete[school_data_complete["reading_score"] >= 70].groupby("Spending Ranges (Per Student)")["Student ID"].count()/spending["Student ID"].count()*100,2)

# Overall Passing Rate (Average of the above two)
spend_percent_passing_overall = round((school_data_complete[(school_data_complete["reading_score"] >= 70) & (school_data_complete["math_score"] >= 70)].groupby("Spending Ranges (Per Student)")["Student ID"].count())/spending["Student ID"].count()*100,2)

    
scores_by_spending = pd.DataFrame({"Average Math Scores": spend_avg_math_score,
                                   "Average Reading Score": spend_avg_rdg_score,
                                   "% Passing Math": spend_percent_passing_math,
                                   "% Passing Reading": spend_percent_passing_rdg,
                                   "% Passing Overall": spend_percent_passing_overall})
scores_by_spending

#My numbers ended up slightly off for this one and I'm not exactly sure why - they're generally the same, but slightly off<jupyter_output><empty_output><jupyter_text>Scores by School Size * Perform the same operations as above, based on school size.<jupyter_code># Create a table that breaks down school performances based on average School Size 
# Use reasonable bins to group school size. 
size_bins = [0, 999.99, 1999.99, 5000]
size_group_names = ["Small (<1000)", "Medium (1000-2000)", "Large (2000-5000)"]

school_data_complete["School Size"] = pd.cut(school_data_complete["size"], size_bins, labels = size_group_names)
sizing = school_data_complete.groupby("School Size")

# Average Math Score
size_avg_math_score = sizing["math_score"].mean()

# Average Reading Score
size_avg_rdg_score = sizing["reading_score"].mean()

# % Passing Math
size_percent_passing_math = school_data_complete[school_data_complete["math_score"] >= 70].groupby("School Size")["Student ID"].count()/sizing["Student ID"].count()*100

# % Passing Reading
size_percent_passing_rdg = school_data_complete[school_data_complete["reading_score"] >= 70].groupby("School Size")["Student ID"].count()/sizing["Student ID"].count()*100

# Overall Passing Rate (Average of the above two)
size_percent_passing_overall = school_data_complete[(school_data_complete["reading_score"] >= 70) & (school_data_complete["math_score"] >= 70)].groupby("School Size")["Student ID"].count()/sizing["Student ID"].count()*100
    
    
scores_by_sizing = pd.DataFrame({"Average Math Scores": size_avg_math_score,
                                 "Average Reading Score": size_avg_rdg_score,
                                 "% Passing Math": size_percent_passing_math,
                                 "% Passing Reading": size_percent_passing_rdg,
                                 "% Passing Overall": size_percent_passing_overall})
scores_by_sizing

#My numbers ended up slightly off for this one and I'm not exactly sure why - they're generally the same, but slightly off<jupyter_output><empty_output><jupyter_text>Scores by School Type * Perform the same operations as above, based on school type<jupyter_code># School Type
school_type_scores = school_data_complete.groupby("type")

# Total Students
type_students_total = school_data_complete.groupby("type").nunique()["Student ID"]

# Average Math Score
type_avg_math_score = school_data_complete.groupby("type").mean()["math_score"]

# Average Reading Score
type_avg_rdg_score = school_data_complete.groupby("type").mean()["reading_score"]

# % Passing Math
type_passing_math = school_data_complete[(school_data_complete["math_score"] >= 70)]
type_total_passing_math = type_passing_math.groupby("type").count()["Student ID"]
type_percent_passing_math = (type_total_passing_math/type_students_total)*100

# % Passing Reading
type_passing_rdg = school_data_complete[(school_data_complete["reading_score"] >= 70)]
type_total_passing_rdg = type_passing_rdg.groupby("type").count()["Student ID"]
type_percent_passing_rdg = (type_total_passing_rdg/type_students_total)*100

# % Overall Passing (The percentage of students that passed math and reading.)
type_percent_passing_overall = school_data_complete[(school_data_complete["reading_score"] >= 70) & (school_data_complete["math_score"] >= 70)].groupby("type")["Student ID"].count()/type_students_total*100


scores_by_type = pd.DataFrame({"Average Math Scores": type_avg_math_score,
                               "Average Reading Score": type_avg_rdg_score,
                               "% Passing Math": type_percent_passing_math,
                               "% Passing Reading": type_percent_passing_rdg,
                               "% Passing Overall": type_percent_passing_overall})
scores_by_type

#My numbers ended up slightly off for this one and I'm not exactly sure why - they're generally the same, but slightly off<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10182207931404073, 'fraction_numerical': 0.016203265872265307, 'mean_word_length': 4.608910891089109, 'pattern_counts': {'":': 40, '<': 43, '<?xml version=': 0, '>': 61, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 27, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '34648', 'license': 'ADSL', 'n_tokens_mistral': 4866, 'n_tokens_neox': 4510, 'n_words': 1140, 'path': 'PyCitySchools/PyCitySchools_FINAL.ipynb', 'repo_name': 'annelaframboise/pandas-challenge', 'size': '15861'}