<jupyter_start><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.
student_data_df.loc[(student_data_df["grade"] == "9th") &
                    (student_data_df["school_name"] == "Thomas High School"),
                    ["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["grade"] == "9th") &
                    (student_data_df["school_name"] == "Thomas High School"),
                    ["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<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"])
# Calculate the Totals (Schools and Students)
student_count = len(school_data_complete_df["Student ID"])
school_count = len(school_data_complete_df["school_name"].unique())
print(student_count)
print(school_count)

# Calculate the Total Budget
total_budget = school_data_df["budget"].sum()
print(total_budget)
# 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()
print(average_reading_score)
print(average_math_score)
# Step 1. Get the number of students that are in ninth grade at Thomas High School.
# These students have no grades. 
studentCount_THS9 = len(school_data_complete_df.loc[(school_data_complete_df['grade'] == '9th')
                                                    & (school_data_complete_df['school_name'] == 'Thomas High School'),
                                                    ['Student ID']])
studentCount_THS9
# 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 - studentCount_THS9
new_student_count
# Calculate the passing rates using the "clean_student_data".

passing_math_scores = school_data_complete_df[school_data_complete_df['math_score'] >= 70].count()["math_score"]

passing_reading_scores = school_data_complete_df[school_data_complete_df['reading_score'] >= 70].count()["reading_score"]

print(passing_math_scores)
print(passing_reading_scores)

# Step 3. Calculate the passing percentages with the new total student count.
passing_math_percent = passing_math_scores/new_student_count * 100
passing_reading_percent =passing_reading_scores/new_student_count * 100

print(passing_math_percent)
print(passing_reading_percent)
# Calculate the students who passed both reading and math.
passing_both = len(school_data_complete_df.loc[(school_data_complete_df['math_score'] >=70) &
                                               (school_data_complete_df['reading_score'] >= 70), ['Student ID']])

print(passing_both)
# Step 4.Calculate the overall passing percentage with new total student count.
passing_both_percent = passing_both/new_student_count * 100

print(passing_both_percent)
# 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_percent,
                      "% Passing Reading": passing_reading_percent,
                      "% Overall Passing": passing_both_percent}])

# 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<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})
# 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.tail()
# Step 5.  Get the number of 10th-12th graders from Thomas High School (THS).
#total students at THS
student_count_THS = len(school_data_complete_df.loc[(school_data_complete_df["school_name"] == 'Thomas High School'),
                                                      ['Student ID']])

# Student count for Thomas High School ninth grade
studentCount_THS9 = len(school_data_complete_df.loc[(school_data_complete_df['grade'] == '9th')
                                                    & (school_data_complete_df['school_name'] == 'Thomas High School'),
                                                    ['Student ID']])

new_student_count_THS = student_count_THS - studentCount_THS9

print(student_count_THS)
print(studentCount_THS9)
print(new_student_count_THS)
# Step 6. Get all the students passing math from THS
student_ths_math_df = pd.DataFrame()
student_ths_math_df = school_data_complete_df.loc[(school_data_complete_df["math_score"] >= 70) &
                                                    (school_data_complete_df["school_name"] == "Thomas High School")&
                                                   (school_data_complete_df["grade"] != "9th"),
                                                    ["student_name", "math_score"]]
# Step 7. Get all the students passing reading from THS
student_ths_reading_df = pd.DataFrame()
student_ths_reading_df = school_data_complete_df.loc[(school_data_complete_df["reading_score"] >= 70) &
                                                    (school_data_complete_df["school_name"] == "Thomas High School")&
                                                   (school_data_complete_df["grade"] != "9th"),
                                                    ["student_name", "reading_score"]]
# Step 8. Get all the students passing math and reading from THS
student_ths_both_df = pd.DataFrame()
student_ths_both_df = school_data_complete_df.loc[(school_data_complete_df["reading_score"] >= 70) &
                                                     (school_data_complete_df["math_score"] >=70) &
                                                    (school_data_complete_df["school_name"] == "Thomas High School") &
                                                   (school_data_complete_df["grade"] != "9th"),
                                                    ["student_name", "math_score", "reading_score"]]
# Step 9. Calculate the percentage of 10th-12th grade students passing math from Thomas High School. 
student_ths_math_count = student_ths_math_df["math_score"].count()

student_ths_math_percent = student_ths_math_count/new_student_count_THS * 100

