# data analysis and manipulation
import numpy as np
import pandas as pd
np.set_printoptions(threshold=1000)

# visualization
import seaborn as sns
import matplotlib.pyplot as plt

#machine learning
import tensorflow as tf

#Regular expression
import re

"""
Explanation: STUDENT LOANS CHALLENGE
COURSERA ML CHALLENGE
<br>
This notebook was created to document the steps taken to solve the Predict Students’ Ability to Repay Educational Loans posted on the Data Science Community in Coursera.
The data is aviable at:
https://ed-public-download.app.cloud.gov/downloads/Most-Recent-Cohorts-All-Data-Elements.csv.
Documentation for the data is available at https://collegescorecard.ed.gov/data/documentation/. There is a data dictionary at https://collegescorecard.ed.gov/assets/CollegeScorecardDataDictionary.xlsx.
WORKFLOW
The Workflow suggested in https://www.kaggle.com/startupsci/titanic-data-science-solutions is going to be followed. The Workflow is the following:
    Question or problem definition.
    Acquire training and testing data.
    Wrangle, prepare, cleanse the data.
    Analyze, identify patterns, and explore the data.
    Model, predict and solve the problem.
    Visualize, report, and present the problem solving steps and final solution.
    Supply the results.

The workflow indicates general sequence of how each stage may follow the other. However, there are use cases with exceptions:

    We may combine mulitple workflow stages. We may analyze by visualizing data.
    Perform a stage earlier than indicated. We may analyze data before and after wrangling.
    Perform a stage multiple times in our workflow. Visualize stage may be used multiple times.
    Drop a stage altogether. We may not need supply stage to productize or service enable our dataset for a competition.

Problem Definition
Test to see if a set of institutional features can be used to predict student otucomes, in particular debt repayment. This solution is intended to try to explore to what extent instututional characteristics as well as certain demographic factors can indicate or predict debt repayment.
The (US) “College Scorecard” (the data set) includes national data on the earnings of former college graduates and new data on student debt.
Import Libraries
First import the libraries that are going to be used:
End of explanation
"""


all_data = pd.read_csv('datasets/CollegeScorecardData.csv')

"""
Explanation: Acqure Data
The data is acquired using pandas (I renamed the file to CollegeScorecardData.csv)
End of explanation
"""


all_data.head()

"""
Explanation: Analyze Data
First, let's see a little bit of the data
End of explanation
"""


all_data.info()

"""
Explanation: Find information about the features
Let's find more about the data
End of explanation
"""


data_dict = pd.read_csv('datasets/CollegeScorecardDataDictionary.csv')

data_dict.head()

data_dict.tail()

data_dict.info()

"""
Explanation: There are 7703 examples and 1743 features.
There are 443 float features that may be numeric, 13 integer features that may be categorical, and 1287 features that are strings, but may be numbers but data was not entered correctly (for example, if there was not data for a given feature, someone could have written "blank"). Given the high number of non numerical features, we need to explore them more. Luckly, there is a dictionary provided with the data, so we can explore it a little bit to learn about the data (The original file was converted do CSV)
End of explanation
"""


data_dict[5:10]

"""
Explanation: There are 1975 entries, but the column NAME OF DATA ELEMENT has only 1734 not nut elements, so something is up. Let's try to explore the dict a little bit more
End of explanation
"""


data_dict[10:20]

"""
Explanation: Nothing suspicius here, lets try again
End of explanation
"""


data_dict_no_nan_names = data_dict.dropna(subset=['NAME OF DATA ELEMENT'])
data_dict_no_nan_names[10:20]

"""
Explanation: Aha! It seems that the feature at index 15 is categorical, and that's why the rows that follow it don't have a value under NAME OF DATA ELEMENT. Just for now, let's get rid of those NAN rows.
End of explanation
"""


data_dict_no_nan_names.info()

"""
Explanation: Lets get the info of the new dict
End of explanation
"""


data_dict_no_nan_names['API data type'].unique()

"""
Explanation: We are interested primarly in the NAME OF DATA ELEMENT, VARIABLE NAME and API data type. They seem complete. Let's see howe many data types there are
End of explanation
"""


