PyCitySchools

"Data can tell, Pandas can do well!"

Background

This repository brings a python pandas solution in the education sector to analyze city's school district data. This project will help school board and mayor to make strategic decisions regarding future school budgets and priorities. This project used two datasets in csv format, one is schools_complete.csv file wich includes the following informations in columns Student ID,school_name,type,size,budget the other dataset is students_complete.csv file which includes the followling information in columns Student ID,student_name,gender,grade,school_name,reading_scoreand math_score. For these analysis both datasets imported, merged, and the aggregate data diplayed in to python pandas dataframes. The project is conducted in Jupyter notebook to showcase, and communicate the analysis report the following link is created: Jupyter Notebook Viewer

Observable Trends

• The dataset encompasses a total of 15 schools, 39,170 students. The total students average reading score is 81.87 which is higher than the average math score 78.98. When we see the passing rate reading (85.80%) has a higher rate than math (74.98 % ),the overall passing rate is 65.17%. These findings indicate that student's score more in reading than math.

• The school summary finding showed that from 15 schools 7 of them are found at the district level, and the other 8 of them found at the charter level. The number of students in schools ranged between 427 students (Holden High School) to 4976 students (Bailey High School). The general finding also showed that district schools have more students than charter schools. It is legitimate to conclude that district schools have more students than charter schools because of their geographical coverage.

• The total budget field of the school summery showed the amount of money each school consumed. The maximum budget is $3,124,928.00 its Bailey High School, and the lower budget is $248,087.00 its Holden High School. We can see later Bailey High School is not in the top performing schools. The schools budget allocaters,and Bailey High School should to be work on this to improve the quality. Regarding budget per student, Huang High School ( $655.000) has a higher budget per student, and Wilson High School ($578.00) allocates a lower budget per student. Huang High School is one of the bottom performing school the same as Bailey High School it one area for future improvement to see education quality as it budgetd. The total district schools budget( $17,347,923.00) is higher than Charter schools budget ($7,301,505.00), however, when we come to the total allocation of Budget Per Student charter schools allocation ($4,796.00) is greater than district schools ($4,505.00). This may due to charter schools have a smaller number of students.

• The findings from the data showed that in all metrics (average, percent, and overall) of reading and math scores Charter schools have a greater score than District schools. Pena High School has the maximum (83.839917) average math score (83.839917), percent of students passing math (94.59%) and average reading score (84.044699). This implies that Pena High School has a good quality, and reputation in teaching math, and reading. On the other side, Huang High School has a lower average math score (76.842711), and percent of students passing math (65.683922 ). Regarding the percent of passing reading class Thomas High School has a higher percentage value (97.30%). On the flip side, Rodriguez High School has a lower Average Reading Score(80.744686), and Ford High School has a lower passing reading. In the overall percentage passing Cabrera High School has the maximum passing percentage rate ( 91.33 %), and Rodriguez High School has a lower percentage of (52.98) passing.

• Grade 11 students at Holden High School holds a higher average Math score (85) comparing all schools and grades. Holden High School grade 12 students possess a higher average reading score (84.698795).

• The other irregular finding of this analysis is that when the budget per student increases student's average math score, the average reading score, percent of passing math, percent of passing reading, and the overall passing decreases. This finding commences investigating the real motivators for academic achievement, and give an open space for researchers to work on this more.

• The data revealed that when the number of students sizes higher in schools the average math scores, average reading scores, passing math, passing reading, and overall passing decrease. So, we can infer that, when student size rises, it negatively influences student achievement. Therefore, schools, cities, districts, and charters should work on student size in schools, and classes because it directly affects student achievement for so many reasons.

Table of Contents

• Import Dependencies and Setup
• Load, Read and Merge the Data
• District Summary
• School Summary
• Top Performing Schools (By % Overall Passing)
• Bottom Performing Schools (By % Overall Passing)
• Math Scores by Grade
• Reading Scores by Grade
• Scores by School Spending
• Scores by School Size
• Scores by School Type

Import Dependencies and Setup

# Dependencies and Setup
import pandas as pd

Load, Read and Merge the Data

 # 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()

District Summary

• A high level analysis on the district's created on the following key metrics

  • Total Schools
  • Total Students
  • Total Budget
  • Average Math Score
  • Average Reading Score
  • % Passing Math (The percentage of students that passed math.)
  • % Passing Reading (The percentage of students that passed reading.)
  • % Overall Passing (The percentage of students that passed math and reading.)

  The district summery table looks as follows:

	Total Schools	Total Students	Total Budget	Average Math Score	Average Reading Score	% Passing Math	% Passing Reading	% Overall Passing
