<jupyter_start><jupyter_text>Setup<jupyter_code># 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)
student_data_df.count()<jupyter_output><empty_output><jupyter_text>Deliverable 1: Replace the reading and math scores. Replace the 9th grade reading and math scores at Thomas High School with NaN.<jupyter_code># 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.

# Use loc to specify school, go into columns, and use logical operators

student_data_df.loc[
    (student_data_df["school_name"] == "Thomas High School") 
     & (student_data_df["grade"] == "9th") & (student_data_df["reading_score"] > 0),
    "reading_score"] = np.nan
student_data_df.loc[
    (student_data_df["school_name"] == "Thomas High School") 
     & (student_data_df["grade"] == "9th") & (student_data_df["math_score"] > 0),
    "math_score"] = np.nan
student_data_df.tail(5)
#  Step 3. Refactor the code in Step 2 to replace the math scores with NaN.
sd_df = student_data_df

sd_df.loc[
    (sd_df["school_name"] == "Thomas High School") 
     & (sd_df["grade"] == "9th") & (sd_df["math_score"] > 0),
    "math_score"] = np.nan
# Step 4. Check the student data for NaN's. 
student_data_df.tail(10)<jupyter_output><empty_output><jupyter_text>Deliverable 2 : Repeat the school district analysis District Summary<jupyter_code># 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()
# Calculate the Average Scores using the "clean_student_data".
average_reading_score = school_data_complete_df["reading_score"].mean()
average_math_score = school_data_complete_df["math_score"].mean()
# Step 1. Get the number of students that are in ninth grade at Thomas High School.
# These students have no grades. 
ninth_student_count_df = student_data_df.loc[
    (student_data_df["school_name"] == "Thomas High School") 
     & (student_data_df["grade"] == "9th"),"Student ID"].count()

# Get the total student count 
student_count = 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.
new_student_count = student_count - ninth_student_count_df
student_count
ninth_student_count_df
new_student_count
# Calculate the passing rates using the "clean_student_data".
passing_math_count = school_data_complete_df[(school_data_complete_df["math_score"] >= 70)].count()["student_name"]
passing_reading_count = school_data_complete_df[(school_data_complete_df["reading_score"] >= 70)].count()["student_name"]
# Step 3. Calculate the passing percentages with the new total student count.
passing_math_percentage = passing_math_count / new_student_count * 100
passing_reading_percentage = passing_reading_count / new_student_count * 100
# 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_math_reading_percentage = overall_passing_math_reading_count / new_student_count * 100
overall_passing_math_reading_percentage
print(passing_math_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": passing_math_percentage,
         "% Passing Reading": passing_reading_percentage,
        "% Overall Passing": overall_passing_math_reading_percentage}])

# Format the "Total Students" and "Total Budget"
district_summary_df["Total Students"] = district_summary_df["Total Students"].map("{:,}".format)
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)

district_summary_df<jupyter_output><empty_output><jupyter_text>School Summary<jupyter_code># 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).
#10th
tenth_student_count = student_data_df.loc[
    (student_data_df["school_name"] == "Thomas High School") 
     & (student_data_df["grade"] == "10th"),"Student ID"].count()

# 11th grade
eleventh_student_count = student_data_df.loc[
    (student_data_df["school_name"] == "Thomas High School") 
     & (student_data_df["grade"] == "11th"),"Student ID"].count()

#12th
twelfth_student_count = student_data_df.loc[
    (student_data_df["school_name"] == "Thomas High School") 
     & (student_data_df["grade"] == "12th"),"Student ID"].count()
tenth_to_twelfth_count = tenth_student_count + eleventh_student_count + twelfth_student_count
tenth_to_twelfth_count
# Step 6. Get all the students passing math from THS
ths_students_math_passing = school_data_complete_df.loc[
    (student_data_df["school_name"] == "Thomas High School") 
    & (student_data_df["math_score"] >= 70)]
ths_students_math_passing
# Step 7. Get all the students passing reading from THS
ths_students_reading_passing = school_data_complete_df.loc[
    (student_data_df["school_name"] == "Thomas High School") 
    & (student_data_df["reading_score"] >= 70)]
# Step 8. Get all the students passing math and reading from THS
ths_math_reading_passing = school_data_complete_df.loc[
    (student_data_df["school_name"] == "Thomas High School") 
    & (student_data_df["reading_score"] >= 70)
    & (student_data_df["math_score"] >= 70)]
# Step 9. Calculate the percentage of 10th-12th grade students passing math from Thomas High School. 
# get count and then percentage; passing math / new student count
student_math_passing_count = len(ths_students_math_passing)

