<jupyter_start><jupyter_text><jupyter_code># instal Library
!pip install pydata_google_auth
# import libraries
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt<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 BETWEEN '2019-05-01' AND '2019-09-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><empty_output><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>Histogram for all Variables<jupyter_code># histograms of professional preference
fplus_df.hist(bins=25, figsize=(20,15))
plt.show()<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># create a new dataframe for co_apps and drop any NAs for co app
co_apps = fplus_df.dropna(subset=['co_app_verifiable_annual_income__c'])
# view columns, data types
co_apps.info()
# get summary stats on co_app
co_apps['co_app_verifiable_annual_income__c'].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 boxplot ax
sns.boxplot(co_apps['co_app_verifiable_annual_income__c'], ax=ax_box)
# Add a distplot ax
sns.distplot(co_apps['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_apps = co_apps[co_apps['co_app_verifiable_annual_income__c'] < 1000000]
# replot
# Cut the window in 2 parts
f, (ax_hist, ax_box) = plt.subplots(2, sharex=True, gridspec_kw={"height_ratios": (.85, .25)})

# Add a boxplot ax
sns.boxplot(co_apps['co_app_verifiable_annual_income__c'], ax=ax_box)
# Add a distplot ax
sns.distplot(co_apps['co_app_verifiable_annual_income__c'], ax=ax_hist)

# Remove x axis name for the boxplot
ax_box.set(xlabel='')
# create function to make a Co-App Flag
def co_app(co_app_income):
    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(co_app)
# 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))

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

# Get co-app Perc for Fund vs Not Fund --- *as_index=False*
co_app_perc = fplus_df[['co_app', 'Fund']].groupby(['Fund'], as_index=False).mean()
# barplot x Fund y = co app 
sns.barplot(x='Fund', y='co_app', data=co_app_perc, order=[1,0], ax=axis2)
co_app_perc = fplus_df[['co_app', 'Fund']].groupby(['Fund'], as_index=False).mean()
co_app_perc
fund_rate_coapp = fplus_df[['co_app', 'Fund']].groupby(['co_app'], as_index=False).mean()
fund_rate_coapp
plt.figure(figsize=(12,8))
sns.barplot('co_app', 'Fund', data=fplus_df, palette='PuBuGn_d')
plt.title('Percentage Funded by Co-App', fontsize=16)
plt.xlabel('Co-App', fontsize=14)
plt.ylabel('Percent Fund', fontsize=14)<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'])
# view summary stats
loan_income['lti__c'].describe()<jupyter_output><empty_output><jupyter_text>Plot Data<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(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 Less than 1 and greater than 45 we will remove<jupyter_code>loan_income[loan_income['lti__c'] > 45].count()
loan_income[loan_income['lti__c'] < 1].count()
## remove outlier...
loan_income = loan_income[loan_income['lti__c'] < 45]
loan_income = loan_income[loan_income['lti__c'] >= 1]
# 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>Filter out bad data in full dataset<jupyter_code>fplus_df = fplus_df[fplus_df['lti__c'] < 45]
fplus_df = fplus_df[fplus_df['lti__c'] >= 1]<jupyter_output><empty_output><jupyter_text>3) - Marketing Channel (utm_source)  View Data<jupyter_code># get counts
fplus_df['utm_source__c'].value_counts()
print(fplus_df['utm_source__c'].value_counts().to_string())
# get percentages of sources
fplus_df['utm_source__c'].value_counts()/len(fplus_df)
# Get co-app Perc for Fund vs Not Fund --- *as_index=False*
utm_source = fplus_df[['utm_source__c', 'Fund']].groupby(['utm_source__c'], as_index=False).mean()
utm_source.sort_values('Fund', ascending=False)
utm_source.sort_values(by=['utm_source__c'])<jupyter_output><empty_output><jupyter_text>Cross Tab<jupyter_code>cm = sns.light_palette("blue", as_cmap=True)
pd.crosstab(fplus_df['utm_source__c'], fplus_df['Fund'], margins=True, margins_name='Total').style.background_gradient(cmap = cm)<jupyter_output><empty_output><jupyter_text>Pivot Table<jupyter_code>def fund_rate(x):
  return np.sum(x)/ np.size(x)


print(pd.pivot_table(fplus_df, values='Fund', index='utm_source__c', aggfunc={'Fund': [np.sum, np.size, fund_rate]}).to_string())<jupyter_output>fund_rate      size      sum
utm_source__c                                       
111                      0.160000      25.0      4.0
115                      0.155906     635.0     99.0
129                      0.000000       1.0      0.0
14                       0.242424     132.0     32.0
15                       0.000000       1.0      0.0
156                      0.156250      32.0      5.0
167                      0.230769      52.0     12.0
17                       0.333333       3.0      1.0
171                      0.066667      30.0      2.0
173                      0.205882      34.0      7.0
18                       0.000000       6.0      0.0
19                       0.000000      12.0      0.0
196                      0.375000       8.0      3.0
20                       0.246914      81.0     20.0
201                      0.139442     251.0     35.0
203                      0.178571      56.0     10.0
212                      0.000000       2.0   [...]
{'fraction_non_alphanumeric': 0.12551312352282623, 'fraction_numerical': 0.045030476427416345, 'mean_word_length': 4.237785016286645, 'pattern_counts': {'":': 4, '<': 64, '<?xml version=': 0, '>': 63, 'https://': 3, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '295753', 'license': 'MIT', 'n_tokens_mistral': 3329, 'n_tokens_neox': 3007, 'n_words': 618, 'path': 'Py_202_F+_Lead_Scoring_Model.ipynb', 'repo_name': 'papagorgio23/Python101', 'size': '8039'}