0	15	39,170	$24,649,428.00	78.985371	81.87784	74.980853	85.805463	65.172326

School Summary

• An overview table that summarizes key metrics about each school was created in the following metrics
  • School Name
  • School Type
  • Total Students
  • Total School Budget
  • Per Student Budget
  • Average Math Score
  • Average Reading Score
  • % Passing Math (The percentage of students that passed math.)
  • % Passing Reading (The percentage of students that passed reading.)
  • % Overall Passing (The percentage of students that passed math and reading.)

The school summery table looks as follows:

	School Type	Total Students	Total School Budget	Per Student Budget	Average Math Score	Average Reading Score	% Passing Math	% Passing Reading	% Overall Passing
Bailey High School	District	4976	$3,124,928.00	$628.00	77.048432	81.033963	66.680064	81.933280	54.642283
Cabrera High School	Charter	1858	$1,081,356.00	$582.00	83.061895	83.975780	94.133477	97.039828	91.334769
Figueroa High School	District	2949	$1,884,411.00	$639.00	76.711767	81.158020	65.988471	80.739234	53.204476
Ford High School	District	2739	$1,763,916.00	$644.00	77.102592	80.746258	68.309602	79.299014	54.289887
Griffin High School	Charter	1468	$917,500.00	$625.00	83.351499	83.816757	93.392371	97.138965	90.599455
Hernandez High School	District	4635	$3,022,020.00	$652.00	77.289752	80.934412	66.752967	80.862999	53.527508
Holden High School	Charter	427	$248,087.00	$581.00	83.803279	83.814988	92.505855	96.252927	89.227166
Huang High School	District	2917	$1,910,635.00	$655.00	76.629414	81.182722	65.683922	81.316421	53.513884
Johnson High School	District	4761	$3,094,650.00	$650.00	77.072464	80.966394	66.057551	81.222432	53.539172
Pena High School	Charter	962	$585,858.00	$609.00	83.839917	84.044699	94.594595	95.945946	90.540541
Rodriguez High School	District	3999	$2,547,363.00	$637.00	76.842711	80.744686	66.366592	80.220055	52.988247
Shelton High School	Charter	1761	$1,056,600.00	$600.00	83.359455	83.725724	93.867121	95.854628	89.892107
Thomas High School	Charter	1635	$1,043,130.00	$638.00	83.418349	83.848930	93.272171	97.308869	90.948012
Wilson High School	Charter	2283	$1,319,574.00	$578.00	83.274201	83.989488	93.867718	96.539641	90.582567
Wright High School	Charter	1800	$1,049,400.00	$583.00	83.682222	83.955000	93.333333	96.611111	90.333333

Top Performing Schools (By % Overall Passing)

• A table that highlights the top 5 performing schools based on % Overall Passing was created and it includes the following metrics.
  • School Name
  • School Type
  • Total Students
  • Total School Budget
  • Per Student Budget
  • Average Math Score
  • Average Reading Score
  • % Passing Math (The percentage of students that passed math.)
  • % Passing Reading (The percentage of students that passed reading.)
  • % Overall Passing (The percentage of students that passed math and reading.)

A table of Top Performing Schools (By % Overall Passing) looks as follows:

	School Type	Total Students	Total School Budget	Per Student Budget	Average Math Score	Average Reading Score	% Passing Math	% Passing Reading	% Overall Passing
Cabrera High School	Charter	1858	$1,081,356.00	$582.00	83.061895	83.975780	94.133477	97.039828	91.334769
Thomas High School	Charter	1635	$1,043,130.00	$638.00	83.418349	83.848930	93.272171	97.308869	90.948012
Griffin High School	Charter	1468	$917,500.00	$625.00	83.351499	83.816757	93.392371	97.138965	90.599455
Wilson High School	Charter	2283	$1,319,574.00	$578.00	83.274201	83.989488	93.867718	96.539641	90.582567
Pena High School	Charter	962	$585,858.00	$609.00	83.839917	84.044699	94.594595	95.945946	90.540541

Bottom Performing Schools (By % Overall Passing)

• A table that highlights the bottom 5 performing schools based on % Overall Passing was created Include all of the same metrics as above.

A table of Bottom Performing Schools (By % Overall Passing) looks as follows:

	School Type	Total Students	Total School Budget	Per Student Budget	Average Math Score	Average Reading Score	% Passing Math	% Passing Reading	% Overall Passing