student_math_passing_percentage = student_math_passing_count / tenth_to_twelfth_count * 100
student_math_passing_percentage
# Step 10. Calculate the percentage of 10th-12th grade students passing reading from Thomas High School.
student_reading_passing_count = len(ths_students_reading_passing)

student_reading_passing_percentage = student_reading_passing_count / tenth_to_twelfth_count * 100
student_reading_passing_percentage
# Step 11. Calculate the overall passing percentage of 10th-12th grade from Thomas High School. 
overall_ths_student_passing_count = len(ths_math_reading_passing)

overall_ths_student_passing_percentage = overall_ths_student_passing_count / tenth_to_twelfth_count * 100
overall_ths_student_passing_percentage
# 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"] = student_math_passing_percentage
# 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"] = student_reading_passing_percentage
# 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"] = overall_ths_student_passing_percentage
per_school_summary_df.tail(5)<jupyter_output><empty_output><jupyter_text>High and Low Performing Schools<jupyter_code># Sort and show top five schools.
top_five_schools = per_school_summary_df.sort_values(["% Overall Passing"], ascending=False)
top_five_schools.head()
# Sort and show bottom five schools.
bottom_five_schools = per_school_summary_df.sort_values(["% Overall Passing"], ascending=True)
bottom_five_schools.head()<jupyter_output><empty_output><jupyter_text>Math and Reading Scores by Grade<jupyter_code># Create a Series of scores by grade levels using conditionals. (grade level DataFrames)
all_ninth_grade = school_data_complete_df[(school_data_complete_df["grade"] == "9th")]
all_tenth_grade = school_data_complete_df[(school_data_complete_df["grade"] == "10th")]
all_eleventh_grade = school_data_complete_df[(school_data_complete_df["grade"] == "11th")]
all_twelfth_grade = school_data_complete_df[(school_data_complete_df["grade"] == "12th")]

# Group each school Series by the school name for the average math score.
all_ninth_grade_math_scores = all_ninth_grade.groupby(["school_name"]).mean()["math_score"]
all_tenth_grade_math_scores = all_tenth_grade.groupby(["school_name"]).mean()["math_score"]
all_eleventh_grade_math_scores = all_eleventh_grade.groupby(["school_name"]).mean()["math_score"]
all_twelfth_grade_math_scores = all_twelfth_grade.groupby(["school_name"]).mean()["math_score"]

# Group each school Series by the school name for the average reading score.
all_ninth_grade_reading_scores = all_ninth_grade.groupby(["school_name"]).mean()["reading_score"]
all_tenth_grade_reading_scores = all_tenth_grade.groupby(["school_name"]).mean()["reading_score"]
all_eleventh_grade_reading_scores = all_eleventh_grade.groupby(["school_name"]).mean()["reading_score"]
all_twelfth_grade_reading_scores = all_twelfth_grade.groupby(["school_name"]).mean()["reading_score"]

# Combine each Series for average math scores by school into single data frame.
math_scores_by_grade = pd.DataFrame({
    "9th" : all_ninth_grade_math_scores,
    "10th" : all_tenth_grade_math_scores,
    "11th" : all_eleventh_grade_math_scores,
    "12th" : all_twelfth_grade_math_scores})

math_scores_by_grade
# Combine each Series for average reading scores by school into single data frame.
reading_scores_by_grade = pd.DataFrame({
    "9th" : all_ninth_grade_reading_scores,
    "10th" : all_tenth_grade_reading_scores,
    "11th" : all_eleventh_grade_reading_scores,
    "12th" : all_twelfth_grade_reading_scores})

reading_scores_by_grade
# Format each grade column (math)
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)
#Format each grade column (reading)
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)
# Remove the index.
math_scores_by_grade.index.name = None

# Display the data frame
math_scores_by_grade
# Remove the index.
reading_scores_by_grade.index.name = None

# Display the data frame
reading_scores_by_grade<jupyter_output><empty_output><jupyter_text>Scores by School Spending<jupyter_code># Establish the spending bins and group names.
spending_bins = [0, 585, 615, 645, 675]
group_names = ["<$585", "$585-629", "$630-644", "$645-675"]
per_school_capita.groupby(pd.cut(per_school_capita, spending_bins)).count()

# 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_capita.describe()
# 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"]

