<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># ===============================================
# <NAME>
# Bootcamp Data Analytics
# Version   1.0.0 03/10/2020
#           1.0.3 03/11/2020
#           1.0.3 03/12/2020
#           1.0.3 03/13/2020
#           1.0.3 03/16/2020
#           1.0.3 03/17/2020
# File to run the Notebook for City Schools Starter
# ===============================================
# Dependencies and Setup
import pandas as pd
import numpy as np

# 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># Borrar
school_data_complete.groupby(by=["school_name","type","size"]).max()
# Create a summary statistics for all the schools
totalSchools  = school_data_complete["School ID"].value_counts().count()
totalStudents = school_data_complete["Student ID"].value_counts().count()
schools       = school_data_complete.groupby(by=["School ID"]).max()
totalBudget   = schools["budget"].sum()
avgMath       = school_data_complete["math_score"].mean()
avgReading    = school_data_complete["reading_score"].mean()
avgPassing    = (avgMath + avgReading) / 2

# Get those students who passed Math and Reading
studentPassMath = school_data_complete.loc[school_data_complete["math_score"] >= 70,"Student ID"].count()
studentPassRead = school_data_complete.loc[school_data_complete["reading_score"] >= 70,"Student ID"].count()

# Calculate the percentages for each subject
perPassMath = (studentPassMath / totalStudents)*100
perPassRead = (studentPassRead / totalStudents)*100
# Create the data frame for the summary statistics with the calculated variables
summary = {"Total Schools":totalSchools,
          "Total Students":totalStudents,
          "Total Budget":totalBudget,
          "Average Math Score":avgMath,
          "Average Reading Score":avgReading,
          "% Passing Math":perPassMath,
          "% Passing Reading":perPassRead,
          "% Overall Passing Rate":avgPassing}
# Convert to data frame
summary_data = pd.DataFrame(summary,index=[0])
# Apply formatting for each of the columns
summary_data = summary_data.style.format({
     "Total Schools":"{:,}",
     "Total Students":"{:,}",
     "Total Budget":"${:,}",
     "Average Math Score":"{:,.2f}",
     "Average Reading Score":"{:,.2f}",
     "% Passing Math":"%{:,.2f}",
     "% Passing Reading":"%{:,.2f}",
     "% Overall Passing Rate":"%{:,.2f}"
     })
# Display the summary table
print("District Summary:")
display(summary_data)<jupyter_output>District Summary:<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>#Summary data table grouped by school
schools_data = school_data_complete.groupby(by=["school_name","type"]).agg(total_students=('Student ID','count'),
                                                              total_school_budget=('budget','max'),
                                                              avg_math_score=('math_score',np.mean),
                                                              avg_read_score=('reading_score',np.mean)
                                                             )
# Get the budget per student for each school
schools_data["Per Student Budget"] = schools_data['total_school_budget'].div(schools_data["total_students"],axis=0)
# Get the total number of students who passed math
schools_data["No Students Passed Math"] = school_data_complete.loc[school_data_complete["math_score"]>= 70].groupby(by=["school_name","type"])["school_name"].count()
# Get the total number of students who passed reading
schools_data["No Students Passed Reading"] = school_data_complete.loc[school_data_complete["reading_score"]>= 70].groupby(by=["school_name","type"])["school_name"].count()
                                                                               
# Calculate the percentages statistics per school in Math and Reading
schools_data["% Passing Math"] = schools_data['No Students Passed Math'].div(schools_data["total_students"],axis=0)*100
schools_data["% Passing Reading"] = schools_data['No Students Passed Reading'].div(schools_data["total_students"],axis=0)*100
schools_data["Overall Passing Rate"] = schools_data['% Passing Math'].add(schools_data["% Passing Reading"]).div(2)

# Rename some columns to facilitate readability
schools_data.rename(columns={"total_students":"Total Students",
                            "total_school_budget":"Total School Budget",
                            "avg_math_score":"Avg Math Score",
                            "avg_read_score":"Avg Reading Score"}, inplace=True)