data_dict_no_nan_names['API data type'].value_counts()

"""
Explanation: Let's find out how many features have each data type
End of explanation
"""


data_dict_no_nan_names[data_dict_no_nan_names['API data type'] == 'autocomplete']

"""
Explanation: So in reality, there are 1206 float features, 521 integers, and 7 string features. (For now we assume that the autocomplete type is string). This numbers differ a lot from our previus analisys, in which we had 443 float features, 13 integer features and 1287 features that are strings.
Also, we cannot asume that all features of type integer are categorical, for example the ZIP code feature is integer but is not a categorical feature.
Let's find more about the autocomplete features:
End of explanation
"""


all_data_no_na_columns = all_data.dropna(axis=1, how='all')

"""
Explanation: We can see that these autocomplete features can be treated as strings. 
Delete features that have all their values NaN
End of explanation
"""


# Create a list to save the features that are above a certain threshold
features_with_high_ratio = []
# Create a list to save the features in all_data but not in the dict
features_not_in_dict = []

#Calculate the ratio
for feature in all_data_no_na_columns.columns.values:    
    # Get the row in the dict wich have VARIABLE NAME == feature
    row_in_dict = data_dict_no_nan_names[data_dict_no_nan_names['VARIABLE NAME'] == feature]
    # Get the data type of the row
    data_type_series = row_in_dict['API data type']
    
    #Check if exists in the dict
    if data_type_series.size > 0:
        # Get the data type
        data_type = data_type_series.values[0]
        # float features (numeric features) are not taken in account
        if data_type == 'integer' or data_type == 'string' or data_type ==  'autocomplete':
            column = all_data_no_na_columns[feature]
            column_no_na = column.dropna()
            r = column_no_na.unique().size / column_no_na.size
            if r > 0.8:
                features_with_high_ratio.append(feature)
                print(str(feature) + ": " + str(r))
    #The feature is not in the dict
    else:
        features_not_in_dict.append(feature)

print ("\nFeatures in data but not in the dictionary:" + str(features_not_in_dict))

"""
Explanation: Delete features that are meaningless
There are features that are meaningless for the problem we are trying to solve. We need to drop these features, but we need a criterion to eliminate them. The criterion that we are going to employ is to eliminate the features that are unique for every entry and don't add information to the problem, for example if we have a unique ID for every institution, this ID doesn't add information to the problem. 
Also, we need to take in account that there area features that may be unique for every entry, but DOES add relevant information. For example, the tuition fees may be unique and add information. 
Let's find the ratio of the number of unique values over number of examples:
End of explanation
"""


npt4_pub = data_dict_no_nan_names['VARIABLE NAME'] == 'NPT4_PUB'
npt41_pub = data_dict_no_nan_names['VARIABLE NAME'] == 'NPT41_PUB'
npt42_pub = data_dict_no_nan_names['VARIABLE NAME'] == 'NPT42_PUB'
data_dict_no_nan_names[npt4_pub | npt41_pub | npt42_pub ]

"""
Explanation: So there are some features in the data that are not explained in the dictionary. Tha is not necessarly an inconvenience, so we won't worry abot this right now.
Lets find what those NTP4 features are about
End of explanation
"""


# Create a list to save the features that are above a certain threshold
features_with_high_ratio = []
# Create a list to save the features in all_data but not in the dict
features_not_in_dict = []

#Calculate the ratio
for feature in all_data_no_na_columns.columns.values:    
    # Get the row in the dict wich have VARIABLE NAME == feature
    row_in_dict = data_dict_no_nan_names[data_dict_no_nan_names['VARIABLE NAME'] == feature]
    # Get the data type of the row
    data_type_series = row_in_dict['API data type']
    
    #Check if exists in the dict
    if data_type_series.size > 0:
        # Get the data type
        data_type = data_type_series.values[0]
        # float features (numeric features) are not taken in account
        if (data_type == 'integer' or data_type == 'string' or data_type ==  'autocomplete') \
        and feature[:4] != 'NPT4':
            column = all_data_no_na_columns[feature]
            column_no_na = column.dropna()
            r = column_no_na.unique().size / column_no_na.size
            if r > 0.5:
                features_with_high_ratio.append(feature)
                print(str(feature) + ": " + str(r))
