<jupyter_start><jupyter_text>PyCitySchools<jupyter_code># Dependencies and Setup
import pandas as pd
import numpy as np

# File to Load (Remember to Change These)
school_data_to_load = "Resources/schools_complete.csv"
student_data_to_load = "Resources/students_complete.csv"

# Read School and Student Data File and store into Pandas DataFrames
school_data = pd.read_csv(school_data_to_load)
student_data = pd.read_csv(student_data_to_load)

# Combine the data into a single dataset.  
school_data_complete = pd.merge(student_data, school_data, how="left", on=["school_name", "school_name"])

school_data_complete.head()<jupyter_output><empty_output><jupyter_text>District Summary<jupyter_code># Calculations for District Summary
# Total number of schools
total_number_schools = len(school_data_complete["school_name"].unique())
print(total_number_schools)

# Total number of students
total_number_students = school_data_complete["student_name"].count()
print(total_number_students)

# Total budget
total_budget = school_data_complete["budget"].unique().sum()
print(total_budget)

# Average math score
average_math_score = school_data_complete["math_score"].mean()
print(average_math_score)

# Average reading score
average_reading_score = school_data_complete["reading_score"].mean()
print(average_reading_score)

# Percentage of students passing math
passing_math = school_data_complete["math_score"] >= 70
percentage_passing_math= school_data_complete[passing_math]["math_score"].count()/total_number_students*100
print(percentage_passing_math)

# Percentage of students passing reading
passing_reading = school_data_complete["reading_score"] >= 70
percentage_passing_reading= school_data_complete[passing_reading]["reading_score"].count()/total_number_students*100
print(percentage_passing_reading)

# Percentage of students passing
passing = school_data_complete[passing_math & passing_reading]["student_name"].count()
percentage_passing = passing/total_number_students*100
print(percentage_passing)
# Create Summary Data Frame
summary_df = pd.DataFrame({"Total Schools": [total_number_schools],
                           "Total Students": [total_number_students],
                           "Total Budget": [total_budget],
                           "Average Math Score": [average_math_score],
                           "Average Reading Score": [average_reading_score],
                           "% Passing Math": [percentage_passing_math],
                           "% Passing Reading": [percentage_passing_reading],
                           "% Overall Passing": [percentage_passing]})


summary_df.head()
#Checking data types of summary table
summary_df.dtypes
# Converting integer columns to floats
summary_df.loc[:, "Total Schools"] = summary_df["Total Schools"].astype("float")
summary_df.loc[:, "Total Students"] = summary_df["Total Students"].astype("float")
summary_df.loc[:, "Total Budget"] = summary_df["Total Budget"].astype("float")
summary_df.dtypes
# Use Map to format columns correctly
summary_df["Total Schools"] = summary_df["Total Schools"].map("{:.0f}".format)
summary_df["Total Students"] = summary_df["Total Students"].map("{:,.0f}".format)
summary_df["Total Budget"] = summary_df["Total Budget"].map("${:,.2f}".format)
summary_df.head()<jupyter_output><empty_output><jupyter_text>School Summary<jupyter_code>#Calculate for School summary
# Grouped CSV data frame by School Name 
grouped_school_df = school_data_complete.groupby(['school_name'])
grouped_school_df.count().head()
# School type
school_type = grouped_school_df['type'].first()
print(school_type)
# Total Students per school
total_students = grouped_school_df.size()
print(total_students)
# School Total Budget
school_budget = grouped_school_df['budget'].first()
print(school_budget)
# School Budget per Student
school_budget_per_student = school_budget/total_students
print(school_budget_per_student)
# Average math score
average_math_score = grouped_school_df['math_score'].mean()
print(average_math_score)
# Average reading score
average_reading_score = grouped_school_df['reading_score'].mean()
print(average_reading_score)
# Percentange of Student Passing Math
grouped_passing_math = school_data_complete[passing_math].groupby(['school_name']).size()
school_percent_passing_math = (grouped_passing_math/total_students)*100
print(school_percent_passing_math)
# Percentange of Student Passing Reading
grouped_passing_reading = school_data_complete[passing_reading].groupby(['school_name']).size()
school_percent_passing_reading = (grouped_passing_reading/total_students)*100
print(school_percent_passing_reading)
# Percentange of Student Passing both Math & Reading
grouped_passing = school_data_complete[passing_math & passing_reading].groupby(['school_name']).size()
school_percent_passing = (grouped_passing/total_students)*100
print(school_percent_passing)
# Create Summary Data Frame
school_summary_df = pd.DataFrame({'School Type': school_type, 'Total Students':total_students,
                               'Total School Budget': school_budget, 'Per Student Budget': school_budget_per_student, 
                               'Average Math Score': average_math_score, 'Average Reading Score': average_reading_score, 
                               '% Passing Math': school_percent_passing_math, '% Passing Reading': school_percent_passing_reading,
                               '% Overall Passing Rate': school_percent_passing})
