<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
import numpy as np

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

# Read School and Student Data File and store into Pandas Data Frames
students_df = pd.read_csv(filepath_students)
schools_df = pd.read_csv(filepath_schools)

# Combine the data into a single dataset
school_data_complete_df = pd.merge(students_df, schools_df, how="left", on=["school_name", "school_name"])

school_data_complete_df.head()
### District Metric Summary

# Calculate total schools in our district
total_schools = len(schools_df.index)
total_schools
# Calculate total students in our district
total_students_district = schools_df["size"].sum()
total_students_district
# Calculate total budget in our district
total_budget_district = schools_df["budget"].sum()
total_budget_district
# Calculate average math score
avg_math_score_district = students_df["math_score"].mean()
avg_math_score_district
# Calculate average reading score
avg_reading_score_district = students_df["reading_score"].mean()
avg_reading_score_district

# Create df with students that scored 60% or better in math
students_passing_math_district = students_df.loc[students_df["math_score"] >= 70]
# Find the total students that passed math
total_students_passing_math_district = len(students_passing_math_district.index)
# Calculate % passing math                                                 
perc_students_pass_math_district = (total_students_passing_math_district / total_students_district) * 100
perc_students_pass_math_district
# Calculate % passing reading
students_passing_reading_district = students_df.loc[students_df["reading_score"] >= 70]
total_students_passing_reading_district = len(students_passing_reading_district.index)

perc_students_pass_reading_district = (total_students_passing_reading_district / total_students_district) * 100
perc_students_pass_reading_district
# Calculate overall passing rate
overall_passing_rate_district = (perc_students_pass_math_district + perc_students_pass_reading_district) / 2
overall_passing_rate_district
# Create District Metric Summary table
district_metric_summary_table_values = [total_schools, total_students_district, total_budget_district,
                                        avg_math_score_district, avg_reading_score_district, 
                                        perc_students_pass_math_district, perc_students_pass_reading_district,
                                        overall_passing_rate_district]
district_metric_summary_table_columns = ['Total Number of Schools \n in District', 'Total Number of Students \n in District',
                                         'Total District Budget', 'Average Math Score in District', 'Average Reading Score in District', 
                                         '% of Students Passing Math', '% of Students Passing Reading', 'Overall District Passing Rate']
district_metric_summary_table_df = pd.DataFrame([district_metric_summary_table_values], columns = district_metric_summary_table_columns)

# Format Columns
district_metric_summary_table_df["Total Number of Students \n in District"] = district_metric_summary_table_df["Total Number of Students \n in District"].map("{:,}".format)
district_metric_summary_table_df["Total District Budget"] = district_metric_summary_table_df["Total District Budget"].map("${:,.2f}".format)

district_metric_summary_table_df.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>### School Metric Sumamry

# copying schools_df into a new dataframe
school_summary_metric_df = schools_df
# remove school ID column
school_summary_metric_df = school_summary_metric_df.drop(columns = ['School ID'])
# rename columns
school_summary_metric_df = school_summary_metric_df.rename(columns = {'school_name' : 'School Name',
                                                                     'type' : 'School Type',
                                                                     'size' : 'Total Students',
                                                                     'budget' : 'Total School Budget'})
#school_summary_metric_df = school_summary_metric_df.set_index("School Name")
#school_summary_metric_df = school_summary_metric_df.sort_index()

school_summary_metric_df.head(5)
# create a list of per student budget for each school
per_student_budget = school_summary_metric_df['Total School Budget'] / school_summary_metric_df['Total Students']

# Create 'Per Student Budget' column and populate with the created list
school_summary_metric_df['Per Student Budget'] = per_student_budget

school_summary_metric_df.columns
# Find the average math score for each school
grouped_by_school_name = students_df.groupby("school_name")

# Find number of students in each school
total_students_per_school = grouped_by_school_name.count()

# Create dataframe with total number of students in each school
total_students_per_school_df = total_students_per_school.reset_index()

# Create new dataframe with desired columns
simplified_total_students_per_school_df = total_students_per_school_df.loc[:, ["Student ID", "school_name"]]

