<jupyter_start><jupyter_text>Note* Instructions have been included for each segment. You do not have to follow them exactly, but they are included to help you think through the steps.<jupyter_code># Dependencies and Setup
import pandas as pd
import os
import csv
from matplotlib import pyplot as plt
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"
schools_data = "Resources/schools_complete.csv"
students_data = "Resources/students_complete.csv"
# Read the data file with the pandas library, Source File to Read
# Not every CSV requires an encoding, but be aware this can come up
# schools_data_df=pd.read_csv('resources/schools_complete.csv')
schools_data_df=pd.read_csv(schools_data, encoding="ISO-8859-1")

# display the header
# schools_data_df.head()

# verify counts - clean the data
# schools_data_df.count()

# display the contents of the data frame : Schools_data.csv
schools_data_df
# Read the data file with the pandas library, Source File to Read
students_data_df=pd.read_csv(students_data, encoding='iso-8859-1')

# display the header
# students_data_df.head()

# verify counts - clean the data
# students_data_df.count()

# display the contents of the data frame : Students_data.csv
students_data_df
# Combine the data into a single dataset.  
# school_data_complete = pd.merge(student_data, school_data, how="left", on=["school_name", "school_name"])
pycityschools_df=pd.merge(schools_data_df,students_data_df,how='left', on=['school_name','school_name'])

# display the contents of the data frame 
pycityschools_df
#Save remade data frame to a new csv file, pycityschools_df to /output/pycityschools_combined.csv
pycityschools_df.to_csv('Output/pycityschools_combined.csv', encoding="utf-8", index="true",header="true")
# verify counts - clean the data
pycityschools_df.count()
# Display a statistical overview of the data frame
students_data_df.describe()
# Display data types
pycityschools_df.dtypes
# Collecting a list of all columns within the DataFrame
pycityschools_df.columns<jupyter_output><empty_output><jupyter_text>District Summary* Calculate the total number of schools* Calculate the total number of students* Calculate the total budget* Calculate the average math score * Calculate the average reading score* Calculate the percentage of students with a passing math score (70 or greater)* Calculate the percentage of students with a passing reading score (70 or greater)* Calculate the percentage of students who passed math **and** reading (% Overall Passing)* Create a dataframe to hold the above results* Optional: give the displayed data cleaner formatting<jupyter_code># Calculate the number of unique schools in the DataFrame
school_total = len(pycityschools_df["school_name"].unique())
school_total
# Calculate the number of unique students in the DataFrame
student_total = pycityschools_df["student_name"].count()
student_total                                      
# Calculate the total budget in the DataFrame
budget_total = schools_data_df["budget"].sum()
budget_total 
# Calculate the average math score in the DataFrame
average_math_score=round(pycityschools_df["math_score"].mean(), 6)
average_math_score
# Calculate the average reading score in the DataFrame
average_reading_score=round(pycityschools_df["reading_score"].mean(), 5)
average_reading_score
# Students with passing math (greater that 70%)
passing_math_df=students_data_df.loc[pycityschools_df['math_score']>=70, :]
passing_math_df.head()
# Calculate the percentage passing math in the DataFrame - #"% Passing Math":[percentage_passing_math]
students_passing_math=(passing_math_df['Student ID'].count()/student_total)*100
students_passing_math_total=round(students_passing_math, 6)
students_passing_math_total
# Students with passing reading (greater that 70%)
passing_reading_df=students_data_df.loc[students_data_df['reading_score']>=70, :]
passing_reading_df.head()
# Calculate the percentage passing reading in the DataFrame - #"% Passing Reading":[percentage_passing_reading]
students_passing_reading=(passing_reading_df["Student ID"].count()/student_total)*100
students_passing_reading_total=round(students_passing_reading, 6)
students_passing_reading_total
# Create data frames for math and reading, then merge the two to determine % passing overall - #"% Overall Passing":[percentage_passing_overall]
percent_overall_passing=(students_passing_math_total+students_passing_reading_total)/2
percent_overall_passing

     #total_student_count=students_data_df['student_name'].count()
     #percent_overall_passing=students_data_df[(students_data_df.loc['math_score']>=70) & (students_data_df.loc['reading_score']>=70)]['student_name'].count()/student_total
     #percent_overall_passing
     #CHECK THIS VALUE... ???
