# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: PythonData
#     language: python
#     name: pythondata
# ---

# +
# The next code corresponds to the PyCitySchools_Challenge_starter_code.ipynb file provided for this challenge.

# +
# Dependencies and Setup
import pandas as pd

# File to Load (Remember to change the path if needed.)
school_data_to_load = "Resources/schools_complete.csv"
student_data_to_load = "Resources/students_complete.csv"

# Read the School Data and Student Data and store into a Pandas DataFrame
school_data_df = pd.read_csv(school_data_to_load)
student_data_df = pd.read_csv(student_data_to_load)

# Cleaning Student Names and Replacing Substrings in a Python String
# Add each prefix and suffix to remove to a list.
prefixes_suffixes = ["Dr. ", "Mr. ","Ms. ", "Mrs. ", "Miss ", " MD", " DDS", " DVM", " PhD"]

# Iterate through the words in the "prefixes_suffixes" list and replace them with an empty space, "".
for word in prefixes_suffixes:
    student_data_df["student_name"] = student_data_df["student_name"].str.replace(word,"")

# Check names.
student_data_df.head(10)
# -

# ## Deliverable 1: Replace the reading and math scores.
#
# ### Replace the 9th grade reading and math scores at Thomas High School with NaN.

# Install numpy using conda install numpy or pip install numpy. 
# Step 1. Import numpy as np.
import numpy as np

# Step 2. Use the loc method on the student_data_df to select all the reading scores from the 9th grade at Thomas High School and replace them with NaN.
student_data_df.loc[(student_data_df["school_name"]=="<NAME> School") & (student_data_df["grade"]=="9th"),["reading_score"]]=np.nan


#  Step 3. Refactor the code in Step 2 to replace the math scores with NaN.
student_data_df.loc[(student_data_df["school_name"]=="<NAME>") & (student_data_df["grade"]=="9th"),["math_score"]]=np.nan


#  Step 4. Check the student data for NaN's. 
student_data_df.tail(10)


# ## Deliverable 2 : Repeat the school district analysis

# ### District Summary

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

# +
# Calculate the Totals (Schools and Students)
school_count = len(school_data_complete_df["school_name"].unique())
student_count = school_data_complete_df["Student ID"].count()

# Calculate the Total Budget
total_budget = school_data_df["budget"].sum()
total_budget
# -

# Calculate the Average Scores using the "clean_student_data".
average_reading_score = school_data_complete_df["reading_score"].mean()
average_reading_score

# Calculate the Average Scores using the "clean_student_data".
average_math_score = school_data_complete_df["math_score"].mean()
average_math_score

# +
# Step 1. Get the number of students that are in ninth grade at Thomas High School.
# These students have no grades. 
students_9th_thomas=school_data_complete_df.loc[(school_data_complete_df["school_name"]=="Thomas High School") & (school_data_complete_df["grade"]=="9th")].count()["Student ID"]

# Get the total student count 
student_count_no_dishon = school_data_complete_df["Student ID"].count()

# Step 2. Subtract the number of students that are in ninth grade at 
# Thomas High School from the total student count to get the new total student count.
student_count=student_count_no_dishon-students_9th_thomas
student_count
# -

# Calculate the passing rates using the "clean_student_data".
pass_math_count = school_data_complete_df[(school_data_complete_df["math_score"] >= 70)].count()["student_name"]
pass_math_count

# Calculate the passing rates using the "clean_student_data".
pass_reading_count = school_data_complete_df[(school_data_complete_df["reading_score"] >= 70)].count()["student_name"]
pass_reading_count

# Step 3. Calculate the passing percentages (perc) with the new total student count.
pass_math_perc=float(passing_math_count)/float(student_count) *100
pass_math_perc

# Step 3.1 Calculate the passing percentages (perc) with the new total student count.
pass_reading_perc=float(passing_reading_count)/float(student_count) *100
pass_reading_perc

# +
# Calculate the students who passed both reading and math.
passing_math_reading = school_data_complete_df[(school_data_complete_df["math_score"] >= 70)
                                               & (school_data_complete_df["reading_score"] >= 70)]

# Calculate the number of students that passed both reading and math.
overall_passing_math_reading_count = passing_math_reading["student_name"].count()


# Step 4.Calculate the overall passing percentage with new total student count.
overall_passing_percentage=float(overall_passing_math_reading_count) / float(student_count) * 100
overall_passing_percentage

# +
# Create a DataFrame
district_summary_df = pd.DataFrame(
          [{"Total Schools": school_count, 
          "Total Students": student_count, 
          "Total Budget": total_budget,
          "Average Math Score": average_math_score, 
          "Average Reading Score": average_reading_score,
          "% Passing Math": pass_math_perc,
         "% Passing Reading": pass_reading_perc,
        "% Overall Passing": overall_passing_percentage}])



# Format the "Total Students" to have the comma for a thousands separator.
district_summary_df["Total Students"] = district_summary_df["Total Students"].map("{:,}".format)
# Format the "Total Budget" to have the comma for a thousands separator, a decimal separator and a "$".
district_summary_df["Total Budget"] = district_summary_df["Total Budget"].map("${:,.2f}".format)
# Format the columns.
district_summary_df["Average Math Score"] = district_summary_df["Average Math Score"].map("{:.1f}".format)
district_summary_df["Average Reading Score"] = district_summary_df["Average Reading Score"].map("{:.1f}".format)
district_summary_df["% Passing Math"] = district_summary_df["% Passing Math"].map("{:.1f}".format)
district_summary_df["% Passing Reading"] = district_summary_df["% Passing Reading"].map("{:.1f}".format)
district_summary_df["% Overall Passing"] = district_summary_df["% Overall Passing"].map("{:.1f}".format)

