<jupyter_start><jupyter_text>PyCity Schools Analysis  Top and Bottom Performing Schools- The most immediate observation is that Charter schools populate the top 5 performing schools and District schools populate the bottom 5 performing schools for standardized test scores. - Overall, District schools had lower standardized test scores than Charter schools. Subject Grades- Math and Reading standardized test scores seem to stay the same across the board between grades in a school. - If the test grades deviated wildly from each other between different grades, further analysis would be required. That doesn't appear to be the case.- Therefore, any solutions that would improve one grade's performance would probably work for all the other grades as well, across most of these schools. Budget & Population- Large Schools (population greater than 2000 students) appear to have a significantly lower overall passing rate than schools with less than 2000 students, by about 20%.- It appears the more that schools spent on their students in \$\$ amounts, the less well they performed on these standardized tests.- Charter schools have smaller budgets than District schools. They also have fewer students. Conclusions- Given the observations and conditions above, one of the conclusions we might draw from this dataset is that a higher school budget doesn't necessarily mean better performance on standardized tests.- Perhaps there is a correlation between the *size* of a school and performance on standardized tests, however, and it would be a good set of data to delve further into.- Specifically, this data shows that the more students a school has, the worse its performance on standardized tests. Opinions- This could be the result of numerous factors; in my opinion it would be due to the lack of attention to every individual student in the time crunch required of teachers to get their lessons across. However this data doesn't necessarily prove that theory in and of itself so we would require further data. Perhaps something like *Average Amount of Time Teacher Spends With Individual Students* or something similar. Other - Why is there someone named 'Dr. <NAME>' in the student list under Huang High School? Are some teachers actually mixed in with these students? What if some of them don't have prefixes like 'Dr.' in their name? Does this data need further scrubbing? My numbers seem generally the same as the example results we were shown so I'm just going to trust the teachers on this one.<jupyter_code>import pandas as pd
#import schools and then students file
schools_file = "Resources/schools_complete.csv"
students_file = "Resources/students_complete.csv"
#read one dataframe for schools
schools_df = pd.read_csv(schools_file)
# schools_df.head()
#rename 'name' column to 'school' to prepare for merge
schools_df = schools_df.rename(columns={"name":"school"})
# schools_df.head()
#read second dataframe for students
students_df = pd.read_csv(students_file)
# students_df.head()
#outer merge on 'school' column
merged_df = pd.merge(schools_df, students_df, on="school", how="outer")
merged_df.head()
#check for missing data
#merged_df.count()
#check columns
merged_df.columns<jupyter_output><empty_output><jupyter_text>District Summary<jupyter_code>#Create Dataframe with headers: 'Total Schools', 'Total Students,' 'Total Budget', 'Avg Math Score', 'Avg Reading Score',
#'% Passing Math', '% Passing Reading,' % Overall Passing Rate'
totalschools = merged_df['school'].nunique()
totalschools
totalstudents = len(merged_df['name'])
totalstudents
totalbudget = sum(schools_df['budget'])
totalbudget
#score averages
mathscore = merged_df['math_score'].mean()
readingscore = merged_df['reading_score'].mean()
#passing score formulas
#return the amount of math scores above 65
#revision: actually this assignment considers 'above 70' to be passing
#anyway this formula passes the amount of students whose scores are over 70. all little harvard prodigies.
passmath = len(merged_df.loc[merged_df['math_score'] > 70, ['math_score']])
passmath
#average is the amount of passing scores divided by all students. example file reads 72.392137
#if passing score is '65' i get 0.83 which doesn't match the example file
#if passing scores is '70' i get '0.72392137' which matches the example file. so i'll go with that then.
#would be nice if the readme file specified what counts as a passing score.
percmath = passmath / totalstudents * 100
percmath
passreading = len(merged_df.loc[merged_df['reading_score'] > 70, ['reading_score']])
percreading = passreading / totalstudents * 100
#according to the example this should read 82.971662
percreading
#get overall passing rate "(Average of the above two)" reading and writing and put it all in a new DF
#in the example the result is clearly wrong and gives 80.431606 as the average of 72.392137 and 82.971662
percpassing = (percmath + percreading) / 2
percpassing
#putting it together
dist_summary = pd.DataFrame({"Total Schools": totalschools,
                            "Total Students": totalstudents,
                            "Total Budget": totalbudget,
                            "Average Math Score": mathscore,
                            "Average Reading Score": readingscore,
                            "% Passing Math": percmath,
                            "% Passing Reading": percreading,
                            "% Overall Passing Rate": percpassing}, index=[0])

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

dist_summary = dist_summary[["Total Schools",
                                            "Total Students",
                                            "Total Budget",
                                            "Average Math Score",
                                            "% Passing Math",
                                            "% Passing Reading",
                                            "% Overall Passing Rate"]]