# Creating a summary DataFrame using above values
district_summary_df=pd.DataFrame({"Total Schools": [school_total],
                                  "Total Students": [student_total],
                                  "Total Budget": [budget_total],
                                  "Average Math Score": [average_math_score],
                                  "Average Reading Score": [average_reading_score],
                                  "% Passing Math": [students_passing_math],
                                  "% Passing Reading": [students_passing_reading],
                                 "% Overall Passing Rate": [percent_overall_passing],
                                 })
district_summary_df
# Use Map to format all the columns
# district_summary_df=district_summary_df["Total Budget"].map("${:,}".format)
# district_summary_df.head()

district_summary_df.style.format({"Total Budget": "${:,.2f}"})
                       
                       #"Average Reading Score": "{:.1f}", 
                       #"Average Math Score": "{:.1f}", 
                       #"% Passing Math": "{:.1%}", 
                       #"% Passing Reading": "{:.0%}", 
                       #"% Overall Passing Rate": "{:.1%}"})
# # # Groupby students passing - get total students for each group and add total...
# # # percentage_passing_overall=["percentage_passing_math"+"Percentage_passing_reading"].sum()
# # # Calculate the number of unique authors in the DataFrame
# # # district_summary_df=pycityschools_df({"Schools Count": [school_count],"Student Count": student_count})

grouped_passing_math_df = passing_math_df.groupby(['Student ID'])
grouped_passing_math_df.head()

grouped_passing_reading_df = passing_reading_df.groupby(['Student ID'])
grouped_passing_reading_df.head()
converted_pycityschools2_df=pycityschools_df.copy()
converted_pycityschools2_df['Student ID']=converted_pycityschools2_df.loc[:, 'Student ID'].astype(float)

# display the contents of the data frame 
converted_pycityschools2_df
# DISTRICT SUMMARY OUTPUT #


district_summary_df<jupyter_output><empty_output><jupyter_text>School Summary * Create an overview table that summarizes key metrics about each school, including:  * School Name  * School Type  * Total Students  * Total School Budget  * Per Student Budget  * Average Math Score  * Average Reading Score  * % Passing Math  * % Passing Reading  * % Overall Passing (The percentage of students that passed math **and** reading.)  * Create a dataframe to hold the above results<jupyter_code># Finding how many students each school has
# school_count = len(pycityschools_df["school_name"].unique())
pycityschools_df['school_name'].value_counts()
# Finding how many schools exist in the list
schools_unique_df=pycityschools_df["school_name"].unique()
schools_unique_df
# Finding the names of the students
students_unique_df=pycityschools_df['student_name'].unique()
students_unique_df
# Calculate the number of unique students in the DataFrame
# student_count = len(pycityschools_df["student_name"].unique())
student_counts = pycityschools_df["student_name"].value_counts()
student_counts
# Average by School Math Score 
school_average_math_score = students_data_df.groupby('school_name')['math_score']. mean() 
school_average_math_score
# Average by School Reading Score 
school_average_reading_score = students_data_df.groupby('school_name')['reading_score']. mean() 
school_average_reading_score
# Create dataframe
school_average_math_score_df = pd.DataFrame({'school_name':school_average_math_score.index,'school_average_math_score':school_average_math_score.values}) 
school_average_math_score_df
# Create dataframe
school_average_reading_score_df = pd.DataFrame({'school_name':school_average_reading_score.index, 'school_average_reading_score':school_average_reading_score.values}) 
school_average_reading_score_df
# Count by school of students passing Math 
school_count_passing_math = passing_math_df.groupby('school_name')['math_score'].count() 
school_count_passing_math 
 