# Display the data frame
district_summary_df
# -

# ##  School Summary

# +
# Determine the School Type
per_school_types = school_data_df.set_index(["school_name"])["type"]

# Calculate the total student count.
per_school_counts = school_data_complete_df["school_name"].value_counts()

# Calculate the total school budget and per capita spending
per_school_budget = school_data_complete_df.groupby(["school_name"]).mean()["budget"]
# Calculate the per capita spending.
per_school_capita = per_school_budget / per_school_counts

# Calculate the average test scores.
per_school_math = school_data_complete_df.groupby(["school_name"]).mean()["math_score"]
per_school_reading = school_data_complete_df.groupby(["school_name"]).mean()["reading_score"]

# Calculate the passing scores by creating a filtered DataFrame.
per_school_passing_math = school_data_complete_df[(school_data_complete_df["math_score"] >= 70)]
per_school_passing_reading = school_data_complete_df[(school_data_complete_df["reading_score"] >= 70)]

# Calculate the number of students passing math and passing reading by school.
per_school_passing_math = per_school_passing_math.groupby(["school_name"]).count()["student_name"]
per_school_passing_reading = per_school_passing_reading.groupby(["school_name"]).count()["student_name"]

# Calculate the percentage of passing math and reading scores per school.
per_school_passing_math = per_school_passing_math / per_school_counts * 100
per_school_passing_reading = per_school_passing_reading / per_school_counts * 100

# Calculate the students who passed both reading and math.
per_passing_math_reading = school_data_complete_df[(school_data_complete_df["reading_score"] >= 70)
                                               & (school_data_complete_df["math_score"] >= 70)]

# Calculate the number of students passing math and passing reading by school.
per_passing_math_reading = per_passing_math_reading.groupby(["school_name"]).count()["student_name"]

# Calculate the percentage of passing math and reading scores per school.
per_overall_passing_percentage = per_passing_math_reading / per_school_counts * 100

# +
# Create the DataFrame
per_school_summary_df = pd.DataFrame({
    "School Type": per_school_types,
    "Total Students": per_school_counts,
    "Total School Budget": per_school_budget,
    "Per Student Budget": per_school_capita,
    "Average Math Score": per_school_math,
    "Average Reading Score": per_school_reading,
    "% Passing Math": per_school_passing_math,
    "% Passing Reading": per_school_passing_reading,
    "% Overall Passing": per_overall_passing_percentage})


per_school_summary_df.head()

# +
# Format the Total School Budget and the Per Student Budget
per_school_summary_df["Total School Budget"] = per_school_summary_df["Total School Budget"].map("${:,.2f}".format)
per_school_summary_df["Per Student Budget"] = per_school_summary_df["Per Student Budget"].map("${:,.2f}".format)

# Display the data frame
per_school_summary_df

# +
# Step 5.  Get the number of 10th-12th graders from Thomas High School (THS).
students_ths_rest = school_data_complete_df.loc[(
    (school_data_complete_df["school_name"] == "Thomas High School") & (school_data_complete_df["grade"] != "9th")), "Student ID"].count()

students_ths_rest

# +
# Step 6. Get all the students passing math from THS
rest_ths_pass_math_score = school_data_complete_df.loc[
    (school_data_complete_df["school_name"] == "Thomas High School") & (school_data_complete_df["math_score"] >= 70), "Student ID"].count()

rest_ths_pass_math_score

# +
# Step 7. Get all the students passing reading from THS
rest_ths_pass_reading_score = school_data_complete_df.loc[
    (school_data_complete_df["school_name"] == "Thomas High School") & (school_data_complete_df["reading_score"] >= 70), "Student ID"].count()

rest_ths_pass_reading_score

# +
# Step 8. Get all the students passing math and reading from THS
rest_ths_pass_reading_math_score = school_data_complete_df.loc[
    (school_data_complete_df["school_name"] == "Thomas High School") & 
    ((school_data_complete_df["math_score"] >= 70) &     
    (school_data_complete_df["reading_score"] >= 70)), "Student ID"].count()

rest_ths_pass_reading_math_score
# -

# Step 9. Calculate the percentage of 10th-12th grade students passing math from Thomas High School. 
ths_pass_math_perc=float(rest_ths_pass_math_score)/float(students_ths_rest)*100
ths_pass_math_perc

# Step 10. Calculate the percentage of 10th-12th grade students passing reading from Thomas High School.
ths_pass_reading_perc=float(rest_ths_pass_reading_score)/float(students_ths_rest)*100
ths_pass_reading_perc

# Step 11. Calculate the overall passing percentage of 10th-12th grade from Thomas High School. 
ths_overall_pass_perc=float(rest_ths_pass_reading_math_score)/float(students_ths_rest)*100
ths_overall_pass_perc

# Step 12. Replace the passing math percent for Thomas High School in the per_school_summary_df.
per_school_summary_df.loc[["Thomas High School"],["% Passing Math"]]=ths_pass_math_perc

# Step 13. Replace the passing reading percentage for Thomas High School in the per_school_summary_df.
per_school_summary_df.loc[["Thomas High School"],["% Passing Reading"]]=ths_pass_reading_perc