school_summary_df
#Checking data types of  table
school_summary_df.dtypes
# Converting integer columns to floats
school_summary_df.loc[:, "Total Students"] = school_summary_df["Total Students"].astype("float")
school_summary_df.loc[:, "Total School Budget"] = school_summary_df["Total School Budget"].astype("float")
school_summary_df.dtypes
# Use Map to format columns correctly
school_summary_df["Total Students"] = school_summary_df["Total Students"].map("{:,.0f}".format)
school_summary_df["Total School Budget"] = school_summary_df["Total School Budget"].map("${:,.2f}".format)
school_summary_df["Per Student Budget"] = school_summary_df["Per Student Budget"].map("${:.2f}".format)
school_summary_df
# Delete fisrt column header
school_summary_df.index.name = None
school_summary_df<jupyter_output><empty_output><jupyter_text>Top Performing Schools (By % Overall Passing)<jupyter_code># Top 5 Performing schools by Percent Passing
top_schools_df = school_summary_df.sort_values("% Overall Passing Rate", ascending = False)
top_schools_df.head(5)<jupyter_output><empty_output><jupyter_text>Bottom Performing Schools (By % Overall Passing)<jupyter_code># Bottom 5 Performing schools by Percent Passing
bottom_schools_df = school_summary_df.sort_values("% Overall Passing Rate", ascending = True)
bottom_schools_df.head(5)<jupyter_output><empty_output><jupyter_text>Math Scores by Grade<jupyter_code># Calculate 9th Grade Math Scores
freshman = school_data_complete.loc[school_data_complete["grade"]=="9th"].groupby("school_name")
freshman_math_score = freshman["math_score"].mean()
print(freshman_math_score)
# Calculate 10th Grade Math Scores
sophmore = school_data_complete.loc[school_data_complete["grade"]=="10th"].groupby("school_name")
sophmore_math_score = sophmore["math_score"].mean()
print(sophmore_math_score)
# Calculate 10th Grade Math Scores
junior = school_data_complete.loc[school_data_complete["grade"]=="11th"].groupby("school_name")
junior_math_score = junior["math_score"].mean()
print(junior_math_score)
# Calculate 10th Grade Math Scores
senior = school_data_complete.loc[school_data_complete["grade"]=="12th"].groupby("school_name")
senior_math_score = senior["math_score"].mean()
print(senior_math_score)
# Create Summary Data Frame
math_score_grade_df = pd.DataFrame({"9th": freshman_math_score,
                                    "10th": sophmore_math_score,
                                    "11th": junior_math_score,
                                    "12th": senior_math_score})
