#Import Dependencies
import pandas as pd
import os
import numpy as np

#File Path for students
StudentsFilePath=os.path.join("raw_data","students_complete.csv")
#Open and read the file
Student_DF=pd.read_csv(StudentsFilePath)

#File Path for School
SchoolFilePath=os.path.join("raw_data","schools_complete.csv")
#Open and read the file
Schools_DF=pd.read_csv(SchoolFilePath)

#Renaming both dataframe school name column to the same name
Student_DF=Student_DF.rename(columns={"school":"School Name"})
Schools_DF=Schools_DF.rename(columns={"name":"School Name"})

#District Summary
Total_Students=len(Student_DF["Student ID"].unique())
District_Summary=pd.DataFrame({"Total Schools":[len(Schools_DF["School ID"].unique())],
                               "Total Students":[Total_Students],
                               "Total Budget":"$"+str(Schools_DF["budget"].sum()),
                               "Average Math Score":[round(Student_DF["math_score"].mean(),2)],
                               "Average Reading Score":[round(Student_DF["reading_score"].mean(),2)],
                               "% Passing Math":[(len(Student_DF[Student_DF["math_score"]>=70])/Total_Students)*100],
                               "% Passing Reading":[(len(Student_DF[Student_DF["reading_score"]>=70])/Total_Students)*100]
                              })
District_Summary["% Overall Passing Rate"]=(District_Summary["% Passing Math"]+District_Summary["% Passing Reading"])/2

#Rearranging the order of columns
District_Summary=District_Summary[["Total Schools","Total Students","Total Budget",
                                   "Average Math Score","Average Reading Score",
                                   "% Passing Math","% Passing Reading","% Overall Passing Rate"]]

#Formatting the columns to the desire format
District_Summary["% Passing Math"]=District_Summary["% Passing Math"].map("{:.2f}%".format)
District_Summary["% Passing Reading"]=District_Summary["% Passing Reading"].map("{:.2f}%".format)
District_Summary["% Overall Passing Rate"]=District_Summary["% Overall Passing Rate"].map("{:.2f}%".format)

District_Summary

#Combined school and student data
School_data_complete=pd.merge(Schools_DF,Student_DF,on="School Name",how="outer").groupby(["School Name"],as_index=True)

#Calculating and storing all value of each school into lists
School_Type=School_data_complete["type"].unique().str.get(0)
Total_Budget=School_data_complete["budget"].unique().str.get(0)

Total_Students=School_data_complete.count()["Student ID"]
per_Student_Budget=Total_Budget/Total_Students
avg_math_score=School_data_complete.mean()["math_score"]
avg_reading_score=School_data_complete.mean()["reading_score"]

Count_Percent_Passing_Math=Student_DF.loc[Student_DF["math_score"]>=70,:].groupby([Student_DF["School Name"]],as_index=True).count()["math_score"]
Percent_Passing_Math=round(Count_Percent_Passing_Math*100/Total_Students,2)

Count_Percent_Passing_Reading=Student_DF.loc[Student_DF["reading_score"]>=70,:].groupby([Student_DF["School Name"]],as_index=True).count()["reading_score"]
Percent_Passing_Reading=round(Count_Percent_Passing_Reading*100/Total_Students,2)

Percent_Overall_Passing=round((Percent_Passing_Math+Percent_Passing_Reading)/2,2)

#School Summary DataFrame
School_Summary=pd.DataFrame({
                             "School Type":School_Type,
                             "Total Students":Total_Students,
                             "Total School Budget":Total_Budget,
                             "Per Student Budget":per_Student_Budget,
                             "Average Math Score":round(avg_math_score,2),
                             "Average Reading Score":round(avg_reading_score,2),
                             "% Passing Maths":Percent_Passing_Math,
                             "% Passing Reading":Percent_Passing_Reading,
                             "% Overall Passing Rate":Percent_Overall_Passing
                            })

#Formatting the columns to the desire format
School_Summary["Total School Budget"]=School_Summary["Total School Budget"].map("${:,}".format)
School_Summary["Per Student Budget"]=School_Summary["Per Student Budget"].astype(int).map("${}".format)
School_Summary["% Passing Maths"]=School_Summary["% Passing Maths"].map("{:.2f}%".format)
School_Summary["% Passing Reading"]=School_Summary["% Passing Reading"].map("{:.2f}%".format)
School_Summary["% Overall Passing Rate"]=School_Summary["% Overall Passing Rate"].map("{:.2f}%".format)

#Rearranging the order of columns
School_Summary=School_Summary[["School Type","Total Students","Total School Budget",
                               "Per Student Budget","Average Math Score","Average Reading Score",
                               "% Passing Maths","% Passing Reading","% Overall Passing Rate"]]

School_Summary

#Top Performing Schools based on Overall Passing Rate
Top_Performing_Schools= School_Summary.sort_values("% Overall Passing Rate",ascending=False).head()

Top_Performing_Schools

#Bottom Performing Schools based on Overall Passing Rate
Bottom_Performing_Schools= School_Summary.sort_values("% Overall Passing Rate",ascending=True).head()

Bottom_Performing_Schools

#Math Scores by grade
Grouped_SchoolName_Grade=Student_DF.groupby([Student_DF["School Name"],Student_DF["grade"]],as_index=False)
Math_Score_Grade_DF=Grouped_SchoolName_Grade["math_score"].mean()

#Creating lists
math_SchoolNames=[]
math_mean_grade_9th=[]
math_mean_grade_10th=[]
math_mean_grade_11th=[]
math_mean_grade_12th=[]


for school in Math_Score_Grade_DF["School Name"].unique():
    School_Name=school
    math_SchoolNames.append(School_Name)
    index=math_SchoolNames.index(school)
    math_mean_grade_9th.insert(index,round(Math_Score_Grade_DF.loc[(Math_Score_Grade_DF["School Name"]==school) &  (Math_Score_Grade_DF["grade"]=="9th"),"math_score"].values[0],2))   
    math_mean_grade_10th.insert(index,round(Math_Score_Grade_DF.loc[(Math_Score_Grade_DF["School Name"]==school) &  (Math_Score_Grade_DF["grade"]=="10th"),"math_score"].values[0],2))   
    math_mean_grade_11th.insert(index,round(Math_Score_Grade_DF.loc[(Math_Score_Grade_DF["School Name"]==school) &  (Math_Score_Grade_DF["grade"]=="11th"),"math_score"].values[0],2))   
    math_mean_grade_12th.insert(index,round(Math_Score_Grade_DF.loc[(Math_Score_Grade_DF["School Name"]==school) &  (Math_Score_Grade_DF["grade"]=="12th"),"math_score"].values[0],2)) 

#Creating the dataframe of Math Scores based on the Grade
New_Math_DF=pd.DataFrame({"School Name":math_SchoolNames,
                          "9th":math_mean_grade_9th,
                          "10th":math_mean_grade_10th,
                          "11th":math_mean_grade_11th,
                          "12th":math_mean_grade_12th})

#Rearranging the columns
New_Math_DF=New_Math_DF[["School Name","9th","10th","11th","12th"]]

#Setting the index
New_Math_DF.set_index("School Name",inplace=True)

New_Math_DF

#Reading Scores by grade
Grouped_SchoolName_Grade=Student_DF.groupby([Student_DF["School Name"],Student_DF["grade"]],as_index=False)
Reading_Score_Grade_DF=Grouped_SchoolName_Grade["reading_score"].mean()

#Creating lists
reading_SchoolNames=[]
reading_mean_grade_9th=[]
reading_mean_grade_10th=[]
reading_mean_grade_11th=[]
reading_mean_grade_12th=[]