# Create the 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
# Format the DataFrame 
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<jupyter_output><empty_output><jupyter_text>Scores by School Size<jupyter_code># Establish the bins.
size_bins = [0, 1000, 2000, 5000]
group_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"], size_bins, labels=group_names)
per_school_summary_df.head()
# 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"]
size_math_scores
# 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
# Format the DataFrame  
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<jupyter_output><empty_output><jupyter_text>Scores by School Type<jupyter_code># 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_scores = per_school_summary_df.groupby(["School Type"]).mean()["% Passing Math"]
type_passing_reading_scores = per_school_summary_df.groupby(["School Type"]).mean()["% Passing Reading"]
type_passing_overall_scores = per_school_summary_df.groupby(["School Type"]).mean()["% Overall Passing"]

# Average Math Score
# Average Reading Score
# Average % Passing Math
# Average % Passing Reading
# Average % Overall Passing

type_reading_scores
# 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_scores,
    "% Passing Reading" : type_passing_reading_scores,
    "% Overall Passing" : type_passing_overall_scores})
# # Format the DataFrame 
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<jupyter_output><empty_output><jupyter_text>Module 4<jupyter_code># Add the Pandas dependency.
import pandas as pd
# Files to load
school_data_to_load = "Resources/schools_complete.csv"
student_data_to_load = "Resources/students_complete.csv"
# Read the school data file and store it in a Pandas DataFrame.
school_data_df = pd.read_csv(school_data_to_load)
school_data_df
# Read the student data file and store it in a Pandas DataFrame.
student_data_df = pd.read_csv(student_data_to_load)
student_data_df.head()
#Next 4.4.5  Missing Values

# Determine if there are any missing values in the school data. Using count() method
school_data_df.count()

# Determine if there are any missing values in the student data.
student_data_df.count()
# Determine if there are any missing values in the school data. False == to no empty rows
school_data_df.isnull()
# Determine if there are any missing values in the student data.
student_data_df.isnull()
# To get the total number of empty rows, or rows that are "True," we can use the 
# Pandas sum() method after the isnull() method. More straightforward than output 
# that shows "False" in thousands of rows

# Determine if there are any missing values in the student data.
student_data_df.isnull().sum()
# Determine if there are not any missing values in the school data.
school_data_df.notnull()
# Determine if there are not any missing values in the student data.
student_data_df.notnull().sum()
# Determine Data Tpes 4.5.3

# Determine data types for the school DataFrame.
school_data_df.dtypes
# Determine data types for the student DataFrame.
student_data_df.dtypes
# Cleaning student data (in other notebook file)
# Fixing the Students 2019 Names: 4.5.7

# 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 DataFrames: 4.7.1

# 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()
# Get the number of datas: 4.7.2

# Get the total number of students.
student_count = school_data_complete_df.count()
student_count
# get the total number of students
student_count = school_data_complete_df["Student ID"].count()
student_count
# get the number of Schools: 4.7.3

# Calculate the total number of schools.
school_count = school_data_df["school_name"].count()
school_count
# Calculate the total number of schools
school_count_2 = school_data_complete_df["school_name"].unique()
school_count_2
# get the total budget

total_budget = school_data_complete_df["budget"].sum()
total_budget
# previous one is incorrect -> use another way
total_budget = school_data_df["budget"].sum()
total_budget
# Calculate the average reading score.
average_reading_score = school_data_complete_df["reading_score"].mean()
average_reading_score

# Calculate the average math score
average_math_score = school_data_complete_df["math_score"].mean()
average_math_score
#To get the percentage of students who passed math and reading, 
# we will write code to:
    #1. Determine the passing grade.
    #2. Get the number of students who passed math and reading in separate DataFrames.
    #3. Calculate the number of students who passed math and reading.
    #4. Calculate the percentage of students who passed math and reading.
    #5. To get the overall passing percentage, we will write code to:
    #6. Get the number of students who passed both math and reading in a DataFrame.
    #7. Calculate the number of students who passed both math and reading.
    #8. Calculate the percentage of students who passed both math and reading.
#Determine the passing code (=70)
passing_math = school_data_complete_df["math_score"] >= 70
passing_reading = school_data_complete_df["reading_score"] >= 70
passing_math = school_data_complete_df["math_score"] >= 70
passing_math
passing_reading = school_data_complete_df["reading_score"] >= 70
passing_reading
#get only the students who passed math

# 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
# Calculate the number of students passing math.
passing_math_count = passing_math["student_name"].count()

# Calculate the number of students passing reading.
passing_reading_count = passing_reading["student_name"].count()
print(passing_reading_count)
print(passing_math_count)

# Get percentage of students who passed math and reading
# To get the percentage of students who passed math and reading, 
# divide the passing_math_count and the passing_reading_count 
# by the total number of students, and then multiply by 100.

