# -*- coding: utf-8 -*-
"""passnyc-data-exploration.ipynb

Automatically generated by Colaboratory.

Original file is located at
    https://colab.research.google.com/drive/1D8-QNWpXTTQWqR6Dv0JvK96MiIw1ajDG

<h1 class='font-effect-3d' style='color:#37c9e1; font-family:Orbitron;'>&#x1F310; &nbsp; Code Library, Styling, and Links</h1>
<details><summary style='color:#37c9e1; font-family:Orbitron;'>Github</summary><br/>

&#x1F4D8; &nbsp; [Python Version](https://github.com/OlgaBelitskaya/kaggle_notebooks/blob/master/passnyc-data-exploration.ipynb)

&#x1F4D8; &nbsp; [R Version](https://github.com/OlgaBelitskaya/kaggle_notebooks/blob/master/passnyc-data-exploration-r.ipynb)

<details><summary style='color:#37c9e1; font-family:Orbitron;'>Useful Links</summary><br/>

&#x1F4E1; &nbsp; [School Quality Reports. Educator Guide](http://schools.nyc.gov/NR/rdonlyres/967E0EE1-7E5D-4E47-BC21-573FEEE23AE2/0/201516EducatorGuideHS9252017.pdf)
    
&#x1F4E1; &nbsp; [New York City Department of Education](https://www.schools.nyc.gov)

&#x1F4E1; &nbsp; [NYC OpenData](https://opendata.cityofnewyork.us/)

&#x1F4E1; &nbsp; [Pandas Visualization](https://pandas.pydata.org/pandas-docs/stable/visualization.html)
    
&#x1F4E1; &nbsp; [Pandas Styling](https://pandas.pydata.org/pandas-docs/stable/style.html)

&#x1F4E1; &nbsp; [ggplot2](https://ggplot2.tidyverse.org/)

&#x1F4E1; &nbsp; [R Tutorial](https://www.tutorialspoint.com/r/index.htm)
"""

# Commented out IPython magic to ensure Python compatibility.
# %%html
# <style> 
# @import url('https://fonts.googleapis.com/css?family=Orbitron|Roboto&effect=3d');
# body {background-color: gainsboro;} 
# h4 {color:#818286; font-family:Roboto;}
# span {color:black; text-shadow:4px 4px 4px #aaa;}
# div.output_prompt,div.output_area pre {color:slategray;}
# div.input_prompt,div.output_subarea {color:#37c9e1;}      
# div.output_stderr pre {background-color:gainsboro;}  
# div.output_stderr {background-color:slategrey;}       
# </style>

import numpy as np,pandas as pd
import pylab as pl,seaborn as sns
from matplotlib import cm
import warnings; warnings.filterwarnings("ignore")
style_dict={'background-color':'slategray','color':'#37c9e1',
            'border-color':'white','font-family':'Roboto'}

"""<h1 class='font-effect-3d' style='color:#37c9e1; font-family:Orbitron;'>&#x1F310; &nbsp; Loading the Data <h1>"""

school_explorer=pd.read_csv('../input/2016 School Explorer.csv')
school_explorer.tail(2).T[3:41].style.set_properties(**style_dict)

na_list=['Adjusted Grade','New?','Other Location Code in LCGMS']
school_explorer=school_explorer.drop(na_list,axis=1)
school_explorer.shape

d5_shsat=pd.read_csv('../input/D5 SHSAT Registrations and Testers.csv')
d5_shsat.head().T.style.set_properties(**style_dict)

d5_shsat.shape

"""<h1 class='font-effect-3d' style='color:#37c9e1; font-family:Orbitron;'>&#x1F310; &nbsp; Missing Values<h1>"""

school_explorer.isna().sum()[school_explorer.isna().sum()!=0]\
.to_frame().style.set_properties(**style_dict)

sum(d5_shsat.isna().sum())

"""<h1 class='font-effect-3d' style='color:#37c9e1; font-family:Orbitron;'> &#x1F310; &nbsp; School Names <h1>"""