# Get the columns to display and apply formatting for each of them
schools_data_disp = schools_data.loc[:,["Total Students",
                                       "Total School Budget",
                                       "Per Student Budget",
                                       "Avg Math Score",
                                       "Avg Reading Score",
                                       "% Passing Math",
                                       "% Passing Reading",
                                       "Overall Passing Rate"]]

print("Schools Summary Statistics")

# Apply formatting to display the results
schools_data_disp.style.format({
     "Total Students":"{:,}",
     "Total School Budget":"${:,}",
     "Avg Math Score":"{:,.2f}",
     "Avg Reading Score":"{:,.2f}",
     "Per Student Budget":"${:,.2f}",
     "% Passing Math":"%{:,.2f}",
     "% Passing Reading":"%{:,.2f}",
     "Overall Passing Rate":"%{:,.2f}",
     "No Students Passed Reading":"{:,.2f}",
     "No Students Passed Math":"{:,.2f}"
     })<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>#  Get the top 5 schools based on Overall Passing rate
top_perf_schools = schools_data_disp.sort_values(by=["Overall Passing Rate"], ascending = False).head(5)

print("Top 5 Schools based on Overall Passing Rate")

# Apply formatting to display the results
top_perf_schools.style.format({
     "Total Students":"{:,}",
     "Total School Budget":"${:,}",
     "Avg Math Score":"{:,.2f}",
     "Avg Reading Score":"{:,.2f}",
     "Per Student Budget":"${:,.2f}",
     "% Passing Math":"%{:,.2f}",
     "% Passing Reading":"%{:,.2f}",
     "Overall Passing Rate":"%{:,.2f}",
     "No Students Passed Reading":"{:,.2f}",
     "No Students Passed Math":"{:,.2f}"
     })<jupyter_output><empty_output><jupyter_text>Bottom Performing Schools (By Passing Rate) * Sort and display the five worst-performing schools<jupyter_code>#  Get the bottom 5 schools based on Overall Passing rate
top_bott_schools = schools_data_disp.sort_values(by=["Overall Passing Rate"], ascending = True).head(5)

print("Bottom 5 Schools based on Overall Passing Rate")

# Apply formatting to display the results
top_bott_schools.style.format({
     "Total Students":"{:,}",
     "Total School Budget":"${:,}",
     "Avg Math Score":"{:,.2f}",
     "Avg Reading Score":"{:,.2f}",
     "Per Student Budget":"${:,.2f}",
     "% Passing Math":"%{:,.2f}",
     "% Passing Reading":"%{:,.2f}",
     "Overall Passing Rate":"%{:,.2f}",
     "No Students Passed Reading":"{:,.2f}",
     "No Students Passed Math":"{:,.2f}"
     })<jupyter_output>Bottom 5 Schools based on Overall Passing Rate<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># Get the average math scores for each grade 
m_9th_grade = school_data_complete.loc[school_data_complete["grade"]=="9th"].groupby(by=["school_name"])["math_score"].mean()
m_10th_grade = school_data_complete.loc[school_data_complete["grade"]=="10th"].groupby(by=["school_name"])["math_score"].mean()
m_11th_grade = school_data_complete.loc[school_data_complete["grade"]=="11th"].groupby(by=["school_name"])["math_score"].mean()
m_12th_grade = school_data_complete.loc[school_data_complete["grade"]=="12th"].groupby(by=["school_name"])["math_score"].mean()

# Combine series to create a single DataFrame
grades_data = {"9th":m_9th_grade,
               "10th":m_10th_grade,
               "11th":m_11th_grade, 
               "12th":m_12th_grade}

math_grades = pd.DataFrame(grades_data)

print("Math Grades Avg per School")

# Apply formatting to display the results
math_grades.style.format({
     "9th":"{:,.2f}",
     "10th":"{:,.2f}",
     "11th":"{:,.2f}",
     "12th":"{:,.2f}"
     })<jupyter_output>Math Grades Avg per School<jupyter_text>Reading Score by Grade  * Perform the same operations as above for reading scores<jupyter_code># Get the average reading scores for each grade 