# Step 14. Replace the overall passing percentage for Thomas High School in the per_school_summary_df.
per_school_summary_df.loc[["Thomas High School"],["% Overall Passing"]]=ths_overall_pass_perc

per_school_summary_df

# ## High and Low Performing Schools 

# Sort and show top five schools.
max_schools=per_school_summary_df.sort_values(["% Overall Passing"], ascending=False)
max_schools.head()

# Sort and show top five schools.
min_schools=per_school_summary_df.sort_values(["% Overall Passing"], ascending=True)
min_schools.head()

# ## Math and Reading Scores by Grade

# +
# Create a Series of scores by grade levels using conditionals.
students_ninth = school_data_complete_df[(school_data_complete_df["grade"] == "9th")]
students_tenth = school_data_complete_df[(school_data_complete_df["grade"] == "10th")]
students_eleventh = school_data_complete_df[(school_data_complete_df["grade"] == "11th")]
students_twelfth = school_data_complete_df[(school_data_complete_df["grade"] == "12th")]

# Group each school Series by the school name for the average math score.
ninth_math_score = students_ninth.groupby(["school_name"]).mean()["math_score"]
tenth_math_score = students_tenth.groupby(["school_name"]).mean()["math_score"]
eleventh_math_score = students_eleventh.groupby(["school_name"]).mean()["math_score"]
twelfth_math_score = students_twelfth.groupby(["school_name"]).mean()["math_score"]

# Group each school Series by the school name for the average reading score.
ninth_reading_score = students_ninth.groupby(["school_name"]).mean()["reading_score"]
tenth_reading_score = students_tenth.groupby(["school_name"]).mean()["reading_score"]
eleventh_reading_score = students_eleventh.groupby(["school_name"]).mean()["reading_score"]
twelfth_reading_score = students_twelfth.groupby(["school_name"]).mean()["reading_score"]

# +
# Combine each Series for average math scores by school into single data frame.
math_scores_per_grade = pd.DataFrame({
               "9th": ninth_math_score,
               "10th": tenth_math_score,
               "11th": eleventh_math_score,
               "12th": twelfth_math_score})

math_scores_per_grade.head()

# +
# Combine each Series for average reading scores by school into single data frame.
reading_scores_per_grade = pd.DataFrame({
              "9th": ninth_reading_score,
              "10th": tenth_reading_score,
              "11th": eleventh_reading_score,
              "12th": twelfth_reading_score})

reading_scores_per_grade.head()
# -

# Format each grade column.
math_scores_per_grade["9th"] = math_scores_per_grade["9th"].map("{:.1f}".format)
math_scores_per_grade["10th"] = math_scores_per_grade["10th"].map("{:.1f}".format)
math_scores_per_grade["11th"] = math_scores_per_grade["11th"].map("{:.1f}".format)
math_scores_per_grade["12th"] = math_scores_per_grade["12th"].map("{:.1f}".format)


# +
# Remove the index.
math_scores_per_grade.index.name = None

# Display the data frame
math_scores_per_grade.head()

# +
## Remove the index.
reading_scores_per_grade.index.name = None

# Display the data frame
reading_scores_per_grade.head()
# -

# ## Scores by School Spending

# +
# Establish the spending bins and group names.
spending_bins=[0,585,630,645,675]
group_names=["<$584","$585-629","$630-644","$645-675"]

# Categorize spending based on the bins.
per_school_summary_df["Spending Ranges (Per Student)"]=pd.cut(per_school_capita,spending_bins,labels=group_names)
# -

# Calculate averages for the desired columns. 
spent_math_score=per_school_summary_df.groupby(["Spending Ranges (Per Student)"]).mean()["Average Math Score"]
spent_reading_score=per_school_summary_df.groupby(["Spending Ranges (Per Student)"]).mean()["Average Reading Score"]
spent_pass_math=per_school_summary_df.groupby(["Spending Ranges (Per Student)"]).mean()["% Passing Math"]
spent_pass_reading=per_school_summary_df.groupby(["Spending Ranges (Per Student)"]).mean()["% Passing Reading"]
spent_pass_overall=per_school_summary_df.groupby(["Spending Ranges (Per Student)"]).mean()["% Overall Passing"]

# Create the DataFrame
spent_summary_df=pd.DataFrame({
    "Average Math Score":spent_math_score,
    "Average Reading Score":spent_reading_score,
    "% Passing Math":spent_pass_math,
    "% Passing Reading":spent_pass_reading,
    "% Overall Passing":spent_pass_overall})

# Format the DataFrame 
spent_summary_df["Average Math Score"] = spent_summary_df["Average Math Score"].map("{:.1f}".format)
spent_summary_df["Average Reading Score"] = spent_summary_df["Average Reading Score"].map("{:.1f}".format)
spent_summary_df["% Passing Math"] = spent_summary_df["% Passing Math"].map("{:.1f}".format)
spent_summary_df["% Passing Reading"] = spent_summary_df["% Passing Reading"].map("{:.1f}".format)
spent_summary_df["% Overall Passing"] = spent_summary_df["% Overall Passing"].map("{:.1f}".format)

# Printing the DataFrame
spent_summary_df

# ## Scores by School Size

# +
# Establish the bins.
enrollment_bins=[0,1000,2000,5000]
enrollment_names=["Small (<1000)","Medium (1000-2000)","Large (2000-5000)"]