# Rename column names to match
simplified_total_students_per_school_df = simplified_total_students_per_school_df.rename(columns = 
                                        {"school_name":"School Name"
                                         , "Student ID" : "Student Count"})
# Merge with summary dataframe
school_summary_metric_df = pd.merge(school_summary_metric_df, simplified_total_students_per_school_df
                                    , on = "School Name")

# Create list of sum of math score per school
sum_of_math_score_school = grouped_by_school_name["math_score"].sum()

# Create dataframe with sum of math score per school
sum_of_math_score_school_df = sum_of_math_score_school.reset_index()

# Rename columns to match
sum_of_math_score_school_df = sum_of_math_score_school_df.rename(columns = 
                                        {"school_name":"School Name"
                                         , "math_score" : "Sum of Math Scores"})

# Merge with summary dataframe
school_summary_metric_df = pd.merge(school_summary_metric_df, sum_of_math_score_school_df
                                    , on = "School Name")

# calculate average math score per school and create column
school_summary_metric_df["Average \n Math \n Score"] = school_summary_metric_df["Sum of Math Scores"] / school_summary_metric_df["Student Count"]

# Create list of sum of reading score per school
sum_of_reading_score_school = grouped_by_school_name["reading_score"].sum()

# Create dataframe with sum of reading score per school
sum_of_reading_score_school_df = sum_of_reading_score_school.reset_index()

# Create new dataframe with desired columns
simplified_sum_of_reading_score_school_df = sum_of_reading_score_school_df.loc[:, ["reading_score", "school_name"]]

# Rename columns to match
simplified_sum_of_reading_score_school_df = simplified_sum_of_reading_score_school_df.rename(columns = 
                                            {"school_name":"School Name"
                                             , "reading_score" : "Sum of Reading Scores"})

# Merge with summary dataframe
school_summary_metric_df = pd.merge(school_summary_metric_df, simplified_sum_of_reading_score_school_df
                                    , on = "School Name")

# calculate average math score per school and create column
school_summary_metric_df["Average \n Reading \n Score"] = school_summary_metric_df["Sum of Reading Scores"] / school_summary_metric_df["Student Count"]

school_summary_metric_df.head(2)
# create new dataframe with all students with pasing scores
passing_math_score_df = students_df.loc[students_df["math_score"] >= 70, :]
passing_reading_score_df = students_df.loc[students_df["reading_score"] >= 70, :]

# group the new dataframes by school name
pms_group_by_school = passing_math_score_df.groupby("school_name")
prs_group_by_school = passing_reading_score_df.groupby("school_name")

# create dataframes with new group object
number_students_pms_school = pms_group_by_school.count()
number_students_prs_school = prs_group_by_school.count()

# reset indices for good practice
number_students_pms_school_df = number_students_pms_school.reset_index()
number_students_prs_school_df = number_students_prs_school.reset_index()

# simplify dataframe by columns
s_number_students_pms_school_df = number_students_pms_school_df.loc[:, ['school_name', 'math_score']]
s_number_students_prs_school_df = number_students_prs_school_df.loc[:, ['school_name', 'reading_score']]
                                                   
# rename columns to match
s_number_students_pms_school_df = s_number_students_pms_school_df.rename(columns = 
                                {"school_name":"School Name", "math_score":"Total students \n pass math"})
s_number_students_prs_school_df = s_number_students_prs_school_df.rename(columns = 
                                {"school_name":"School Name", "reading_score":"Total students \n pass reading"})

# merge dataframs on "School Name" column
school_summary_metric_df = pd.merge(school_summary_metric_df, s_number_students_pms_school_df, on = "School Name")
school_summary_metric_df = pd.merge(school_summary_metric_df, s_number_students_prs_school_df, on = "School Name")
                                                                    
# use dataframe values to calculate % passing math and reading per school
school_summary_metric_df["% \n Passing \n Math"] = (school_summary_metric_df["Total students \n pass math"]
                                                    / school_summary_metric_df["Student Count"]) * 100
school_summary_metric_df["% \n Passing \n Reading"] = (school_summary_metric_df["Total students \n pass reading"]
                                                    / school_summary_metric_df["Student Count"]) * 100
                                                                   
                                                                
# Create Column of Overal Passing Rate
school_summary_metric_df["% Overall \n Passing \n Rate"] = (school_summary_metric_df["% \n Passing \n Math"]
                                                    + school_summary_metric_df["% \n Passing \n Reading"]) / 2

# remove Student Count, Sum of Math Scores columns, Sum of Reading Scores, and two total math/reading columns

school_summary_metric_df = school_summary_metric_df.drop(["Student Count", 
                                                          "Sum of Math Scores",
                                                          "Sum of Reading Scores",
                                                          "Total students \n pass math",
                                                          "Total students \n pass reading"],
                                                          axis=1)
# make dataframe copy

raw_school_summary_metric_df = school_summary_metric_df.copy()
raw_school_summary_metric_df.dtypes
# reformat

school_summary_metric_df["Total School Budget"] = school_summary_metric_df["Total School Budget"].map("${:,.2f}".format)
school_summary_metric_df["Per Student Budget"] = school_summary_metric_df["Per Student Budget"].map("${:,.2f}".format)

school_summary_metric_df.head(2)<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># make school name the index
school_summary_metric_df = school_summary_metric_df.set_index("School Name")
# sort and display the top five schools in overall passing rate, descending order

sort_by_top_performing_schools_df = school_summary_metric_df.sort_values("% Overall \n Passing \n Rate", ascending=False)

sort_by_top_performing_schools_df.head(5)<jupyter_output><empty_output><jupyter_text>Bottom Performing Schools (By Passing Rate) * Sort and display the five worst-performing schools<jupyter_code># sort and display the bottom five schools in overall passing rate, ascending order

sort_by_worst_performing_schools_df = school_summary_metric_df.sort_values("% Overall \n Passing \n Rate")

sort_by_worst_performing_schools_df.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 panda series for each grade
grade_9th_df = students_df.loc[students_df["grade"] == "9th", :]
grade_10th_df = students_df.loc[students_df["grade"] == "10th", :]
grade_11th_df = students_df.loc[students_df["grade"] == "11th", :]
grade_12th_df = students_df.loc[students_df["grade"] == "12th", :]

# Group each panda series by school_name
grade_9th_group_by_school = grade_9th_df.groupby("school_name")
grade_10th_group_by_school = grade_10th_df.groupby("school_name")
grade_11th_group_by_school = grade_11th_df.groupby("school_name")
grade_12th_group_by_school = grade_12th_df.groupby("school_name")

# Calculate the average math score for each grade at each school and store into dataframe
average_math_score_9th_school = grade_9th_group_by_school["math_score"].mean()
average_math_score_10th_school = grade_10th_group_by_school["math_score"].mean()
average_math_score_11th_school = grade_11th_group_by_school["math_score"].mean()
average_math_score_12th_school = grade_12th_group_by_school["math_score"].mean()

# Convert into Pandas
average_math_score_9th_school = pd.DataFrame(average_math_score_9th_school)
average_math_score_10th_school = pd.DataFrame(average_math_score_10th_school)
average_math_score_11th_school = pd.DataFrame(average_math_score_11th_school)
average_math_score_12th_school = pd.DataFrame(average_math_score_12th_school)

# Merge Pandas and rename columns
math_scores_by_grade_df = pd.merge(average_math_score_9th_school, average_math_score_10th_school, left_index=True, right_index=True)
math_scores_by_grade_df = math_scores_by_grade_df.rename(columns = {"math_score_x" : "9th Grade", "math_score_y" : "10th Grade"})
math_scores_by_grade_df = pd.merge(math_scores_by_grade_df, average_math_score_11th_school, left_index=True, right_index=True)
math_scores_by_grade_df = pd.merge(math_scores_by_grade_df, average_math_score_12th_school, left_index=True, right_index=True)
math_scores_by_grade_df = math_scores_by_grade_df.rename(columns = {"math_score_x" : "11th Grade", "math_score_y" : "12th Grade"})