school_count_passing_math_df = pd.DataFrame({'school_name':school_count_passing_math.index ,'school_count_passing_math':school_count_passing_math.values}) 
school_count_passing_math_df 
# Count by school of students passing Reading 
school_count_passing_reading = passing_reading_df.groupby('school_name')['reading_score'].count() 
school_count_passing_reading 
 
school_count_passing_reading_df = pd.DataFrame({'school_name':school_count_passing_reading.index ,'school_count_passing_reading':school_count_passing_math.values}) 
school_count_passing_reading_df 
# Join Schools with their Average Math Score
schools_join_average_math_df = pd.merge(schools_data_df, school_average_math_score_df, on="school_name", how="outer") 
schools_join_average_math_df.head()
# Join Schools with their Average Reading Score
schools_join_average_reading_df = pd.merge(schools_join_average_math_df, school_average_reading_score_df, on="school_name", how="outer") 
schools_join_average_reading_df.head() 
# Join Schools Count of Students Passing Math 
schools_join_count_math_df = pd.merge(schools_join_average_reading_df, school_count_passing_math_df, on="school_name", how="outer") 
schools_join_count_math_df.head()
# Join Schools Count of Students Passing Reading 
schools_join_count_reading_df = pd.merge(schools_join_count_math_df, school_count_passing_reading_df, on="school_name", how="outer") 
schools_join_count_reading_df.head()
# Naming Convention Change - Duplicate and rename merged datasets to new name 
# (schools_join_count_reading_df to schools_merged_df)
schools_merged_df = schools_join_count_reading_df
schools_merged_df
# By School Calculate Percent of Students Passing Math 
schools_merged_df['percent_passing_math'] = (schools_merged_df["school_count_passing_math"]/ schools_merged_df["size"]) * 100 
schools_merged_df
# By School Calculate Percent of Students Passing Reading 
schools_merged_df['percent_passing_reading'] = (schools_merged_df["school_count_passing_reading"]/ schools_merged_df["size"]) * 100 
schools_merged_df
# By School Calculate Overall Passing Rate 
schools_merged_df['overall_passing_rate'] = (schools_merged_df["percent_passing_math"] + schools_merged_df["percent_passing_math"])/ 2 
schools_merged_df.head()
# By School Calculate Per Student Budget 
schools_merged_df['per_student_budget'] = (schools_merged_df["budget"] / schools_merged_df["size"]) 
schools_merged_df.head()
# Naming Convention Change - Duplicate and rename merged datasets to new name 
# (schools_merged_df to schools_summary_raw_df)
schools_summary_raw_df = schools_merged_df
schools_summary_raw_df
# df = df.reindex(columns=['mean',0,1,2,3,4])
schools_summary_reindexed_df=schools_summary_raw_df.reindex(columns=['School ID', 'school_name','type','size','budget','per_student_budget','school_average_math_score','school_average_reading_score','school_count_passing_math','school_count_passing_reading','percent_passing_math','percent_passing_reading','overall_passing_rate'])
schools_summary_reindexed_df

# Review data counts
schools_summary_reindexed_df.count()
# Rename columns in dataframe
# change , back into : and brackets to {}
schools_header_rename_df = schools_summary_reindexed_df.rename(columns={"school_name":"School Name",
                                 "type":"School Type",
                                 "size": "Total Students",
                                 "budget": "Total School Budget",
                                 "per_student_budget": "Per Student Budget",
                                 "school_average_math_score": "Average Math Score",
                                 "school_average_reading_score": "Average Reading Score",
                                 "school_count_passing_math": "Count Passing Math",
                                 "school_count_passing_reading": "Count Passing Reading",                                                                        
                                 "percent_passing_math": "% Passing Math",
                                 "percent_passing_reading": "% Passing Reading",
                                 "overall_passing_rate":"% Overall Passing Rate"}) 