school_explorer['School Name'].value_counts()[:5]\
.to_frame().style.set_properties(**style_dict)

# different schools have the same names
c1,c2=school_explorer['School Name']=='P.S. 212',\
school_explorer['School Name']=='P.S. 253'
school_explorer[c1 | c2].T[:10].style.set_properties(**style_dict)

# replacing the same values in 'School Name'
school_explorer.loc[427,'School Name']='P.S. 212 D12'
school_explorer.loc[1023,'School Name']='P.S. 212 D30'
school_explorer.loc[712,'School Name']='P.S. 253 D21'
school_explorer.loc[908,'School Name']='P.S. 253 D27'
len(set(school_explorer['School Name']))==school_explorer.shape[0]

pl.style.use('seaborn-whitegrid')
fig,ax=pl.subplots(1,figsize=(14,14))
sns.countplot(y='School name',data=d5_shsat,palette='Blues',ax=ax,
              order=d5_shsat['School name'].value_counts().index)
ax.tick_params('both',labelsize=15)
ax.set_xlabel(''); ax.set_ylabel('')
pl.title('D5 SHSAT. Number of Notes by Schools',fontsize=20);

"""<h1 class='font-effect-3d' style='color:#37c9e1; font-family:Orbitron;'>&#x1F310; &nbsp; Preprocessing Variables </h1>"""

school_explorer['School Income Estimate']=\
school_explorer['School Income Estimate'].astype('str') 
for s in [",","$"," "]:
    school_explorer['School Income Estimate']=\
    school_explorer['School Income Estimate'].str.replace(s,"")
school_explorer['School Income Estimate']=\
school_explorer['School Income Estimate'].str.replace("nan","0")
school_explorer['School Income Estimate']=\
school_explorer['School Income Estimate'].astype(float)
school_explorer['School Income Estimate'].replace(0,np.NaN,inplace=True)

percent_list=['Percent ELL','Percent Asian','Percent Black','Percent Hispanic',
              'Percent Black / Hispanic','Percent White','Student Attendance Rate',
              'Percent of Students Chronically Absent','Rigorous Instruction %',
              'Collaborative Teachers %','Supportive Environment %',
              'Effective School Leadership %',
              'Strong Family-Community Ties %','Trust %']
target_list=['Average ELA Proficiency','Average Math Proficiency']
economic_list=['Economic Need Index','School Income Estimate']
rating_list=['Rigorous Instruction Rating','Collaborative Teachers Rating',
             'Supportive Environment Rating','Effective School Leadership Rating',
             'Strong Family-Community Ties Rating','Trust Rating',
             'Student Achievement Rating']
for el in percent_list:
    school_explorer[el]=school_explorer[el].astype('str')
    school_explorer[el]=school_explorer[el].str.replace("%","")
    school_explorer[el]=school_explorer[el].str.replace("nan","0")
    school_explorer[el]=school_explorer[el].astype(float)
    school_explorer[el].replace(0,np.NaN,inplace=True)
    school_explorer[el]=school_explorer[el].interpolate()    
for el in target_list+economic_list:
    school_explorer[el]=school_explorer[el].interpolate()    
for el in rating_list:
    moda_value = school_explorer[el].value_counts().idxmax()
    school_explorer[el]=school_explorer[el].fillna(moda_value)

print('Number of Missing Values: ',sum(school_explorer.isna().sum()))

"""<h1 class='font-effect-3d' style='color:#37c9e1; font-family:Orbitron;'> &#x1F310; &nbsp; Indicators' Visualisation </h1>
Let's try to find the best way of representing each variable.

#### Distribution Plots
"""

f,ax=pl.subplots(ncols=2,figsize=(14,5))
for i in range(2):
    school_explorer[economic_list[i]]\
    .plot.hist(bins=100,alpha=.8,cmap=cm.bwr,
               title=economic_list[i],ax=ax[i]);

ti='Average Proficiency for Schools'
school_explorer[target_list].plot.hist(bins=100,alpha=.8,title=ti,
                                       figsize=(14,5),cmap=cm.bwr);