# Categorize spending based on the bins.
per_school_summary_df["School Size"]=pd.cut(per_school_summary_df["Total Students"],enrollment_bins,labels=enrollment_names)

# -

# Calculate averages for the desired columns. 
size_math_score=per_school_summary_df.groupby(["School Size"]).mean()["Average Math Score"]
size_reading_score=per_school_summary_df.groupby(["School Size"]).mean()["Average Reading Score"]
size_pass_math=per_school_summary_df.groupby(["School Size"]).mean()["% Passing Math"]
size_pass_reading=per_school_summary_df.groupby(["School Size"]).mean()["% Passing Reading"]
size_pass_overall=per_school_summary_df.groupby(["School Size"]).mean()["% Overall Passing"]

# Assemble into DataFrame. 
size_score_summary_df=pd.DataFrame({
    "Average Math Score":size_math_score,
    "Average Reading Score":size_reading_score,
    "% Passing Math":size_pass_math,
    "% Passing Reading":size_pass_reading,
    "% Overall Passing":size_pass_overall})

# Format the DataFrame  
size_score_summary_df["Average Math Score"]=size_score_summary_df["Average Math Score"].map("{:.1f}".format)
size_score_summary_df["Average Reading Score"]=size_score_summary_df["Average Reading Score"].map("{:.1f}".format)
size_score_summary_df["% Passing Math"]=size_score_summary_df["% Passing Math"].map("{:.1f}".format)
size_score_summary_df["% Passing Reading"]=size_score_summary_df["% Passing Reading"].map("{:.1f}".format)
size_score_summary_df["% Overall Passing"]=size_score_summary_df["% Overall Passing"].map("{:.1f}".format)

# +
# Printing the DataFrame

size_score_summary_df
# -

# ## Scores by School Type

# +
# WE DO NOT ADD ANOTHER COLUMN SIN "SCHOOL TYPE" IS ALREADY IN THE DATAFRAME WE'RE WORKING ON

# Calculate averages for the desired columns. 
type_math_score=per_school_summary_df.groupby(["School Type"]).mean()["Average Math Score"]
type_reading_score=per_school_summary_df.groupby(["School Type"]).mean()["Average Reading Score"]
type_pass_math=per_school_summary_df.groupby(["School Type"]).mean()["% Passing Math"]
type_pass_reading=per_school_summary_df.groupby(["School Type"]).mean()["% Passing Reading"]
type_pass_overall=per_school_summary_df.groupby(["School Type"]).mean()["% Overall Passing"]
# -

# Assemble into DataFrame. 
type_score_summary_df=pd.DataFrame({
    "Average Math Score":type_math_score,
    "Average Reading Score":type_reading_score,
    "% Passing Math":type_pass_math,
    "% Passing Reading":type_pass_reading,
    "% Overall Passing":type_pass_overall})

# # Format the DataFrame 
type_score_summary_df["Average Math Score"]=type_score_summary_df["Average Math Score"].map("{:.1f}".format)
type_score_summary_df["Average Reading Score"]=type_score_summary_df["Average Reading Score"].map("{:.1f}".format)
type_score_summary_df["% Passing Math"]=type_score_summary_df["% Passing Math"].map("{:.1f}".format)
type_score_summary_df["% Passing Reading"]=type_score_summary_df["% Passing Reading"].map("{:.1f}".format)
type_score_summary_df["% Overall Passing"]=type_score_summary_df["% Overall Passing"].map("{:.1f}".format)

type_score_summary_df

# +
# END OF THE CHALLENGE 4 CODE
# -

# ## Previous code performed for Module 4 activites

# +
# Reading the schools csv file

#Adding Pandas dependency.
import pandas as pd

#Adding the CSV files
school_data_to_load = "Resources/schools_complete.csv"
student_data_to_load = "Resources/students_complete.csv"

#Confirmming Jupyter is reading the csv files correctly
school_data_df = pd.read_csv(school_data_to_load)
school_data_df

# +
# PRO TIP 1 - Head

#Confirmming Jupyter is reading the csv files correctly
school_data_df = pd.read_csv(school_data_to_load)
school_data_df.head()

# +
# PRO TIP 2 - Tail

#Confirmming Jupyter is reading the csv files correctly
school_data_df = pd.read_csv(school_data_to_load)
school_data_df.tail()

# +
# Reading the students csv file

#Confirmming Jupyter is reading the csv files correctly
student_data_df = pd.read_csv(student_data_to_load)
student_data_df

# +
# COUNT METHOD - SCHOOLS

# count function
school_data_df.count()

# +
# COUNT METHOD (total data) - STUDENTS

# count function
student_data_df.count()

# +
# ISNULL METHOD (emtpy cells) - SCHOOLS

# is null function
school_data_df.isnull()

# +
# ISNULL METHOD - STUDENTS

# is null function
student_data_df.isnull()

# +
# ISNULL METHOD + SUM - STUDENTS

# is null function
student_data_df.isnull().sum()

# +
# NOTNULL METHOD (full cels)- SCHOOLS

# not null function
school_data_df.notnull()

# +
# NOTNULL METHOD + SUM - STUDENTS

# not null function
student_data_df.notnull().sum()

# +
# DATA TYPE - SCHOOLS

# dtypes function
school_data_df.dtypes

# +
# DATA TYPE/ONE COLUMN WITH ONE WORD - SCHOOLS

# dtypes function
school_data_df.budget.dtype

# +
# DATA TYPE/ONE COLUMN WITH TWO WORDS - SCHOOLS

# dtypes function
school_data_df["School ID"].dtype

# +
# DATATYPE - STUDENTS

# not null function
student_data_df.dtypes

# +
#NEW LIST TO CLEAN DATA

# Add each prefix and suffix to remove to a list.
prefixes_suffixes = ["Dr. ", "Mr. ","Ms. ", "Mrs. ", "Miss ", " MD", " DDS", " DVM", " PhD"]

# Iterate through the words in the "prefixes_suffixes" list and replace them with an empty space, "".
for word in prefixes_suffixes:
    student_data_df["student_name"] = student_data_df["student_name"].str.replace(word,"")

student_data_df

# +
# MERGE

# Combine the data into a single dataset.
school_data_complete_df = pd.merge(student_data_df, school_data_df, on=["school_name", "school_name"])
school_data_complete_df.head()

# +
# CHECKING THE DATA WITH "COUNT" AGAINST FIRST "COUNT" BEFORE CLEANSE

# Get the total number of students.
student_count = school_data_complete_df.count()
student_count

# +
# COUNT the number of students

student_count = school_data_complete_df["Student ID"].count()
student_count

# +
# Getting the number of schools with the original DataFrame - Alternative 1

# Calculate the total number of schools.
school_count = school_data_df["school_name"].count()
school_count

# +
# UNIQUE - Getting the number 15 schools using the combined DataFrame - Alternative 2.1

# Calculate the total number of schools
school_count_2 = school_data_complete_df["school_name"].unique()
school_count_2

# +
# Getting the number of schools using the combined DataFrame after UNIQUE function - Alternative 2.2

#Using len to get the total numbers of schools
len(school_data_complete_df["school_name"].unique())


# +
# Getting the budget in the correct way (not using sum on the combined DataFrame)

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

# +
# Getting the average reading score

# Calculate the average reading score.
average_reading_score = school_data_complete_df["reading_score"].mean()
average_reading_score

# +
# Getting the average math score

# Calculate the average math score.
average_math_score = school_data_complete_df["math_score"].mean()
average_math_score

# +
# Determining passing grade for math

passing_math = school_data_complete_df["math_score"] >= 70
passing_math

# +
# Determining passing grade for reading

passing_reading = school_data_complete_df["reading_score"] >= 70
passing_reading
# -

# Get all the students who are passing math in a new DataFrame.
passing_math = school_data_complete_df[school_data_complete_df["math_score"] >= 70]
passing_math.head()

# Get all the students that are passing reading in a new DataFrame.
passing_reading = school_data_complete_df[school_data_complete_df["reading_score"] >= 70]
passing_reading.head()

# +
# Getting the students that passed math

passing_math_count = passing_math["student_name"].count()
passing_math_count

# +
# Getting the students that passed reading

passing_reading_count = passing_reading["student_name"].count()
passing_reading_count

# +
# Getting the percentages

# Calculate the percent that passed math.
passing_math_percentage = passing_math_count / float(student_count) * 100

# Calculate the percent that passed reading.
passing_reading_percentage = passing_reading_count / float(student_count) * 100

print(passing_math_percentage)
print(passing_reading_percentage)

# +
# Getting who passed both 

# Calculate the students who passed both math and reading.
passing_math_reading = school_data_complete_df[(school_data_complete_df["math_score"] >= 70) & (school_data_complete_df["reading_score"] >= 70)]

passing_math_reading.head()

# +
# Number of students who passed both

overall_passing_math_reading_count = passing_math_reading["student_name"].count()
overall_passing_math_reading_count
# -

# Calculate the overall passing percentage.
overall_passing_percentage = overall_passing_math_reading_count / student_count * 100
overall_passing_percentage

# +
# Creating a DataFrame summary

# declaring the new DataFrame dictionary
district_summary_df = pd.DataFrame([{"Total Schools": school_count,
          "Total Students": student_count,
          "Total Budget": total_budget,
          "Average Math Score": average_math_score,
          "Average Reading Score": average_reading_score,
          "% Passing Math": passing_math_percentage,
         "% Passing Reading": passing_reading_percentage,
        "% Overall Passing": overall_passing_percentage}])

district_summary_df

# +
# FORMAT function

# Format the "Total Students" to have the comma for a thousands separator.
district_summary_df["Total Students"] = district_summary_df["Total Students"].map("{:,}".format)

district_summary_df["Total Students"]


# +
# FUNCTIONS

# Define a function that calculates the percentage of students that passed both 
# math and reading and returns the passing percentage when the function is called.

def passing_math_percent(pass_math_count, student_count):
    return pass_math_count / float(student_count) * 100


# -

passing_math_count = 29370
total_student_count = 39170

# Call the function.
passing_math_percent (passing_math_count, total_student_count)

# +
 # FORMAT function

# Format "Total Budget" to have the comma for a thousands separator, a decimal separator, and a "$".

district_summary_df["Total Budget"] = district_summary_df["Total Budget"].map("${:,.2f}".format)

district_summary_df["Total Budget"]

# +
# FORMAT function

# Format the columns.
district_summary_df["Average Math Score"] = district_summary_df["Average Math Score"].map("{:.1f}".format)

district_summary_df["Average Reading Score"] = district_summary_df["Average Reading Score"].map("{:.1f}".format)

