import pandas as pd

all_high_schools = pd.read_csv('data/DOE_High_School_Directory_2017.csv')
all_high_schools.shape

pd.set_option('display.max_columns', 453)
all_high_schools.head(3)

"""
Explanation: High Schools dataset cleaning and exploration
In this notebook we will clean and explore the 2017 High Schools dataset by the NYC Department of Education.
Let's start by opening and examining it.
End of explanation
"""


boys_only = all_high_schools['boys'] == 1

columns_of_interest = ['dbn', 'school_name', 'boro', 'academicopportunities1', 
                       'academicopportunities2', 'academicopportunities3',
                       'academicopportunities4', 'academicopportunities5', 'neighborhood', 
                       'location', 'subway', 'bus', 'total_students', 'start_time', 'end_time',
                       'graduation_rate', 'attendance_rate', 'pct_stu_enough_variety',
                       'college_career_rate', 'girls', 'specialized', 'earlycollege',
                       'program1', 'program2', 'program3', 'program4', 'program5', 'program6',
                       'program7', 'program8', 'program9', 'program10', 'interest1',
                       'interest2', 'interest3', 'interest4', 'interest5', 'interest6',
                       'interest7', 'interest8', 'interest9', 'interest10', 'city', 'zip']

df = all_high_schools[~boys_only][columns_of_interest]
df.set_index('dbn', inplace=True)
df.shape

df.head(3)

"""
Explanation: As ou can see there are over 400 columns so let's keep only columns of interest.
Also notice that the boys column is a flag for boys-only schools. Since we are trying to help solving the problem of women in tech it wouldn't make sense to keep them - let's filter them out.
End of explanation
"""


df['all'] = ""
df['girls'] = df['girls'].map({1: 'Girls-only'})
df['girls'].fillna('Mixed', inplace=True)

%matplotlib inline
import pylab as plt
import seaborn as sns

sns.set(style="whitegrid", color_codes=True)
ax = sns.violinplot(data=df, x='all', y="college_career_rate", hue="girls", split=True)
sns.despine(left=True)

ax.set_xlabel("")
plt.suptitle('College Career Rate by Type of School (Mixed or Girls-only)')

plt.savefig('figures/girls-only.png', bbox_inches='tight')

"""
Explanation: Let's now make a quick comparison of college_career_rate in girls-only schools vs mixed ones.
End of explanation
"""


import numpy as np

def contains_terms(column_name, terms=["tech"]):
    """Checks if at least one of the terms is present in the given column."""
    contains = []
    for i, term in enumerate(terms):
        contains.append(df[column_name].str.contains(terms[i], case=False))

    not_null = df[column_name].notnull()
    return (not_null) & (np.any(contains, axis=0))
    

def contains_terms_columns(column_root, n_columns, terms=["tech"]):
    """Checks if at least one of the terms is present in the columns given by its root name."""
    if n_columns == 1:
        return contains_terms(column_root, terms)
    
    tech = []
    for i in range(n_columns):
        column_name = column_root + str(i + 1)
        tech.append(contains_terms(column_name, terms))
    
    return np.any(tech, axis=0)

tech_academicopportunities = contains_terms_columns('academicopportunities', 5, 
                                                    terms=['technology', 'computer', 'web', 
                                                           'programming', 'coding'])
len(df[tech_academicopportunities])

# searching for 'tech' might match the word 'technical' 
all_tech_program = contains_terms_columns('program', 10, terms=['programming', 'computer', 
                                                                'tech'])
technical_program = contains_terms_columns('program', 10, terms=['technical'])
tech_program = (all_tech_program) & ~(technical_program)
len(df[tech_program])

tech_interest = contains_terms_columns('interest', 10, terms=['computer', 'technology'])
len(df[tech_interest])

tech_inclined = (tech_academicopportunities) | (tech_program) | (tech_interest)
print(len(df[tech_inclined]))
print("{:.1f}%".format(100 * len(df[tech_inclined]) / len(df)))

"""
Explanation: Now notice that there are 5 columns on "academic opportunities", 10 columns on "programs", and 10 more columns on "interests", and that in each of these areas some schools might have something that is tech-related. For each school, let's try to find whether we can find some tech related words in any of those areas and let's call it "tech inclination".
End of explanation
"""


df['tech_academicopportunities'] = tech_academicopportunities.astype(int)
df['tech_program'] = tech_program.astype(int)
df['tech_interest'] = tech_interest.astype(int)
df.head(3)

def fill_tech_summary(academicopportunities, program, interest):
    if academicopportunities:
        if program:
            if interest:
                return 'tech_academicopportunities+program+interest'
            else:
                return 'tech_academicopportunities+program'
            
        elif interest:
            return 'tech_academicopportunities+interest'
        else:
            return 'tech_academicopportunities'
    elif program:
        if interest:
                return 'tech_program+interest'
        else:
            return 'tech_program'
    elif interest:
        return 'tech_interest'
    else:
        return 'no_tech_inclination'

df['tech_summary'] = df.apply(lambda x: fill_tech_summary(x.loc['tech_academicopportunities'],
                                                          x.loc['tech_program'],
                                                          x.loc['tech_interest']), 
                              axis='columns')
df['tech_summary'].head()

fig, ax = plt.subplots(figsize=(20, 10))

ax = sns.violinplot(data=df, x='all', y="college_career_rate", hue="tech_summary", ax=ax,
                    hue_order=['no_tech_inclination', 'tech_interest', 'tech_program', 
                               'tech_academicopportunities', 'tech_program+interest',
                               'tech_academicopportunities+program',
                               'tech_academicopportunities+interest', 
                               'tech_academicopportunities+program+interest'])
sns.despine(left=True)
ax.set_xlabel("")
plt.suptitle('College Career Rate by Types of Tech Inclination')

plt.savefig('figures/types-tech-inclination.png', bbox_inches='tight')

def fill_tech_summary_compact(academicopportunities, program, interest):
    if academicopportunities or program or interest:
        return 'tech_inclined'
    else:
        return 'not_tech_inclined'

df['tech_summary_compact'] = df.apply(lambda x: fill_tech_summary_compact(
                                                          x.loc['tech_academicopportunities'],
                                                          x.loc['tech_program'],
                                                          x.loc['tech_interest']), 
                              axis='columns')
df['tech_summary_compact'].head()

fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(20, 8))

ax1 = sns.violinplot(data=df, x='all', y="college_career_rate", hue="tech_summary_compact",
                     split=True, inner="quartile", ax=ax1)

ax2 = sns.violinplot(data=df, x='all', y="total_students", hue="tech_summary_compact", 
                     split=True, inner="quartile", ax=ax2)

ax1.set_xlabel("")
ax2.set_xlabel("")
sns.despine(left=True)
plt.suptitle('College Career Rate and Total Students by Tech Inclination')

plt.savefig('figures/breakdown-tech-inclination.png', bbox_inches='tight')

"""
Explanation: Since 46% of schools are tech inclined and our assumption here was that 200 high schools were enough let's use only tech-inclined schools going forward. It could help the canvassing team if they were talking to female students from schools that have some tech-inclination.
However, let's first see how schools compare with each other taking that into consideration.
End of explanation
"""


new_columns = ['school_name', 'boro', 'tech_academicopportunities', 'neighborhood', 'location',
               'subway', 'bus', 'total_students', 'start_time', 'end_time', 'graduation_rate',
               'attendance_rate', 'pct_stu_enough_variety', 'college_career_rate', 'girls',
               'specialized', 'earlycollege', 'tech_program', 'tech_interest', 'city', 'zip']
tech_schools = df[tech_inclined][new_columns]
tech_schools.head(3)

