<jupyter_start><jupyter_text>Validate F+ Lead Scoring 2.0 Model  Load Data<jupyter_code># Installing Library
!pip install pydata_google_auth
# 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',
]

credentials = pydata_google_auth.get_user_credentials(
    SCOPES,
    auth_local_webserver=False)
sql = """

SELECT 
    a.id
    ,m.name
    ,m.loan_application_id
    ,m.lead_type__c
    ,m.createddate
    ,m.prequal_submitted_date__c
    ,m.full_app_submitted_date__c
    ,m.Loan_Officer_Assigned
    ,m.date_funded__c
    ,m.funded_flag
    ,m.loan_officer_name__c
    ,m.amount_of_loan_requested__c
    ,m.first_amount_of_loan_requested
    ,m.verifiable_annual_income__c
    ,m.first_income_p1
    ,m.co_app_verifiable_annual_income__c
    ,m.first_income_p2
    ,m.co_app
    ,m.first_coapp
    ,m.income_sum
    ,CAST(m.first_income_sum AS INT64) AS first_income_sum
    ,m.c_LTI
    ,m.First_LTI
    ,m.loan_use__c
    ,m.first_loan_use
    ,m.employment_status__c
    ,m.ndi_ratio__c
    ,m.first_ndi_ratio__c
    ,m.fico__c
    ,m.first_FICO
    ,m.utm_source__c
    ,m.bcc0300__c
    ,m.first_bcc0300__c
    ,a.interest_rate__c
    ,a.risk_group__c
    ,a.final_risk_group__c
    ,a.risk_group_p1__c
    
FROM 
    `ffn-dw-bigquery-prd.Credit_Views.Check_Sales_NPV_Model_Inputs` m
    
LEFT JOIN 
    `freedom-dw.salesforce_ffam.application__c` a ON m.name = a.name

WHERE 
    m.createddate BETWEEN '2019-05-01' AND '2019-10-15'
"""
df1 = pandas_gbq.read_gbq(sql, project_id='ffn-dw-bigquery-prd', credentials=credentials, dialect='standard')

df = df1
df1.head()
df.columns
df.columns<jupyter_output><empty_output><jupyter_text>Model Test 1 - First touch variables<jupyter_code># removed duplicate columns and save the first values
firstDf = df.drop(["id", "amount_of_loan_requested__c", "verifiable_annual_income__c", \
                "co_app_verifiable_annual_income__c", "co_app", "c_LTI", \
                "loan_use__c", "employment_status__c", "ndi_ratio__c", \
                "fico__c", "bcc0300__c"], axis=1)
firstDf.columns<jupyter_output><empty_output><jupyter_text>Model Test 2 - Current model  Mount Drive<jupyter_code># Load the Drive helper and mount
from google.colab import drive