district_summary_df["% Passing Math"] = district_summary_df["% Passing Math"].map("{:.0f}".format)

district_summary_df["% Passing Reading"] = district_summary_df["% Passing Reading"].map("{:.0f}".format)

district_summary_df["% Overall Passing"] = district_summary_df["% Overall Passing"].map("{:.0f}".format)
# -

district_summary_df

# +
# REORDERING columns

# Reorder the columns in the order you want them to appear.
new_column_order = ["Total Schools", "Total Students", "Total Budget","Average Math Score", "Average Reading Score", "% Passing Math", "% Passing Reading", "% Overall Passing"]

# Assign district summary df the new column order.
district_summary_df = district_summary_df[new_column_order]
district_summary_df

# +
# Placing a NEW INDEX

# Determine the school type.
per_school_types = school_data_df.set_index(["school_name"])["type"]
per_school_types

# +
# Making it a dataframe

# Add the per_school_types into a DataFrame for testing.
df = pd.DataFrame(per_school_types)
df

# +
# Getting the number of students using SCHOOL_DATA_DF

# Calculate the total student count.
per_school_counts = school_data_df["size"]
per_school_counts
# -

# Calculate the total student count.
per_school_counts = school_data_df.set_index(["school_name"])["size"]
per_school_counts

# +
# Getting the number of students using SCHOOL_DATA_COMPLETE_DF

# Calculate the total student count.
per_school_counts = school_data_complete_df["school_name"].value_counts()
per_school_counts

# +
# Getting the budget per student

# Calculate the total school budget.
per_school_budget = school_data_df.set_index(["school_name"])["budget"]
per_school_budget

# -

# Calculate the per capita spending.
per_school_capita = per_school_budget / per_school_counts
per_school_capita

# +
# Getting the average grades per school

# Calculate the math scores.
student_school_math = student_data_df.set_index(["school_name"])["math_score"]
student_school_math

# +
# GROUP BY function

# Calculate the average math scores.
per_school_averages = school_data_complete_df.groupby(["school_name"]).mean()
per_school_averages

# +
# Calculate the average test score for math
per_school_math = school_data_complete_df.groupby(["school_name"]).mean()["math_score"]

per_school_math

# +
# Calculate the average test score for math
per_school_reading = school_data_complete_df.groupby(["school_name"]).mean()["reading_score"]

per_school_reading

# +
# Getting the passing percentages per school

 # To get the passing percentages, we need to:
 # 1. Determine what is the passing grade.
 # 2. Get the number of students who passed math and reading.
 # 3. Get the students who passed math and passed reading

# Calculate the passing scores by creating a filtered DataFrame.
per_school_passing_math = school_data_complete_df[(school_data_complete_df["math_score"] >= 70)]

per_school_passing_reading = school_data_complete_df[(school_data_complete_df["reading_score"] >= 70)]

per_school_passing_math

# +
# Calculate the number of students passing math and passing reading by school.
per_school_passing_math = per_school_passing_math.groupby(["school_name"]).count()["student_name"]

per_school_passing_reading = per_school_passing_reading.groupby(["school_name"]).count()["student_name"]

per_school_passing_math

# +
# Calculate the percentage of passing math and reading scores per school.
per_school_passing_math = per_school_passing_math / per_school_counts * 100

per_school_passing_reading = per_school_passing_reading / per_school_counts * 100

per_school_passing_math

# +
# Calculate the students who passed both math and reading.
per_passing_math_reading = school_data_complete_df[(school_data_complete_df["math_score"] >= 70) & (school_data_complete_df["reading_score"] >= 70)]

per_passing_math_reading.head()
# -

# Calculate the number of students who passed both math and reading.
per_passing_math_reading = per_passing_math_reading.groupby(["school_name"]).count()["student_name"]

# Calculate the overall passing percentage.
per_overall_passing_percentage = per_passing_math_reading / per_school_counts * 100
per_overall_passing_percentage

# +
# Adding a list of values with keys to create a new DataFrame.

per_school_summary_df = pd.DataFrame({
             "School Type": per_school_types,
             "Total Students": per_school_counts,
             "Total School Budget": per_school_budget,
             "Per Student Budget": per_school_capita,
             "Average Math Score": per_school_math,
           "Average Reading Score": per_school_reading,
           "% Passing Math": per_school_passing_math,
           "% Passing Reading": per_school_passing_reading,
           "% Overall Passing": per_overall_passing_percentage})
per_school_summary_df.head()

# +
# Adding $ sign, two decimals and thousands separator

# Format the Total School Budget and the Per Student Budget columns.
per_school_summary_df["Total School Budget"] = per_school_summary_df["Total School Budget"].map("${:,.2f}".format)

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


# Display the data frame
per_school_summary_df.head()

# +
# 4.8.8 In case we had to reorder columns
# Reorder the columns in the order you want them to appear.
new_column_order = ["School Type", "Total Students", "Total School Budget", "Per Student Budget", "Average Math Score", "Average Reading Score", "% Passing Math", "% Passing Reading", "% Overall Passing"]

# Assign district summary df the new column order.
per_school_summary_df = per_school_summary_df[new_column_order]

per_school_summary_df.head()

# +
# 4.9.1 Sort values function for higher overall scores

# Sort and show top five schools.
top_schools = per_school_summary_df.sort_values(["% Overall Passing"], ascending=False)

