import pandas as pd#cd EnviroScreen4
CES4 = pd.read_excel("calenviroscreen40resultsdatadictionary_F_2021.xlsx", sheet_name='CES4.0FINAL_results')CES4!lsCollegeCodes= pd.read_excel("College_codes_EVDtype.xlsx")
CollegeCodes#count if EVDCode = 1
CollegeCodes['EVDCode'].value_counts()#drop rows where EVDCode = 4
CollegeCodes_Public = CollegeCodes[CollegeCodes['EVDCode'] != 4]#merge CollegeCodes_Public with CES4
CES4_Public = pd.merge(CES4, CollegeCodes_Public, how='inner', left_on='ZIP', right_on='Zip')# Find the most polluted zip codes and show the college there
CES4_Public.sort_values(by='CES 4.0 Score', ascending=False).head(10)# Find the least polluted zip codes and show the college there
CES4_Public.sort_values(by='CES 4.0 Score', ascending=True).head(10)# what is the score at El Camino College
CES4_Public[CES4_Public['College'] == 'EL CAMINO COLLEGE']