<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
school_data_load = "Resources/schools_complete.csv"
student_data_load = "Resources/students_complete.csv"

# Read School and Student Data File and store into Pandas Data Frames
school_data = pd.read_csv(school_data_load)
student_data = pd.read_csv(student_data_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>#total number of schools
total_schools=(school_data_complete["school_name"]).nunique()
total_schools

#total number of students
total_students=(school_data_complete["student_name"]).count()
total_students

#total budget
total_budget=sum(school_data_complete["budget"])
total_budget
#average math score
avg_math= sum(school_data_complete["math_score"])/total_students
avg_math
#average reading score
avg_reading=sum(school_data_complete["reading_score"])/total_students
avg_reading
#overall passing (math score + reading score)/2
overall_passing=(avg_math+avg_reading)/2
overall_passing
pass_math=(school_data_complete["math_score"]>= 70)
pass_math=(pass_math).sum()
total_pass_math=(pass_math/total_students)*100
total_pass_math
pass_reading=(school_data_complete["reading_score"]>= 70)
pass_reading=(pass_reading).sum()
total_pass_reading=(pass_reading/total_students)*100
total_pass_reading
dis_sum={'Total Number of Schools': total_schools,
           'Total Number of Students': total_students,
           'Total Budget': total_budget,
           'Average Math Score': avg_math,
           'Average Reading score': avg_reading,
           'Total Pass Math %':total_pass_math,
           'Total Pass Reading %': total_pass_reading,
         'Overall Passing': overall_passing
        }
dis_sum<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>#create a copy of the master data in order not to alter master file then print
school_complete_copy= school_data_complete.copy()
school_copy=school_data.copy()
#school_copy
#remove unncessary column 'School ID' then print to verify
#del school_copy['School ID']
#school_copy
#number of total students per school
school_data_complete['size'].unique()
#Per Student Budget: 'budget' divided by 'size' then print
school_copy['Per Student Budget']=school_copy['budget']/school_copy['size']
#school_copy
#Average Math Score: Use groupby function for 'school_name' and 'math_score'
#Reset index and print
avg_math_score= school_complete_copy.groupby(['school_name'])['math_score'].mean().reset_index()
#avg_math_score
#Average Reading Score: Use groupby function for 'school_name' and 'reading_score'
#Reset index and print
avg_reading_score= school_complete_copy.groupby(['school_name'])['reading_score'].mean().reset_index()
#avg_reading_score
#Merge 'avg_math_score' with 'school_copy' then print
school_copy=school_copy.merge(avg_math_score, on='school_name',how='outer')
#school_copy
#Merge 'avg_reading_score' with 'school_copy' then print
school_copy=school_copy.merge(avg_reading_score, on='school_name',how='outer')
#school_copy

#Use a conditional function to find %Passing Math (passing >= 70%) then print
pass_math_total=school_complete_copy[school_complete_copy['math_score']>=70]
#pass_math_total
#Use a conditional function to find %Passing Reading (passing >= 70%) then print
pass_reading_total=school_complete_copy[school_complete_copy['reading_score']>=70]
#pass_reading_total
#Count students passing math then reset index followed by print
pass_math_group=pass_math_total.groupby(['school_name'])['math_score'].count().reset_index()
#Rename column 'math_score' to 'math pass count'
pass_math_group.rename({'math_score' : 'math pass count'}, axis=1, inplace=True)
#pass_math_group
#Count students passing reading then reset index followed by print
pass_reading_group=pass_reading_total.groupby(['school_name'])['reading_score'].count().reset_index()
#Rename column 'reading_score' to 'reading pass count'
pass_reading_group.rename({'reading_score' : 'reading pass count'}, axis=1, inplace=True)
#pass_reading_group
#merge the grouped math pass and reading pass counts by school then print
pass_math_reading=pass_math_group.merge(pass_reading_group, on='school_name',how='inner')
#pass_math_reading
#merge the pass_math_reading dataset with the copy of master file (school_copy) then print
school_copy=school_copy.merge(pass_math_reading, on='school_name',how='outer')
#school_copy
#math pass count divided by school size*100 to find %passing math then print
school_copy['% Passing Math']=(school_copy['math pass count']/school_copy['size'])*100
#school_copy

#reading pass count divided by school size*100 to find %passing reading then print
school_copy['% Passing Reading']=(school_copy['reading pass count']/school_copy['size'])*100
#school_copy

#%overall passing = adding %passing math and %passing reading and dividing by 2 then print
school_copy['% Overall Passing'] = (school_copy['% Passing Math'] + school_copy['% Passing Reading'])/2
#school_copy

#delete extra copy of average math score and print
#del school_copy['math_score_x']
#school_copy
#delete math pass count and reading pass count columns
#del school_copy['math pass count']
#del school_copy['reading pass count']
#school_copy
#rename each of the columns and print
school_copy.rename({'school_name': 'School Name',
                   'type':'School Type',
                   'size':'Total Students',
                    'budget':'Total Budget',
                   'math_score':'Average Math Score',
                   'reading_score':'Average Reading Score'}, axis=1, inplace=True)
school_copy<jupyter_output><empty_output><jupyter_text>Top Performing Schools (By Passing Rate) * Sort and display the top five schools in overall passing rate<jupyter_code>#sort schools by % overall passing to determine top performing schools then print
best_schools = school_copy.sort_values(by=['% Overall Passing'], ascending=False).head()
best_schools<jupyter_output><empty_output><jupyter_text>Bottom Performing Schools (By Passing Rate) * Sort and display the five worst-performing schools<jupyter_code>#sort schools by % overall passing to determine worst performing schools then print
worst_schools = school_copy.sort_values(by=['% Overall Passing'], ascending=True).head()
worst_schools<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>#pandas pivot table to sort math scores by grade then print
math_grade_scores = pd.pivot_table(school_complete_copy, values=['math_score'], index=['school_name'], 
                                         columns=['grade'])
math_grade_scores = math_grade_scores.reindex(labels=['9th',
                                                                    '10th',
                                                                    '11th',
                                                                    '12th'], axis=1, level=1)
#math_grade_scores
#rename columns and header for cleaner dataset then print
math_grade_scores.rename({'math_score': 'Math Score',
                   '9th':'9th Grade',
                   '10th':'10th Grade',
                    '11th':'11th Grade',
                   'school_name':'School Name',
                   'grade':'Grade'}, axis=1, inplace=True)
math_grade_scores<jupyter_output><empty_output><jupyter_text>Reading Score by Grade<jupyter_code>#pandas pivot table to sort reading scores by grade then print
reading_grade_scores = pd.pivot_table(school_complete_copy, values=['reading_score'], index=['school_name'], 
                                         columns=['grade'])
reading_grade_scores = reading_grade_scores.reindex(labels=['9th',
                                                                    '10th',
                                                                    '11th',
                                                                    '12th'], axis=1, level=1)
#reading_grade_scores
#rename columns and header for cleaner dataset then print
reading_grade_scores.rename({'reading_score': 'Reading Score',
                   '9th':'9th Grade',
                   '10th':'10th Grade',
                    '11th':'11th Grade',
                   'school_name':'School Name',
                   'grade':'Grade'}, axis=1, inplace=True)
reading_grade_scores<jupyter_output><empty_output><jupyter_text>* Perform the same operations as above for reading scores  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_labels = ["<$585", "$585-615", "$615-645", "$645-675"]
#create copy of master school data (school_copy) to manipulate new data then print
school_spend=school_copy.copy()
#school_spend
#Per Student Budget binned (with previously established bins) and labeled by group labels then print
school_spend_binned=pd.cut(school_spend['Per Student Budget'],spending_bins,labels=group_labels)
#school_spend_binned
#create dataset from binned data
school_spend_binned=pd.DataFrame(school_spend_binned)
#add column 'Spending Per Student' to binned dataset then print
school_spend['Spending Per Student']=school_spend_binned
#school_spend_binned
#use groupby function to add columns from master data set then print
school_spend = school_spend.groupby(['Spending Per Student'])['Average Reading Score','Average Math Score','% Passing Reading','% Passing Math','% Overall Passing'].max()
school_spend<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]
size_names = ["Small (<1000)", "Medium (1000-2000)", "Large (2000-5000)"]
#create copy of master school data (school_copy) to manipulate new data then print
school_size=school_copy.copy()
#school_size
#Total Students binned (with previously established bins) and labeled by group labels then print
school_size_binned=pd.cut(school_size['Total Students'],size_bins,labels=size_names)
#school_size_binned
#create dataset from binned data
school_size_binned=pd.DataFrame(school_size_binned)
#add column 'Size of Population' to binned dataset then print
school_size['Size of Population']=school_size_binned
#school_size_binned
#use groupby function to add columns from master data set then print
school_size = school_size.groupby(['Size of Population'])['Average Reading Score','Average Math Score','% Passing Reading','% Passing Math','% Overall Passing'].max()
school_size<jupyter_output><empty_output><jupyter_text>Scores by School Type * Perform the same operations as above, based on school type.<jupyter_code>#create copy of master school data (school_copy) to manipulate new data then print
school_type=school_copy.copy()
#school_type
#use groupby function to add columns from master data set then print
school_type = school_type.groupby(['School Type'])['Average Reading Score','Average Math Score','% Passing Reading','% Passing Math','% Overall Passing'].max()
school_type<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09393845678057831, 'fraction_numerical': 0.012138549559301067, 'mean_word_length': 4.847649186256781, 'pattern_counts': {'":': 0, '<': 43, '<?xml version=': 0, '>': 47, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 16, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '402441', 'license': 'ADSL', 'n_tokens_mistral': 3843, 'n_tokens_neox': 3570, 'n_words': 1156, 'path': 'PyCitySchools/PyCitySchools_starter.ipynb', 'repo_name': 'shamiltajuddin/pandas-challenge', 'size': '12934'}