c— title: “Indirect PTI Markdown” output: html_document —
library(ggplot2)
# The transformation factor
transf_fact <- max(p$total)/max(p$BR)
# Plot
ggplot(data = p,
mapping = aes(x = rownames(p),
y = total)) +
geom_col(fill = 'dodgerblue') +
# Apply the factor on values appearing on second OY axis
geom_line(aes(y = transf_fact * BR), group = 1) +
# Add second OY axis; note the transformation back (division)
scale_y_continuous(sec.axis = sec_axis(trans = ~ . / transf_fact,
name = "Bad Rate")) +
geom_label(aes(y = transf_fact * BR,
label = round(BR, 2))) +
theme_bw() +
theme(axis.text.x = element_text(angle = 20, hjust = 1)) +
ggtitle("Combinied PTI Interval Bad Rate and Population Density") +
labs(subtitle = "Population: Apr 2017 - Mar 2018 Funded Indirect Auto", caption = "Bad Definition: 90+ Over MOB 24") +
ylab("Total # Funded Accts") +
xlab("Payment to Income (%)")
library(ggplot2)
# The transformation factor
transf_fact <- max(pc$total)/max(pc$BR)
# Plot
ggplot(data = pc,
mapping = aes(x = rownames(pc),
y = total)) +
geom_col(fill = 'midnightblue') +
# Apply the factor on values appearing on second OY axis
geom_line(aes(y = transf_fact * BR), group = 1) +
# Add second OY axis; note the transformation back (division)
scale_y_continuous(sec.axis = sec_axis(trans = ~ . / transf_fact,
name = "Bad Rate")) +
geom_label(aes(y = transf_fact * BR,
label = round(BR, 2))) +
theme_bw() +
theme(axis.text.x = element_text(angle = 20, hjust = 1)) +
ggtitle("Individual PTI Interval Bad Rate and Population Density") +
labs(subtitle = "Population: Apr 2017 - Mar 2018 Funded Indirect Auto", caption = "Bad Definition: 90+ Over MOB 24") +
ylab("Total # Funded Accts") +
xlab("Payment to Income (%)")
ggplot(p_br, aes(pti, group =1)) +
geom_line(aes(y = as.numeric(as.character(`BR_Combined`)), color = "Combined")) +
geom_line(aes(y = as.numeric(as.character(`BR_Co`)), color = "Individual")) +
theme_bw() +
theme(axis.text.x = element_text(angle = 20, hjust = 1), legend.title=element_blank()) +
coord_cartesian(ylim = c(0, 40)) +
ggtitle("Combined vs Individual PTI Interval Bad Rate") +
labs(subtitle = "Population: Apr 2017 - Mar 2018 Funded Direct Auto", caption = "Bad Definition: 90+ Over MOB 24") +
ylab("Bad Rate") +
xlab("Payment to Income (%)") ## Warning: Removed 1 rows containing missing values (geom_path).
## f = Full
f = as.data.frame.matrix(table(t9$PTI_grp, t9$bad90))
f$total <- f$`0`+f$`1`
f$BR <- (f$`1`/ (f$`0`+f$`1`))*100
transf_fact <- max(f$total)/max(f$BR)
ggplot(data = f,
mapping = aes(x = rownames(f),
y = total)) +
geom_col(fill = 'darkcyan') +
# Apply the factor on values appearing on second OY axis
geom_line(aes(y = transf_fact * BR), group = 1) +
# Add second OY axis; note the transformation back (division)
scale_y_continuous(sec.axis = sec_axis(trans = ~ . / transf_fact,
name = "Bad Rate")) +
geom_label(aes(y = transf_fact * BR,
label = round(BR, 2))) +
theme_bw() +
theme(axis.text.x = element_text(angle = 15, hjust = 1)) +
ggtitle("Combined PTI Interval Bad Rate and Population Density") +
labs(subtitle = "Population: Apr 2017 - Mar 2018 Funded Indirect Auto", caption = "Bad Definition: 90+ Over MOB 24") +
ylab("Total # Funded Accts") +
xlab("Payment to Income (%)")
t9$pti_bin <- evenbins(t9$Combined.PTI.Funded)
#t9$pti_bin <- evenbins(t9$pti_individual)
pb = as.data.frame.matrix(table(t9$pti_bin,t9$bad90))
pb$BR <- (pb$`1`/ (pb$`0`+pb$`1`))*100
ggplot(data=pb, aes(x=as.numeric(rownames(pb)), y=BR, group=1)) +
geom_line()+
geom_point()+
scale_x_continuous(breaks = round(seq(min(as.numeric(rownames(pb))), max(as.numeric(rownames(pb))), by = 1),1)) +
#scale_y_continuous(breaks = round(seq(min(pb$BR), max(pb$BR), by = 1),1)) +
scale_y_continuous(breaks = round(seq(-1, 10, by = 1),1)) +
ggtitle("Combined PTI Interval Bad Rate by Twentile") +
labs(subtitle = "Population: Apr 2017 - Mar 2018 Funded Indirect Auto", caption = "Bad Definition: 90+ Over MOB 24") +
ylab("# Acct % Bad") +
xlab("PTI Twentile") +
geom_text(aes(label= round(BR, 2), vjust = -1.5)) +
geom_smooth(method = "lm")
t9$dti_bin <- evenbins(t9$App.DTI.With.New.Loan.Less.Trade.In.Funded)
db = as.data.frame.matrix(table(t9$dti_bin,t9$bad90))
db$BR <- (db$`1`/ (db$`0`+db$`1`))*100
ggplot(data=db, aes(x=as.numeric(rownames(db)), y=BR, group=1)) +
geom_line()+
geom_point()+
scale_x_continuous(breaks = round(seq(min(as.numeric(rownames(db))), max(as.numeric(rownames(db))), by = 1),1)) +
#scale_y_continuous(breaks = round(seq(min(pb$BR), max(pb$BR), by = 1),1)) +
scale_y_continuous(breaks = round(seq(-1, 10, by = 1),1)) +
ggtitle("Combined DTI Interval Bad Rate by Twentile") +
labs(subtitle = "Population: Apr 2017 - Mar 2018 Funded Indirect Auto", caption = "Bad Definition: 90+ Over MOB 24") +
ylab("# Acct % Bad") +
xlab("DTI Twentile") +
geom_text(aes(label= round(BR, 2), vjust = -1.5)) +
geom_smooth(method = "lm")
f800 = as.data.frame.matrix(table(subset(t9, t9$FICO_grp=="800+")$PTI_grp,
subset(t9, t9$FICO_grp=="800+")$bad90))
f800$BR <- (f800$`1`/ (f800$`0`+f800$`1`))*100
f800$total <- f800$`0`+f800$`1`
f800$cumBR <- 100*cumsum(f800$`1`)/cumsum(f800$total)
f740 = as.data.frame.matrix(table(subset(t9, t9$FICO_grp=="740-799")$PTI_grp,
subset(t9, t9$FICO_grp=="740-799")$bad90))
f740$BR <- (f740$`1`/ (f740$`0`+f740$`1`))*100
f740$total <- f740$`0`+f740$`1`
f740$cumBR <- 100*cumsum(f740$`1`)/cumsum(f740$total)
f740p = as.data.frame.matrix(table(subset(t9, t9$FICO_grp=="740-799" | t9$FICO_grp=="800+")$PTI_grp,
subset(t9, t9$FICO_grp=="740-799" | t9$FICO_grp=="800+")$bad90))
f740p$BR <- (f740p$`1`/ (f740p$`0`+f740p$`1`))*100
f740p$total <- f740p$`0`+f740p$`1`
f740p$cumBR <- 100*cumsum(f740p$`1`)/cumsum(f740p$total)
f700 = as.data.frame.matrix(table(subset(t9, t9$FICO_grp=="700-739")$PTI_grp,
subset(t9, t9$FICO_grp=="700-739")$bad90))
f700$BR <- (f700$`1`/ (f700$`0`+f700$`1`))*100
f700$total <- f700$`0`+f700$`1`
f700$cumBR <- 100*cumsum(f700$`1`)/cumsum(f700$total)
f675 = as.data.frame.matrix(table(subset(t9, t9$FICO_grp=="675-699")$PTI_grp,
subset(t9, t9$FICO_grp=="675-699")$bad90))
f675$BR <- (f675$`1`/ (f675$`0`+f675$`1`))*100
f675$total <- f675$`0`+f675$`1`
f675$cumBR <- 100*cumsum(f675$`1`)/cumsum(f675$total)
f650 = as.data.frame.matrix(table(subset(t9, t9$FICO_grp=="650-674")$PTI_grp,
subset(t9, t9$FICO_grp=="650-674")$bad90))
f650$BR <- (f650$`1`/ (f650$`0`+f650$`1`))*100
f650$total <- f650$`0`+f650$`1`
f650$cumBR <- 100*cumsum(f650$`1`)/cumsum(f650$total)
f650lt = as.data.frame.matrix(table(subset(t9, t9$FICO_grp=="650-674" | t9$FICO_grp=="675-699" | t9$FICO_grp=="700-739")$PTI_grp,
subset(t9, t9$FICO_grp=="650-674" | t9$FICO_grp=="675-699" | t9$FICO_grp=="700-739")$bad90))
f650lt$BR <- (f650lt$`1`/ (f650lt$`0`+f650lt$`1`))*100
f650lt$total <- f650lt$`0`+f650lt$`1`
f650lt$cumBR <- 100*cumsum(f650lt$`1`)/cumsum(f650lt$total)pti_fico <- as.data.frame(cbind(rownames(f800), as.numeric(f800$BR), as.numeric(f740$BR), as.numeric(f700$BR), as.numeric(f675$BR), as.numeric(f650$BR)))## Warning in cbind(rownames(f800), as.numeric(f800$BR),
## as.numeric(f740$BR), : number of rows of result is not a multiple of vector
## length (arg 1)colnames(pti_fico) = c("PTI", "800+","740-799","700-739","675-699","650-674")
# pti_fico$`650-674` <- NULL
# library(reshape2)
#
# pti_fico_2 <- melt(pti_fico, id="PTI")
#
# ggplot(data=pti_fico_2,
# aes(x=PTI, y=as.numeric(value), colour=variable, group =1)) +
# # scale_y_continuous(breaks = round(0, 25, by = 1),1)) +
# geom_line()
ggplot(pti_fico, aes(PTI, group =1)) +
geom_line(aes(y = as.numeric(as.character(`800+`)), color = "800+")) +
geom_line(aes(y = as.numeric(as.character(`740-799`)), color = "740-799")) +
geom_line(aes(y = as.numeric(as.character(`700-739`)), color = "700-739")) +
geom_line(aes(y = as.numeric(as.character(`675-699`)), color = "675-699")) +
geom_line(aes(y = as.numeric(as.character(`650-674`)), color = "650-674")) +
theme_bw() +
theme(axis.text.x = element_text(angle = 20, hjust = 1), legend.title=element_blank()) +
coord_cartesian(ylim = c(0, 13)) +
ggtitle("PTI Interval Bad Rate by FICO Band") +
labs(subtitle = "Population: 2017 - Jan 2018 Funded Direct Auto", caption = "Bad Definition: 90+ Over MOB 24") +
ylab("Bad Rate") +
xlab("Payment to Income (%)") 
# The transformation factor
transf_fact <- max(f800$total)/max(f800$BR)
# Plot
ggplot(data = f800,
mapping = aes(x = rownames(f800),
y = total)) +
geom_col(fill = 'steelblue4') +
# Apply the factor on values appearing on second OY axis
geom_line(aes(y = transf_fact * BR), group = 1) +
# Add second OY axis; note the transformation back (division)
scale_y_continuous(sec.axis = sec_axis(trans = ~ . / transf_fact,
name = "Bad Rate")) +
geom_label(aes(y = transf_fact * BR,
label = round(BR, 2))) +
theme_bw() +
theme(axis.text.x = element_text(angle = 20, hjust = 1)) +
ggtitle("800+ PTI Interval Bad Rate and Population Density") +
labs(subtitle = "Population: Apr 2017 - Mar 2018 Funded Indirect Auto", caption = "Bad Definition: 90+ Over MOB 24") +
ylab("Total # Funded Accts") +
xlab("Payment to Income (%)")
# The transformation factor
transf_fact <- max(f740$total)/max(f740$BR)
# Plot
ggplot(data = f740,
mapping = aes(x = rownames(f740),
y = total)) +
geom_col(fill = 'sandybrown') +
# Apply the factor on values appearing on second OY axis
geom_line(aes(y = transf_fact * BR), group = 1) +
# Add second OY axis; note the transformation back (division)
scale_y_continuous(sec.axis = sec_axis(trans = ~ . / transf_fact,
name = "Bad Rate")) +
geom_label(aes(y = transf_fact * BR,
label = round(BR, 2))) +
theme_bw() +
theme(axis.text.x = element_text(angle = 20, hjust = 1)) +
ggtitle("740-799 PTI Interval Bad Rate and Population Density") +
labs(subtitle = "Population: Apr 2017 - Mar 2018 Funded Indirect Auto", caption = "Bad Definition: 90+ Over MOB 24") +
ylab("Total # Funded Accts") +
xlab("Payment to Income (%)")
# The transformation factor
transf_fact <- max(f740p$total)/max(f740p$BR)
# Plot
ggplot(data = f740p,
mapping = aes(x = rownames(f740p),
y = total)) +
geom_col(fill = 'sandybrown') +
# Apply the factor on values appearing on second OY axis
geom_line(aes(y = transf_fact * BR), group = 1) +
# Add second OY axis; note the transformation back (division)
scale_y_continuous(sec.axis = sec_axis(trans = ~ . / transf_fact,
name = "Bad Rate")) +
geom_label(aes(y = transf_fact * BR,
label = round(BR, 2))) +
theme_bw() +
theme(axis.text.x = element_text(angle = 20, hjust = 1)) +
ggtitle("740+ PTI Interval Bad Rate and Population Density") +
labs(subtitle = "Population: Apr 2017 - Mar 2018 Funded Indirect Auto", caption = "Bad Definition: 90+ Over MOB 24") +
ylab("Total # Funded Accts") +
xlab("Payment to Income (%)")
# The transformation factor
transf_fact <- max(f650lt$total)/max(f650lt$BR)
# Plot
ggplot(data = f650lt,
mapping = aes(x = rownames(f650lt),
y = total)) +
geom_col(fill = 'lightcoral') +
# Apply the factor on values appearing on second OY axis
geom_line(aes(y = transf_fact * BR), group = 1) +
# Add second OY axis; note the transformation back (division)
scale_y_continuous(sec.axis = sec_axis(trans = ~ . / transf_fact,
name = "Bad Rate")) +
geom_label(aes(y = transf_fact * BR,
label = round(BR, 2))) +
theme_bw() +
theme(axis.text.x = element_text(angle = 20, hjust = 1)) +
ggtitle("650-739 PTI Interval Bad Rate and Population Density") +
labs(subtitle = "Population: Apr 2017 - Mar 2018 Funded Indirect Auto", caption = "Bad Definition: 90+ Over MOB 24") +
ylab("Total # Funded Accts") +
xlab("Payment to Income (%)")
# The transformation factor
transf_fact <- max(f700$total)/max(f700$BR)
# Plot
ggplot(data = f700,
mapping = aes(x = rownames(f700),
y = total)) +
geom_col(fill = 'lightcoral') +
# Apply the factor on values appearing on second OY axis
geom_line(aes(y = transf_fact * BR), group = 1) +
# Add second OY axis; note the transformation back (division)
scale_y_continuous(sec.axis = sec_axis(trans = ~ . / transf_fact,
name = "Bad Rate")) +
geom_label(aes(y = transf_fact * BR,
label = round(BR, 2))) +
theme_bw() +
theme(axis.text.x = element_text(angle = 20, hjust = 1)) +
ggtitle("700-739 PTI Interval Bad Rate and Population Density") +
labs(subtitle = "Population: Apr 2017 - Mar 2018 Funded Indirect Auto", caption = "Bad Definition: 90+ Over MOB 24") +
ylab("Total # Funded Accts") +
xlab("Payment to Income (%)")
# The transformation factor
transf_fact <- max(f675$total)/max(f675$BR)
# Plot
ggplot(data = f675,
mapping = aes(x = rownames(f675),
y = total)) +
geom_col(fill = 'aquamarine1') +
# Apply the factor on values appearing on second OY axis
geom_line(aes(y = transf_fact * BR), group = 1) +
# Add second OY axis; note the transformation back (division)
scale_y_continuous(sec.axis = sec_axis(trans = ~ . / transf_fact,
name = "Bad Rate")) +
geom_label(aes(y = transf_fact * BR,
label = round(BR, 2))) +
theme_bw() +
theme(axis.text.x = element_text(angle = 20, hjust = 1)) +
ggtitle("675-699 PTI Interval Bad Rate and Population Density") +
labs(subtitle = "Population: Apr 2017 - Mar 2018 Funded Indirect Auto", caption = "Bad Definition: 90+ Over MOB 24") +
ylab("Total # Funded Accts") +
xlab("Payment to Income (%)")
# The transformation factor
transf_fact <- max(f650$total)/max(f650$BR)
# Plot
ggplot(data = f650,
mapping = aes(x = rownames(f650),
y = total)) +
geom_col(fill = 'orange') +
# Apply the factor on values appearing on second OY axis
geom_line(aes(y = transf_fact * BR), group = 1) +
# Add second OY axis; note the transformation back (division)
scale_y_continuous(sec.axis = sec_axis(trans = ~ . / transf_fact,
name = "Bad Rate")) +
geom_label(aes(y = transf_fact * BR,
label = round(BR, 2))) +
theme_bw() +
theme(axis.text.x = element_text(angle = 20, hjust = 1)) +
ggtitle("650-674 PTI Interval Bad Rate and Population Density") +
labs(subtitle = "Population: Apr 2017 - Mar 2018 Funded Indirect Auto", caption = "Bad Definition: 90+ Over MOB 24") +
ylab("Total # Funded Accts") +
xlab("Payment to Income (%)")
f$cumBR <- 100*cumsum(f$`1`)/cumsum(f$total)
# The transformation factor
transf_fact <- max(f$total)/max(f$cumBR)
# Plot
ggplot(data = f,
mapping = aes(x = rownames(f),
y = total)) +
geom_col(fill = 'darkcyan') +
# Apply the factor on values appearing on second OY axis
geom_line(aes(y = transf_fact * cumBR), group = 1) +
# Add second OY axis; note the transformation back (division)
scale_y_continuous(sec.axis = sec_axis(trans = ~ . / transf_fact,
name = "Cum Bad Rate")) +
geom_label(aes(y = transf_fact * cumBR,
label = round(cumBR, 2)), vjust = 1.5) +
theme_bw() +
theme(axis.text.x = element_text(angle = 20, hjust = 1)) +
ggtitle("PTI Cumulative Bad Rate and Population Density") +
labs(subtitle = "Population: 2017 - Jan 2018 Funded Direct Auto", caption = "Bad Definition: 90+ Over MOB 24") +
ylab("Total # Funded Accts") +
xlab("Payment to Income (%)")
ggplot(data=pti_pop, aes(x=as.numeric(pti_r), y=Cum_Apps, group=1)) +
geom_line()+
geom_point()+
geom_vline(xintercept = 20)+
geom_vline(xintercept = 15)+
geom_hline(yintercept=90.925232)+
geom_hline(yintercept=76.884001)+
scale_x_continuous(breaks = round(seq(min(pti_pop$pti_r), max(pti_pop$pti_r), by = 5),1)) +
scale_y_continuous(breaks = round(seq(min(pti_pop$Cum_Apps), max(pti_pop$Cum_Apps), by = 5),1)) +
ggtitle("PTI Cumulative Application Population") +
ylab("Cum % of Apps") +
xlab("PTI")
ggplot(data=dti_pop, aes(x=as.numeric(dti_r), y=Cum_Apps, group=1)) +
geom_line()+
geom_point()+
geom_vline(xintercept = 56)+
geom_vline(xintercept = 51)+
geom_hline(yintercept=84.71376973)+
geom_hline(yintercept=79.97872446)+
scale_x_continuous(breaks = round(seq(min(dti_pop$dti_r), max(dti_pop$dti_r), by = 5),1)) +
scale_y_continuous(breaks = round(seq(min(dti_pop$Cum_Apps), max(dti_pop$Cum_Apps), by = 5),1)) +
ggtitle("DTI Cumulative Application Population") +
ylab("Cum % of Apps") +
xlab("DTI")
ggplot(data=pti_pop0, aes(x=as.numeric(pti_r0), y=Cum_Apps, group=1)) +
geom_line()+
geom_point()+
geom_vline(xintercept = 20)+
geom_vline(xintercept = 15)+
geom_hline(yintercept=90.925232)+
geom_hline(yintercept=76.884001)+
scale_x_continuous(breaks = round(seq(min(pti_pop0$pti_r0), max(pti_pop0$pti_r0), by = 5),1)) +
scale_y_continuous(breaks = round(seq(min(pti_pop0$Cum_Apps), max(pti_pop0$Cum_Apps), by = 5),1)) +
ggtitle("PTI Cumulative Application Population") +
ylab("Cum % of Apps") +
xlab("PTI")
ggplot(data=dti_pop0, aes(x=as.numeric(dti_r0), y=Cum_Apps, group=1)) +
geom_line()+
geom_point()+
geom_vline(xintercept = 56)+
geom_vline(xintercept = 51)+
geom_hline(yintercept=84.71376973)+
geom_hline(yintercept=79.97872446)+
scale_x_continuous(breaks = round(seq(min(dti_pop0$dti_r0), max(dti_pop0$dti_r0), by = 5),1)) +
scale_y_continuous(breaks = round(seq(min(dti_pop0$Cum_Apps), max(dti_pop0$Cum_Apps), by = 5),1)) +
ggtitle("DTI Cumulative Application Population") +
ylab("Cum % of Apps") +
xlab("DTI")
# library(readr)
# cu2 <- read_delim("F:/Secured Lending Policy & Analytics/PTI/Indirect/Q419 to Apr20 CUDL.txt",
# "|", escape_double = FALSE, trim_ws = TRUE)
#
# cu3 <- data.frame(cu2$`Application Number`
# ,cu2$`Combined Current DTI with New Loan Less Trade-In`
# ,cu2$`Initial Decision Status`
# ,cu2$`Application Stage`
# ,cu2$`Application Status`
# ,cu2$`Applicant CB1 Score`
# ,cu2$`Co-App CB1 Score`
# )
library(RODBC)
connection <- odbcDriverConnect('driver={SQL Server};server=10.40.0.28,50003;database=prod_starmart;trusted_connection=true')
cu3 <- sqlQuery(connection,
"
SELECT
cu.[Application Number]
,cu.[Combined Current DTI with New Loan Less Trade-In]
,cu.[Initial Decision Status]
,cu.[Application Stage]
,cu.[Application Status]
,cu.[Applicant CB1 Score]
,cu.[Co-App CB1 Score]
,cu.[Initial Decision Rate]
,cu.[Initial Decision Term]
,cu.[Initial Decision Approved Amount]
,cu.[Loan Amount]
,cu.Current_Pmt_Amt
FROM [CreditBooks].[dbo].[CUDL_20200401_Add_Var] cu
where cu.[Date Submitted] >= '2019-09-01'
", believeNRows = FALSE)
close(connection)
colnames(cu3) = c("Application.Number","DTI","InitialDec","Stage","Status","AppFICO","CoAppFICO","Init_rate","init_term","init_amt","loan_amt","cur_pmt")
cu4 <- merge(x = cudl, y = cu3, by = "Application.Number", all.x = TRUE)
cu9 <- merge(x = cu4, y = data.frame(ind_pti$Application.Number, ind_pti$Combined.PTI.Initial, ind_pti$Combined.PTI.Funded, ind_pti$App.PTI.Initial, ind_pti$CoApp.PTI.Initial)
, by.x = "Application.Number", by.y = "ind_pti.Application.Number", all.x = TRUE)
# cu9 <- merge(x = cu5, y = data.frame(Income_PTI$Application.Number, Income_PTI$App.PTI.Initial, Income_PTI$CoApp.PTI.Initial)
# , by.x = "Application.Number", by.y = "Income_PTI.Application.Number", all.x = TRUE)
# bridge$Application.Number <- (as.numeric(as.character(bridge$APP_NO)))
# cu9 <- merge(x = cu5, y = bridge, by = "Application.Number", all.x = TRUE)
# library(RODBC)
# connection <- odbcDriverConnect('driver={SQL Server};server=dsvr;database=prod_starmart;trusted_connection=true')
# pmt <- sqlQuery(connection,
# "
# select il.account_nbr, il.il_lntypcd, il.il_pmt_amt
# from account_ils il
# where il.il_lntypcd in ('nvi','uvi')
#
# ", believeNRows = FALSE)
# close(connection)
#
# cu9 <- merge(x = cu6, y = pmt
# , by.x = "il_account_nbr", by.y = "account_nbr", all.x = TRUE)
rm(cu3,cu4,cu5,cu6,pmt)## Warning in rm(cu3, cu4, cu5, cu6, pmt): object 'cu5' not found## Warning in rm(cu3, cu4, cu5, cu6, pmt): object 'cu6' not found## Warning in rm(cu3, cu4, cu5, cu6, pmt): object 'pmt' not foundcu9$dti_band <- ifelse(cu9$App.DTI.Before.New.Loan.Initial<=50, "LTE50",
ifelse(cu9$App.DTI.With.New.Loan.Less.Trade.In.Initial<100, "LT100",
ifelse(cu9$App.DTI.With.New.Loan.Less.Trade.In.Initial<100000000, "100+","NA")))
cu9$pti_band <- ifelse(cu9$ind_pti.Combined.PTI.Initial<=15, "LTE15",
ifelse(cu9$ind_pti.Combined.PTI.Initial<=20, "15<pti<=20",
ifelse(cu9$ind_pti.Combined.PTI.Initial<1000000000, ">20", "NA")))
cu9$FICO <- ifelse(is.na(as.numeric(as.character(cu9$CoAppFICO)))==TRUE, as.numeric(as.character(cu9$AppFICO)), pmax(as.numeric(as.character(cu9$AppFICO)), as.numeric(as.character(cu9$CoAppFICO))))
cu9$FICO_grp <- ifelse(cu9$FICO>=800, "800+",
ifelse(cu9$FICO>=740, "740-799",
ifelse(cu9$FICO>=700, "700-739",
ifelse(cu9$FICO>=675, "675-699",
ifelse(cu9$FICO>=650, "650-674",
ifelse(cu9$FICO>=600, "600-649",
ifelse(cu9$FICO>=350, "350-599","NA")))))))
cu9$FICO_cut <- ifelse(cu9$FICO <650, "FICO LT 650",
ifelse(as.numeric(as.character(cu9$CoAppFICO))<565 | as.numeric(as.character(cu9$AppFICO))<565, "Co LT 565",
ifelse(as.numeric(is.na(cu9$FICO))==TRUE, "NoScore", "Other")))
cu9$Joint_fail <- ifelse(cu9$FICO < 700 & cu9$ind_pti.Combined.PTI.Initial<= 15
& (cu9$ind_pti.App.PTI.Initial>20 | cu9$ind_pti.CoApp.PTI.Initial>20)
, "Comb Pass; Indiv Fail", "Other")
cu9$new_PTIgt20 <- ifelse(cu9$ind_pti.Combined.PTI.Initial>20 & trimws(cu9$Vehicle.Condition)== "New","Y","N")
cu9$used_450 <- ifelse(cu9$cur_pmt >450 & trimws(cu9$Vehicle.Condition)== "Used","Y","N")
cu9$pti_individual <- pmax(as.numeric(cu9$ind_pti.App.PTI.Initial), as.numeric(cu9$ind_pti.CoApp.PTI.Initial))
cu9$IndPTI_Policy <- ifelse(cu9$FICO >= 740 & cu9$pti_individual > 20, "740PTI_gt20",
ifelse(cu9$FICO<740 & cu9$pti_individual >20, "lt740PTI_gt20",
ifelse(cu9$FICO< 740 & cu9$pti_individual >15 & cu9$pti_individual<=20, "lt740_pti15_20",
ifelse(cu9$pti_individual>20, "ptigt20", "pass"))))##### PTI Evaluation
# q <-data.frame(t9$APP_NO
# ,t9$account_nbr
# ,t9$il_pmt_amt
# ,t9$`Applicant Employment Income`
# ,t9$`Co-App Employment Income`
# ,t9$CoApp.Total.Gross.Monthly.Inc
# ,t9$Combined.PTI.Funded
# ,t9$App.PTI.Funded
# ,t9$CoApp.PTI.Funded
# )
#
# q$comb_pti_calc <- (as.numeric(q$t9..Applicant.Employment.Income.))
library(readxl)
Income_PTI <- read_excel("Income & PTI (Plus App Num).xlsx")## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C2699 / R2699C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C3500 / R3500C3: got 'Yearly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C3623 / R3623C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C4215 / R4215C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C4647 / R4647C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C10116 / R10116C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C11644 / R11644C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C12956 / R12956C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C14562 / R14562C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C19016 / R19016C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C22331 / R22331C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C23357 / R23357C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C24571 / R24571C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C24672 / R24672C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C24673 / R24673C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C24905 / R24905C3: got 'Bi-Weekly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in H26536 / R26536C8: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C26832 / R26832C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C26942 / R26942C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C27257 / R27257C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C27269 / R27269C3: got 'Yearly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C27583 / R27583C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C27904 / R27904C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C29378 / R29378C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C39502 / R39502C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in H41222 / R41222C8: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in H41995 / R41995C8: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C42139 / R42139C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in H44858 / R44858C8: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C46096 / R46096C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C46097 / R46097C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C54048 / R54048C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in H57019 / R57019C8: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C58363 / R58363C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in H59400 / R59400C8: got 'Yearly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C63634 / R63634C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C64583 / R64583C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C66337 / R66337C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C69557 / R69557C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C69683 / R69683C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C73021 / R73021C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C75056 / R75056C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C75793 / R75793C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C76819 / R76819C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in H76828 / R76828C8: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C77743 / R77743C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in H79027 / R79027C8: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C79873 / R79873C3: got 'Weekly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in H80355 / R80355C8: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C80430 / R80430C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C83425 / R83425C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C83675 / R83675C3: got 'Yearly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C83748 / R83748C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C84591 / R84591C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C88781 / R88781C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C101400 / R101400C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C103114 / R103114C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C104774 / R104774C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C105309 / R105309C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C105771 / R105771C3: got 'Monthly'## Warning in read_fun(path = enc2native(normalizePath(path)), sheet_i =
## sheet, : Expecting logical in C114681 / R114681C3: got 'Monthly'##117891
Income_PTI <- merge(x = Income_PTI, y = bridge, by.x = "Application.Number", by.y = "APP_NO", all.x = TRUE)
f <- subset(Income_PTI, is.na(Income_PTI$il_account_nbr)==FALSE)
f$app_ind_pti_c <- sprintf("%.2f %%", 100*f$Current.Pmt.Amt/f$App.Total.Gross.Monthly.Inc.Funded)
f$app_ind_pti_il <- sprintf("%.2f %%", 100*f$il_pmt_amt/f$App.Total.Gross.Monthly.Inc.Funded)
f$coapp_ind_pti_c <- sprintf("%.2f %%", 100*f$Current.Pmt.Amt/f$CoApp.Total.Gross.Monthly.Inc.Funded)
f$coapp_ind_pti_il <- sprintf("%.2f %%", 100*f$il_pmt_amt/f$CoApp.Total.Gross.Monthly.Inc.Funded)
f$comb_ind_pti_c <- sprintf("%.2f %%", 100*f$Current.Pmt.Amt/(f$App.Total.Gross.Monthly.Inc.Funded + f$CoApp.Total.Gross.Monthly.Inc.Funded))
f$comb_ind_pti_il <- sprintf("%.2f %%", 100*f$il_pmt_amt/(f$App.Total.Gross.Monthly.Inc.Funded + f$CoApp.Total.Gross.Monthly.Inc.Funded))
f$comb_inc <-(f$App.Total.Gross.Monthly.Inc.Funded + f$CoApp.Total.Gross.Monthly.Inc.Funded)
f2 <- data.frame(f$Application.Number
,f$Current.Pmt.Amt
,f$il_pmt_amt
,f$Combined.PTI.Funded
,f$comb_ind_pti_c
,f$comb_ind_pti_il
,f$App.Total.Gross.Monthly.Inc.Funded
,f$App.Total.Gross.Monthly.Inc.Funded
,f$CoApp.Total.Gross.Monthly.Inc.Funded
,f$CoApp.PTI.Funded
,f$comb_inc
,f$Combined.Gross.Monthly.Inc
,f$app_ind_pti_c
,f$App.PTI.Funded
,f$app_ind_pti_il
,f$coapp_ind_pti_c
,f$coapp_ind_pti_il
,f$Combined.Other.Inc
,f$App.Total.Other.Inc
,f$App.Other.Inc.Frequency
,f$CoApp.Total.Other.Inc
,f$CoApp.Other.Inc.Frequency
)
f2$coapp_inc_xxxxx <- sprintf("%.20f",f2$f.CoApp.Total.Gross.Monthly.Inc.Funded)
f2$co_pti_div_c <- sprintf("%.6f",f2$f.Current.Pmt.Amt/f2$f.CoApp.PTI.Funded)
f2$co_pti_div_il <- sprintf("%.6f",f2$f.il_pmt_amt/f2$f.CoApp.PTI.Funded)### joint vs Individual risk
table(subset(t9, is.na(t9$CoApp.Total.Gross.Monthly.Inc)==TRUE)$FICO_grp, subset(t9, is.na(t9$CoApp.Total.Gross.Monthly.Inc)==TRUE)$bad90)##
## 0 1
## 350-599 1 0
## 600-649 223 41
## 650-674 636 66
## 675-699 840 79
## 700-739 1963 114
## 740-799 2010 71
## 800+ 1076 14
## NA 9 3table(t9_c$FICO_grp, t9_c$bad90)##
## 0 1
## 350-599 1 1
## 600-649 137 19
## 650-674 380 52
## 675-699 545 64
## 700-739 1284 115
## 740-799 1451 61
## 800+ 1105 33
## NA 3 0table(subset(t9,t9$il_lntypcd=="UVI" & (t9$il_crscr_generic>=740 & t9$Combined.PTI.Funded<20) | (t9$il_crscr_generic<740 & t9$Combined.PTI.Funded<20))$bad90)##
## 0 1
## 8090 629table(subset(t9,t9$il_lntypcd=="UVI" & t9$il_pmt_amt<450 & t9$il_crscr_generic>=740 & t9$Combined.PTI.Funded<20)$bad90)##
## 0 1
## 1802 71table(subset(t9,t9$il_lntypcd=="UVI" & t9$il_crscr_generic>=740 & t9$Combined.PTI.Funded<20)$bad90)##
## 0 1
## 2217 90table(subset(t9,t9$il_lntypcd=="UVI" & t9$il_pmt_amt<450 & t9$il_crscr_generic<740 & t9$Combined.PTI.Funded<15)$bad90)##
## 0 1
## 2057 190table(subset(t9,t9$il_lntypcd=="UVI" & t9$il_crscr_generic<740 & t9$Combined.PTI.Funded<15)$bad90)##
## 0 1
## 2671 277table(subset(t9,t9$il_lntypcd=="UVI" & t9$il_pmt_amt<450)$bad90)##
## 0 1
## 3960 271table(subset(t9,t9$il_lntypcd=="UVI" )$bad90)##
## 0 1
## 5139 395table(subset(t9,t9$il_lntypcd=="UVI" & t9$il_pmt_amt<500 & t9$il_crscr_generic>=740 & t9$Combined.PTI.Funded<20)$bad90)##
## 0 1
## 1942 76table(subset(t9,t9$il_lntypcd=="UVI" & t9$il_crscr_generic>=740 & t9$Combined.PTI.Funded<20)$bad90)##
## 0 1
## 2217 90table(subset(t9,t9$il_lntypcd=="UVI" & t9$il_pmt_amt<500 & t9$il_crscr_generic<740 & t9$Combined.PTI.Funded<15)$bad90)##
## 0 1
## 2238 216table(subset(t9,t9$il_lntypcd=="UVI" & t9$il_crscr_generic<740 & t9$Combined.PTI.Funded<15)$bad90)##
## 0 1
## 2671 277