"""
Explanation: We can see from the violin plots above that even though tech inclined high schools have a sligtly higher college career rate median, they have slightly lower 25% and 75% quartiles. On the other hand, most high schools with 1500 or more students seem to have some kind of tech inclination.
End of explanation
"""


fig, ax = plt.subplots(figsize=(20, 8))
ax.set_xlim(0, 1.02)
ax.set_ylim(0, 1.05)

sns.regplot(tech_schools['graduation_rate'], tech_schools['college_career_rate'], order=3)

ax.set_xlabel('Graduation Rate')
ax.set_ylabel('College Career Rate')
plt.suptitle('College Career Rate by Graduation Rate')

plt.savefig('figures/college-career-and-graduation-rate.png', bbox_inches='tight')

"""
Explanation: Let's now shift our focus to the graduation_rate and college_career_rate columns. In particular, college_career_rate's definition is "at the end of the 2014-15 school year, the percent of students who graduated 'on time' by earning a diploma four years after they entered 9th grade".
We could multiply that by the total number of students in each school and calculate the potential number of college schools each school has.
End of explanation
"""


potential = tech_schools['college_career_rate'] * tech_schools['total_students']
potential.sort_values(inplace=True, ascending=False)
potential

null_college_career_rate = tech_schools.college_career_rate.isnull()
print("{:.1f}%".format(100 * len(tech_schools[null_college_career_rate]) / len(tech_schools)))

null_graduation_rate = tech_schools.graduation_rate.isnull()
print("{:.1f}%".format(100 * len(tech_schools[null_graduation_rate]) / len(tech_schools)))

print("{:.1f}%".format(100 * len(tech_schools[(null_college_career_rate) & \
                                              (null_graduation_rate)]) \
                       / len(tech_schools)))

fig, ax = plt.subplots(figsize=(20, 8))
sns.distplot(tech_schools['total_students'], bins=range(0, 6000, 250), kde=False, rug=True)

ax.set_xlabel('Total Students')
ax.set_ylabel('Number of Schools with that Many Students')
plt.suptitle('Number of Schools by Total Students')

tech_schools[(null_college_career_rate) & (null_graduation_rate)]['total_students'].max()

"""
Explanation: We can see that graduation_rate and college_career_rate have a strong correlation. That means if we have too many college_career_rate null values we can use graduation_rate as a proxy.
End of explanation
"""


import numpy as np

fig, ax = plt.subplots(figsize=(20, 8))
ax.set_xlim(0.05, 1.15)

schools_to_plot = tech_schools[~(null_college_career_rate)]
sns.distplot(schools_to_plot['college_career_rate'], bins=np.arange(0, 1, 0.1))

ax.set_xlabel('College Career Rate')
ax.set_ylabel('Number of Schools with that Rate')
plt.suptitle('Number of Schools by College Career Rate')

fig.savefig('figures/college-career-rate.png', bbox_inches='tight')

"""
Explanation: It seems that 14% of schools don't have figures on the graduation rate. Let's plot its distribution to help decide if we should either ignore the column or the schools without that data.
End of explanation
"""


# Copy to avoid chained indexing and the SettingWithCopy warning (http://bit.ly/2kkXW5B)
tech_col_potential = pd.DataFrame(tech_schools, copy=True)
tech_col_potential.dropna(subset=['college_career_rate'], inplace=True)

tech_col_potential['potential_college_students'] = (tech_col_potential['total_students'] *\
                                                    tech_col_potential['college_career_rate'])\
                                                .astype(int)

tech_col_potential.sort_values('potential_college_students', inplace=True, ascending=False)
tech_col_potential.head(3)

fig, ax = plt.subplots(figsize=(20, 8))
sns.distplot(tech_col_potential['potential_college_students'], bins=range(0, 6000, 250),
             kde=False, rug=True)

ax.set_xlabel('Potential College Students')
ax.set_ylabel('Number of Schools')
plt.suptitle('Number of Schools by Potential College Students')