# 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)
# Calculate the overall passing percentage, getting the students 
#passed both math and reading divided by the total number of students

# 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()
# Calculate the number of students who 
# passed both math and reading.

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
# Create a District Summary DataFrame: 4.7.7

# Adding a list of values with keys to create a new 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": passing_math_percentage,
         "% Passing Reading": passing_reading_percentage,
        "% Overall Passing": overall_passing_percentage}])
district_summary_df
# Define a function that calculates the percentage of students that passed both # math and reading and prints the passing percentage to the output 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)
# Pandas map () function -> practice in other notebook file
# 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"]
# 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 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)

# Run the code
district_summary_df
# Reorder Columns: 4.7.9

# Reorder the columns in the order you want them to appear
# new_column_order = ["column2", "column4", "column1"]

#  Assign a new or the same DataFrame the new column order.
# df = df[new_column_order]

#  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
# 4.8.1 Set the Index to the School

# Determine the school type.
per_school_types = school_data_df.set_index(["school_name"])["type"]
per_school_types
# Add the per_school_types into a DataFrame for testing.
df = pd.DataFrame(per_school_types)
df
# 4.8.2 Get the Student Count Per School

# 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
# Calculate the total student count.
per_school_counts = school_data_complete_df["school_name"].value_counts()
per_school_counts
# 4.8.3 Get the Budget Per Student

# Calculate the total school budget.
per_school_budget = school_data_df.set_index(["school_name"])["budget"]
per_school_budget
# To get the budget per student, we'll divide the 
# per_school_budget by the per_school_counts.

# Calculate the per capita spending.
per_school_capita = per_school_budget / per_school_counts
per_school_capita
# 4.8.4 Get the Score Averages Per School

# Calculate the math scores.
student_school_math = student_data_df.set_index(["school_name"])["math_score"]
student_school_math
# Calculate the average math scores.
per_school_averages = school_data_complete_df.groupby(["school_name"]).mean()
per_school_averages
# 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"]

print(per_school_math)
print(per_school_reading)
# 4.8.5 Get 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
# Determine the Percentage of Students Passing Math and Reading

# 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
#Get the Overall Passing Percentage for All Students for Each School

# 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
#4.8.6 Create the School Summary DataFrame

# 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()
# 4.8.7 Clean Up the DataFrame
# 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.9.1 Find the Highest Performing Schools

#To sort the schools on the % Overall Passing column from 
#highest to lowest, add the following code and run the cell.

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

top_schools.head()
# 4.9.2 Find the Lowest Performing Schools

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

bottom_schools.head()
# 4.9.3 Object Oriented Programming -> in other notebook file (function)
# 4.10.1 Create Grade-Level DataFrames

# 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()
tenth_graders.head()
# 4.10.2 Score Averages Grouped by School Name

#get the average math scores

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

ninth_grade_math_scores
eleventh_grade_math_scores
# Get the average reading scores by school

# 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"]
ninth_grade_reading_scores.head()
twelfth_grade_reading_scores
# 4.10.3 Combine 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

# 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()
# 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
#To create this DataFrame, we need to do the following:

#1Group the schools into four bins, or ranges, based on the budget per student.
#2For each spending range, get the following data:
    #Average math and reading scores
    #The percentage of students passing math and reading
    #The overall passing percentage, which is the average of the percentage of students passing math and reading
# Get the descriptive statistics for the per_school_capita.
per_school_capita.describe()
# Cut the per_school_capita into the spending ranges.
spending_bins = [0, 585, 615, 645, 675]
pd.cut(per_school_capita, spending_bins)
# Cut the per_school_capita into the spending ranges.
spending_bins = [585, 615, 645, 675]
pd.cut(per_school_capita, spending_bins)
# Cut the per_school_capita into the spending ranges.
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

#create 4 new series:
    # Average Math Score
    # Average Reading Score
    # Average % Passing Math
    # Average % Passing Reading
    # Average % Overall Passing
# 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: adhere to the format/grade-reporting standards
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 Create a DataFrame for the 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<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10888712731756726, 'fraction_numerical': 0.014532963403355793, 'mean_word_length': 6.250159642401021, 'pattern_counts': {'":': 62, '<': 45, '<?xml version=': 0, '>': 71, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '57474', 'license': 'MIT', 'n_tokens_mistral': 15260, 'n_tokens_neox': 14121, 'n_words': 3616, 'path': 'PyCitySchools_Challenge.ipynb', 'repo_name': 'retroxsky06/School_District_Analysis', 'size': '45414'}