pl.figure(figsize=(14,5))
sns.countplot(x="Year of SHST",hue="Grade level",
              data=d5_shsat,palette='Blues')
pl.title('Year Distribution. D5 SHSAT',fontsize=12);

"""#### Area Plots"""

ax=pl.gca()
school_explorer[['Percent Asian','Percent White','Percent Black']]\
.plot.area(figsize=(14,7),cmap=cm.bwr_r,ax=ax,
           title='Distribution by Population Groups')
ax.set_facecolor('slategray');

"""#### Scatter Plots"""

f,ax=pl.subplots(ncols=2,figsize=(14,5))
for i in range(2):
    if i==0: c=cm.bwr
    else: c=cm.bwr_r
    school_explorer.plot(kind="scatter",x="Longitude",y="Latitude",
                         s=10,c=school_explorer[economic_list[i]], 
                         title=economic_list[i],ax=ax[i],
                         cmap=c,colorbar=True,alpha=.8);

f,ax=pl.subplots(ncols=2,figsize=(14,5))
for i in range(2):
    school_explorer.plot(kind="scatter",x="Longitude",y="Latitude",
                         c=school_explorer[target_list[i]], 
                         title=target_list[i],ax=ax[i],
                         cmap=cm.bwr_r,colorbar=True,alpha=.8);

"""Fortunately, economic problems are not correlated with educational results so strongly."""

f,axes=pl.subplots(ncols=2,nrows=2,figsize=(14,14))
school_explorer.plot(kind='scatter',x='Longitude',y='Latitude', 
                     alpha=.8,color='#ff3636',s=school_explorer['Percent Hispanic'],
                     label='Hispanic',ax=axes[0,0])
school_explorer.plot(kind='scatter',x='Longitude',y='Latitude',ax=axes[0,0],
                     alpha=.8,color='#3636ff',s=school_explorer['Percent ELL'],
                     label='ELL',title='ELL and Hispanic Population of Schools') 
school_explorer.plot(kind='scatter',x='Longitude',y='Latitude', 
                     alpha=.8,color='white',s=school_explorer['Percent White'],
                     label='white',ax=axes[0,1],
                     title='Black, White, and Asian Population of Schools')    
school_explorer.plot(kind='scatter',x='Longitude',y='Latitude', 
                     alpha=.5,color='#3636ff',s=school_explorer['Percent Black'],
                     label='black',ax=axes[0,1])    
school_explorer.plot(kind='scatter',x='Longitude',y='Latitude', 
                     alpha=.3,color='#ff3636',s=school_explorer['Percent Asian'],
                     label='asian',ax=axes[0,1])
axes[0,1].set_facecolor('slategray')
school_explorer.plot(kind='scatter',x='Longitude',y='Latitude', 
                     alpha=.8,color='white', 
                     s=(100-school_explorer['Rigorous Instruction %'])*10,
                     label='Rigorous Instruction',ax=axes[1,0],
                     title='Social Environment Problems by Schools') 
school_explorer.plot(kind='scatter',x='Longitude',y='Latitude', 
                     alpha=.5,color='#3636ff', 
                     s=(100-school_explorer['Supportive Environment %'])*5,
                     label='Supportive Environment',ax=axes[1,0])
school_explorer.plot(kind='scatter',x='Longitude',y='Latitude', 
                     alpha=0.3,color='#ff3636', 
                     s=(100-school_explorer['Trust %']),
                     label='Trust',ax=axes[1,0])
axes[1,0].set_facecolor('slategray')
school_explorer.plot(kind="scatter",ax=axes[1,1],x="Longitude",y="Latitude",
                     s=school_explorer['Percent of Students Chronically Absent'],
                     c=school_explorer['Percent of Students Chronically Absent'], 
                     title='Students Chronically Absent by Schools',
                     cmap=cm.bwr,colorbar=False,alpha=.8);

"""As it was expected there are some correlation tendencies between economic problems and 

social environment, discipline, ethnic characteristics.
#### Pie Plots
"""

