<jupyter_start><jupyter_text>Team Lead Scorecard  9 Key Metrics * Co App %  * Full App Submit Rate  * Full App Approved %  * Pull Through Rate  * 24 Hour Fund Rate  * Fund Rate  * Loans Funded* ACH %  * Average Effort Score   Total Score Equation $$ Total Score = (Co App)w_1 + (FAS\%)w_2 + (FAA\%)w_3 + (PTR)w_4 + (24hr Fund) w_5 + (Fund Rate)w_6 + (\Funded)w_7 + (ACH\%)w_8 + (Effort)w_9 $$    Weights ![weights](https://aisportsfirm.com/wp-content/uploads/2019/05/image-5.png) * Fund Rate  = 20  * Loans Funded  = 15  * 24 Hour Fund Rate  = 5  * Co App %  = 10  * Full App Approved %  = 10  * Pull Through Rate  = 10  * ACH %  = 5  * Average Effort Score  = 10  * Full App Submit Rate  = 15  **Total = 100  ** **Baseline Score = 50**    Programming  Load Libraries<jupyter_code>from functools import reduce
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>Functions<jupyter_code># function to get dataframe names
def get_df_name(df):
    name =[x for x in globals() if globals()[x] is df][0]
    return name<jupyter_output><empty_output><jupyter_text>Load Data<jupyter_code>Dec = pd.read_csv("Dec_LC_Performance_data.csv")
Jan = pd.read_csv("Jan_LC_Performance_data.csv")
Feb = pd.read_csv("Feb_LC_Performance_data.csv")
Mar = pd.read_csv("Mar_LC_Performance_data.csv")
Apr = pd.read_csv("Apr_LC_Performance_data.csv")
# load data files
oneDayFundRate = pd.read_csv("https://raw.githubusercontent.com/papagorgio23/Northwestern/master/422%20-%20Practical%20Machine%20Learning/Week1/24fundRate.csv")
ACH = pd.read_csv("https://raw.githubusercontent.com/papagorgio23/Northwestern/master/422%20-%20Practical%20Machine%20Learning/Week1/ACH.csv")
Effort = pd.read_csv("https://raw.githubusercontent.com/papagorgio23/Northwestern/master/422%20-%20Practical%20Machine%20Learning/Week1/Avg%20Effort.csv")
CoApp = pd.read_csv("https://raw.githubusercontent.com/papagorgio23/Northwestern/master/422%20-%20Practical%20Machine%20Learning/Week1/CoApp.csv")
FAS = pd.read_csv("https://raw.githubusercontent.com/papagorgio23/Northwestern/master/422%20-%20Practical%20Machine%20Learning/Week1/FAS%25.csv")
FullApprove = pd.read_csv("https://raw.githubusercontent.com/papagorgio23/Northwestern/master/422%20-%20Practical%20Machine%20Learning/Week1/Full%20App%20Approved.csv")
FundRate = pd.read_csv("https://raw.githubusercontent.com/papagorgio23/Northwestern/master/422%20-%20Practical%20Machine%20Learning/Week1/Fund%20Rate.csv")
LoansFunded = pd.read_csv("https://raw.githubusercontent.com/papagorgio23/Northwestern/master/422%20-%20Practical%20Machine%20Learning/Week1/Loans%20Funded.csv")
PullThrough = pd.read_csv("https://raw.githubusercontent.com/papagorgio23/Northwestern/master/422%20-%20Practical%20Machine%20Learning/Week1/Pull%20Through%20Rate.csv")<jupyter_output><empty_output><jupyter_text>Check Data  Loop Files<jupyter_code># test to see if it works
get_df_name(Dec)
# put all the datafiles into one object (list)
files = [Dec, Jan, Feb , Mar, Apr]
for x in files:
  objectName = get_df_name(x)
  details = x.shape
  print("Dataframe name is {}. There are {} number of observations with {} columns in this dataframe.\n".format(objectName, details[0], details[1]))
for x in files:
  objectName = get_df_name(x)
  print("\n File Name = {} \n".format(objectName), x.columns, "\n")
for x in files:
  objectName = get_df_name(x)
  print("\n File Name = {} \n \n".format(objectName), x.head(), "\n")
for x in files:
  objectName = get_df_name(x)
  details = x.shape
  print("{}: {} columns with {} rows".format(objectName, details[1], details[0]))<jupyter_output>Dec: 6 columns with 330 rows
Jan: 6 columns with 330 rows
Feb: 6 columns with 330 rows
Mar: 6 columns with 330 rows
Apr: 6 columns with 330 rows<jupyter_text>Join Data There's always a faster way with programming...<jupyter_code># import function to help speed up the process
from functools import reduce

# combine ALL the datasets
full_data = reduce(lambda left, right: pd.merge(left, right, on= ['Loan Officer Name', 'Month of effective_date']), files)

# view data
full_data.head()
full_data.shape<jupyter_output><empty_output><jupyter_text>EDA (Exploratory Data Analysis)<jupyter_code>full_data.info()
# Check NA
full_data.isna().sum()
full_data
# check null values
full_data[full_data['24 Hour Fund Rate'].isnull()]<jupyter_output><empty_output><jupyter_text>Drop NAs<jupyter_code># drop nas
full_data = full_data.dropna()
# check data again
full_data.info()
# get summary statistics of each variable
full_data.describe()<jupyter_output><empty_output><jupyter_text>A few things stick out to me: * Medians and Mean are very different between a few of the variables (Signal of outliers)* Effort Score of 0 seems wrong* 24 Hour Fund rate over 1<jupyter_code># Look at Effort Score of 0
full_data[full_data['Avg Effort Score'] == 0]
# get rid of Bryan's December scores
full_data = full_data[full_data['Avg Effort Score'] != 0]
full_data.describe()
# Look at 24hr fund rate over 1
full_data[full_data['24 Hour Fund Rate'] > 1]<jupyter_output><empty_output><jupyter_text>Individual Analysis<jupyter_code># drop the column from the dataframe
oneDayFundRate = oneDayFundRate.dropna()
ACH = ACH.dropna()
Effort = Effort.dropna()
CoApp = CoApp.dropna()
FAS = FAS.dropna()
FullApprove = FullApprove.dropna()
FundRate = FundRate.dropna()
LoansFunded = LoansFunded.dropna()
PullThrough = PullThrough.dropna()<jupyter_output><empty_output><jupyter_text>24 Hr Fund Rate<jupyter_code>oneDayFundRate.describe()<jupyter_output><empty_output><jupyter_text>**Visualize 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(oneDayFundRate['24 Hour Fund Rate'], ax=ax_box)
sns.distplot(oneDayFundRate['24 Hour Fund Rate'], ax=ax_hist)

# Remove x axis name for the boxplot
#ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>**Eliminate Outliers**Anything over 60%<jupyter_code>oneDayFundRate = oneDayFundRate[oneDayFundRate['24 Hour Fund Rate'] < 0.6]
# 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(oneDayFundRate['24 Hour Fund Rate'], ax=ax_box)
sns.distplot(oneDayFundRate['24 Hour Fund Rate'], ax=ax_hist)
 
# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>ACH<jupyter_code>ACH.describe()<jupyter_output><empty_output><jupyter_text>**Visualize 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.distplot(ACH['ACH %'], ax=ax_hist)
sns.boxplot(ACH['ACH %'], ax=ax_box)

# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>**Eliminate Outliers**<jupyter_code>ACH = ACH[ACH['ACH %'] >= 0.6]
# 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.distplot(ACH['ACH %'], ax=ax_hist)
sns.boxplot(ACH['ACH %'], ax=ax_box)

# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>Effort<jupyter_code>Effort.describe()<jupyter_output><empty_output><jupyter_text>**Visualize 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(Effort['Avg Effort Score'], ax=ax_box)
sns.distplot(Effort['Avg Effort Score'], ax=ax_hist)
 
# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>**Eliminate Outliers**<jupyter_code>Effort = Effort[(Effort['Avg Effort Score'] > 150) & (Effort['Avg Effort Score'] <= 450)]
# 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(Effort['Avg Effort Score'], ax=ax_box)
sns.distplot(Effort['Avg Effort Score'], ax=ax_hist)
 
# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>CoApp<jupyter_code>CoApp.describe()<jupyter_output><empty_output><jupyter_text>**Visualize 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(CoApp['Co-App %'], ax=ax_box)
sns.distplot(CoApp['Co-App %'], ax=ax_hist)
 
# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>**Eliminate Outliers**<jupyter_code>CoApp = CoApp[(CoApp['Co-App %'] >= 0.2) & (CoApp['Co-App %'] <= 0.5)]
# 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(CoApp['Co-App %'], ax=ax_box)
sns.distplot(CoApp['Co-App %'], ax=ax_hist)
 
# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>FAS<jupyter_code>FAS.describe()<jupyter_output><empty_output><jupyter_text>**Visualize 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(FAS['FAS%'], ax=ax_box)
sns.distplot(FAS['FAS%'], ax=ax_hist)
 
# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>**Eliminate Extreme Outliers**<jupyter_code>FAS = FAS[(FAS['FAS%'] > 0) & (FAS['FAS%'] < 0.75)]
# 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(FAS['FAS%'], ax=ax_box)
sns.distplot(FAS['FAS%'], ax=ax_hist)
 
# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>**Eliminate Outliers**<jupyter_code>FAS = FAS[(FAS['FAS%'] >= 0.15) & (FAS['FAS%'] < 0.39)]
# 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(FAS['FAS%'], ax=ax_box)
sns.distplot(FAS['FAS%'], ax=ax_hist)
 
# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>FullApprove<jupyter_code>FullApprove.describe()<jupyter_output><empty_output><jupyter_text>**Visualize 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(FullApprove['Full App Approved %'], ax=ax_box)
sns.distplot(FullApprove['Full App Approved %'], ax=ax_hist)
 
# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>**Eliminate Extreme Outliers**<jupyter_code>FullApprove = FullApprove[(FullApprove['Full App Approved %'] > 0) & (FullApprove['Full App Approved %'] < 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(FullApprove['Full App Approved %'], ax=ax_box)
sns.distplot(FullApprove['Full App Approved %'], ax=ax_hist)
 
# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>**Eliminate Outliers**<jupyter_code>FullApprove = FullApprove[(FullApprove['Full App Approved %'] >= 0.41) & (FullApprove['Full App Approved %'] <= 0.81)]
# 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(FullApprove['Full App Approved %'], ax=ax_box)
sns.distplot(FullApprove['Full App Approved %'], ax=ax_hist)
 
# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>FundRate<jupyter_code>FundRate.describe()<jupyter_output><empty_output><jupyter_text>**Visualize 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(FundRate['Fund Rate'], ax=ax_box)
sns.distplot(FundRate['Fund Rate'], ax=ax_hist)
 
# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>**Eliminate Outliers**<jupyter_code>FundRate = FundRate[(FundRate['Fund Rate'] > 0.02) & (FundRate['Fund Rate'] < 0.19)]
# 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(FundRate['Fund Rate'], ax=ax_box)
sns.distplot(FundRate['Fund Rate'], ax=ax_hist)
 
# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>LoansFunded<jupyter_code>LoansFunded.describe()<jupyter_output><empty_output><jupyter_text>**Visualize 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(LoansFunded['Applications Funded'], ax=ax_box)
sns.distplot(LoansFunded['Applications Funded'], ax=ax_hist)
 
# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>**Eliminate Outliers**<jupyter_code>LoansFunded = LoansFunded[(LoansFunded['Applications Funded'] > 10) & (LoansFunded['Applications Funded'] <= 50)]
# 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(LoansFunded['Applications Funded'], ax=ax_box)
sns.distplot(LoansFunded['Applications Funded'], ax=ax_hist)
 
# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>PullThrough<jupyter_code>PullThrough.describe()<jupyter_output><empty_output><jupyter_text>**Visualize 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(PullThrough['Pull Through Rate'], ax=ax_box)
sns.distplot(PullThrough['Pull Through Rate'], ax=ax_hist)
 
# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>**Eliminate Outliers**<jupyter_code>PullThrough = PullThrough[(PullThrough['Pull Through Rate'] >= 0.23) & (PullThrough['Pull Through Rate'] < 0.75)]
# 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(PullThrough['Pull Through Rate'], ax=ax_box)
sns.distplot(PullThrough['Pull Through Rate'], ax=ax_hist)
 
# Remove x axis name for the boxplot
ax_box.set(xlabel='')<jupyter_output><empty_output><jupyter_text>Final Metrics<jupyter_code># put all the updated datafiles into object (list)
files1 = [oneDayFundRate, ACH, Effort , CoApp, FAS, FullApprove, FundRate, LoansFunded, PullThrough]

# combine ALL the datasets
#final_data = reduce(lambda left, right: pd.merge(left, right, on= ['Loan Officer Name', 'Month of effective_date']), files)

# since the datasets are different sizes we need to designate this as an outer join
final_data = reduce(lambda left, right: pd.merge(left, right, on= ['Loan Officer Name', 'Month of effective_date'], how = 'outer'), files1)<jupyter_output><empty_output><jupyter_text>**Calculate Metrics**<jupyter_code># calculate median
final_metrics = pd.DataFrame(final_data.select_dtypes(exclude=['object']).apply(np.nanmedian, axis=0))
final_metrics.columns = ['Median']

# calculate standard deviation
final_metrics['Stdev'] = final_data.select_dtypes(exclude=['object']).apply(np.nanstd, axis=0)

# get lower bound and upper bounds for each metric (2 times the standard deviation)
final_metrics['LowerBound'] = final_metrics['Median'] - (final_metrics['Stdev'] * 2)
final_metrics['UpperBound'] = final_metrics['Median'] + (final_metrics['Stdev'] * 2)

# view data
final_metrics<jupyter_output><empty_output><jupyter_text>Compare to data without filtering<jupyter_code># calculate median
final_metrics1 = pd.DataFrame(full_data.select_dtypes(exclude=['object']).apply(np.nanmedian, axis=0))
final_metrics1.columns = ['Median']

# calculate standard deviation
final_metrics1['Stdev'] = full_data.select_dtypes(exclude=['object']).apply(np.nanstd, axis=0)

# get lower bound and upper bounds for each metric (2 times the standard deviation)
final_metrics1['LowerBound'] = final_metrics1['Median'] - (final_metrics1['Stdev'] * 2)
final_metrics1['UpperBound'] = final_metrics1['Median'] + (final_metrics1['Stdev'] * 2)

# view data
final_metrics1<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1480824912840513, 'fraction_numerical': 0.025527388760858003, 'mean_word_length': 6.926932084309134, 'pattern_counts': {'":': 20, '<': 174, '<?xml version=': 0, '>': 175, 'https://': 10, 'lorem ipsum': 0, 'www.': 0, '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': '41', 'dir': 'jupyter-structured-clean-dedup', 'id': '378286', 'license': 'MIT', 'n_tokens_mistral': 6878, 'n_tokens_neox': 6348, 'n_words': 1366, 'path': 'Farmer_TL_Scorecard.ipynb', 'repo_name': 'papagorgio23/Python101', 'size': '16923'}