plt.savefig('figures/potential-college-students.png', bbox_inches='tight')

high_potential = tech_col_potential['potential_college_students'] > 1000
high_potential_schools = tech_col_potential[high_potential]
len(high_potential_schools)

"""
Explanation: Since some schools have a really low college career rate let's use that data and filter schools that don't have that data point.
Let's do that and also plot the distribution of schools by their number of potential college students.
End of explanation
"""


high_potential_schools.loc[:, ('subway', 'bus')]

high_potential_schools['subway_nearby'] = df.apply(lambda x: 'no subway' if pd.isnull(x['subway'])
                                                       else 'subway nearby', 
                                                   axis='columns')
high_potential_schools['subway_nearby']

fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(20, 8))
high_potential_schools['all'] = ""

ax1 = sns.violinplot(data=high_potential_schools, x='all', y="college_career_rate", 
                     hue="subway_nearby", split=True, inner="quartile", ax=ax1)

ax2 = sns.violinplot(data=high_potential_schools, x='all', y="total_students",
                     hue="subway_nearby", split=True, inner="quartile", ax=ax2)

ax1.set_xlabel("")
ax2.set_xlabel("")
sns.despine(left=True)
plt.suptitle('College Career Rate and Total Students by Subway Nearby')

fig.savefig('figures/subway-vs-no-subway.png', bbox_inches='tight')

"""
Explanation: There seems to be a big gap in the number of schools with more than 1000 potential college students as compared to the number of schools with fewer potential college students.
Since we want to reduce the number of recommended stations by at least 90% and there are 24 schools with at least 1000 potential college students let's filter those and ignore the other ones.
Next, let's examine the subway and  bus columns, which tells us which subway and bus lines are near each school.
End of explanation
"""


# Copy to avoid chained indexing and the SettingWithCopy warning (http://bit.ly/2kkXW5B)
close_to_subway = pd.DataFrame(high_potential_schools, copy=True)
close_to_subway.dropna(subset=['subway'], inplace=True)
close_to_subway

"""
Explanation: Notice how the 75% percentile of college career rate in high schools with a subway nearby is much higher. Also notice that the schools with the highest number of students all seem to have a subway nearby.
Going forward we will filter schools without a subway station nearby.
End of explanation
"""


import coordinates as coord

coord.add_coord_columns(close_to_subway, 'location')
close_to_subway.loc[:, ('latitude', 'longitude')]

"""
Explanation: Let's turn our attention to the location column. We have to extract latitude and longitude in order to be able to match this dataset with the subway stations location coordinates. Let's use add_coord_columns() which is defined in coordinates.py.
End of explanation
"""


!pip install folium

import folium

close_to_subway_map = folium.Map([40.72, -73.92], zoom_start=11, tiles='CartoDB positron',
                                 width='60%')

for i, school in close_to_subway.iterrows():
    marker = folium.RegularPolygonMarker([school['latitude'], school['longitude']],
                                         popup=school['school_name'], color='RoyalBlue', 
                                         fill_color='RoyalBlue', radius=5)
    marker.add_to(close_to_subway_map)

close_to_subway_map.save('maps/close_to_subway.html')
close_to_subway_map

"""
Explanation: Let's plot the the schools coordinates to see their geographical distribution:
End of explanation
"""


close_to_subway.rename(columns={'subway': 'subway_lines'}, inplace=True)

df_to_pickle = close_to_subway.loc[:, ('school_name', 'potential_college_students', 'latitude',
                                       'longitude', 'start_time', 'end_time', 'subway_lines',
                                       'city')]
df_to_pickle

df_to_pickle.to_pickle('pickle/high_schools.p')

"""
Explanation: The interactive map is available here.
It seems like we have all school data we need to perform the recommendations. Let's just clean the DataFrame columns and save it as a pickle binary file for later use in another Jupyter notebook.
End of explanation
"""