Rodriguez High School	District	3999	$2,547,363.00	$637.00	76.842711	80.744686	66.366592	80.220055	52.988247
Figueroa High School	District	2949	$1,884,411.00	$639.00	76.711767	81.158020	65.988471	80.739234	53.204476
Huang High School	District	2917	$1,910,635.00	$655.00	76.629414	81.182722	65.683922	81.316421	53.513884
Hernandez High School	District	4635	$3,022,020.00	$652.00	77.289752	80.934412	66.752967	80.862999	53.527508
Johnson High School	District	4761	$3,094,650.00	$650.00	77.072464	80.966394	66.057551	81.222432	53.539172

Math Scores by Grade

• A table that lists the average Math Score for students of each grade level (9th, 10th, 11th, 12th) at each school was created.

A table of Math Scores by Grade looks as follows:

	9th	10th	11th	12th
School Name
Bailey High School	77.083676	76.996772	77.515588	76.492218
Cabrera High School	83.094697	83.154506	82.765560	83.277487
Figueroa High School	76.403037	76.539974	76.884344	77.151369
Ford High School	77.361345	77.672316	76.918058	76.179963
Griffin High School	82.044010	84.229064	83.842105	83.356164
Hernandez High School	77.438495	77.337408	77.136029	77.186567
Holden High School	83.787402	83.429825	85.000000	82.855422
Huang High School	77.027251	75.908735	76.446602	77.225641
Johnson High School	77.187857	76.691117	77.491653	76.863248
Pena High School	83.625455	83.372000	84.328125	84.121547
Rodriguez High School	76.859966	76.612500	76.395626	77.690748
Shelton High School	83.420755	82.917411	83.383495	83.778976
Thomas High School	83.590022	83.087886	83.498795	83.497041
Wilson High School	83.085578	83.724422	83.195326	83.035794
Wright High School	83.264706	84.010288	83.836782	83.644986

Reading Scores by Grade

• A table that lists the average Reading Score for students of each grade level (9th, 10th, 11th, 12th) at each school was created.

A table of Reading Scores by Grade looks as follows:

	9th	10th	11th	12th
School Name
Bailey High School	81.303155	80.907183	80.945643	80.912451
Cabrera High School	83.676136	84.253219	83.788382	84.287958
Figueroa High School	81.198598	81.408912	80.640339	81.384863
Ford High School	80.632653	81.262712	80.403642	80.662338
Griffin High School	83.369193	83.706897	84.288089	84.013699
Hernandez High School	80.866860	80.660147	81.396140	80.857143
Holden High School	83.677165	83.324561	83.815534	84.698795
Huang High School	81.290284	81.512386	81.417476	80.305983
Johnson High School	81.260714	80.773431	80.616027	81.227564
Pena High School	83.807273	83.612000	84.335938	84.591160
Rodriguez High School	80.993127	80.629808	80.864811	80.376426
Shelton High School	84.122642	83.441964	84.373786	82.781671
Thomas High School	83.728850	84.254157	83.585542	83.831361
Wilson High School	83.939778	84.021452	83.764608	84.317673
Wright High School	83.833333	83.812757	84.156322	84.073171

Scores by School Spending

• A table that breaks down school performances based on average Spending Ranges (Per Student), and 4 reasonable bins to group school spending was created.The table includes each of the following:
  • Average Math Score
  • Average Reading Score
  • % Passing Math (The percentage of students that passed math.)
  • % Passing Reading (The percentage of students that passed reading.)
  • % Overall Passing (The percentage of students that passed math and reading.)

A table of Scores by School Spending looks as follows:

	Average Math Score	Average Reading Score	% Passing Math	% Passing Reading	% Overall Passing
Per Student Budget
<$584	83.36	83.96	93.70	96.69	90.64
$585-629	79.98	82.31	79.11	88.51	70.94
$630-644	77.82	81.30	70.62	82.60	58.84
$645-675	77.05	81.01	66.23	81.11	53.53

Scores by School Size

• The following table created by grouping schools based on a reasonable approximation of school size (Small, Medium, Large).

A table of Scores by School Size looks as follows:

	Average Math Score	Average Reading Score	% Passing Math	% Passing Reading	% Overall Passing
Total Students
Small (<1000)	83.828654	83.828654	93.952484	96.040317	90.136789
Medium (1000-2000)	83.372682	83.372682	93.616522	96.773058	90.624267
Large (2000-5000)	77.477597	77.477597	68.652380	82.125158	56.574046

Scores by School Type

• Finally a solution that group schools based on school type (Charter vs. District) was created.

A table of Scores by School Type looks as follows:

	Average Math Score	Average Reading Score	% Passing Math	% Passing Reading	% Overall Passing
Type of School
Charter	83.406183	83.406183	93.701821	96.645891	90.560932
District	76.987026	76.987026	66.518387	80.905249	53.695878

Copyright

Trilogy Education Services © 2019. All Rights Reserved.