fig,ax=pl.subplots(ncols=2,nrows=3,figsize=(14,21))
id=[(0,0),(0,1),(1,0),(1,1),(2,0),(2,1)]
for i in range(6):   
    school_explorer[rating_list[i]].value_counts()\
    .plot.pie(autopct='%0.1f%%',ax=ax[id[i]],cmap=cm.bwr,
              labeldistance=.3,pctdistance=1.2,fontsize=12)

"""#### Bar Plots"""

fig,ax=pl.subplots(1,figsize=(14,7))
range_list=['Meeting Target','Exceeding Target',
            'Approaching Target','Not Meeting Target']
pl.suptitle('Student Achievement Rating by Districts')
pd.crosstab(school_explorer['District'],
            school_explorer['Student Achievement Rating'])\
[range_list].plot.bar(ax=ax,cmap=cm.bwr)
ax.tick_params('x',rotation=0);

fig,ax=pl.subplots(1,figsize=(14,7))
city_list=['NEW YORK','BRONX','BROOKLYN',
           'STATEN ISLAND','JAMAICA',
          'FLUSHING','LONG ISLAND CITY']
data=school_explorer.loc[school_explorer['City'].isin(city_list)]
pl.suptitle('Student Achievement Rating by Cities')
pd.crosstab(data['City'],data['Student Achievement Rating'])\
[range_list].sort_values('Meeting Target').plot.barh(ax=ax,cmap=cm.bwr);

gradelow_list=['PK','0K','01','02','03','05','06','07']
gradehigh_list=['0K','02','03','04','05','06','07','08','09','10','12']
fig,ax=pl.subplots(1,figsize=(14,7))
pl.suptitle('High and Low Grades by Districts',fontsize=20)
pd.crosstab(school_explorer['District'],
            school_explorer['Grade Low'])[gradelow_list]\
.plot.bar(ax=ax,cmap=cm.Spectral)
ax.grid(color='white'); ax.set_title('Grade Low')
ax.legend(loc=10,bbox_to_anchor=(1.1,.5))
ax.tick_params('x',rotation=0);
fig,ax=pl.subplots(1,figsize=(14,7))
pd.crosstab(school_explorer['District'],
            school_explorer['Grade High'])[gradehigh_list]\
.plot.bar(ax=ax,cmap=cm.Spectral)
ax.grid(color='white'); ax.set_title('Grade High')
ax.legend(loc=10,bbox_to_anchor=(1.1,.5));
ax.tick_params('x',rotation=0);

g4s_list=["All Students Tested","All Students 4s",
          "American Indian or Alaska Native 4s",
          "Black or African American 4s",
          "Hispanic or Latino 4s",
          "Asian or Pacific Islander 4s",
          "White 4s","Multiracial 4s",
          "Limited English Proficient 4s",
          "Economically Disadvantaged 4s"]
grades_ela=pd.DataFrame({'3':list(school_explorer.iloc[:,38:48].sum()),
                         '4':list(school_explorer.iloc[:,58:68].sum()),
                         '5':list(school_explorer.iloc[:,78:88].sum()),
                         '6':list(school_explorer.iloc[:,98:108].sum()),
                         '7':list(school_explorer.iloc[:,118:128].sum()),
                         '8':list(school_explorer.iloc[:,138:148].sum())},
                         index=g4s_list)
grades_math=pd.DataFrame({'3':list(school_explorer.iloc[:,48:58].sum()),
                          '4':list(school_explorer.iloc[:,68:78].sum()),
                          '5':list(school_explorer.iloc[:,88:98].sum()),
                          '6':list(school_explorer.iloc[:,108:118].sum()),
                          '7':list(school_explorer.iloc[:,128:138].sum()),
                          '8':list(school_explorer.iloc[:,148:158].sum())},
                          index=g4s_list)