print(features_with_high_ratio)

"""
Explanation: So those NTP4 features are about Average Net prices, so they are defenetly numeric features, and it makes sense to keep them.
Let's run our previous analysis again with out those features so we can have a cleaner visualization as we lower the threshold
End of explanation
"""


high_ratio_features = pd.DataFrame()
for feature in features_with_high_ratio:
    high_ratio_features = high_ratio_features.append(data_dict_no_nan_names[data_dict_no_nan_names['VARIABLE NAME'] == feature])
high_ratio_features

"""
Explanation: Let's see what are these features about:
End of explanation
"""


all_data_no_id_cols = all_data_no_na_columns.drop(['UNITID', 'OPEID', 'OPEID6', 'INSTNM', 'INSTURL', 'NPCURL', 'ALIAS', 'ZIP'], axis = 1)

all_data_no_id_cols.head()

"""
Explanation: So UNITID, OPEID, OPEID6, INSTNM, INSTURL, NPCURL and ALIAS are features that have to do with the identity of the institution, so they don't add relevant information to the problem, therfore they will be eliminated. (flag_e)
The ZIP code could be useful if it is used to group the schools to some sort of category about it's location. We are not going to to this so we are going to eliminate it as well.
End of explanation
"""


data_dict_no_nan_names[data_dict_no_nan_names['API data type'] == 'string']

"""
Explanation: Work on the string and autocmplet data
End of explanation
"""


all_data_no_id_cols['STABBR']

"""
Explanation: We already dropped INSTURL and NPCURL. Let's explore the STABBR feature
End of explanation
"""


all_data_no_id_cols['ACCREDAGENCY']

all_data_no_id_cols['ACCREDAGENCY'].value_counts()

"""
Explanation: So this feature has to do with the state where the school is located. Let's explore the ACCREDAGENCY feature:
End of explanation
"""


data_dict_no_nan_names[data_dict_no_nan_names['API data type'] == 'autocomplete']

"""
Explanation: Now les's explore the autocomplete data type:
End of explanation
"""


all_data_no_id_cols['CITY']

"""
Explanation: INSTNM and ALIAS where dropped, let's see the CITY feature:
End of explanation
"""


all_data_no_strings = all_data_no_id_cols.copy()

#STABBR mapping
values = all_data_no_strings['STABBR'].unique()
mapping = {}
numeric_value = 1
for value in values:
    mapping[value] = numeric_value
    numeric_value += 1
all_data_no_strings['STABBR'] = all_data_no_strings['STABBR'].map(mapping)

#ACCREDAGENCY mapping
values = all_data_no_id_cols['ACCREDAGENCY'].unique()
mapping = {}
numeric_value = 1
for value in values:
    mapping[value] = numeric_value
    numeric_value += 1
all_data_no_strings['ACCREDAGENCY'] = all_data_no_strings['ACCREDAGENCY'].map(mapping)

#CITY mapping
values = all_data_no_id_cols['CITY'].unique()
mapping = {}
numeric_value = 1
for value in values:
    mapping[value] = numeric_value
    numeric_value += 1
all_data_no_strings['CITY'] = all_data_no_strings['CITY'].map(mapping)

all_data_no_strings.head()

"""
Explanation: So STABBR, ACCREDAGENCY and CITY are features that we are going to keep, but they need to be transformed to an ordinal (using numbers) representation, since the ML algorithms use numbers and not strings.
End of explanation
"""


all_data_no_strings.info()

"""
Explanation: Let's see how our data looks so far
End of explanation
"""


regex = re.compile('[0-9]+(\.[0-9]+)?$')
words = []
for column in all_data_no_strings:
    if all_data_no_strings[column].dtypes == 'object':
        for data in all_data_no_strings[column]:
            if not regex.match(str(data)):
                words.append(data)

pd.Series(words).value_counts()

"""
Explanation: Although we mapped or eliminated the string features, we still have a lot object (not numeric) data types. Let's work on them
Fetures with object dtype
Let's try to find a sample of features that should be numbers, but for some reason in the data they are not numbers
End of explanation
"""


