# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernel_info:
#     name: python3
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# ### 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.

# +
# 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
# -

# ## 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

# +
#Calculate the total number of schools
tot_schools = school_data_complete["school_name"].unique()
tot_schools

total_schools = len(tot_schools)
total_schools
# -

#Calculate the total number of students
total_stud = school_data_complete["size"].count()
total_stud

#Calculate the total budget
budg_total = school_data["budget"].sum()
budg_total

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

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

#Calculate the percentage of students with a passing math score (70 or greater)
passing_math = school_data_complete.query("math_score >= 70")["reading_score"].count()/total_stud
passing_math

#Calculate the percentage of students with a passing reading score (70 or greater)
passing_read = school_data_complete.query("reading_score >= 70")["reading_score"].count()/total_stud
passing_read

#Calculate the percentage of students who passed math and reading (% Overall Passing)
over_passing = ((passing_math + passing_read)/2)
over_passing

# +
#Create a dataframe to hold the above results

DistrictSum = pd.DataFrame({"Total Schools":[total_schools],
                        "Total Students":[total_stud],
                        "Total Budget":[budg_total],
                        "Average Math Score":[average_math],
                        "Average Reading Score":[average_read],
                        "Percent Passing Math":[passing_math],
                        "Percent Passing Reading":[passing_read],
                        "Percent Passing Overall":[over_passing]
                       })
DistrictSum


#Optional: give the displayed data cleaner formatting
# -

# ## School Summary

# +
#School Name
school_nombre = school_data_complete.set_index('school_name').groupby(['school_name'])

school_nombre
# -

#SchoolTyoe
school_type = school_data.set_index('school_name')['type']
school_type

# * 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

#total school budget
school_budget = school_data.set_index("school_name")["budget"]
school_budget

#total Studentss
total_students = school_nombre['Student ID'].count()
total_students

#Per Student Budget: group the schools divide it by the numbner of students
ps_budg = school_budget/total_students
ps_budg

avg_math = school_nombre["math_score"].mean()
avg_math

avg_read = school_nombre["reading_score"].mean()
avg_read

avg_overall = ((avg_math + avg_read)/2)
avg_overall

# +
# % Passing Math
percent_pass_math = school_data_complete.loc[school_data_complete['math_score'] >= 70].groupby('school_name')['Student ID'].count()/total_students


percent_pass_math

# +
# % Passing reading
percent_pass_read = school_data_complete.loc[school_data_complete['reading_score'] >= 70].groupby('school_name')['Student ID'].count()/total_students


percent_pass_read
# -

overview_df = pd.DataFrame({"School Type":school_type, "Total School Budget":school_budget, "Per Student Budget":ps_budg, "Total Students":total_students, "Average Math Score":avg_math, "Average Reading Score":avg_read,"% Passing Reading":percent_pass_read, "% Passing Math":percent_pass_math, "Overall Average":avg_overall
                       
                       })
overview_df

# ## Top Performing Schools (By % Overall Passing)

# * Sort and display the top five performing schools by % overall passing.

#sort it
sorted_df = overview_df.sort_values(by=['Overall Average'], ascending=False)
sorted_df
#grab the first five schools
first_five = sorted_df.head(5)
first_five

# ## Bottom Performing Schools (By % Overall Passing)

# * Sort and display the five worst-performing schools by % overall passing.

# +
bottom_df = overview_df.sort_values(by=['Overall Average'], ascending=True)
bottom_df

bottom_five = bottom_df.head(5)
bottom_five
# -

# ## 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

# +
#Average Math Scores
math_9th = school_data_complete.loc[school_data_complete["grade"] == '9th'].groupby('school_name')['math_score'].mean()
math_10th = school_data_complete.loc[school_data_complete["grade"] == '10th'].groupby('school_name')['math_score'].mean()
math_11th = school_data_complete.loc[school_data_complete["grade"] == '11th'].groupby('school_name')['math_score'].mean()
math_12th = school_data_complete.loc[school_data_complete["grade"] == '12th'].groupby('school_name')['math_score'].mean()

#Dataframe

mean_math_df = pd.DataFrame({"9th": math_9th,
                            "10th": math_10th,
                            "11th": math_11th,
                            "12th": math_12th,
    
                            })

#Rearrange the order
mean_math_df = mean_math_df[["9th","10th","11th","12th"]]

mean_math_df

# -

# ## Reading Score by Grade 

# * Perform the same operations as above for reading scores

# +
#Average reading scores
eng_9th = school_data_complete.loc[school_data_complete["grade"] == '9th'].groupby('school_name')['reading_score'].mean()
eng_10th = school_data_complete.loc[school_data_complete["grade"] == '10th'].groupby('school_name')['reading_score'].mean()
eng_11th = school_data_complete.loc[school_data_complete["grade"] == '11th'].groupby('school_name')['reading_score'].mean()
eng_12th = school_data_complete.loc[school_data_complete["grade"] == '12th'].groupby('school_name')['reading_score'].mean()

#Dataframe

mean_eng_df = pd.DataFrame({"9th": eng_9th,
                            "10th": eng_10th,
                            "11th": eng_11th,
                            "12th": eng_12th,
    
                            })

#Rearrange the order
mean_eng_df = mean_eng_df[["9th","10th","11th","12th"]]

mean_eng_df

# -

# ## 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)

# +
#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)

overview_df["Average Math Score"] = pd.to_numeric(overview_df["Average Math Score"])
overview_df["Average Reading Score"] = pd.to_numeric(overview_df["Average Math Score"])

overview_df["Per Student Budget"] = pd.to_numeric(overview_df["Per Student Budget"])