math_score_grade_df
# Delete fisrt column header
math_score_grade_df.index.name = None
math_score_grade_df<jupyter_output><empty_output><jupyter_text>Reading Score by Grade<jupyter_code># Calculate 9th Grade Math Scores
freshman_reading_score = freshman["reading_score"].mean()
print(freshman_reading_score)
# Calculate 10th Grade Math Scores
sophmore_reading_score = sophmore["reading_score"].mean()
print(sophmore_reading_score)
# Calculate 11th Grade Math Scores
junior_reading_score = junior["reading_score"].mean()
print(junior_reading_score)
# Calculate 10th Grade Math Scores
senior_reading_score = senior["reading_score"].mean()
print(senior_reading_score)
# Create Summary Data Frame
reading_score_grade_df = pd.DataFrame({"9th": freshman_reading_score,
                                    "10th": sophmore_reading_score,
                                    "11th": junior_reading_score,
                                    "12th": senior_reading_score})
reading_score_grade_df
# Delete fisrt column header
reading_score_grade_df.index.name = None
reading_score_grade_df<jupyter_output><empty_output><jupyter_text>Scores by School Spending<jupyter_code># Create the bins in which Data will be held
# Create Bins based of school spending per student <585, 585-630, 630-645, 645-680
bins = [0, 585, 630, 645, 680] 

# Create the name for the Bins
group_labels = ["<$585", "$585-630", "$630-645", "$645-680"]
# Copy School Summary Data Frame to create new data frame sorted by bins
per_student_spending_df = school_summary_df.copy()
per_student_spending_df
# Place the data series into a new column inside of the DataFrame
per_student_spending_df["Spending Ranges (Per Student)"] = pd.cut(school_budget_per_student, bins, labels=group_labels)
per_student_spending_df
# Group by Spending Range
groupped_spending_df = per_student_spending_df.groupby("Spending Ranges (Per Student)")
groupped_spending_df.count().head()
# Calculations for Per Student Spending Summary
# Average math score
average_math_score_spending = groupped_spending_df["Average Math Score"].mean()
print(average_math_score_spending)

# Average reading score
average_reading_score_spending = groupped_spending_df["Average Reading Score"].mean()
print(average_reading_score_spending)

# Percentage of students passing math
percentage_passing_math_spending = groupped_spending_df["% Passing Math"].mean()
print(percentage_passing_math_spending)

# Percentage of students passing reading
percentage_passing_reading_spending = groupped_spending_df["% Passing Reading"].mean()
print(percentage_passing_reading_spending)


# Percentage of students passing math
percentage_passing_spending = groupped_spending_df["% Overall Passing Rate"].mean()
print(percentage_passing_spending)

# Create Summary Data Frame
spending_df = pd.DataFrame({"Average Math Score": average_math_score_spending, "Average Reading Score": average_reading_score_spending,
                            "% Passing Math": percentage_passing_math_spending, "% Passing Reading": percentage_passing_reading_spending,
                            "% Overall Passing": percentage_passing_spending})
spending_df
# Use Map to format columns correctly
spending_df["Average Math Score"] = spending_df["Average Math Score"].map("{:.2f}".format)
spending_df["Average Reading Score"] = spending_df["Average Reading Score"].map("{:.2f}".format)
spending_df["% Passing Math"] = spending_df["% Passing Math"].map("{:.2f}".format)
spending_df["% Passing Reading"] = spending_df["% Passing Reading"].map("{:.2f}".format)
spending_df["% Overall Passing"] = spending_df["% Overall Passing"].map("{:.2f}".format)
spending_df<jupyter_output><empty_output><jupyter_text>Scores by School Size<jupyter_code># Create the bins in which Data will be held
# Create Bins based of School Size <1000, 1000-2000, 2000-5000
bins_school_size = [0, 1000, 2000, 5000] 

# Create the name for the Bins
group_school_size = ["Small (<1000)", "Medium (1000-2000)", "Large (2000-5000)"]
# Copy School Summary Data Frame to create new data frame sorted by bins
school_size_df = school_summary_df.copy()
school_size_df
# Place the data series into a new column inside of the DataFrame
school_size_df["School Size"] = pd.cut(total_students, bins_school_size, labels=group_school_size)
school_size_df
# Group by School Size
groupped_school_size_df = school_size_df.groupby("School Size")
groupped_school_size_df.count().head()
# Calculations for School Size Summary
# Average math score
average_math_school_size = groupped_school_size_df["Average Math Score"].mean()
print(average_math_school_size)