math_scores_by_grade_df.head()<jupyter_output><empty_output><jupyter_text>Reading Score by Grade  * Perform the same operations as above for reading scores<jupyter_code># Calculate the average reading score for each grade at each school and store into dataframe
average_reading_score_9th_school = grade_9th_group_by_school["reading_score"].mean()
average_reading_score_10th_school = grade_10th_group_by_school["reading_score"].mean()
average_reading_score_11th_school = grade_11th_group_by_school["reading_score"].mean()
average_reading_score_12th_school = grade_12th_group_by_school["reading_score"].mean()

# Convert into Pandas
average_reading_score_9th_school = pd.DataFrame(average_reading_score_9th_school)
average_reading_score_10th_school = pd.DataFrame(average_reading_score_10th_school)
average_reading_score_11th_school = pd.DataFrame(average_reading_score_11th_school)
average_reading_score_12th_school = pd.DataFrame(average_reading_score_12th_school)

# Merge Pandas and rename columns
reading_scores_by_grade_df = pd.merge(average_reading_score_9th_school, average_reading_score_10th_school, left_index=True, right_index=True)
reading_scores_by_grade_df = reading_scores_by_grade_df.rename(columns = {"reading_score_x" : "9th Grade", "reading_score_y" : "10th Grade"})
reading_scores_by_grade_df = pd.merge(reading_scores_by_grade_df, average_reading_score_11th_school, left_index=True, right_index=True)
reading_scores_by_grade_df = pd.merge(reading_scores_by_grade_df, average_reading_score_12th_school, left_index=True, right_index=True)
reading_scores_by_grade_df = reading_scores_by_grade_df.rename(columns = {"reading_score_x" : "11th Grade", "reading_score_y" : "12th Grade"})

reading_scores_by_grade_df.head()<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"]
# add bin column
raw_school_summary_metric_df["Spending Ranges (Per Student)"] = pd.cut(raw_school_summary_metric_df["Per Student Budget"], spending_bins, labels = group_names)

# groupby the bins
group_by_bins_df = raw_school_summary_metric_df.groupby("Spending Ranges (Per Student)")

# create column lists by using the mean function on the groupby dataframe
## Average Math score
math_score_l = group_by_bins_df["Average \n Math \n Score"].mean()
### convert to dataframe
math_score_df = pd.DataFrame(math_score_l)
## Average Reading Score
reading_score_l = group_by_bins_df["Average \n Reading \n Score"].mean()
### convert to dataframe
reading_score_df = pd.DataFrame(reading_score_l)

## % passing math
math_pass_l = group_by_bins_df["% \n Passing \n Math"].mean()
### convert to dataframe
math_pass_df = pd.DataFrame(math_pass_l)
## % passing reading
reading_pass_l = group_by_bins_df["% \n Passing \n Reading"].mean()
### convert to dataframe
reading_pass_df = pd.DataFrame(reading_pass_l)

## Overall Passing Rate Dataframe
overall_pass_l = (math_pass_df["% \n Passing \n Math"] + reading_pass_df["% \n Passing \n Reading"]) / 2
overall_pass_df = pd.DataFrame(overall_pass_l)
overall_pass_df = overall_pass_df.rename(columns = {0 : "Overall \n Pass \n Percentage"})

## Merge databases together
final_bin_df = pd.merge(math_score_df, reading_score_df, left_index=True, right_index=True)
final_bin_df = pd.merge(final_bin_df, math_pass_df, left_index=True, right_index=True)
final_bin_df = pd.merge(final_bin_df, reading_pass_df, left_index=True, right_index=True)
final_bin_df = pd.merge(final_bin_df, overall_pass_df, left_index=True, right_index=True)

final_bin_df.head(4)<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)"]
# add bin column
raw_school_summary_metric_df["Scores by School Size"] = pd.cut(raw_school_summary_metric_df["Total Students"], size_bins, labels = group_names)

# grpby the bins
grp_by_bins_df = raw_school_summary_metric_df.groupby("Scores by School Size")