print(student_ths_math_percent)
# Step 10. Calculate the percentage of 10th-12th grade students passing reading from Thomas High School.
student_ths_reading_count = student_ths_reading_df["reading_score"].count()

student_ths_reading_percent = student_ths_reading_count/new_student_count_THS * 100

print(student_ths_reading_percent)
# Step 11. Calculate the overall passing percentage of 10th-12th grade from Thomas High School. 
student_ths_both_count = student_ths_both_df["student_name"].count()

student_ths_both_percent = student_ths_both_count/new_student_count_THS * 100

print(student_ths_both_percent)
# Step 12. Replace the passing math percent for Thomas High School in the per_school_summary_df.
per_school_summary_df.loc[pd.Index(["Thomas High School"]), "% Passing Math"] = student_ths_math_percent

# Step 13. Replace the passing reading percentage for Thomas High School in the per_school_summary_df.
per_school_summary_df.loc[pd.Index(["Thomas High School"]), "% Passing Reading"] = student_ths_reading_percent
# Step 14. Replace the overall passing percentage for Thomas High School in the per_school_summary_df.
per_school_summary_df.loc[pd.Index(["Thomas High School"]), "% Overall Passing"] = student_ths_both_percent
# per_school_summary_df
per_school_summary_df.tail()<jupyter_output><empty_output><jupyter_text>High and Low Performing Schools<jupyter_code># Sort and show top five schools.
per_school_summary_df.sort_values(by='% Overall Passing', ascending= False).head()

# Sort and show bottom five schools.
per_school_summary_df.sort_values(by='% Overall Passing', ascending= True).head()<jupyter_output><empty_output><jupyter_text>Math and Reading Scores by Grade<jupyter_code># Create a DataFrame of scores by grade levels using conditionals.
grade_9_df = school_data_complete_df.loc[(school_data_complete_df["grade"] == "9th")]
grade_10_df = school_data_complete_df.loc[(school_data_complete_df["grade"] == "10th")]
grade_11_df = school_data_complete_df.loc[(school_data_complete_df["grade"] == "11th")]
grade_12_df = school_data_complete_df.loc[(school_data_complete_df["grade"] == "12th")]
# Group each school DataFrame by the school name for the average math score.
grade_9_avg_math = grade_9_df.groupby(['school_name']).mean()['math_score']

grade_10_avg_math = grade_10_df.groupby(['school_name']).mean()['math_score']

grade_11_avg_math = grade_11_df.groupby(['school_name']).mean()['math_score']

grade_12_avg_math = grade_12_df.groupby(['school_name']).mean()['math_score']
# Group each school DataFrame by the school name for the average reading score.
grade_9_avg_reading = grade_9_df.groupby(['school_name']).mean()['reading_score']

grade_10_avg_reading = grade_10_df.groupby(['school_name']).mean()['reading_score']

grade_11_avg_reading = grade_11_df.groupby(['school_name']).mean()['reading_score']

grade_12_avg_reading = grade_12_df.groupby(['school_name']).mean()['reading_score']
# Combine each DataFrame for average math scores by school into single data frame.
math_score_avg = pd.concat([grade_9_avg_math, grade_10_avg_math, grade_11_avg_math,
                            grade_12_avg_math], axis=1)

math_score_avg.columns = ['9th','10th','11th','12th']
# Combine each DataFrame for average reading scores by school into single data frame.

reading_score_avg = pd.concat([grade_9_avg_reading, grade_10_avg_reading, grade_11_avg_reading,
                               grade_12_avg_reading], axis=1)

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

# Display the data frame math
math_score_avg
# Display the data frame reading
reading_score_avg<jupyter_output><empty_output><jupyter_text>Scores by School Spending<jupyter_code>per_school_capita
# Calculate averages for the desired columns. 
per_school_capita.describe()
# 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)

per_school_summary_df.head(1)
# 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})
# 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>per_school_counts
# 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(1)
# Calculate averages for the desired columns. 

size_math_average = per_school_summary_df.groupby(["School Size"]).mean()["Average Math Score"]
size_reading_average = per_school_summary_df.groupby(["School Size"]).mean()["Average Reading Score"]
size_math_passing = per_school_summary_df.groupby(["School Size"]).mean()["% Passing Math"]
size_reading_passing = per_school_summary_df.groupby(["School Size"]).mean()["% Passing Reading"]
size_overall_passing = per_school_summary_df.groupby(["School Size"]).mean()["% Overall Passing"]
# Create the DataFrame