schools_header_rename_df.head() 
# Reformate columns in dataframe 
schools_header_rename_df["Total School Budget"] = schools_header_rename_df["Total School Budget"].map("${:,.2f}".format) 
schools_header_rename_df["Per Student Budget"] = schools_header_rename_df["Per Student Budget"].map("${:,.2f}".format) 
schools_header_rename_df.head() 
# Naming Convention Change - Duplicate and rename merged datasets to new name 
# (schools_header_rename_df to schools_summary_df)
schools_summary_df = schools_header_rename_df
schools_summary_df.head()
# Create the Final Dataframe - presentation 
schools_summary_df
# SCHOOL SUMMARY OUTPUT #


# Schools sorted by 'School Name'
schools_summary_sorted_df=schools_summary_df.sort_values("School Name")
schools_summary_sorted_df<jupyter_output><empty_output><jupyter_text>Top Performing Schools (By % Overall Passing) * Sort and display the top five performing schools by % overall passing.<jupyter_code># Top Performing Schools By Pass Rate
top_performing_schools_df = schools_summary_df.sort_values("% Overall Passing Rate",ascending=False) 
top_performing_schools_df
# TOP PERFORMING SCHOOLS BY OVERALL PASSING RATE - TOP FIVE OUTPUT #


top_performing_schools_new_index = top_performing_schools_df.reset_index(drop=True) 
top_performing_schools_new_index.head()<jupyter_output><empty_output><jupyter_text>Bottom Performing Schools (By % Overall Passing) * Sort and display the five worst-performing schools by % overall passing.<jupyter_code># Bottom Performing Schools By Pass Rate
bottom_performing_schools_df = schools_summary_df.sort_values("% Overall Passing Rate",ascending=True) 
bottom_performing_schools_df
# BOTTOM PERFORMING SCHOOLS BY OVERALL PASSING RATE - BOTTOM FIVE OUTPUT #


bottom_performing_schools_new_index = bottom_performing_schools_df.reset_index(drop=True) 
bottom_performing_schools_new_index.head()<jupyter_output><empty_output><jupyter_text>Math Scores by Grade * Create a table that lists the average Reading Score for students of each grade level (9th, 10th, 11th, 12th) at each school.  * Create a pandas series for each grade. Hint: use a conditional statement.    * Group each series by school    * Combine the series into a dataframe    * Optional: give the displayed data cleaner formatting<jupyter_code>#column_order=['school_name','9th','10th','11th','12th']
#math_scores_by_grade_reindex=converted_pycityschools2_df.reindex_axis(column_order, axis=1)
# Math Scores By Grade 
math_scores_by_grade = converted_pycityschools2_df[["school_name", "math_score", "grade"]] 
math_scores_by_grade 
# Math Scores By Grade Output
#math_scores_by_grade_df = pd.pivot_table(converted_pycityschools2_df,values=['math_score'], index=["school_name"], columns=['grade']) 
#math_scores_by_grade_df = math_scores_by_grade_df.reindex_axis(labels=['9th','10th','11th','12th'])
#math_scores_by_grade_df

# df = df.reindex(columns=['mean',0,1,2,3,4])
#math_scores_by_grade_reindexed_df=math_scores_by_grade_df.reindex(columns=['grade',' 9th','10th','11th','12th'])
#math_scores_by_grade_reindexed_df

# MATH SCORES BY GRADE OUTPUT #  ----NEEDS WORK to fix COLUMNS----


math_scores_by_grade_df = math_scores_by_grade.pivot_table(index='school_name',columns='grade')
math_scores_by_grade_df<jupyter_output><empty_output><jupyter_text>Reading Score by Grade  * Perform the same operations as above for reading scores<jupyter_code># Reading Scores By Grade
reading_scores_by_grade_df = converted_pycityschools2_df[["school_name", "reading_score", "grade"]] 
reading_scores_by_grade_df 
reading_df = reading_scores_by_grade_df["grade"].unique()
reading_df
# READING SCORES BY GRADE OUTPUT #  ----NEEDS WORK to fix COLUMNS----