# create column lists by using the mean function on the groupby dataframe
## Average Math score
math_score_l_1 = grp_by_bins_df["Average \n Math \n Score"].mean()
### convert to dataframe
math_score_df_1 = pd.DataFrame(math_score_l_1)
## Average Reading Score
reading_score_l_1 = grp_by_bins_df["Average \n Reading \n Score"].mean()
### convert to dataframe
reading_score_df_1 = pd.DataFrame(reading_score_l_1)

## % passing math
math_pass_l_1 = grp_by_bins_df["% \n Passing \n Math"].mean()
### convert to dataframe
math_pass_df_1 = pd.DataFrame(math_pass_l_1)
## % passing reading
reading_pass_l_1 = grp_by_bins_df["% \n Passing \n Reading"].mean()
### convert to dataframe
reading_pass_df_1 = pd.DataFrame(reading_pass_l_1)

## Overall Passing Rate Dataframe
overall_pass_l_1 = (math_pass_df_1["% \n Passing \n Math"] + reading_pass_df_1["% \n Passing \n Reading"]) / 2
overall_pass_df_1 = pd.DataFrame(overall_pass_l_1)
overall_pass_df_1 = overall_pass_df_1.rename(columns = {0 : "Overall \n Pass \n Percentage"})

## Merge databases together
final_bin_df_1 = pd.merge(math_score_df_1, reading_score_df_1, left_index=True, right_index=True)
final_bin_df_1 = pd.merge(final_bin_df_1, math_pass_df_1, left_index=True, right_index=True)
final_bin_df_1 = pd.merge(final_bin_df_1, reading_pass_df_1, left_index=True, right_index=True)
final_bin_df_1 = pd.merge(final_bin_df_1, overall_pass_df_1, left_index=True, right_index=True)

final_bin_df_1.head()<jupyter_output><empty_output><jupyter_text>Scores by School Type * Perform the same operations as above, based on school type.<jupyter_code># g by school type
g_by_school_type_df = raw_school_summary_metric_df.groupby("School Type")

# create column lists by using the mean function on the groupby dataframe
## Average Math score
math_score_l_2 = g_by_school_type_df["Average \n Math \n Score"].mean()
### convert to dataframe
math_score_df_2 = pd.DataFrame(math_score_l_2)
## Average Reading Score
reading_score_l_2 = g_by_school_type_df["Average \n Reading \n Score"].mean()
### convert to dataframe
reading_score_df_2 = pd.DataFrame(reading_score_l_2)

## % passing math
math_pass_l_2 = g_by_school_type_df["% \n Passing \n Math"].mean()
### convert to dataframe
math_pass_df_2 = pd.DataFrame(math_pass_l_2)
## % passing reading
reading_pass_l_2 = g_by_school_type_df["% \n Passing \n Reading"].mean()
### convert to dataframe
reading_pass_df_2 = pd.DataFrame(reading_pass_l_2)

## Overall Passing Rate Dataframe
overall_pass_l_2 = (math_pass_df_2["% \n Passing \n Math"] + reading_pass_df_2["% \n Passing \n Reading"]) / 2
overall_pass_df_2 = pd.DataFrame(overall_pass_l_2)
overall_pass_df_2 = overall_pass_df_2.rename(columns = {0 : "Overall \n Pass \n Percentage"})

## Merge databases together
final_school_type_2 = pd.merge(math_score_df_2, reading_score_df_2, left_index=True, right_index=True)
final_school_type_2 = pd.merge(final_school_type_2, math_pass_df_2, left_index=True, right_index=True)
final_school_type_2 = pd.merge(final_school_type_2, reading_pass_df_2, left_index=True, right_index=True)
final_school_type_2 = pd.merge(final_school_type_2, overall_pass_df_2, left_index=True, right_index=True)

final_school_type_2.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0812959986464766, 'fraction_numerical': 0.012520091362828865, 'mean_word_length': 4.829142011834319, 'pattern_counts': {'":': 7, '<': 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': 58, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '599990', 'license': 'ADSL', 'n_tokens_mistral': 7636, 'n_tokens_neox': 7164, 'n_words': 1724, 'path': 'PyCitySchools/PyCitySchools_main.ipynb', 'repo_name': 'danielchenDA/pandas-challenge', 'size': '23642'}