<jupyter_start><jupyter_text><jupyter_code># Installing Library
!pip install pydata_google_auth
# import base packages into the namespace for this program
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
from sklearn.linear_model import LogisticRegression
from sklearn.naive_bayes import BernoulliNB
from sklearn import model_selection
from sklearn.model_selection import train_test_split
from sklearn.model_selection import StratifiedKFold
from yellowbrick.classifier import ROCAUC
from yellowbrick.classifier import ClassificationReport<jupyter_output><empty_output><jupyter_text>F+ Lead Scoring Model ![NPV Model](https://raw.githubusercontent.com/papagorgio23/Python101/master/NPV%20Model.png)  Get Data<jupyter_code># Using GBQ shout Out to Hughes
import pandas_gbq
import pydata_google_auth

SCOPES = [
    'https://www.googleapis.com/auth/cloud-platform',
    'https://www.googleapis.com/auth/drive',
]

# get credentials
credentials = pydata_google_auth.get_user_credentials(
    SCOPES,
    auth_local_webserver=False)
# GBQ
sql = """
SELECT 
  id
, co_app_verifiable_annual_income__c
, loan_use__c
, employment_status__c
, amount_of_loan_requested__c
, fico__c
, lti__c
, bcc0300__c
, ndi_ratio__c
, utm_source__c
, CASE WHEN date_funded__c IS NOT NULL THEN 1 ELSE 0 END AS Fund

FROM `freedom-dw.salesforce_ffam.application__c` a 

WHERE createddate >= '2019-07-01'
AND a.loan_officer__c  IS NOT NULL
"""
# run query
fplus_df = pandas_gbq.read_gbq(sql, project_id='ffn-dw-bigquery-prd', credentials=credentials, dialect='standard')<jupyter_output>Please visit this URL to authorize this application: https://accounts.google.com/o/oauth2/auth?response_type=code&client_id=495642085510-k0tmvj2m941jhre2nbqka17vqpjfddtd.apps.googleusercontent.com&redirect_uri=urn%3Aietf%3Awg%3Aoauth%3A2.0%3Aoob&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fbigquery&state=itYZ2IhTZtgiiyzIGt27X6NLt3kt3t&prompt=consent&access_type=offline
Enter the authorization code: 4/sAH_ofoqm6sa-9jNj0O7dIXDbq1l-5OzmugfAcQPDkzU6eGRxO9L6VI<jupyter_text>View Data<jupyter_code># view top 5 observations
fplus_df.head()
# view bottom 5 observations
fplus_df.tail()
# view columns, data types
fplus_df.info()
# get summary statistics
fplus_df.describe()
# check NA
fplus_df.isna().sum()<jupyter_output><empty_output><jupyter_text>Investigate Variables  1) - Co AppWe only care if an application has a Co App with them. We are using co_app_verifiable_annual_income__c to let us know if they have one. *   If NA then No Co App*   If Not NA then Co App  View data<jupyter_code># drop na for co app and view distribution
co_app = fplus_df.dropna(subset=['co_app_verifiable_annual_income__c'])
co_app.info()
co_app.describe()<jupyter_output><empty_output><jupyter_text>Plot<jupyter_code># Cut the window in 2 parts
f, (ax_hist, ax_box) = plt.subplots(2, sharex=True, gridspec_kw={"height_ratios": (.85, .25)})

# Add a graph in each part
sns.boxplot(co_app['co_app_verifiable_annual_income__c'], ax=ax_box)
sns.distplot(co_app['co_app_verifiable_annual_income__c'], ax=ax_hist)

# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>Remove outliers<jupyter_code>## remove outlier...
co_app = co_app[co_app['co_app_verifiable_annual_income__c'] < 200000]
# Cut the window in 2 parts
f, (ax_hist, ax_box) = plt.subplots(2, sharex=True, gridspec_kw={"height_ratios": (.85, .25)})

# Add a graph in each part
sns.boxplot(co_app['co_app_verifiable_annual_income__c'], ax=ax_box)
sns.distplot(co_app['co_app_verifiable_annual_income__c'], ax=ax_hist)

# Remove x axis name for the boxplot
ax_box.set(xlabel='')
def get_co_app_cat(co_app_income):
    """This function creates a Co-App Flag"""
    if pd.isnull(co_app_income):
        return 0
    return 1
# apply function to dataset
fplus_df['co_app'] = fplus_df['co_app_verifiable_annual_income__c'].apply(get_co_app_cat)
# view counts now
fplus_df['co_app'].value_counts()
# view percentage
fplus_df['co_app'].value_counts()/len(fplus_df)<jupyter_output><empty_output><jupyter_text>How does Co-App affect Funding?<jupyter_code># plot 2 barplots, 1 showing total counts, 1 showing co app percent by fund
fig, (axis1,axis2) = plt.subplots(1,2, sharex=True, figsize=(10,5))