f,ax=pl.subplots(ncols=2,nrows=3,figsize=(14,15))
df=[grades_math,grades_ela]
for i in range(2):
    df[i][:1].plot.bar(ax=ax[0,i],cmap=cm.Spectral,
                       alpha=.3,legend=False)
    df[i][1:2].plot.bar(ax=ax[0,i],title='All Students Tested',
                        cmap=cm.Spectral)
    df[i][1:2].plot.bar(ax=ax[1,i],cmap=cm.Spectral,
                        alpha=.3,legend=False)
    df[i][-1:].plot.bar(ax=ax[1,i],title='All Students 4s',
                        cmap=cm.Spectral)    
    df[i][1:2].plot.bar(ax=ax[2,i],cmap=cm.Spectral,
                        alpha=.3,legend=False)
    df[i][-2:-1].plot.bar(ax=ax[2,i],title='All Students 4s',
                          cmap=cm.Spectral)        
for i in range(3):
    for j in range(2):
        ax[i,j].tick_params('x',rotation=0,labelsize=12)        
pl.suptitle('Math'+70*' '+'ELA',fontsize=20);

f,ax =pl.subplots(ncols=2,figsize=(14,5))
for i in range(2):
    df[i][2:-2].plot.bar(ax=ax[i],title='4s Results',
                         cmap=cm.Spectral)
    ax[i].tick_params('x',labelsize=12)
pl.suptitle('Math'+70*' '+'ELA',fontsize=20);

"""Users could realize immediately that 

- the indicator `Economically Disadvantaged` is not a reason for worse exam scores, and 
- the indicator `Limited English Proficient` affects them really strongly.

It's not a secret that many test texts could have confused meaning so it's important to understand them correctly. 

The same explanation could be applied for understanding this problem in different ethnic groups: 

- worse English speakers have worse results.

So for the successful exam process, it needs to move in two directions: 

- make exam materials more understandable by visualization, 
- improve English knowledge among students as much as it is possible.
"""

d5_shsat_2016=\
d5_shsat[['Number of students who registered for the SHSAT',
          'Number of students who took the SHSAT']][d5_shsat['Year of SHST']==2016]
d5_shsat_2016=\
d5_shsat_2016.set_index(d5_shsat['School name'][d5_shsat['Year of SHST']==2016])
fig,ax=pl.subplots(1,figsize=(14,14))
d5_shsat_2016.plot.barh(ax=ax,cmap=cm.bwr)
ax.set_xlabel(''); ax.set_ylabel('')
ax.tick_params('y',labelsize=15)
pl.legend(fontsize=15);

"""<h1 class='font-effect-3d' style='color:#37c9e1; font-family:Orbitron;'> &#x1F310; &nbsp; School Types </h1>"""

fig,ax=pl.subplots(nrows=3,ncols=2,figsize=(14,16))
pl.suptitle('Community & Non-Community Schools',fontsize=15)
for i in range(2):
    sns.boxplot(x="Community School?",y=economic_list[i],
                data=school_explorer,ax=ax[0,i],palette='bwr')
    sns.distplot(school_explorer[target_list[i]][school_explorer['Community School?']=='Yes'],
                 ax=ax[1,i],color='#3636ff',label='Yes')
    sns.distplot(school_explorer[target_list[i]][school_explorer['Community School?']=='No'],
                 ax=ax[1,i],color='#ff3636',label='No')
    ax[1,i].legend()
    sns.countplot(x="Community School?",hue=rating_list[6-i], 
                  data=school_explorer,ax=ax[2,i],palette='bwr');

school_explorer[percent_list]\
.groupby(school_explorer['Community School?']).mean().T\
.style.background_gradient(cmap='bwr')\
.set_properties(**{'color':'black','font-family':'Roboto','font-size':'120%'})

"""Community schools need a lot of attention in all spheres, in the education results - at first.
<h1 class='font-effect-3d' style='color:#37c9e1; font-family:Orbitron;'> &#x1F310; &nbsp; Let's Go Ahead </h1>
Of course, this notebook is just only having a look at the data. 

It' s time to move to the next step.

&#x1F4D8; &nbsp; [PASSNYC. Numeric and Categorical Variables](https://www.kaggle.com/olgabelitskaya/passnyc-numeric-and-categorical-variables)
"""