dist_summary<jupyter_output><empty_output><jupyter_text>School Summary<jupyter_code>#Group by school, show 'School Type', 'Total Students', 'Total School Budget', 'Per Student Budget', 'Average Math Score',
#'% Passing Math', '% Passing Reading', '% Overall Passing Rate'
school_group = merged_df.groupby(['school'])
#basic stuff
#i borrowed this schooltype thing from someone else's solution because it took me like 6 hours to figure this out to no avail.
#takeaway: strings are annoying
schooltype = schools_df.set_index('school')["type"]
totalstudents = school_group['name'].count()
totschoolbud = school_group['budget'].mean()
perstudentbud = totschoolbud / totalstudents

#average math score
mathscore = school_group['math_score'].mean()

#average reading score
readingscore = school_group['reading_score'].mean()

#% passing math
#also took these % passing from another solution because i couldn't figure out why i can't use .loc with groupby
passmath = merged_df[merged_df['math_score'] > 70].groupby('school')['Student ID'].count() / totalstudents * 100

#% passing reading
passreading = merged_df[merged_df['reading_score'] > 70].groupby('school')['Student ID'].count() / totalstudents * 100

#% overall passing rate
percpassing = (passmath + passreading) / 2
school_summary = pd.DataFrame({"School Type": schooltype,
                              "Total Students": totalstudents,
                              "Total School Budget": totschoolbud,
                              "Per Student Budget": perstudentbud,
                              "Average Math Score": mathscore,
                              "Average Reading Score": readingscore,
                              "% Passing Math": passmath,
                              "% Passing Reading": passreading,
                              "% Overall Passing": percpassing})


#formating
school_summary["Total School Budget"] = school_summary["Total School Budget"].map("${:,.2f}".format)

school_summary = school_summary[["School Type",
                                "Total Students",
                                "Total School Budget",
                                "Per Student Budget",
                                "Average Math Score",
                                "Average Reading Score",
                                "% Passing Math",
                                "% Passing Reading",
                                "% Overall Passing"]]

school_summary<jupyter_output><empty_output><jupyter_text>Top Performing Schools (By Passing Rate)<jupyter_code>#sort by overall passing and put highest on top
top_schools = school_summary.sort_values(['% Overall Passing'], ascending=False)
top_schools.head()<jupyter_output><empty_output><jupyter_text>Bottom Performing Schools (By Passing Rate)<jupyter_code>#"I put my thing down, flip it and reverse it" - <NAME>, 2002
bottom_schools = school_summary.sort_values(['% Overall Passing'], ascending=True)
bottom_schools.head()<jupyter_output><empty_output><jupyter_text>Math Scores by Grade<jupyter_code># breaking down this formula:
# first it locates all entries matching '9th' under the 'grade' column in students_df
# then it groups them by 'school'
# then it returns the mean of the values in the math_score column
# the index is still the schools from the previous dataframes so we don't have to do anything

#yes, this would probably work a lot better in a loop of some sort but i'm against the clock here
ninth = students_df.loc[students_df['grade'] == '9th'].groupby('school')['math_score'].mean()
tenth = students_df.loc[students_df['grade'] == '10th'].groupby('school')['math_score'].mean()
eleventh = students_df.loc[students_df['grade'] == '11th'].groupby('school')['math_score'].mean()
twelfth = students_df.loc[students_df['grade'] == '12th'].groupby('school')['math_score'].mean()

math_grade = pd.DataFrame({"9th": ninth.map("{:,.2f}".format),
                          "10th": tenth.map("{:,.2f}".format),
                          "11th": eleventh.map("{:,.2f}".format),
                          "12th": twelfth.map("{:,.2f}".format)})

math_grade = math_grade [['9th', '10th', '11th', '12th']]

math_grade<jupyter_output><empty_output><jupyter_text>Reading Scores by Grade<jupyter_code>ninth = students_df.loc[students_df['grade'] == '9th'].groupby('school')['reading_score'].mean()
tenth = students_df.loc[students_df['grade'] == '10th'].groupby('school')['reading_score'].mean()
eleventh = students_df.loc[students_df['grade'] == '11th'].groupby('school')['reading_score'].mean()
twelfth = students_df.loc[students_df['grade'] == '12th'].groupby('school')['reading_score'].mean()

reading_grade = pd.DataFrame({"9th": ninth.map("{:,.2f}".format),
                          "10th": tenth.map("{:,.2f}".format),
                          "11th": eleventh.map("{:,.2f}".format),
                          "12th": twelfth.map("{:,.2f}".format)})

reading_grade = reading_grade [['9th', '10th', '11th', '12th']]

reading_grade<jupyter_output><empty_output><jupyter_text>Scores by School Spending<jupyter_code>#creating bins based on example, then making a new variable to hold them
spending_bins = [0, 585, 615, 645, 675]
group_names = ["<$585", "$585-615", "$615-645", "$645-675"]
merged_df["Spending Range"] = pd.cut(merged_df['budget']/merged_df['size'], spending_bins, labels=group_names)
# school_summary
spending_group = merged_df.groupby("Spending Range")

