El Camino College Showcase

import pandas as pd

#cd EnviroScreen4
CES4 = pd.read_excel("calenviroscreen40resultsdatadictionary_F_2021.xlsx", sheet_name='CES4.0FINAL_results')

CES4

!ls

CES1-intro.ipynb
CES2-CommColl.ipynb
College_codes_EVDtype.xlsx
Envroscreen4.ipynb
calenviroscreen40resultsdatadictionary_F_2021.xlsx
calenviroscreen40shpf2021shp

CollegeCodes= pd.read_excel("College_codes_EVDtype.xlsx")
CollegeCodes

#count if EVDCode = 1
CollegeCodes['EVDCode'].value_counts()

1    81
4    63
2    20
3     9
Name: EVDCode, dtype: int64

#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']