all_data_replaced_with_nan = all_data_no_strings.replace(to_replace = 'PrivacySuppressed', value = np.nan)
all_data_replaced_with_nan = all_data_replaced_with_nan.replace(to_replace = '12/31/2999', value = np.nan)
all_data_replaced_with_nan = all_data_replaced_with_nan.dropna(axis=1, how='all')

all_data_replaced_with_nan.info()

"""
Explanation: We can see that there is a lot of data suppresed for privacy reasons. Also, there are dates, and one of them 12/31/2999 seems to be invalid. Let's go ahead and replace these values with nan, so we will treat it as any nan value. Also, if any column ends having all of its values as Nan, we will delete this column.
End of explanation
"""


features_with_date = []
for column in all_data_replaced_with_nan:
    if all_data_replaced_with_nan[column].dtypes == 'object':
        if all_data_replaced_with_nan[column].str.match('[0-9]{2}/[0-9]{2}/[0-9]{4}').any():
            features_with_date.append(column)

features_with_date

data_dict_no_nan_names[data_dict_no_nan_names['VARIABLE NAME'] == 'SEPAR_DT_MDN']

"""
Explanation: Lets find wich features are date features
End of explanation
"""


all_data_no_dates = all_data_replaced_with_nan.drop(['SEPAR_DT_MDN'], axis = 1)

"""
Explanation: It seems that SEPAR_DT_MDN don't add valuable information to the problem, so we are going to drop it
End of explanation
"""


all_data_no_objects = all_data_no_dates.copy()
for feature in all_data_no_dates:
    if all_data_no_dates[feature].dtypes == 'object':
        #Make all data numeric
        all_data_no_objects[feature] = pd.to_numeric(all_data_no_dates[feature])        

all_data_no_objects.info()

"""
Explanation: Now we will transfore all the object features to numeric
End of explanation
"""


high_nan_features = []
for feature in all_data_no_objects:
    size = all_data_no_objects[feature].size
    number_of_valid = all_data_no_objects[feature].count()
    number_of_nan =  size - number_of_valid
    ratio = number_of_nan / size
    if ratio > 0.9:
        high_nan_features.append(feature)
print (len(high_nan_features))

all_data_no_high_nan = all_data_no_objects.drop(high_nan_features, axis = 1)

all_data_no_high_nan.info()

"""
Explanation: Now we have gotten rid of the object dtype
Eliminate features with high number of NaN values
We already deleted features with that had all of their value as NaN, but now we will eliminate features with a high percentage of NaN values (more than 90%)
End of explanation
"""


data_dict[15:25]

"""
Explanation: Filling missing data
We need to fill the mising data. To do this we need to know if the feature is numeric or categorical. Let's use the dictionary to get that info.
End of explanation
"""


categorical_features = []
is_null = data_dict['NAME OF DATA ELEMENT'].isnull()
for i in range(len(is_null) - 1):
    if not is_null[i] and is_null[i+1]:
        categorical_features.append(data_dict['VARIABLE NAME'][i])

"""
Explanation: We can see that after the name of a categorical feature, there is at least one item with value NaN. Let's use this to get a list of categorical features
End of explanation
"""


all_data_no_nan = all_data_no_high_nan.copy()
for feature in all_data_no_high_nan:
    if feature in categorical_features:
        mode = all_data_no_high_nan[feature].mode()[0]        
        all_data_no_nan[feature] = all_data_no_high_nan[feature].fillna(mode)
    else:
        mean = all_data_no_high_nan[feature].mean()
        all_data_no_nan[feature] = all_data_no_high_nan[feature].fillna(mean)

all_data_no_nan.head()

all_data_no_nan.info()

"""
Explanation: To fill the missing data that belongs to a categorical feature, we will use the most common value of the data (mode). To fill the missing data that belongs to a numeric feature, we will use the the average of the data (mean).
End of explanation
"""


all_data_no_nan.to_csv('datasets/CollegeScorecardDataCleaned.csv', index = False)

"""
Explanation: Let's save the data in a file
End of explanation
"""