size_summary_df = pd.DataFrame({"Average Math Score": size_math_average,
                             "Average Reading Score": size_reading_average,
                             "% Passing Math": size_math_passing,
                             "% Passing Reading": size_reading_passing,
                             "% Overall Passing": size_overall_passing})
# 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("{:.1f}".format)
size_summary_df["% Passing Reading"] = size_summary_df["% Passing Reading"].map("{:.1f}".format)
size_summary_df["% Overall Passing"] = size_summary_df["% Overall Passing"].map("{:.1f}".format)

size_summary_df<jupyter_output><empty_output><jupyter_text>Scores by School Type<jupyter_code>per_school_summary_df
# Calculate averages for the desired columns. 
type_average_math = per_school_summary_df.groupby(["School Type"]).mean()["Average Math Score"]
type_average_reading = 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_passing_overall = per_school_summary_df.groupby(["School Type"]).mean()["% Overall Passing"]
# Assemble into DataFrame. 
type_summary_df = pd.DataFrame({"Average Math Score": type_average_math,
                                "Average Reading Score": type_average_reading,
                                "% Passing Math": type_passing_math,
                                "% Passing Reading": type_passing_reading,
                                "% Overall Passing": type_passing_overall})
# 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>PyCitySchool Mod Work<jupyter_code># Add the Pandas dependency
import pandas as pd
import os

# Files to load
#school_data_to_load = "Resources/schools_complete.csv"
#student_data_to_load = "Resources/students_complete.csv"
school_data_to_load = os.path.join("Resources", "schools_complete.csv")
student_data_to_load = os.path.join("Resources", "students_complete.csv")

school_data_df = pd.read_csv(school_data_to_load)
school_data_df
student_data_df = pd.read_csv(student_data_to_load)
student_data_df.head()
# Determin if there are any missing values in the school data.
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.
school_data_df.isnull()
#Determine if there are any missing values in the student data.
student_data_df.isnull().sum()
#determine if there are any missing values in the school data.
school_data_df.notnull().sum()
#determine if there are any missing values in the student data.
student_data_df.notnull().sum()
# Determine data type for the school DataFrame.
school_data_df.dtypes
# Determine data type for the student DataFrame.
student_data_df.dtypes
# 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,"")
# 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 total number of students.
student_count2 = school_data_complete_df.count()
student_count2
#Get the total number of students
student_count = school_data_complete_df["Student ID"].count()
student_count
#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()
len(school_count_2)
# Calculate the total budget.
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
passing_math = school_data_complete_df["math_score"] >= 70
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
# 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_math_count)
print(passing_reading_count)
# Calculate the percent that passed math.
passing_math_percentage = passing_math_count / student_count * 100

# Calculate the percent that passed reading.
passing_reading_percentage = passing_reading_count / student_count * 100
print(float(passing_math_percentage))
print(float(passing_reading_percentage))
# 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
# 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
# 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"]
district_summary_df.dtypes
# 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
# 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
# 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
# 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
# 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"]
per_school_math
per_school_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
per_school_passing_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
per_school_passing_reading
# 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()
# 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)
per_school_summary_df.head()
# Sort and show top five schools.
top_schools = per_school_summary_df.sort_values(["% Overall Passing"], ascending=False)

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

bottom_schools.head()
# 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
# 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
# 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
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()
  # 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()
per_school_capita
# 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 = [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"]
# 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
# 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
# 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
per_school_counts
# Establish the bins.
size_bins = [0, 1000, 2000, 5000]
group_names = ["Small (<1000)", "Medium (1000-2000)", "Large (2000-5000)"]
per_school_summary_df
per_school_summary_df["School Size"] = pd.cut(per_school_summary_df["Total Students"], size_bins, labels=group_names)

per_school_summary_df
# 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
# 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
type_math_scores = per_school_summary_df.groupby(["School Type"]).count()["Average Math Score"]

type_math_scores
# 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"]
type_math_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,
          "% 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.11528738860659296, 'fraction_numerical': 0.01376299057784133, 'mean_word_length': 5.492705570291777, 'pattern_counts': {'":': 68, '<': 41, '<?xml version=': 0, '>': 59, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 38, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '307562', 'license': 'MIT', 'n_tokens_mistral': 12962, 'n_tokens_neox': 12001, 'n_words': 2647, 'path': 'PyCitySchools_Challenge.ipynb', 'repo_name': 'QQrex/School_District_Analysis', 'size': '39163'}