reading_scores_by_grade_format_df = reading_scores_by_grade_df.pivot_table(index='school_name',columns= 'grade')
reading_scores_by_grade_format_df<jupyter_output><empty_output><jupyter_text>Scores by School Spending * Create a table that breaks down school performances based on average Spending Ranges (Per Student). Use 4 reasonable bins to group school spending. Include in the table each of the following:  * Average Math Score  * Average Reading Score  * % Passing Math  * % Passing Reading  * Overall Passing Rate (Average of the above two)<jupyter_code>scores_by_school_spending = schools_summary_sorted_df.copy()
scores_by_school_spending.head()
# Create bins - we will need labels and bins
spending_bins = [0, 585, 615, 645, 675]
spending_labels = ["<$585", "$585-629", "$630-644", "$645-675"]
# Slice the data and place it into bins
#school_spending_summary["Spending Ranges (Per Student)"]=pd.cut(scores_by_school_spending["Per Student Budget"].str.strip("$").astype(float), spending_bins, labels=spending_labels)
pd.cut(scores_by_school_spending["Per Student Budget"].str.strip("$").astype(float), spending_bins, labels=spending_labels)
# Place the data series into a new column inside of the DataFrame
scores_by_school_spending["Spending Ranges (Per Student)"] = pd.cut(scores_by_school_spending["Per Student Budget"].str.strip("$").astype(float), spending_bins, labels=spending_labels)
scores_by_school_spending.head()
# Create a GroupBy object based upon "school spending"
school_spending_group =scores_by_school_spending.groupby("Spending Ranges (Per Student)")

# Find how many rows fall into each bin
print(school_spending_group["Per Student Budget"].count())
# SCORES BY SCHOOL SPENDING OUTPUT #


# Get the average of each column within the GroupBy object
school_spending_group[['Average Math Score','Average Reading Score','% Passing Math','% Passing Reading','% Overall Passing Rate']].mean()<jupyter_output><empty_output><jupyter_text>Scores by School Size * Perform the same operations as above, based on school size.<jupyter_code>scores_by_school_size = schools_summary_sorted_df.copy() 
scores_by_school_size.head()
# Create bins - we will need labels and bins
bins = [0, 1000, 2000, 5000]
size_labels = ['Small (<1000)', 'Medium (1000-1999)', 'Large (2000-5000)']
# Slice the data and place it into bins
pd.cut(scores_by_school_size["Total Students"], bins, labels=size_labels)
# Place the data series into a new column inside of the DataFrame
scores_by_school_size["School Size"] = pd.cut(scores_by_school_size["Total Students"], bins, labels=size_labels)
scores_by_school_size.head()
# Create a GroupBy object based upon "View Group"
school_size_group =scores_by_school_size.groupby("School Size")

# Find how many rows fall into each bin
print(school_size_group["School ID"].count())
# SCORES BY SCHOOL SIZE OUTPUT #


# Get the average of each column within the GroupBy object
school_size_group[['Average Math Score','Average Reading Score','% Passing Math','% Passing Reading','% Overall Passing Rate']].mean()<jupyter_output><empty_output><jupyter_text>Scores by School Type * Perform the same operations as above, based on school type<jupyter_code>scores_by_school_Type = schools_summary_sorted_df.copy()    
scores_by_school_Type.head()
# Do a groupby on School Type
scores_by_school_type = schools_summary_sorted_df.groupby(['School Type'])['Average Math Score',
                                                                'Average Reading Score',
                                                                '% Passing Math',
                                                                '% Passing Reading',
                                                                '% Overall Passing Rate'].mean()

# SCORES BY SCHOOL TYPE OUTPUT #


scores_by_school_type<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09215581384039294, 'fraction_numerical': 0.008170014103000535, 'mean_word_length': 5.321549339071626, 'pattern_counts': {'":': 31, '<': 43, '<?xml version=': 0, '>': 45, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 30, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '121157', 'license': 'ADSL', 'n_tokens_mistral': 6541, 'n_tokens_neox': 6117, 'n_words': 1476, 'path': 'Versions/PyCitySchoolsmain-GauerA.ipynb', 'repo_name': 'robgauer/pandas-challenge', 'size': '20563'}