#Average Math Score, Average Reading Score, % Passing Math, % Passing Reading, % Overall Passing Rate
avgmath = spending_group['math_score'].mean()
avgreading = spending_group['reading_score'].mean()

#passing math
passmath = merged_df[merged_df['math_score'] > 70].groupby("Spending Range")['Student ID'].count() / spending_group['Student ID'].count() * 100

#% passing reading
passreading = merged_df[merged_df['reading_score'] > 70].groupby("Spending Range")['Student ID'].count() / spending_group['Student ID'].count() * 100
#% overall passing rate
percpassing = (passmath + passreading) / 2
spending_scores = pd.DataFrame({"Average Math Score": avgmath.map("{:,.2f}".format),
                               "Average Reading Score": avgreading.map("{:,.2f}".format),
                               "% Passing Math": passmath.map("{:,.2f}".format),
                               "% Passing Reading": passreading.map("{:,.2f}".format),
                               "% Overall Passing Rate": percpassing.map("{:,.2f}".format)})

spending_scores = spending_scores[['Average Math Score', 
                                   'Average Reading Score', 
                                   '% Passing Math', 
                                   '% Passing Reading', 
                                   '% Overall Passing Rate']]

spending_scores.index.name = "Spending Ranges (Per Student)"

spending_scores<jupyter_output><empty_output><jupyter_text>Scores by School Size<jupyter_code>#just using the Example's bins
size_bins = [0, 1000, 2000, 5000]
group_names = ["Small (<1000)", "Medium (1000-2000)", "Large (2000-5000)"]
merged_df['School Sizes'] = pd.cut(merged_df['size'], size_bins, labels = group_names)
size_group = merged_df.groupby('School Sizes')

#Average Math Score, Average Reading Score, % Passing Math, % Passing Reading, % Overall Passing Rate
avgmath = size_group['math_score'].mean()
avgreading = size_group['reading_score'].mean()

#passing math
passmath = merged_df[merged_df['math_score'] > 70].groupby("School Sizes")['Student ID'].count() / size_group['Student ID'].count() * 100

#% passing reading
passreading = merged_df[merged_df['reading_score'] > 70].groupby("School Sizes")['Student ID'].count() / size_group['Student ID'].count() * 100
#% overall passing rate
percpassing = (passmath + passreading) / 2
size_scores = pd.DataFrame({"Average Math Score": avgmath.map("{:,.2f}".format),
                               "Average Reading Score": avgreading.map("{:,.2f}".format),
                               "% Passing Math": passmath.map("{:,.2f}".format),
                               "% Passing Reading": passreading.map("{:,.2f}".format),
                               "% Overall Passing Rate": percpassing.map("{:,.2f}".format)})

size_scores = size_scores[['Average Math Score', 
                                   'Average Reading Score', 
                                   '% Passing Math', 
                                   '% Passing Reading', 
                                   '% Overall Passing Rate']]

size_scores.index.name = "School Size"

size_scores<jupyter_output><empty_output><jupyter_text>Scores by School Type<jupyter_code>#group by school type
type_group = merged_df.groupby('type')

#Average Math Score, Average Reading Score, % Passing Math, % Passing Reading, % Overall Passing Rate
avgmath = type_group['math_score'].mean()
avgreading = type_group['reading_score'].mean()

#passing math
passmath = merged_df[merged_df['math_score'] > 70].groupby("type")['Student ID'].count() / type_group['Student ID'].count() * 100

#% passing reading
passreading = merged_df[merged_df['reading_score'] > 70].groupby("type")['Student ID'].count() / type_group['Student ID'].count() * 100
#% overall passing rate
percpassing = (passmath + passreading) / 2
type_scores = pd.DataFrame({"Average Math Score": avgmath.map("{:,.2f}".format),
                               "Average Reading Score": avgreading.map("{:,.2f}".format),
                               "% Passing Math": passmath.map("{:,.2f}".format),
                               "% Passing Reading": passreading.map("{:,.2f}".format),
                               "% Overall Passing Rate": percpassing.map("{:,.2f}".format)})

type_scores = type_scores[['Average Math Score', 
                                   'Average Reading Score', 
                                   '% Passing Math', 
                                   '% Passing Reading', 
                                   '% Overall Passing Rate']]

type_scores.index.name = "School Type"

type_scores<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11608706529897424, 'fraction_numerical': 0.016887665749311984, 'mean_word_length': 3.227657324167107, 'pattern_counts': {'":': 41, '<': 45, '<?xml version=': 0, '>': 53, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 39, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '7068', 'license': 'ADSL', 'n_tokens_mistral': 4719, 'n_tokens_neox': 4303, 'n_words': 1384, 'path': 'PyCitySchools/PyCitySchools_solution.ipynb', 'repo_name': 'jpchdiaz/Pandas-Data-Mining', 'size': '16001'}