<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

schools_complete = "Resources/schools_complete.csv"
students_complete = "Resources/students_complete.csv"

# Read School and Student Data File and store into Pandas DataFrames
schools_df = pd.read_csv(schools_complete, encoding = "utf-8")
schools_df.head()

students_df = pd.read_csv(students_complete, encoding = "utf-8")
students_df.head()

# Combine the data into a single dataset  
merge_table = pd.merge(students_df, schools_df, how="outer", on=["school_name", "school_name"])
merge_table.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 
schools_total = schools_df["school_name"].count()


#Calculate the total number of students 
students_total = students_df["Student ID"].count()


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


#Calculate the average math score 
math_average = students_df["math_score"].mean()
math_average_rounded = round(math_average, 2)

#Calculate the average reading score 
reading_average = students_df ["reading_score"].mean()
reading_average_rounded = round(reading_average, 2)

#Calculate the percentage of students with a passing math score (70 or greater)
passing_math_df = students_df.loc[students_df["math_score"]>= 70, :]
passing_math_df.head()

passing_math_total = (passing_math_df["Student ID"].count()/ students_total) * 100
passing_math_rounded = round(passing_math_total, 2)



#Calculate the percentage of students with a passing reading score (70 or greater)
passing_reading_df = students_df.loc[students_df["reading_score"]>= 70, :]
passing_reading_df.head()

passing_reading = (passing_reading_df["Student ID"].count()/students_total)*100
passing_reading_rounded = round(passing_reading,2)

#Calculate the percentage of students who passed math and reading (% Overall Passing)
overall_pass = (passing_math_rounded + passing_reading_rounded)/2

#Create a dataframe to hold the above results
results_df = pd.DataFrame({"Total Schools": [schools_total],
                           "Total Students": [students_total],
                           "Total Budget": [total_budget],
                           "Average Math Score": [math_average_rounded],
                           "Average Reading Score": [reading_average_rounded],
                           "% Passing Math": [passing_math_rounded],
                           "% Passing Reading": [passing_reading_rounded],
                           "Overall Passing Rate": [overall_pass]})
results_df<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>#reading score by school
average_reading_score = students_df.groupby('school_name')['reading_score'].mean()
average_reading_score_df = pd.DataFrame({'school_name':average_reading_score.index, 'average_reading_score':average_reading_score.values})


#math score by school
average_math_score = students_df.groupby('school_name')['math_score'].mean()
average_math_score_df = pd.DataFrame({'school_name':average_math_score.index, 'average_math_score':average_math_score.values})

#students at each school passing math
count_passing_math = passing_math_df.groupby('school_name')['math_score'].count()
count_passing_math_df = pd.DataFrame({'school_name':count_passing_math.index, 'count_passing_math':count_passing_math.values})

#students at each school passing reading
count_passing_reading = passing_reading_df.groupby('school_name')['reading_score'].count()
count_passing_reading_df = pd.DataFrame({'school_name':count_passing_reading.index, 'count_passing_reading':count_passing_reading.values})

#join schools w/ average reading scores calculated  
schools_join_averageread_df = pd.merge(schools_df, average_reading_score_df, on="school_name", how="outer")
schools_join_averageread_df.head()

#join schools w/average math scores calculated 
schools_join_averagemath_df = pd.merge(schools_join_averageread_df, average_math_score_df, on="school_name", how="outer")
schools_join_averagemath_df.head()

#outer join, number of kids passing reading per school 
schools_join_countread_df = pd.merge(schools_join_avgmath_df, count_passing_reading_df, on="school_name", how="outer")
schools_join_countread_df.head()

#outer join, number of kids passing math per school 
schools_join_countmath_df = pd.merge(schools_join_countread_df, count_passing_math_df, on="school_name", how="outer")
schools_join_countmath_df.head()

#percent of kids passing math per school 
schools_join_countmath_df['percent_passing_math'] = (schools_join_countmath_df["count_passing_math"]/ schools_join_countmath_df["size"]) * 100
schools_join_countmath_df

#percent passing reading per school 
schools_join_countmath_df['percent_passing_reading'] = (schools_join_countmath_df["count_passing_reading"]/ schools_join_countmath_df["size"]) * 100
schools_join_countmath_df

#overall pass rate per school 
schools_join_countmath_df['Overall_passing_rate'] = (schools_join_countmath_df["percent_passing_reading"] + schools_join_countmath_df["percent_passing_math"])/ 2
schools_join_countmath_df.head()

#budget per student for each school 
schools_join_countmath_df['Per_student_budget'] = (schools_join_countmath_df["budget"] / schools_join_countmath_df["size"])
schools_join_countmath_df.head()

#rename df for use in scores by school spending
schools_summary_noformat_df = schools_join_countmath_df
schools_summary_noformat_df.head()

#rename df for use in scores by school type
schools_summary_noformat_fortype_df = schools_join_countmath_df
schools_summary_noformat_fortype_df.head()

#rename columns
school_rename_df = schools_summary_noformat_df.rename(columns={"school_name":"School Name", 
                                                             "type":"School Type",
                                                             "size": "Total Students", 
                                                             "budget": "Total School Budget", 
                                                             "Per_student_budget": "Per Student Budget", 
                                                             "avg_math_score": "Average Math Score",
                                                             "avg_reading_score": "Average Reading Score",
                                                             "percent_passing_math": "% Passing Math",
                                                             "percent_passing_reading": "% Passing Reading",
                                                             "Overall_passing_rate": "% Overall Passing Rate"})

school_rename_df.head()