overview_df["% Passing Math"] = overview_df["% Passing Math"].replace('%', '', regex=True).astype('float')
overview_df["% Passing Math"] = pd.to_numeric(overview_df["% Passing Math"])

overview_df["% Passing Reading"] = overview_df["% Passing Reading"].replace('%', '', regex=True).astype('float')
overview_df["% Passing Reading"] = pd.to_numeric(overview_df["% Passing Reading"])

bins = [0, 585, 615, 645, 675]

group_sch = ['< $585', "$585-615", "$615-645", "$645-675"]

overview_df['Spending Ranges (Per Student)'] = pd.cut(overview_df['Per Student Budget'], bins = bins, labels = group_sch)

spending_df = overview_df.groupby(['Spending Ranges (Per Student)'])



# +
avg_math_score = spending_df["Average Math Score"].mean()
avg_reading_score = spending_df["Average Reading Score"].mean()
avg_percent_math = spending_df["% Passing Math"].mean()
avg_percent_reading = spending_df["% Passing Reading"].mean()
overall_passing = (avg_percent_math + avg_percent_reading)/2

#create a df to store Scores by School Spending
by_spending = pd.DataFrame({"Average Math Score":avg_math_score,
                                          "Average Reading Score":avg_reading_score,
                                          "% Passing Math":avg_percent_math,
                                          "% Passing Reading":avg_percent_reading,
                                          "% Overall Passing Rate":overall_passing
                                         })

# reformating
by_spending['Average Math Score'] = by_spending['Average Math Score'].map("{:,.2f}".format)
by_spending['Average Reading Score'] = by_spending['Average Reading Score'].map("{:,.2f}".format)
by_spending['% Passing Math'] = by_spending['% Passing Math'].map("{:.2f}".format)
by_spending['% Passing Reading'] = by_spending['% Passing Reading'].map("{:.2f}".format)
by_spending['% Overall Passing Rate'] = by_spending['% Overall Passing Rate'].map("{:.2f}".format)

#rearrage columns
by_spending = by_spending [["Average Math Score",
                            "Average Reading Score",
                            "% Passing Math",
                            "% Passing Reading",
                            "% Overall Passing Rate"]]

# print out the table
by_spending
# -

# ## Scores by School Size

# * Perform the same operations as above, based on school size.

#

# +
#<NAME>
bins_one = [0, 1000, 2000, 5000]

#categories
group_one = ['Small (<1000)', "Medium (1000-2000)", "Large (2000-5000)"]

# +
overview_df['Total Students'] = overview_df['Total Students'].replace(',', '', regex=True).astype('float')
overview_df['Total Students'] = pd.to_numeric(overview_df['Total Students'])
overview_df['School Size'] = pd.cut(overview_df['Total Students'], bins = bins_one, labels = group_one)

by_school_size = overview_df.groupby(['School Size'])

avg_math_score = by_school_size["Average Math Score"].mean()
avg_reading_score = by_school_size["Average Reading Score"].mean()
avg_percent_math = by_school_size["% Passing Math"].mean()
avg_percent_reading = by_school_size["% Passing Reading"].mean()
overall_percent = (avg_percent_math + avg_percent_reading)/2\

size_df = pd.DataFrame({"Average Math Score":avg_math_score,
                                          "Average Reading Score":avg_reading_score,
                                          "% Passing Math":avg_percent_math,
                                          "% Passing Reading":avg_percent_reading,
                                          "% Overall Passing Rate":overall_percent
                                         })
# reformating
size_df['Average Math Score'] = size_df['Average Math Score'].map("{:,.2f}".format)
size_df['Average Reading Score'] = size_df['Average Reading Score'].map("{:,.2f}".format)
size_df['% Passing Math'] = size_df['% Passing Math'].map("{:.2f}".format)
size_df['% Passing Reading'] = size_df['% Passing Reading'].map("{:.2f}".format)
size_df['% Overall Passing Rate'] = size_df['% Overall Passing Rate'].map("{:.2f}".format)

#rearrage columns
size_df = size_df [["Average Math Score",
                            "Average Reading Score",
                            "% Passing Math",
                            "% Passing Reading",
                            "% Overall Passing Rate"]]

# print out the table
size_df
# -

# ## Scores by School Type
# * Perform the same operations as above, based on school type

# +
type_df = overview_df.groupby(['School Type'])

avg_math_score = type_df["Average Math Score"].mean()
avg_reading_score = type_df["Average Reading Score"].mean()
avg_percent_math = type_df["% Passing Math"].mean()
avg_percent_reading = type_df["% Passing Reading"].mean()
overall_passing = (avg_percent_math + avg_percent_reading)/2



school_type_df = pd.DataFrame({"Average Math Score":avg_math_score,
                                          "Average Reading Score":avg_reading_score,
                                          "% Passing Math":avg_percent_math,
                                          "% Passing Reading":avg_percent_reading,
                                          "% Overall Passing Rate":overall_passing
                                         })

# reformating
school_type_df['Average Math Score'] = school_type_df['Average Math Score'].map("{:,.2f}".format)
school_type_df['Average Reading Score'] = school_type_df['Average Reading Score'].map("{:,.2f}".format)
school_type_df['% Passing Math'] = school_type_df['% Passing Math'].map("{:.2f}".format)
school_type_df['% Passing Reading'] = school_type_df['% Passing Reading'].map("{:.2f}".format)
school_type_df['% Overall Passing Rate'] = school_type_df['% Overall Passing Rate'].map("{:.2f}".format)

#rearrage columns
school_type_df = school_type_df [["Average Math Score",
                            "Average Reading Score",
                            "% Passing Math",
                            "% Passing Reading",
                            "% Overall Passing Rate"]]

# print out the table
school_type_df
# -









StarcoderdataJupyterScriptsDedupFiltered