for school in Reading_Score_Grade_DF["School Name"].unique():
    School_Name=school
    reading_SchoolNames.append(School_Name)
    index=reading_SchoolNames.index(school)
    reading_mean_grade_9th.insert(index,round(Reading_Score_Grade_DF.loc[(Reading_Score_Grade_DF["School Name"]==school) &  (Reading_Score_Grade_DF["grade"]=="9th"),"reading_score"].values[0],2))   
    reading_mean_grade_10th.insert(index,round(Reading_Score_Grade_DF.loc[(Reading_Score_Grade_DF["School Name"]==school) &  (Reading_Score_Grade_DF["grade"]=="10th"),"reading_score"].values[0],2))   
    reading_mean_grade_11th.insert(index,round(Reading_Score_Grade_DF.loc[(Reading_Score_Grade_DF["School Name"]==school) &  (Reading_Score_Grade_DF["grade"]=="11th"),"reading_score"].values[0],2))   
    reading_mean_grade_12th.insert(index,round(Reading_Score_Grade_DF.loc[(Reading_Score_Grade_DF["School Name"]==school) &  (Reading_Score_Grade_DF["grade"]=="12th"),"reading_score"].values[0],2)) 

#Creating the dataframe of Math Scores based on the Grade
New_Reading_DF=pd.DataFrame({"School Name":reading_SchoolNames,
                             "9th":reading_mean_grade_9th,
                             "10th":reading_mean_grade_10th,
                             "11th":reading_mean_grade_11th,
                             "12th":reading_mean_grade_12th})

#Rearranging the columns
New_Reading_DF=New_Reading_DF[["School Name","9th","10th","11th","12th"]]

#Setting the index
New_Reading_DF.set_index("School Name",inplace=True)

New_Reading_DF

#Scores by School Spending
bins=[0,584,614,644,675]
label_value=["<$585","$585-615","$615-645","$645-675"]

New_School_Summary=School_Summary
New_School_Summary["% Passing Maths"]=round(New_School_Summary["% Passing Maths"].str.replace("%", '').astype(float),2)
New_School_Summary["% Passing Reading"]=round(New_School_Summary["% Passing Reading"].str.replace("%", '').astype(float),2)
New_School_Summary["% Overall Passing Rate"]=round(New_School_Summary["% Overall Passing Rate"].str.replace("%", '').astype(float),2)

#Cutting based on the bins
New_School_Summary["Spending Ranges (Per Student)"]=pd.cut(New_School_Summary["Per Student Budget"].str.replace("$", '').astype(float),bins,labels=label_value)

#Grouping based on Spending Ranges (Per Student)
Grouped_School_Summary_Spending=New_School_Summary.groupby("Spending Ranges (Per Student)",as_index=False).mean()

#Extracting the required columns from the dataframe
Grouped_School_Summary_Spending=Grouped_School_Summary_Spending[["Spending Ranges (Per Student)","Average Math Score",
                                                                 "Average Reading Score","% Passing Maths",
                                                                 "% Passing Reading","% Overall Passing Rate"]]

#Setting the index
Grouped_School_Summary_Spending.set_index("Spending Ranges (Per Student)",inplace=True)

Grouped_School_Summary_Spending

#Scores by School Size
bins=[0,999,1999,5000]
label_value=["Small (<1000)","Medium (1000-2000)","Large (2000-5000)"]

#Cutting based on the bins
New_School_Summary["School Size"]=pd.cut(New_School_Summary["Total Students"],bins,labels=label_value)

#Grouping based on School Size
Grouped_School_Summary_Size=New_School_Summary.groupby("School Size",as_index=False).mean()

#Setting the index
Grouped_School_Summary_Size.set_index("School Size",inplace=True)

#Extracting the required columns from the dataframe
Grouped_School_Summary_Size=Grouped_School_Summary_Size[["Average Math Score","Average Reading Score",
                                                         "% Passing Maths","% Passing Reading","% Overall Passing Rate"]]

Grouped_School_Summary_Size

#Scores by School Type

#Grouping based on School Type
Grouped_School_Summary_Type=New_School_Summary.groupby("School Type",as_index=False).mean()

#Setting the index
Grouped_School_Summary_Type.set_index("School Type",inplace=True)

#Extracting the required columns from the dataframe
Grouped_School_Summary_Type=Grouped_School_Summary_Type[["Average Math Score","Average Reading Score",
                                                         "% Passing Maths","% Passing Reading","% Overall Passing Rate"]]

Grouped_School_Summary_Type