# Average reading score
average_reading_school_size = groupped_school_size_df["Average Reading Score"].mean()
print(average_reading_school_size)

# Percentage of students passing math
percentage_passing_math_school_size = groupped_school_size_df["% Passing Math"].mean()
print(percentage_passing_math_school_size)

# Percentage of students passing reading
percentage_passing_reading_school_size = groupped_school_size_df["% Passing Reading"].mean()
print(percentage_passing_reading_school_size)

# Percentage of students passing math
percentage_passing_school_size = groupped_school_size_df["% Overall Passing Rate"].mean()
print(percentage_passing_school_size)
# Create Summary Data Frame
size_df = pd.DataFrame({"Average Math Score": average_math_school_size, "Average Reading Score": average_reading_school_size,
                            "% Passing Math": percentage_passing_math_school_size, "% Passing Reading": percentage_passing_reading_school_size,
                            "% Overall Passing": percentage_passing_school_size})
size_df
# Use Map to format columns correctly
size_df["Average Math Score"] = size_df["Average Math Score"].map("{:.2f}".format)
size_df["Average Reading Score"] = size_df["Average Reading Score"].map("{:.2f}".format)
size_df["% Passing Math"] = size_df["% Passing Math"].map("{:.2f}".format)
size_df["% Passing Reading"] = size_df["% Passing Reading"].map("{:.2f}".format)
size_df["% Overall Passing"] = size_df["% Overall Passing"].map("{:.2f}".format)
size_df<jupyter_output><empty_output><jupyter_text>Scores by School Type<jupyter_code># Group by School Type
groupped_school_type_df = school_summary_df.groupby("School Type")
groupped_school_type_df.count().head()
# Calculations for School Type Summary
# Average math score
average_math_school_type = groupped_school_type_df["Average Math Score"].mean()
print(average_math_school_type)

# Average reading score
average_reading_school_type = groupped_school_type_df["Average Reading Score"].mean()
print(average_reading_school_type)

# Percentage of students passing math
percentage_passing_math_school_type = groupped_school_type_df["% Passing Math"].mean()
print(percentage_passing_math_school_type)

# Percentage of students passing reading
percentage_passing_reading_school_type = groupped_school_type_df["% Passing Reading"].mean()
print(percentage_passing_reading_school_type)

# Percentage of students passing math
percentage_passing_school_type = groupped_school_type_df["% Overall Passing Rate"].mean()
print(percentage_passing_school_type)
# Create Summary Data Frame
type_df = pd.DataFrame({"Average Math Score": average_math_school_type, "Average Reading Score": average_reading_school_type,
                            "% Passing Math": percentage_passing_math_school_type, "% Passing Reading": percentage_passing_reading_school_type,
                            "% Overall Passing": percentage_passing_school_type})
type_df
# Use Map to format columns correctly
type_df["Average Math Score"] = type_df["Average Math Score"].map("{:.2f}".format)
type_df["Average Reading Score"] = type_df["Average Reading Score"].map("{:.2f}".format)
type_df["% Passing Math"] = type_df["% Passing Math"].map("{:.2f}".format)
type_df["% Passing Reading"] = type_df["% Passing Reading"].map("{:.2f}".format)
type_df["% Overall Passing"] = type_df["% Overall Passing"].map("{:.2f}".format)
type_df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10493672472121288, 'fraction_numerical': 0.011903270266883849, 'mean_word_length': 6.349447513812155, 'pattern_counts': {'":': 31, '<': 45, '<?xml version=': 0, '>': 43, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '291942', 'license': 'ADSL', 'n_tokens_mistral': 5195, 'n_tokens_neox': 4826, 'n_words': 1050, 'path': 'PyCitySchools/PyCitySchools.ipynb', 'repo_name': 'rteare/Pandas-Challenge', 'size': '15962'}