top_schools.head()

# +
# 4.9.2 Sort values function for lowers overall scores

# Sort and show top five schools.
bottom_schools = per_school_summary_df.sort_values(["% Overall Passing"], ascending=True)

bottom_schools.head()

# +
# 4.10.1 - Grades per level

# Create a grade level DataFrames.
ninth_graders = school_data_complete_df[(school_data_complete_df["grade"] == "9th")]

tenth_graders = school_data_complete_df[(school_data_complete_df["grade"] == "10th")]

eleventh_graders = school_data_complete_df[(school_data_complete_df["grade"] == "11th")]

twelfth_graders = school_data_complete_df[(school_data_complete_df["grade"] == "12th")]

ninth_graders.head()

# +
# 4.10.2 - Score Averages Grouped by School Name

# Group each grade level DataFrame by the school name for the average math score.
ninth_grade_math_scores = ninth_graders.groupby(["school_name"]).mean()["math_score"]

tenth_grade_math_scores = tenth_graders.groupby(["school_name"]).mean()["math_score"]

eleventh_grade_math_scores = eleventh_graders.groupby(["school_name"]).mean()["math_score"]

twelfth_grade_math_scores = twelfth_graders.groupby(["school_name"]).mean()["math_score"]

eleventh_grade_math_scores

# +
# Group each grade level DataFrame by the school name for the average reading score.
ninth_grade_reading_scores = ninth_graders.groupby(["school_name"]).mean()["reading_score"]

tenth_grade_reading_scores = tenth_graders.groupby(["school_name"]).mean()["reading_score"]

eleventh_grade_reading_scores = eleventh_graders.groupby(["school_name"]).mean()["reading_score"]

twelfth_grade_reading_scores = twelfth_graders.groupby(["school_name"]).mean()["reading_score"]

twelfth_grade_reading_scores

# +
# 4.10.3 Comine each grade level Series into a DataFrame

# Combine each grade level Series for average math scores by school into a single DataFrame.
math_scores_by_grade = pd.DataFrame({
               "9th": ninth_grade_math_scores,
               "10th": tenth_grade_math_scores,
               "11th": eleventh_grade_math_scores,
               "12th": twelfth_grade_math_scores})

math_scores_by_grade.head()

# +
# Combine each grade level Series for average reading scores by school into a single DataFrame.
reading_scores_by_grade = pd.DataFrame({
              "9th": ninth_grade_reading_scores,
              "10th": tenth_grade_reading_scores,
              "11th": eleventh_grade_reading_scores,
              "12th": twelfth_grade_reading_scores})

reading_scores_by_grade.head()

# +
# 4.10.4 - Format the Averages and Remove the Index Name

# Math
# Format each grade column.
math_scores_by_grade["9th"] = math_scores_by_grade["9th"].map("{:.1f}".format)

math_scores_by_grade["10th"] = math_scores_by_grade["10th"].map("{:.1f}".format)

math_scores_by_grade["11th"] = math_scores_by_grade["11th"].map("{:.1f}".format)

math_scores_by_grade["12th"] = math_scores_by_grade["12th"].map("{:.1f}".format)

# Make sure the columns are in the correct order.
math_scores_by_grade = math_scores_by_grade[
             ["9th", "10th", "11th", "12th"]]

# Remove the index name.
math_scores_by_grade.index.name = None

# Display the DataFrame.
math_scores_by_grade.head()

# +
# Reading
# Format each grade column.
reading_scores_by_grade["9th"] = reading_scores_by_grade["9th"].map("{:,.1f}".format)

reading_scores_by_grade["10th"] = reading_scores_by_grade["10th"].map("{:,.1f}".format)

reading_scores_by_grade["11th"] = reading_scores_by_grade["11th"].map("{:,.1f}".format)

reading_scores_by_grade["12th"] = reading_scores_by_grade["12th"].map("{:,.1f}".format)

# Make sure the columns are in the correct order.
reading_scores_by_grade = reading_scores_by_grade[
             ["9th", "10th", "11th", "12th"]]

# Remove the index name.
reading_scores_by_grade.index.name = None

# Display the data frame.
reading_scores_by_grade.head()

# +
# 4.11.1 Establish the Spending Ranges per Student

# Get the descriptive statistics for the per_school_capita.
# per_school_capita.describe()

# +
# CUT FUNCTION

# Cut the per_school_capita into the spending ranges.

# ALWAYS put the 0 when defining separtions
spending_bins = [0, 585, 615, 645, 675]
pd.cut(per_school_capita, spending_bins)

# +
# Cut the per_school_capita into the spending ranges.

# At first we used 615 but the amount of schools was not proportionate, so we changed to 630 as the second paremeter.
spending_bins = [0, 585, 630, 645, 675]
per_school_capita.groupby(pd.cut(per_school_capita, spending_bins)).count()
# -

# Establish the spending bins and group names.
spending_bins = [0, 585, 630, 645, 675]
group_names = ["<$584", "$585-629", "$630-644", "$645-675"]

# +
# 4.11.2 - Categorize the Spending Bins

# Categorize spending based on the bins.
per_school_summary_df["Spending Ranges (Per Student)"] = pd.cut(per_school_capita, spending_bins, labels=group_names)

per_school_summary_df

# +
# 4.11.3 - Group by the Spending Ranges

# Calculate averages for the desired columns.
spending_math_scores = per_school_summary_df.groupby(["Spending Ranges (Per Student)"]).mean()["Average Math Score"]