# barplot
sns.countplot(x='co_app', data=fplus_df, order=[1,0], ax=axis1)

# Get Fund rate for Co-App vs No Co-App
fund_perc = fplus_df[["Fund", "co_app"]].groupby(['Fund'], as_index=False).mean()
sns.barplot(x='Fund', y='co_app', data=fund_perc, order=[1,0], ax=axis2)<jupyter_output><empty_output><jupyter_text>2) - Loan to Income (LTI)  View Data<jupyter_code># drop na for lti and view distribution
loan_income = fplus_df.dropna(subset=['lti__c'])
loan_income['lti__c'].describe()
# Cut the window in 2 parts
f, (ax_hist, ax_box) = plt.subplots(2, sharex=True, gridspec_kw={"height_ratios": (.85, .25)})

# Add a graph in each part
sns.boxplot(loan_income['lti__c'], ax=ax_box)
sns.distplot(loan_income['lti__c'], ax=ax_hist)

# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>Remove Outliers<jupyter_code>744/139230<jupyter_output><empty_output><jupyter_text>3) - Marketing Channel (utm_source)<jupyter_code># Cross tab
cm = sns.light_palette("green", as_cmap=True)
pd.crosstab(fplus_df['utm_source__c'], fplus_df['Fund'], values=fplus_df['Fund'], aggfunc=[len, np.mean], margins=True, margins_name="Total").style.background_gradient(cmap = cm)
# pivot table
def fund_rate(x):
  '''This function is used within pivot_tables to calculate ratios'''
  return np.sum(x) / np.size(x)

pivoting = pd.pivot_table(fplus_df, values='Fund', index='utm_source__c',
              aggfunc={'Fund': [np.sum, np.size, fund_rate]})

print(pivoting.sort_values('size', ascending=False).to_string())<jupyter_output><empty_output><jupyter_text>4) - FICO  5) - Employment  6) - Debt to Income (NDI)  7) - Debt to Income Squared  8) - Loan Use  9) - Bank Card Trades (bcc0300)  10) - Loan Amount  Prediction Model<jupyter_code>model_data = fplus_df.dropna()
def get_co_app_cat(co_app_income):
    
    if pd.isnull(co_app_income):
        return 0
    return 1

model_data['co_app_verifiable_annual_income__c'] = model_data['co_app_verifiable_annual_income__c'].apply(get_co_app_cat)
# create dummies
cat_vars = ['loan_use__c','employment_status__c','utm_source__c']
for var in cat_vars:
    cat_list = pd.get_dummies(model_data[var], prefix=var)
    temp = model_data.join(cat_list)
    model_data = temp
data_vars = model_data.columns.values.tolist()
to_keep = [i for i in data_vars if i not in cat_vars]
model_data = model_data[to_keep]
model_data.info()
model_data = model_data.drop(['id'], axis = 1)
# segment out the variable we are predicting from the rest of the data
y = model_data['Fund']
X = model_data.drop(['Fund'], axis = 1)

# Split the data into a train and test dataset
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2)
print(X_train.shape, y_train.shape)
print(X_test.shape, y_test.shape)

# 10 Fold Cross Validation. 
num_instances = len(X)
seed = 7
kfold = model_selection.KFold(n_splits=10, random_state=seed)
model = LogisticRegression()
results = model_selection.cross_val_score(model, X, y, cv=kfold)
print("Accuracy: %.3f%% (%.3f%%)" % (results.mean()*100.0, results.std()*100.0))
# Set classes for all plots
classes = ['Not Fund', 'Fund']

# Instantiate the visualizer with the classification model
visualizer = ROCAUC(model, classes=classes)


visualizer.fit(X_train, y_train)  # Fit the training data to the visualizer
visualizer.score(X_test, y_test)  # Evaluate the model on the test data
g = visualizer.poof() 
# Instantiate the classification model and visualizer
visualizer = ClassificationReport(model, classes=classes, support=True)

visualizer.fit(X_train, y_train)  # Fit the visualizer and the model
visualizer.score(X_test, y_test)  # Evaluate the model on the test data
g = visualizer.poof()             # Draw/show/poof the data<jupyter_output>/usr/local/lib/python3.6/dist-packages/sklearn/linear_model/logistic.py:432: FutureWarning: Default solver will be changed to 'lbfgs' in 0.22. Specify a solver to silence this warning.
  FutureWarning)<jupyter_text>Data Transformation Functions<jupyter_code>import numpy as np
import pandas as pd

def get_co_app_cat(co_app_income):
    
    if pd.isnull(co_app_income):
        return 0
    return 1