#column reformat 
school_rename_df["Total School Budget"] = school_rename_df["Total School Budget"].map("${:,.2f}".format)
school_rename_df["Per Student Budget"] = school_rename_df["Per Student Budget"].map("${:,.2f}".format)
school_rename_df.head()

#finished df 
school_summary_df = school_rename_df.loc[:, ['School Name', 'School Type', 'Total Students',
       'Total School Budget', 'Per Student Budget','Average Reading Score', 'Average Math Score',
       '% Passing Math','% Passing Reading', '% Overall Passing Rate']]

school_summary_df<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>#Sort and display the top five performing schools by % overall passing.
top_schools_df = school_summary_df.sort_values("% Overall Passing Rate", ascending=False)
top_schools_new_index = top_schools_df.reset_index(drop=True)
top_schools_new_index.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>#Sort and display the five worst-performing schools by % overall passing. 
bottom_schools_df = school_summary_df.sort_values("% Overall Passing Rate", ascending=True)
bottom_schools_new_index = bottom_schools_df.reset_index(drop=True)
bottom_schools_new_index.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>#Math Scores
math_scores = students_df[["school_name", "math_score", "grade"]]

math_scores_df = math_scores.pivot_table(index='school_name',columns='grade')
math_scores_df<jupyter_output><empty_output><jupyter_text>Reading Score by Grade  * Perform the same operations as above for reading scores<jupyter_code>#Reading Scores
reading_scores = students_df[["school_name", "reading_score", "grade"]]

reading_scores_df = reading_scores.pivot_table(index='school_name',columns='grade')
reading_scores_df<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 bins 
spending_bins = [0, 585, 615, 645, 675]
group_names = ["<$585", "$585-615", "$615-645", "$645-675"]

schools_summary_noformat_df.head()
schools_summary_noformat_df.dtypes

#Replace all NaN values with 0 
schools_summary_noformat_df["Per_student_budget"] = schools_summary_noformat_df["Per_student_budget"].fillna(0)

#slice data and put it into bins
schools_summary_noformat_df["Spending Ranges(per Student)"]= pd.cut(schools_summary_noformat_df["Per_student_budget"], spending_bins, labels=group_names).head()
schools_summary_noformat_df.head()

#GroupBy object based upon "View Group"
schools_score_spending = schools_summary_noformat_df.groupby("Spending Ranges(per Student)")

#average of each column within the GroupBy object
schools_score_spending_df = schools_score_spending[["avg_math_score", "avg_reading_score", "percent_passing_math","percent_passing_reading", "Overall_passing_rate"]].mean()




#rename columns

schools_score_spending_rename = schools_score_spending_df.rename(columns={"avg_math_score":"Average Math Score",
                                                                          "avg_reading_score":"Average Reading Score",
                                                                          "percent_passing_math":"% Passing Math",
                                                                          "percent_passing_reading":"% Passing Reading",
                                                                          "Overall_passing_rate":"Overall Passing Rate"})

schools_score_spending_rename<jupyter_output><empty_output><jupyter_text>Scores by School Size * Perform the same operations as above, based on school size.<jupyter_code>#Scores by School Size

size_bins = [0, 1000, 2000, 5000]
group_names_2 = ["Small (<1000)", "Medium (1000-2000)", "Large (2000-5000)"]

schools_join_countmath_df.head()
schools_join_countmath_df.dtypes


#slice the data and place in bins
schools_join_countmath_df["School Size"]= pd.cut(schools_join_countmath_df["size"], size_bins, labels=group_names_2).head()
schools_join_countmath_df.head()

#create a GroupBy object based upon "View Group"
schools_score_size = schools_join_countmath_df.groupby("School Size")

#average of each column within the GroupBy object
schools_score_size_df = schools_score_size[["average_math_score", "average_reading_score", "percent_passing_math","percent_passing_reading", "Overall_passing_rate"]].mean()




#rename columns

schools_score_size_rename = schools_score_size_df.rename(columns={"avg_math_score":"Average Math Score",
                                                                         "average_reading_score":"Average Reading Score",
                                                                         "percent_passing_math":"% Passing Math",
                                                                         "percent_passing_reading":"% Passing Reading",
                                                                         "Overall_passing_rate":"Overall Passing Rate"})

schools_score_size_rename<jupyter_output><empty_output><jupyter_text>Scores by School Type * Perform the same operations as above, based on school type<jupyter_code>#scores by school type 

schools_score_type = schools_summary_noformat_df.groupby(['type'])[["type",
                                                             "avg_math_score",
                                                             "avg_reading_score",
                                                             "percent_passing_math",
                                                             "percent_passing_reading",
                                                             "Overall_passing_rate"]].mean()
schools_score_type.head()


#rename columns
school_score_type_rename_df = schools_score_type.rename(columns={"type":"School Type",
                                                             "average_math_score": "Average Math Score",
                                                             "average_reading_score": "Average Reading Score",
                                                             "percent_passing_math": "% Passing Math",
                                                             "percent_passing_reading": "% Passing Reading",
                                                             "Overall_passing_rate": "% Overall Passing Rate"})
school_score_type_rename_df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08696524064171123, 'fraction_numerical': 0.007553475935828877, 'mean_word_length': 3.494142385100631, 'pattern_counts': {'":': 34, '<': 43, '<?xml version=': 0, '>': 43, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 62, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '469427', 'license': 'ADSL', 'n_tokens_mistral': 4314, 'n_tokens_neox': 4036, 'n_words': 1009, 'path': 'PyCitySchools/PyCitySchools_starter.ipynb', 'repo_name': 'tstenner1/pandas-challenge', 'size': '14960'}