r_9th_grade = school_data_complete.loc[school_data_complete["grade"]=="9th"].groupby(by=["school_name"])["reading_score"].mean()
r_10th_grade = school_data_complete.loc[school_data_complete["grade"]=="10th"].groupby(by=["school_name"])["reading_score"].mean()
r_11th_grade = school_data_complete.loc[school_data_complete["grade"]=="11th"].groupby(by=["school_name"])["reading_score"].mean()
r_12th_grade = school_data_complete.loc[school_data_complete["grade"]=="12th"].groupby(by=["school_name"])["reading_score"].mean()
# Combine series
grades_read_data = {"9th":r_9th_grade,
         "10th":r_10th_grade,
         "11th":r_11th_grade, 
         "12th":r_12th_grade}

reading_data = pd.DataFrame(grades_read_data)

print("Reading Grades Avg per School")

reading_data.style.format({
     "9th":"{:,.2f}",
     "10th":"{:,.2f}",
     "11th":"{:,.2f}",
     "12th":"{:,.2f}"
     })<jupyter_output>Reading Grades Avg per School<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"]
# Get the columns to display and use for categorization
school_spending = schools_data.loc[:,["Avg Math Score",
                                  "Avg Reading Score",
                                  "Per Student Budget",
                                  "% Passing Math",
                                  "% Passing Reading",
                                  "Overall Passing Rate"]]
# Set categories 
school_spending["Category"] = pd.cut(school_spending["Per Student Budget"], bins=spending_bins, labels=group_names )
# Calculate means according to the category
schoolsSpendingData = school_spending.groupby(by="Category").mean()

# Apply formatting to display results
schoolsSpendingData.style.format({
     "Avg Math Score":"{:,.2f}",
     "Avg Reading Score":"{:,.2f}",
     "Per Student Budget":"${:,.2f}",
     "% Passing Math":"%{:,.2f}",
     "% Passing Reading":"%{:,.2f}",
     "Overall Passing Rate":"%{:,.2f}"
     })<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)"]
# Get the columns to display and use for categorization
school_size = schools_data.loc[:,["Avg Math Score",
                                  "Avg Reading Score",
                                  "% Passing Math",
                                  "% Passing Reading",
                                  "Overall Passing Rate"]]

# Set categories 
school_size["Category"] = pd.cut(schools_data["Total Students"], bins=size_bins, labels=group_names)
# Calculate means according to the category
schoolsSizeData = school_size.groupby(by="Category").mean()

# Apply formatting to display results
schoolsSizeData.style.format({
     "Avg Math Score":"{:,.2f}",
     "Avg Reading Score":"{:,.2f}",
     "% Passing Math":"%{:,.2f}",
     "% Passing Reading":"%{:,.2f}",
     "Overall Passing Rate":"%{:,.2f}"
     })<jupyter_output><empty_output><jupyter_text>Scores by School Type * Perform the same operations as above, based on school type.<jupyter_code># Calculate means according to the category
school_type = school_size.groupby(by="type").mean()

# Apply formatting to display results
school_type.style.format({
     "Avg Math Score":"{:,.2f}",
     "Avg Reading Score":"{:,.2f}",
     "% Passing Math":"%{:,.2f}",
     "% Passing Reading":"%{:,.2f}",
     "Overall Passing Rate":"%{:,.2f}"
     })<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14263835711799513, 'fraction_numerical': 0.021023320570831883, 'mean_word_length': 3.6074406670942913, 'pattern_counts': {'":': 82, '<': 40, '<?xml version=': 0, '>': 42, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 32, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '610399', 'license': 'ADSL', 'n_tokens_mistral': 4464, 'n_tokens_neox': 4192, 'n_words': 1131, 'path': 'PyCitySchools_starter.ipynb', 'repo_name': 'palolo02/pandas-challenge', 'size': '14369'}