def get_loan_use_cat(loan_use):
    
    if pd.isnull(loan_use):
        return 3
    
    loan_use = loan_use.strip()
    if (loan_use == 'Credit Card Refinancing'):
        return 4
    if (loan_use in ['Major Purchase','Other']):
        return 2
    if (loan_use == 'Auto Purchase'):
        return 1
    
    return 3

def get_employment_cat(employment_status):
    
    if pd.isnull(employment_status):
        employment_status = ''
    
    employment_status = employment_status.strip()
    if (employment_status == 'Retired'):
        return 4
    if (employment_status in ['Self-employed']):
        return 2
    if (employment_status in ['Other', '']):
        return 1
    
    return 3

def get_loan_amount_cat(loan_amount):
    
    if pd.isnull(loan_amount):
        return 1
    
    loan_amount = float(loan_amount)
    if (loan_amount < 15000):
        return 4
    if (loan_amount >= 15000) and (loan_amount < 20000):
        return 3
    if (loan_amount >= 20000) and (loan_amount < 25000):
        return 2
    
    return 1

def get_mkt_chan_cat(utm_source):
    
    if pd.isnull(utm_source):
        return 3
    
    utm_source = utm_source.strip()
    if (utm_source in ['creditkarma','nerdwallet']):
        return 7
    if (utm_source in ['credible','experian']):
        return 6
    if (utm_source in ['website', 'google','msn','ck','nerd',
                        '115','save','dm','SLH','201']):
        return 5
    if (utm_source in ['facebook', 'even','uplift','Quinstreet',
                       'Personalloanpro','113']):
        return 2
    if (utm_source in ['LendEDU', 'monevo','247','sfl']):
        return 1
    
    return 3

def get_fico(fico):
    
    if pd.isnull(fico):
        return 990
    
    fico = int(fico)
    if (fico >= 9000):
        return 990
    if fico < 600:
        return 990
    
    return fico

def get_lti(lti):
    
    if pd.isnull(lti):
        return 36
    
    lti = float(lti)
    if (lti > 35) or (lti < 1):
        return 36
    if (lti >= 1) and (lti < 2):
        return 35
    if (lti >= 2) and (lti < 3):
        return 34
    
    return np.floor(lti)

def get_bcc0300(bcc0300):
    
    if pd.isnull(bcc0300):
        return 99
    
    bcc0300 = int(bcc0300)
    if (bcc0300 >= 25):
        return 30
    
    return bcc0300

def get_ndi_ratio(ndi_ratio):
    
    if pd.isnull(ndi_ratio):
        return 5
    
    ndi_ratio = float(ndi_ratio)
    ndi_ratio = np.floor(ndi_ratio)
    
    if (ndi_ratio < 10):
        return 5
    if (ndi_ratio > 75):
        return 80
    
    return ndi_ratio  


# Compute the correlation matrix
corr = model_data.corr()

# Generate a mask for the upper triangle
mask = np.zeros_like(corr, dtype=np.bool)
mask[np.triu_indices_from(mask)] = True

# Set up the matplotlib figure
f, ax = plt.subplots(figsize=(11, 9))

# Generate a custom diverging colormap
cmap = sns.diverging_palette(220, 10, as_cmap=True)

# Draw the heatmap with the mask and correct aspect ratio
sns.heatmap(corr, mask=mask, cmap=cmap, vmax=.3, center=0,
            square=True, linewidths=.5, cbar_kws={"shrink": .5})

# select only numeric data 
num_data = fplus_df.select_dtypes(include='number')

# Compute the correlation matrix
corr = num_data.corr()

# Generate a mask for the upper triangle
mask = np.zeros_like(corr, dtype=np.bool)
mask[np.triu_indices_from(mask)] = True

# Set up the matplotlib figure
f, ax = plt.subplots(figsize=(11, 9))

# Generate a custom diverging colormap
cmap = sns.diverging_palette(220, 10, as_cmap=True)

# Draw the heatmap with the mask and correct aspect ratio
sns.heatmap(corr, mask=mask, cmap=cmap, vmax=.3, center=0,
            square=True, linewidths=.5, cbar_kws={"shrink": .5})<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11459530454769332, 'fraction_numerical': 0.026596617960925956, 'mean_word_length': 4.497743682310469, 'pattern_counts': {'":': 5, '<': 56, '<?xml version=': 0, '>': 56, 'https://': 4, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '22945', 'license': 'MIT', 'n_tokens_mistral': 4829, 'n_tokens_neox': 4459, 'n_words': 1030, 'path': 'Py_202_F+_Model_Answers.ipynb', 'repo_name': 'papagorgio23/Python101', 'size': '12182'}