spending_reading_scores = per_school_summary_df.groupby(["Spending Ranges (Per Student)"]).mean()["Average Reading Score"]

spending_passing_math = per_school_summary_df.groupby(["Spending Ranges (Per Student)"]).mean()["% Passing Math"]

spending_passing_reading = per_school_summary_df.groupby(["Spending Ranges (Per Student)"]).mean()["% Passing Reading"]

overall_passing_spending = per_school_summary_df.groupby(["Spending Ranges (Per Student)"]).mean()["% Overall Passing"]

overall_passing_spending

# +
# 4.11.4 - Create a DataFrame for the Scores by School Spending

# Assemble into DataFrame.
spending_summary_df = pd.DataFrame({
          "Average Math Score" : spending_math_scores,
          "Average Reading Score": spending_reading_scores,
          "% Passing Math": spending_passing_math,
          "% Passing Reading": spending_passing_reading,
          "% Overall Passing": overall_passing_spending})

spending_summary_df

# +
# Formatting
spending_summary_df["Average Math Score"] = spending_summary_df["Average Math Score"].map("{:.1f}".format)

spending_summary_df["Average Reading Score"] = spending_summary_df["Average Reading Score"].map("{:.1f}".format)

spending_summary_df["% Passing Math"] = spending_summary_df["% Passing Math"].map("{:.0f}".format)

spending_summary_df["% Passing Reading"] = spending_summary_df["% Passing Reading"].map("{:.0f}".format)

spending_summary_df["% Overall Passing"] = spending_summary_df["% Overall Passing"].map("{:.0f}".format)

spending_summary_df

# +
# 4.12.1 - Create Bins for School Size

# Establish the bins.
size_bins = [0, 1000, 2000, 5000]
group_names = ["Small (<1000)", "Medium (1000-2000)", "Large (2000-5000)"]

# +
# 4.12.2 Categorize the Size Bins

# Categorize spending based on the bins.
per_school_summary_df["School Size"] = pd.cut(per_school_summary_df["Total Students"], size_bins, labels=group_names)

per_school_summary_df.head()

# +
# 4.12.3 - Group by School Size

# Calculate averages for the desired columns.
size_math_scores = per_school_summary_df.groupby(["School Size"]).mean()["Average Math Score"]

size_reading_scores = per_school_summary_df.groupby(["School Size"]).mean()["Average Reading Score"]

size_passing_math = per_school_summary_df.groupby(["School Size"]).mean()["% Passing Math"]

size_passing_reading = per_school_summary_df.groupby(["School Size"]).mean()["% Passing Reading"]

size_overall_passing = per_school_summary_df.groupby(["School Size"]).mean()["% Overall Passing"]

# +
# 4.12.4 - Creat a DataFrame for th Scores by School Size

# Assemble into DataFrame.
size_summary_df = pd.DataFrame({
          "Average Math Score" : size_math_scores,
          "Average Reading Score": size_reading_scores,
          "% Passing Math": size_passing_math,
          "% Passing Reading": size_passing_reading,
          "% Overall Passing": size_overall_passing})

size_summary_df

# +
# Formatting.
size_summary_df["Average Math Score"] = size_summary_df["Average Math Score"].map("{:.1f}".format)

size_summary_df["Average Reading Score"] = size_summary_df["Average Reading Score"].map("{:.1f}".format)

size_summary_df["% Passing Math"] = size_summary_df["% Passing Math"].map("{:.0f}".format)

size_summary_df["% Passing Reading"] = size_summary_df["% Passing Reading"].map("{:.0f}".format)

size_summary_df["% Overall Passing"] = size_summary_df["% Overall Passing"].map("{:.0f}".format)

size_summary_df

# +
# 4.13.1 - Group by School Type

# Calculate averages for the desired columns.
type_math_scores = per_school_summary_df.groupby(["School Type"]).mean()["Average Math Score"]

type_reading_scores = per_school_summary_df.groupby(["School Type"]).mean()["Average Reading Score"]

type_passing_math = per_school_summary_df.groupby(["School Type"]).mean()["% Passing Math"]

type_passing_reading = per_school_summary_df.groupby(["School Type"]).mean()["% Passing Reading"]

type_overall_passing = per_school_summary_df.groupby(["School Type"]).mean()["% Overall Passing"]

# +
# 4.13.2 - Create a DataFrame for the Scores by School Type

# Assemble into DataFrame.
type_summary_df = pd.DataFrame({
          "Average Math Score" : type_math_scores,
          "Average Reading Score": type_reading_scores,
          "% Passing Math": type_passing_math,
          "% Passing Reading": type_passing_reading,
          "% Overall Passing": type_overall_passing})

type_summary_df

# +
# Formatting
type_summary_df["Average Math Score"] = type_summary_df["Average Math Score"].map("{:.1f}".format)

type_summary_df["Average Reading Score"] = type_summary_df["Average Reading Score"].map("{:.1f}".format)

type_summary_df["% Passing Math"] = type_summary_df["% Passing Math"].map("{:.0f}".format)

type_summary_df["% Passing Reading"] = type_summary_df["% Passing Reading"].map("{:.0f}".format)

type_summary_df["% Overall Passing"] = type_summary_df["% Overall Passing"].map("{:.0f}".format)

type_summary_df
# -



StarcoderdataJupyterScriptsDedupFiltered