# This will prompt for authorization.
drive.mount('/content/drive')
# set working directory
import os
os.chdir("/content/drive/My Drive/Data Scientist/F+ Lead Scoring Model/Validation/")
os.getcwd()
ls<jupyter_output>final_df.csv         main_leadLevel.py           scoringData.py
 first_df.csv         model.py                    unitEconomicsData.csv
 Fplus_Lead_Scoring   [0m[01;34m__pycache__[0m/               'Validation F+ Model.ipynb'
 full_data.csv        Retrain_Fplus.csv
 library_binning.py   score_collector_phase1.py<jupyter_text>Module Setup<jupyter_code>import numpy as np
import pandas as pd
import logging
import os
from sklearn.externals import joblib


#dirdata = os.path.join(os.path.dirname(__file__), 'Data')

global initialized
initialized = False

def init():
    
    global fplus_ls_model
    global unitEconomicsData
    
    global initialized
    if (initialized == True):
        return
    
    try:
        fplus_ls_model = joblib.load('Fplus_Lead_Scoring')
        unitEconomicsData = pd.read_csv('unitEconomicsData.csv')
        #fplus_ls_model = joblib.load(os.path.join(dirdata, 'Fplus_Lead_Scoring'))
        #unitEconomicsData = pd.read_csv(os.path.join(dirdata, 'unitEconomicsData.csv'))
        
    except Exception as e:
        #print(e)
        return
    
    initialized = True


## initialize Model
init()
## doesn't work.... So here we go manual
#fplus_ls_model = joblib.load('Data/Fplus_Lead_Scoring')
#unitEconomicsData = pd.read_csv('Data/unitEconomicsData.csv')
unitEconomicsData.head()<jupyter_output><empty_output><jupyter_text>Library Binning<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  



import numpy as np
import pandas as pd
from sklearn.linear_model import LogisticRegression

import library_binning as lib
#import scoringData as sd

def score_collector_phase1(lead, debug = False):
    
    mod_lead = pd.DataFrame()
    bin_vars = {}
    error = False
    error_msg = ''
    prob_prediction = 0
    npv = 0
    MODEL_NAME = 'fplus_ls_201902_v1'
    
    try:
        # create binned/transformed variables
        bin_vars['co_app'] = pd.Series(lib.get_co_app_cat(lead['co_app_verifiable_annual_income']))
        bin_vars['loan_use'] = lib.get_loan_use_cat(lead['loan_use'])
        bin_vars['employment'] = lib.get_employment_cat(lead['employment_status'])
        bin_vars['loan_amount'] = lib.get_loan_amount_cat(lead['final_loan_amount'])
        bin_vars['mkt_chan'] = lib.get_mkt_chan_cat(lead['utm_source'])
        bin_vars['ficox'] = lib.get_fico(lead['fico'])
        bin_vars['lti'] = lib.get_lti(lead['lti'])
        bin_vars['bcc0300'] = lib.get_bcc0300(lead['xpn_bcc0300'])
        bin_vars['ndi'] = lib.get_ndi_ratio(lead['ndi_ratio'])
        bin_vars['ndisq'] = bin_vars['ndi'] * bin_vars['ndi']
        
        mod_lead = pd.DataFrame(bin_vars)
        
        # create dummies
        cat_vars=['co_app','loan_use','employment','loan_amount','mkt_chan']
        for var in cat_vars:
            cat_list = pd.get_dummies(mod_lead[var], prefix=var)
            temp=mod_lead.join(cat_list)
            mod_lead=temp
        data_vars=mod_lead.columns.values.tolist()
        to_keep=[i for i in data_vars if i not in cat_vars]
        mod_lead=mod_lead[to_keep]
        # print(mod_lead.columns.values)
        
        # re-index to have same columns as the model
        mod_lead = mod_lead.reindex(columns = ['ficox', 'lti', 'bcc0300', 'ndi', 'ndisq', 'co_app_0', 'co_app_1',
                                       'loan_use_1', 'loan_use_2', 'loan_use_3', 'loan_use_4', 'employment_1',
                                       'employment_2', 'employment_3', 'employment_4', 'loan_amount_1',
                                       'loan_amount_2', 'loan_amount_3', 'loan_amount_4', 'mkt_chan_1',
                                       'mkt_chan_2', 'mkt_chan_3', 'mkt_chan_5', 'mkt_chan_6', 
                                       'mkt_chan_7'], fill_value=0)
        
        # score
        #prob_prediction = sd.fplus_ls_model.predict_proba(mod_lead)[0][1] I'm changing this to remove sd and just loading scoring model and data already
        prob_prediction = fplus_ls_model.predict_proba(mod_lead)[0][1]
        #print(prob_prediction)
        
        # calc NPV
        npv = get_npv_calc(lead['final_loan_amount'], prob_prediction, lead['utm_source'])
        
    except Exception as e:
        # print(e)
        error = True
        error_msg = 'Error in Scoring.'
    
    # add an error flag
    if (debug):
        return_scores = {
            'bin_lead': mod_lead,
            'fuse.score':prob_prediction,
            'fuse.npv': npv,
            'fuse.model': MODEL_NAME,
            'fuse.error_flag': error,
            'fuse.error_reason': error_msg
        }
        return return_scores
    
    # return the dictionary
    return_scores = {
        'fuse.score':prob_prediction,
        'fuse.npv': npv,
        'fuse.model': MODEL_NAME,
        'fuse.error_flag': error,
        'fuse.error_reason': error_msg
    }
    return return_scores


def get_npv_calc(loan_amt,prob_prediction,utm_source):
    
    if pd.notnull(utm_source):
        utm_src = utm_source.strip()
        utm_src = utm_src.lower()
    
    npv = 0
    cpa = 0
    
    #if pd.notnull(utm_source) and (utm_src in set(sd.unitEconomicsData['utm_source'])):   commenting this out because of the sd and loading it locally
    #    cpa = sd.unitEconomicsData.loc[sd.unitEconomicsData['utm_source'] == utm_src, 'CPA'].values[0]
    if pd.notnull(utm_source) and (utm_src in set(unitEconomicsData['utm_source'])):
        cpa = unitEconomicsData.loc[unitEconomicsData['utm_source'] == utm_src, 'CPA'].values[0]
                
    if pd.isnull(loan_amt):
        loan_amt = 0
    else:
        loan_amt = float(loan_amt) 
        
    npv = prob_prediction * ((loan_amt * 0.0785) - (707.9 + cpa))
    
    return npv<jupyter_output><empty_output><jupyter_text>Main Lead Level<jupyter_code>import numpy as np
import pandas as pd
import score_collector_phase1 as sc1

def main_leadlevel(leads, phase=1, provide="scored_lead"):
    
    if phase == 1:
        checked_lead = check_lead_phase1(leads)
        
        if checked_lead["error"]:
            return_value = {
                "fuse.score" : 0,
                "fuse.npv" : 0,
                "fuse.model" : '',
                "fuse.error_flag": True,
                "fuse_error_reason" : "Lead Validation Failed."
            }
            return return_value
        error_msg = checked_lead["warning"]
        
        return_value = sc1.score_collector_phase1(checked_lead["return_lead"])
        if len(error_msg.strip()) > 0:
            return_value['fuse.error_reason'] = error_msg + ' ' + return_value['fuse.error_reason']
        
        return return_value

# Check that the lead has all the required fields
# if a value is missing, return warning message
def check_lead_phase1(lead):
    
    # convert all keys to lower case
    raw_lead = {k.lower(): v for k, v in lead.items()}
    
    error = False
    return_lead = {}
    missing_variables = []
    error_msg = ''
    
    required_fields = ["co_app_verifiable_annual_income","loan_use","employment_status",
                       "final_loan_amount","fico","lti","xpn_bcc0300","ndi_ratio","utm_source"]

    try:
        for field in required_fields:
            if (field not in raw_lead):
                raw_lead[field] = np.NaN
        
        for key in raw_lead:
            var = returnNaNifEmptyorNaN(raw_lead[key])
            return_lead[key] = var
            if (pd.isnull(return_lead[key])):
                missing_variables.append(key) 
        
        if (len(missing_variables)>0):
            error_msg = ",".join(missing_variables)
            error_msg = "Imputing values for: " + error_msg
            
    except Exception as e: 
        #print(e)
        error=True
    
    return_value = {
        "return_lead" : return_lead,
        "warning": error_msg,
        "error": error
    }
    
    return return_value

def returnNaNifEmptyorNaN(variable):
    
    if isinstance(variable, int):
        return variable
    if isinstance(variable, float):
        return variable
    # if null or empty
    if pd.isnull(variable) or len(variable)<1:
        return np.NaN
    if variable.isnumeric():
        return variable

    return variable.strip()


lead = {"co_app_verifiable_annual_income":342,"loan_use":'Debt Consolidation', \
        "employment_status":'Full-time', "final_loan_amount":25000, "fico":700,\
        "lti":25.8, "xpn_bcc0300":42, "ndi_ratio":40, "utm_source":'Lending Tree'}
check_lead_phase1(lead)<jupyter_output><empty_output><jupyter_text>Model<jupyter_code>import scoringData as sd
import main_leadLevel as ml
import pandas as pd
import json

sd.init()

# Assumption : gets dictionary as a input
def run(leads):
    score = ml.main_leadlevel(leads)
    #score is dictionary, score['fuse_score'] is dataframe
    
    #add scores to the lead
    if ('co_app_verifiable_annual_income' in leads):
        leads['co_app_verifiable_annual_income']=str(leads['co_app_verifiable_annual_income'])
    if ('fico' in leads):
        leads['fico']=str(leads['fico'])
    if ('LTI' in leads):
        leads['LTI']=str(leads['LTI'])
    if ('xpn_bcc0300' in leads):
        leads['xpn_bcc0300']=str(leads['xpn_bcc0300'])
    if ('ndi_ratio' in leads):
        leads['ndi_ratio']=str(leads['ndi_ratio'])
    if ('final_loan_amount' in leads):
        leads['final_loan_amount']=str(leads['final_loan_amount'])
    leads.update(score)
    
    return_list = []
    return_list.append(leads)
    return return_list

def format_scores_returned(score):

    # convert everything to a dataframe
    score = pd.DataFrame.from_dict(score,orient='index').transpose() 
    
    # convert back to dictionary in list format
    score = score.to_dict('list')
    
    return score


run(lead)
main_leadlevel(lead)
score_collector_phase1(lead)
lead<jupyter_output><empty_output><jupyter_text>New Score  Load Data<jupyter_code># load data
df = pd.read_csv('full_data.csv')
df.head()
df.columns
lead = {"co_app_verifiable_annual_income":342,"loan_use":'Debt Consolidation', \
        "employment_status":'Full-time', "final_loan_amount":25000, "fico":700,\
        "lti":18.8, "xpn_bcc0300":42, "ndi_ratio":40, "utm_source":'Lending Tree'}<jupyter_output><empty_output><jupyter_text>Split Final DF and First DF<jupyter_code>final_var = ['SF_App_Id', 'Funding_Score', 'Funding_Model_Segment', 'NPV_Score',\
            'NPV_Model_Segment', 'Funding', 'NPV_Actual', 'co_app', 'loan_use__c',\
            'employment_status__c', 'amount_of_loan_requested__c', 'fico__c', \
            'c_LTI', 'bcc0300__c', 'ndi_ratio__c', 'utm_source__c']

first_var = ['SF_App_Id', 'Funding_Score', 'Funding_Model_Segment', 'NPV_Score',\
            'NPV_Model_Segment', 'Funding', 'NPV_Actual', 'first_coapp', 'first_loan_use',\
            'employment_status__c', 'first_amount_of_loan_requested', 'first_FICO', \
            'First_LTI', 'first_bcc0300__c', 'first_ndi_ratio__c', 'utm_source__c']
final_df = df[final_var]
first_df = df[first_var]
print(final_df.head())
print(first_df.head())<jupyter_output>SF_App_Id  Funding_Score  ...  ndi_ratio__c  utm_source__c
0  a010f00000Vy85MAAR   9.801852e-08  ...           NaN            NaN
1  a010f00000WN27oAAD   6.886856e-01  ...          62.3            115
2  a010f00000Tq9b3AAB   8.452948e-02  ...          29.0            115
3  a010f00000WSBY6AAP   5.279838e-01  ...          44.6            115
4  a010f00000WsXcoAAF   4.749825e-01  ...          28.1            115

[5 rows x 16 columns]
            SF_App_Id  Funding_Score  ...  first_ndi_ratio__c  utm_source__c
0  a010f00000Vy85MAAR   9.801852e-08  ...            0.441415            NaN
1  a010f00000WN27oAAD   6.886856e-01  ...            0.457451            115
2  a010f00000Tq9b3AAB   8.452948e-02  ...            0.290281            115
3  a010f00000WSBY6AAP   5.279838e-01  ...            0.446276            115
4  a010f00000WsXcoAAF   4.749825e-01  ...            0.281249            115

[5 rows x 16 columns]<jupyter_text>Transform Variables **These Metrics need to be multiplied by 100**c_LTIFirst_LTIfirst_ndi_ratio__c  Initial Transform<jupyter_code>final_df['c_LTI'] = final_df['c_LTI']*100
first_df['First_LTI'] = first_df['First_LTI']*100
first_df['first_ndi_ratio__c'] = first_df['first_ndi_ratio__c']*100
first_df.describe()
final_df.columns<jupyter_output><empty_output><jupyter_text>Apply Model Transformations<jupyter_code># Transform variables
final_df['co_app'] = final_df['co_app'].apply(get_co_app_cat)
final_df['loan_use'] = final_df['loan_use__c'].apply(get_loan_use_cat)
final_df['employment'] = final_df['employment_status__c'].apply(get_employment_cat)
final_df['loan_amount'] = final_df['amount_of_loan_requested__c'].apply(get_loan_amount_cat)
final_df['mkt_chan'] = final_df['utm_source__c'].apply(get_mkt_chan_cat)
final_df['ficox'] = final_df['fico__c'].apply(get_fico)
final_df['lti'] = final_df['c_LTI'].apply(get_lti)
final_df['bcc0300'] = final_df['bcc0300__c'].apply(get_bcc0300)
final_df['ndi'] = final_df['ndi_ratio__c'].apply(get_ndi_ratio)
final_df['ndisq'] = final_df['ndi'] * final_df['ndi']

# Transform variables
first_df['co_app'] = first_df['first_coapp'].apply(get_co_app_cat)
first_df['loan_use'] = first_df['first_loan_use'].apply(get_loan_use_cat)
first_df['employment'] = first_df['employment_status__c'].apply(get_employment_cat)
first_df['loan_amount'] = first_df['first_amount_of_loan_requested'].apply(get_loan_amount_cat)
first_df['mkt_chan'] = first_df['utm_source__c'].apply(get_mkt_chan_cat)
first_df['ficox'] = first_df['first_FICO'].apply(get_fico)
first_df['lti'] = first_df['First_LTI'].apply(get_lti)
first_df['bcc0300'] = first_df['first_bcc0300__c'].apply(get_bcc0300)
first_df['ndi'] = first_df['first_ndi_ratio__c'].apply(get_ndi_ratio)
first_df['ndisq'] = first_df['ndi'] * first_df['ndi']<jupyter_output>/usr/local/lib/python3.6/dist-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  """Entry point for launching an IPython kernel.
/usr/local/lib/python3.6/dist-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  
/usr/local/lib/python3.6/dist-packages/ipykernel_launcher.py:3: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/p[...]<jupyter_text>Dummy Variables  Final DF<jupyter_code>final_df.columns
# create dummies
cat_vars = ['co_app','loan_use','employment','loan_amount','mkt_chan']
for var in cat_vars:
    cat_list = pd.get_dummies(final_df[var], prefix=var)
    temp = final_df.join(cat_list)
    final_df = temp
data_vars = final_df.columns.values.tolist()
to_keep = [i for i in data_vars if i not in cat_vars]
final_df = final_df[to_keep]
final_df.columns
# re-index to have same columns as the model
final_df_indexed = final_df.reindex(columns = ['ficox', 'lti', 'bcc0300', 'ndi', 'ndisq', 'co_app_0', 'co_app_1',
                               'loan_use_1', 'loan_use_2', 'loan_use_3', 'loan_use_4', 'employment_1',
                               'employment_2', 'employment_3', 'employment_4', 'loan_amount_1',
                               'loan_amount_2', 'loan_amount_3', 'loan_amount_4', 'mkt_chan_1',
                               'mkt_chan_2', 'mkt_chan_3', 'mkt_chan_5', 'mkt_chan_6', 
                               'mkt_chan_7'], fill_value=0)
prob_prediction = fplus_ls_model.predict_proba(final_df_indexed)[0][1]
final_df['New_Funding_Score'] = fplus_ls_model.predict_proba(final_df_indexed)[:,1]
final_df['New_NPV_Score'] = final_df.apply(lambda row : get_npv_calc(row['amount_of_loan_requested__c'], 
                                  row['New_Funding_Score'], row['utm_source__c']), axis = 1)<jupyter_output>/usr/local/lib/python3.6/dist-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy<jupyter_text>First DF<jupyter_code>first_df.columns
# create dummies
cat_vars = ['co_app','loan_use','employment','loan_amount','mkt_chan']
for var in cat_vars:
    cat_list = pd.get_dummies(first_df[var], prefix=var)
    temp = first_df.join(cat_list)
    first_df = temp
data_vars = first_df.columns.values.tolist()
to_keep = [i for i in data_vars if i not in cat_vars]
first_df = first_df[to_keep]
first_df.columns
# re-index to have same columns as the model
first_df_indexed = first_df.reindex(columns = ['ficox', 'lti', 'bcc0300', 'ndi', 'ndisq', 'co_app_0', 'co_app_1',
                               'loan_use_1', 'loan_use_2', 'loan_use_3', 'loan_use_4', 'employment_1',
                               'employment_2', 'employment_3', 'employment_4', 'loan_amount_1',
                               'loan_amount_2', 'loan_amount_3', 'loan_amount_4', 'mkt_chan_1',
                               'mkt_chan_2', 'mkt_chan_3', 'mkt_chan_5', 'mkt_chan_6', 
                               'mkt_chan_7'], fill_value=0)<jupyter_output><empty_output><jupyter_text>Predict<jupyter_code>first_df['First_Funding_Score'] = fplus_ls_model.predict_proba(first_df_indexed)[:,1]
first_df['New_NPV_Score'] = first_df.apply(lambda row : get_npv_calc(row['first_amount_of_loan_requested'], 
                                  row['First_Funding_Score'], row['utm_source__c']), axis = 1) 
first_df.to_csv('first_df.csv', index=False)
final_df.to_csv('final_df.csv', index=False)
['SF_App_Id', 'Funding_Score', 'Funding_Model_Segment', 'NPV_Score',\
            'NPV_Model_Segment', 'Funding', 'NPV_Actual', 'co_app', 'loan_use__c',\
            'employment_status__c', 'amount_of_loan_requested__c', 'fico__c', \
            'c_LTI', 'bcc0300__c', 'ndi_ratio__c', 'utm_source__c']<jupyter_output><empty_output><jupyter_text>Austen 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',
]

credentials = pydata_google_auth.get_user_credentials(
    SCOPES,
    auth_local_webserver=False)
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

FROM `freedom-dw.salesforce_ffam.application__c` a 

WHERE createddate >= '2019-01-01'
"""
df1 = pandas_gbq.read_gbq(sql, project_id='ffn-dw-bigquery-prd', credentials=credentials, dialect='standard')

df = df1
final_df = df
final_df.head()
# Transform variables
final_df['co_app'] = final_df['co_app_verifiable_annual_income__c'].apply(get_co_app_cat)
final_df['loan_use'] = final_df['loan_use__c'].apply(get_loan_use_cat)
final_df['employment'] = final_df['employment_status__c'].apply(get_employment_cat)
final_df['loan_amount'] = final_df['amount_of_loan_requested__c'].apply(get_loan_amount_cat)
final_df['mkt_chan'] = final_df['utm_source__c'].apply(get_mkt_chan_cat)
final_df['ficox'] = final_df['fico__c'].apply(get_fico)
final_df['lti'] = final_df['lti__c'].apply(get_lti)
final_df['bcc0300'] = final_df['bcc0300__c'].apply(get_bcc0300)
final_df['ndi'] = final_df['ndi_ratio__c'].apply(get_ndi_ratio)
final_df['ndisq'] = final_df['ndi'] * final_df['ndi']
# create dummies
cat_vars = ['co_app','loan_use','employment','loan_amount','mkt_chan']
for var in cat_vars:
    cat_list = pd.get_dummies(final_df[var], prefix=var)
    temp = final_df.join(cat_list)
    final_df = temp
data_vars = final_df.columns.values.tolist()
to_keep = [i for i in data_vars if i not in cat_vars]
final_df = final_df[to_keep]
final_df.info()
# re-index to have same columns as the model
final_df_indexed = final_df.reindex(columns = ['ficox', 'lti', 'bcc0300', 'ndi', 'ndisq', 'co_app_0', 'co_app_1',
                               'loan_use_1', 'loan_use_2', 'loan_use_3', 'loan_use_4', 'employment_1',
                               'employment_2', 'employment_3', 'employment_4', 'loan_amount_1',
                               'loan_amount_2', 'loan_amount_3', 'loan_amount_4', 'mkt_chan_1',
                               'mkt_chan_2', 'mkt_chan_3', 'mkt_chan_5', 'mkt_chan_6', 
                               'mkt_chan_7'], fill_value=0)
final_df_indexed.info()
prob_prediction = fplus_ls_model.predict_proba(final_df_indexed)[0][1]
final_df['New_Funding_Score'] = fplus_ls_model.predict_proba(final_df_indexed)[:,1]
final_df['New_NPV_Score'] = final_df.apply(lambda row : get_npv_calc(row['amount_of_loan_requested__c'], 
                                  row['New_Funding_Score'], row['utm_source__c']), axis = 1) 
df = final_df[['id', 'amount_of_loan_requested__c', 'utm_source__c', 'New_Funding_Score', 'New_NPV_Score']]
df.head()
df.info()
sql_1 = """
SELECT 
application_key

FROM `ffam-data-platform.standardized_data.fplus_application` 

WHERE 

new_lead_datetime  >= '2019-01-01'
AND flag_eligible_lead = TRUE
AND lead_type = 'Web'
AND latest_prequal_decision <> 'DECLINED'
"""
good_leads = pandas_gbq.read_gbq(sql_1, project_id='ffn-dw-bigquery-prd', credentials=credentials, dialect='standard')
good_leads.info()
good_leads1 = good_leads.merge(df, left_on='application_key', right_on='id')
good_leads1 = good_leads1.drop(columns=['application_key'])
good_leads1.info()
good_leads2 = good_leads1.drop_duplicates(subset=['id'], keep='first')
good_leads2.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 697536 entries, 0 to 697535
Data columns (total 5 columns):
id                             697536 non-null object
amount_of_loan_requested__c    697536 non-null float64
utm_source__c                  697536 non-null object
New_Funding_Score              697536 non-null float64
New_NPV_Score                  697536 non-null float64
dtypes: float64(3), object(2)
memory usage: 31.9+ MB<jupyter_text>Send Back to GBQ<jupyter_code>#set destination table to insert data into
destinationtable = 'Jason.Austen_NPV_Fplus'
project_id='ffn-dw-bigquery-prd'

#send data to GBQ (pandas_gqb.to_gbq)
#https://pandas-gbq.readthedocs.io/en/latest/api.html#pandas_gbq.to_gbq
#params are: dataframe, destination_table, project_id, if_exists (append), and table_schema (list of dicts, name and type)
pandas_gbq.to_gbq(dataframe = good_leads1,
                  destination_table = destinationtable,
                  project_id = project_id,
                  if_exists = 'append',
                  table_schema = [{'name':'id',
                                   'type':'STRING'},
                                  {'name':'amount_of_loan_requested__c',
                                   'type':'FLOAT'},
                                  {'name':'utm_source__c',
                                   'type':'STRING'},
                                  {'name':'New_Funding_Score',
                                   'type':'FLOAT'},
                                  {'name':'New_NPV_Score',
                                   'type':'FLOAT'}])<jupyter_output>0it [00:00, ?it/s][A[A[A


1it [00:06,  6.88s/it][A[A[A


[A[A[A<jupyter_text>Shit for Aleks<jupyter_code># load data
import pandas as pd
df = pd.read_csv('Retrain_Fplus.csv')
df.head()
df1 = df.dropna()
df1.info()

#set destination table to insert data into
destinationtable = 'Jason.retrain_fplus'
project_id='ffn-dw-bigquery-prd'

#send data to GBQ (pandas_gqb.to_gbq)
#https://pandas-gbq.readthedocs.io/en/latest/api.html#pandas_gbq.to_gbq
#params are: dataframe, destination_table, project_id, if_exists (append), and table_schema (list of dicts, name and type)
pandas_gbq.to_gbq(dataframe = df1,
                  destination_table = destinationtable,
                  project_id = project_id,
                  if_exists = 'append',
                  table_schema = [
{'name':'SF_App_Id', 'type':'STRING'},
{'name':'Funding', 'type':'INTEGER'},
{'name':'first_coapp', 'type':'INTEGER'},
{'name':'first_loan_use', 'type':'STRING'},
{'name':'employment_status__c', 'type':'STRING'},
{'name':'first_amount_of_loan_requested', 'type':'INTEGER'},
{'name':'first_FICO', 'type':'INTEGER'},
{'name':'First_LTI', 'type':'FLOAT'},
{'name':'first_bcc0300__c', 'type':'INTEGER'},
{'name':'first_ndi_ratio__c', 'type':'FLOAT'},
{'name':'utm_source__c', 'type':'INTEGER'},
{'name':'ficox', 'type':'INTEGER'},
{'name':'lti', 'type':'INTEGER'},
{'name':'bcc0300', 'type':'INTEGER'},
{'name':'ndi', 'type':'INTEGER'},
{'name':'ndisq', 'type':'INTEGER'},
{'name':'co_app_0', 'type':'INTEGER'},
{'name':'co_app_1', 'type':'INTEGER'},
{'name':'loan_use_1', 'type':'INTEGER'},
{'name':'loan_use_2', 'type':'INTEGER'},
{'name':'loan_use_3', 'type':'INTEGER'},
{'name':'loan_use_4', 'type':'INTEGER'},
{'name':'employment_1', 'type':'INTEGER'},
{'name':'employment_2', 'type':'INTEGER'},
{'name':'employment_3', 'type':'INTEGER'},
{'name':'employment_4', 'type':'INTEGER'},
{'name':'loan_amount_1', 'type':'INTEGER'},
{'name':'loan_amount_2', 'type':'INTEGER'},
{'name':'loan_amount_3', 'type':'INTEGER'},
{'name':'loan_amount_4', 'type':'INTEGER'},
{'name':'mkt_chan_1', 'type':'INTEGER'},
{'name':'mkt_chan_2', 'type':'INTEGER'},
{'name':'mkt_chan_3', 'type':'INTEGER'},
{'name':'mkt_chan_5', 'type':'INTEGER'},
{'name':'mkt_chan_6', 'type':'INTEGER'},
{'name':'mkt_chan_7', 'type':'INTEGER'}])
#set destination table to insert data into
destinationtable = 'Jason.retrain'
project_id='ffn-dw-bigquery-prd'

#send data to GBQ (pandas_gqb.to_gbq)
#https://pandas-gbq.readthedocs.io/en/latest/api.html#pandas_gbq.to_gbq
#params are: dataframe, destination_table, project_id, if_exists (append), and table_schema (list of dicts, name and type)
pandas_gbq.to_gbq(dataframe = df1,
                  destination_table = destinationtable,
                  project_id = project_id,
                  if_exists = 'append',
                  table_schema = [
{'name':'SF_App_Id', 'type':'STRING'},
{'name':'Funding', 'type':'INTEGER'},
{'name':'first_coapp', 'type':'INTEGER'},
{'name':'first_loan_use', 'type':'STRING'},
{'name':'employment_status__c', 'type':'STRING'},
{'name':'first_amount_of_loan_requested', 'type':'INTEGER'},
{'name':'first_FICO', 'type':'INTEGER'},
{'name':'First_LTI', 'type':'FLOAT'},
{'name':'first_bcc0300__c', 'type':'INTEGER'},
{'name':'first_ndi_ratio__c', 'type':'FLOAT'},
{'name':'utm_source__c', 'type':'INTEGER'},
{'name':'ficox', 'type':'INTEGER'},
{'name':'lti', 'type':'INTEGER'},
{'name':'bcc0300', 'type':'INTEGER'},
{'name':'ndi', 'type':'INTEGER'},
{'name':'ndisq', 'type':'INTEGER'},
{'name':'co_app_0', 'type':'INTEGER'},
{'name':'co_app_1', 'type':'INTEGER'},
{'name':'loan_use_1', 'type':'INTEGER'},
{'name':'loan_use_2', 'type':'INTEGER'},
{'name':'loan_use_3', 'type':'INTEGER'},
{'name':'loan_use_4', 'type':'INTEGER'},
{'name':'employment_1', 'type':'INTEGER'},
{'name':'employment_2', 'type':'INTEGER'},
{'name':'employment_3', 'type':'INTEGER'},
{'name':'employment_4', 'type':'INTEGER'},
{'name':'loan_amount_1', 'type':'INTEGER'},
{'name':'loan_amount_2', 'type':'INTEGER'},
{'name':'loan_amount_3', 'type':'INTEGER'},
{'name':'loan_amount_4', 'type':'INTEGER'},
{'name':'mkt_chan_1', 'type':'INTEGER'},
{'name':'mkt_chan_2', 'type':'INTEGER'},
{'name':'mkt_chan_3', 'type':'INTEGER'},
{'name':'mkt_chan_5', 'type':'INTEGER'},
{'name':'mkt_chan_6', 'type':'INTEGER'},
{'name':'mkt_chan_7', 'type':'INTEGER'}])<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup