<jupyter_start><jupyter_text>Data cleansing Outlier detection In this notbooke we identified and treated the univariate and multivariate outliers  Most common causes of outliers1. Data entry errors (human errors)2. Measurement error (instrument errors)3. Experimental errors (data extraction planning/executing errors)4. Intentional (made to test detection methods)5. Processing errors (data manipulation or by imputation)5. Sampling errors (data from a different distribution/source)6. Natural (not an error, novelty in data)<jupyter_code>##Importing required packages 
if(!require(tidyr)){install.packages("tidyr"); require(tidyr)}
if(!require(ggcorrplot)){install.packages("ggcorrplot"); require(ggcorrplot)}
if(!require(rcompanion)){install.packages("rcompanion"); require(rcompanion)}
if(!require(dbscan)){install.packages("dbscan");require(dbscan)}
if(!require(data.table)){install.packages("data.table");require(data.table)}
if(!require(dplyr)){install.packages("dplyr"); require(dplyr)}
if(!require(ggplot2)) {install.packages("ggplot2");require(ggplot2)}
if(!require(mlr)) {install.packages("mlr");require(mlr)}
if(!require(DBI)){install.packages("DBI"); require(DBI)}
if(!require(RSQLite)){install.packages("RSQLite"); require(RSQLite)}


##WorkPath
DB_FILE = (paste0(getwd(),"/Data/loans.db"))

###############################################################################
##########                DATABASE FUNCTIONS                     #############
##############################################################################
            
#### Read function to import data from the SQL to a pandas dataframe.
readSQL <- function(query, db=DB_FILE) {
    require(DBI)
    require(RSQLite)
    con <- dbConnect(SQLite(), DB_FILE)
    df <- dbGetQuery(con, query)
    return(df)
}
#### Write a pandas dataframe into an SQL table. Use overwrite=True if you want to delete 
#### first a pre-existent table with the same name. Use append=True if you want to append
#### the data in the dataframe to a pre-existent table.
writeSQL <- function(df,tablename,overwrite=FALSE, append=FALSE,db=DB_FILE) {
    require(DBI)
    require(RSQLite)
    con <- dbConnect(SQLite(), DB_FILE)
    dbWriteTable(con,tablename,df,overwrite,append)
}
####
listTables <- function(db=DB_FILE) {
    require(DBI)
    require(RSQLite)
    con <- dbConnect(SQLite(), DB_FILE)
    ### list the tables on the DB
    res <- dbListTables(con)
    return(res)
} 
loans = readSQL("SELECT * From loans_dataset_eng")
dim(loans)
loans_coppy_1<-loans

loans$purpose<-factor(loans$purpose)
loans$pymnt_plan<-factor(loans$pymnt_plan)
loans$credit_hist_grp<-factor(loans$credit_hist_grp)
loans$verification_status<-factor(loans$verification_status)
loans$initial_list_status<-factor(loans$initial_list_status)
loans$term<-factor(loans$term)
loans$home_ownership<-factor(loans$home_ownership)
loans$cat_pub_rec<-factor(loans$cat_pub_rec)
loans$pymnt_plan<-factor(loans$pymnt_plan)
loans$emp_length<-factor(loans$emp_length)
loans$default<-factor(loans$default)
loans$cat_delinq_2yrs<-factor(loans$cat_delinq_2yrs)
loans$full_state<-factor(loans$full_state)
loans$inc_grp<-factor(loans$inc_grp)
loans$cat_inq_last_6mths <-factor(loans$cat_inq_last_6mths )
loans$cat_collections_12_mths_ex_med<-factor(loans$cat_collections_12_mths_ex_med)




cat_vars <- loans %>% sapply(is.factor) %>% which() %>% names()
cat_vars
length(cat_vars)
num_vars <- 
  loans %>% 
  sapply(is.numeric) %>% 
  which() %>% 
  names()
num_vars<jupyter_output><empty_output><jupyter_text>Univariate Outlier detection for continuous numeric variablesthare is to common ways of definning a outlaiers:1. by z score2. by quntiles  Using Standard Deviation (z-scores)![image.png](attachment:image.png) This method is good only for a normal distributed variabels.  Using median and Interquartile range (Boxplot)![image.png](attachment:image.png) This method is good also to an unnormal distributed variabels. In this artical, we checked each variable, one by one.We started with identified univariate outliers in our data. As we so in the EDA chapter, according to the AD test, the only numerical variable which normally distributed is all_util. Therefore, in this work, we decided to use median and Interquartile range, as showed by Turkey (Tukey 1977), for all variables and the z score test only for all_util variable. That due to the fact that it is more robust to un-normal distribution.  (1) loan amount<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt1 <- boxplot(loans$loan_amnt)<jupyter_output><empty_output><jupyter_text>we can see that most outliers are near the outliers boundary.<jupyter_code>head(plt1$out,100)
length(plt1$out)<jupyter_output><empty_output><jupyter_text>We can see that most of the "outliers" getting the same value of 35000! Is there any value higher then 35000?<jupyter_code>sum(plt1$out>35000)
sum(plt1$out==35000)
sum(plt1$out<35000)<jupyter_output><empty_output><jupyter_text>From the LC website, as of 2019:                            "Personal loans through LendingClub range from $1,000 to $40,000. The loan offer you receive is based upon what we believe is affordable for you, given the information on your application and credit report. As a marketplace, our goal is to ensure borrowers have a really good shot at successfully repaying their loans." Therefore, we can conclude that $ 35,000 is in the range of loans given by LC in past years and is not an error.For those reasons, we decided not to change those values.                                                                  but label the loans with the maximum amount of 35000, for future work.<jupyter_code>loans$max_L<-(loans$loan_amnt==35000)*1
bin_vars<-"max_L"<jupyter_output><empty_output><jupyter_text>(2) funded_amnt This parameter has a high correlation with "loan amount" and have very similar interpretation, so we expect to see results similar to the "loan amount" test.<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt2 <- boxplot(loans$funded_amnt)<jupyter_output><empty_output><jupyter_text>we can see that there is  a lot of outliers just in the outliers border.<jupyter_code>length(plt2$out)
sum(plt2$out>35000)
sum(plt2$out==35000)
sum(plt2$out<35000)<jupyter_output><empty_output><jupyter_text>Indeed, we can see that the results of testing this parameter are similar to Loan amount testing results and therefore our conclusion is the same.<jupyter_code>loans$max_fund_L=(loans$funded_amnt==35000)*1
bin_vars<-c(bin_vars,"max_fund_L")<jupyter_output><empty_output><jupyter_text>(3) funded_amnt_inv This parameter has a high correlation with "loan amount" and "funded_amnt", so we expect to see results similar to the "loan amount" test.<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt3 <- boxplot(loans$funded_amnt_inv)
head((plt2$out),70)
length(plt2$out)
sum(plt2$out>35000)
sum(plt2$out==35000)
sum(plt2$out<35000)<jupyter_output><empty_output><jupyter_text>Indeed, we can see that the results of testing this parameter are exactly  the same to funded_amnt testing results and therefore our conclusion is the same.<jupyter_code>loans$MAX_fund_L_inv=(loans$funded_amnt==35000)*1
bin_vars<-c(bin_vars,"MAX_fund_L_inv")<jupyter_output><empty_output><jupyter_text>(4) loan_installment<jupyter_code>head(loans$loan_installment, 40)
options(repr.plot.width = 4, repr.plot.height = 4)
plt4<- boxplot(loans$loan_installment)
head((plt4$out),70)
length(plt4$out)

options(repr.plot.width = 4, repr.plot.height = 4)
plt4.1<- boxplot(loans$loan_installment, range = 2)
head((plt4.1$out),70)
length(plt4.1$out)

sum(plt4.1$out==972.22)<jupyter_output><empty_output><jupyter_text>The installment parameter is an outcome of two other parameters interaction: loan term and loan amount.There is just two loan terms available, 36 months and 64 months.We have to remember that 972.22 is just the outcome of 35,000/36 and we decide to not call 35000 loan amount an outlier.In order to make a decision more carefully, let's look on the different loan amount in different term.<jupyter_code>plt4.2<-boxplot(loans$loan_amnt~loans$term) 

plt4.3<-boxplot(loans$loan_amnt~loans$term,range=2)<jupyter_output><empty_output><jupyter_text>Distinguished by term, all of the loan amount outliers are in the 36 term.<jupyter_code>out4.2<-plt4.2$out
length(out4.2)
head(out4.2)
sum(out4.2==35000)
min(out4.2)<jupyter_output><empty_output><jupyter_text>Approximately half of the outliers are not equal to the maximum loan amount. (5)  mths_since_last_major_derog A count variable<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt5<- boxplot(loans$mths_since_last_major_derog)

head((plt5$out),70)
length(plt5$out)
min(plt5$out)
plt5$stats
max(plt5$out)
159/12<jupyter_output><empty_output><jupyter_text>159 month since the last derog is sound reasonable<jupyter_code>hist(loans$mths_since_last_major_derog)<jupyter_output><empty_output><jupyter_text>We can see that to the mths_sine_las_major thare is a long tale distrabution.  (6) annual_inc [annual_inc_log, annual_inc_rt]<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt6<- boxplot(loans$annual_inc)
head((plt6$out),70)
length(plt6$out)
plt6$stats
min(plt6$out)

quantile(x = loans$annual_inc, na.rm = TRUE)<jupyter_output><empty_output><jupyter_text>It is look like there is a lot of outliers, but from this boxplot and from the EDA stage, we can remember that this variable is distributed exponentially. In the feature engineering stage we did transform this variable with a log function. now we will check the log transformation influence on the outliers detection process<jupyter_code>e <- exp(1) 
log_annual_inc<-log(loans$annual_inc,base = e )
hist(loans$annual_inc,col = 20)
hist(log_annual_inc,col = 20)
plt6_l<- boxplot(log(loans$annual_inc,base = e))
length(plt6_l$out)<jupyter_output><empty_output><jupyter_text>The outliers number is decrees dramatically, as consequence of the log transformation. We also can see that there are very far outliers. What is there values?<jupyter_code>max(loans$annual_inc,na.rm = TRUE)<jupyter_output><empty_output><jupyter_text>the maximum annual inc is 870,6582 dollars. Is it a mistake?  According to the cnbc ,based on the IRS, $480,930, makes you one of the 1% high income in the US.https://www.cnbc.com/2018/07/10/heres-how-much-money-the-1-percent-really-make.html According to Wikipedia, as 3% presents of households in the USA have an annual inc over 200,000 dollarsand two working individuals in a mean household.https://en.wikipedia.org/wiki/Household_income_in_the_United_States<jupyter_code>sum(loans$annual_inc>200000,na.rm = TRUE)/sum(loans$annual_inc<10000000000,na.rm = TRUE)
sum(loans$annual_inc>480930,na.rm = TRUE)/sum(loans$annual_inc<10000000000,na.rm = TRUE)
sum(loans$annual_inc>1000000,na.rm = TRUE) 
sum(loans$annual_inc>4000000,na.rm = TRUE)
sum(loans$annual_inc>6000000,na.rm = TRUE)<jupyter_output><empty_output><jupyter_text>We can see that in our data set the percentage of the high salary individuals is less than  general, which make sense.  we did labeled the loans took by high income individuals as part of the feature engineering stage. (7) dti<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt7<- boxplot(loans$dti)

head((plt7$out),70)
length(plt7$out)
min(plt7$out)
quantile(x = loans$dti)
options(repr.plot.width = 4, repr.plot.height = 4)
plt16.1<- boxplot(loans$dti,range = 1.6)

head((plt16.1$out),70)
length(plt16.1$out)

plt16.1<- boxplot(loans$dti,range = 1.61)

head((plt16.1$out),70)
length(plt16.1$out)<jupyter_output><empty_output><jupyter_text>We can see that most outliers are near the outlier's boundary, and all of them are less than 40. From LC web site we can study that 40 is the higher boundery of this:"Ideally, your debt-to-income ratio would be lower than 40%. That’s generally the threshold used across the industry. If your DTI is higher than 40%, your loan application will likely be denied." (8) delinq_2yrs [cat_delinq_2yrs] A count variable<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt8<- boxplot(loans$delinq_2yrs)

head((plt8$out),70)
length(plt8$out)
plt8$stats
hist(loans$delinq_2yrs,col = 20)
hist(log(loans$delinq_2yrs,e),col = 20)
min(loans$delinq_2yrs,  na.rm=TRUE)
quantile(x = loans$delinq_2yrs, na.rm=TRUE)
sum(loans$delinq_2yrs==0,  na.rm=TRUE)
sum(loans$delinq_2yrs!=0,  na.rm=TRUE)<jupyter_output><empty_output><jupyter_text>Most of the values are equal to zero, and all the 41148 outliers are the values that are differ from zero.Therefore, for now, we decided mark all the number that are differ from zero.It is seems that is better to treat that count variable as categorical variable. (9) acc_now_delinq A count variable<jupyter_code>plt8<-boxplot(loans$acc_now_delinq)
length(plt8$out)

quantile(x = loans$acc_now_delinq, na.rm=TRUE)
sum(loans$acc_now_delinq==0,  na.rm=TRUE)
sum(loans$acc_now_delinq!=0,  na.rm=TRUE)<jupyter_output><empty_output><jupyter_text>most of the values are equal to zero, and all the 739 outliers are the values that are differ from zero. Therfore, for now, we dicided to mark all the number that are differ from zero.It is seems that is better to treat that variable as categorical variable.<jupyter_code>loans$is_acc_now_delinq_not_zero<-(loans$acc_now_delinq!=0)*1
bin_vars<-c(bin_vars,"is_acc_now_delinq_not_zero")<jupyter_output><empty_output><jupyter_text>(10) inq_last_6mths [cat_inq_last_6mths] A count variable<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt9<- boxplot(loans$inq_last_6mths)

head((plt9$out),70)
length(plt9$out)
max((plt9$out))
sum(loans$inq_last_6mths==0,na.rm = TRUE)
sum(loans$inq_last_6mths!=0,na.rm = TRUE)<jupyter_output><empty_output><jupyter_text>About as half of values are  zero<jupyter_code>hist(loans$inq_last_6mths)
hist(log(loans$inq_last_6mths))<jupyter_output><empty_output><jupyter_text>It is seems that is better to treat that variable as categorical variable. As we did on feature engineering notebook (11) mths_since_last_delinq [mths_since_last_delinq_rt] A count variable<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt10<- boxplot(loans$mths_since_last_delinq)

head((plt10$out),70)
length(plt10$out)
hist(loans$mths_since_last_delinq)
hist(loans$mths_since_last_delinq^0.5)

plt10<- boxplot(loans$mths_since_last_delinq^0.5)

head((plt10$out),70)
length(plt10$out)<jupyter_output><empty_output><jupyter_text>The outliers number is decrees dramatically, as consequence of the root transformation. (12) mths_since_last_record A count variable<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt11<- boxplot(loans$mths_since_last_record)

head((plt11$out),70)
length(plt11$out)<jupyter_output><empty_output><jupyter_text>there is no outliers probably because there is alot of NAs (13) open_acc [open_acc_rt]<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt12<- boxplot(loans$open_acc)

head((plt12$out),70)
length(plt12$out)
max(plt12$out)
hist(loans$open_acc)
hist(loans$open_acc^0.5)
hist(log(loans$open_acc))
rootb9<-boxplot(loans$open_acc^0.5)
length(rootb9$out)
logb9<-boxplot(log(loans$open_acc))
length(logb9$out)<jupyter_output><empty_output><jupyter_text>The outliers number is decrees dramatically, as consequence of both the root transformation and the log transformation. (14) revol_bal [ revol_bal_rt]<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt13<- boxplot(loans$revol_bal)

head((plt13$out),70)
length(plt13$out)
hist(loans$revol_bal)

hist((loans$revol_bal)^0.5)
plt13<- boxplot(loans$revol_bal^0.5)

head((plt13$out),70)
length(plt13$out)<jupyter_output><empty_output><jupyter_text>The outliers number did decrees, as consequence of both the root transformation. (15) revol_util<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt14<- boxplot(loans$revol_util)

head((plt14$out),70)
length(plt14$out)
plt14$stats<jupyter_output><empty_output><jupyter_text>there is only 4 outliers but just one oulaier is very far from the others.it is interesting to find what loan is it?<jupyter_code>max(loans$revol_util, na.rm=TRUE)<jupyter_output><empty_output><jupyter_text>As noted in previous notebooks, this variable is a rate representing the amount of the credit line  used relative to total credit available.<jupyter_code>sum(100>loans$revol_util, na.rm=TRUE)
sum(100<loans$revol_util, na.rm=TRUE)<jupyter_output><empty_output><jupyter_text>As accpected, most of the loans revol_util rates are less than 100. what makes some revol_util rates to be more than an 100? how peaple can borrow more than thar credit line? Sopraisly, We found avidence that the answer is yes!https://www.thebalance.com/can-you-go-over-your-credit-limit-961095https://www.creditcards.com/credit-card-news/credit-limit-how-far-over-before-card-declined.phphttps://www.valuepenguin.com/what-happens-if-you-go-over-your-credit-limit<jupyter_code>max(loans$revol_util[loans$revol_util!=max(loans$revol_util, na.rm=TRUE)], na.rm=TRUE)

Lmax<-loans[loans$revol_util==max(plt14$out),]
Lmax[!is.na(Lmax$verification_status),]
892.3/2677<jupyter_output><empty_output><jupyter_text>this value is very far from other values, while the other characters of the loan looks like a regular loan.The revol_bal is 2677, Total revolving high credit/credit limit is 300.<jupyter_code>2677/300<jupyter_output><empty_output><jupyter_text>If this is an error it resulting from the revoll bal or from revoll_util and not from errors in calculations. As we didn't find sufficient proof for an error, but we decided to keep this value. (16) total_acc A count variable<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt15<- boxplot(loans$total_acc)

head((plt15$out),70)
length(plt15$out)<jupyter_output><empty_output><jupyter_text>The total number of credit lines currently in the borrower’s credit file<jupyter_code>Lmax<-loans[loans$total_acc==max(plt15$out),]
Lmax[!is.na(Lmax$total_acc),]<jupyter_output><empty_output><jupyter_text>(17) tot_coll_amt<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt16<- boxplot(loans$tot_coll_amt)

head((plt16$out),70)
length(plt16$out)
m1<-max(plt16$out)
m2<-max( plt16$out[plt16$out!=max(plt16$out)] )
m1
m2
m1/m2<jupyter_output><empty_output><jupyter_text>Over than 9 milion dollars supposedly collected from this borrower over all times, that is 30 times more than the second high amount collected!who is this individual?<jupyter_code>Lmax<-loans[loans$tot_coll_amt==max(plt16$out),]
print(Lmax[!is.na(Lmax$tot_coll_amt),])<jupyter_output>verification_status    issue_d pymnt_plan purpose initial_list_status
190946            Verified 2014-04-01          0   other                   0
       emp_length loan_amnt funded_amnt funded_amnt_inv term
190946     1 year     10800       10800           10800    0
       collections_12_mths_ex_med mths_since_last_major_derog home_ownership
190946                          0                          46       MORTGAGE
       annual_inc dti delinq_2yrs acc_now_delinq inq_last_6mths
190946     122000 9.1           0              0              1
       mths_since_last_delinq mths_since_last_record open_acc pub_rec revol_bal
190946                     46                     NA        8       0      4742
       revol_util total_acc tot_coll_amt tot_cur_bal open_acc_6m open_il_6m
190946       34.9        34      9152545      233924          NA         NA
       open_il_12m open_il_24m mths_since_rcnt_il total_bal_il il_util
190946          NA          NA                 NA          [...]<jupyter_text>This person have a low dti, nice but not very high annual income  and a mortgage.For a person that dont own a home and is annual inc less than 150,000 $, more than 9 milion dollars paid in the past looks seems unreasonable!Therefore, it was reasonable for us to assume that this outlaier is an outcome of an error. and we raplaced it with a NA.<jupyter_code>loans$tot_coll_amt[loans$tot_coll_amt==max(plt16$out)]<-NA
options(repr.plot.width = 4, repr.plot.height = 4)
plt16<- boxplot(loans$tot_coll_amt)

head((plt16$out),70)
length(plt16$out)<jupyter_output><empty_output><jupyter_text>(18) tot_cur_bal<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt17<- boxplot(loans$tot_cur_bal)

head((plt17$out),70)
length(plt17$out)
min(loans$tot_cur_bal,na.rm=TRUE) 
hist(loans$tot_cur_bal)
hist(log(loans$tot_cur_bal))
hist((loans$tot_cur_bal)^0.5)
plt17.1<- boxplot(log(loans$tot_cur_bal))
length(plt17.1$out)
plt17.2<- boxplot(loans$tot_cur_bal^0.5)
length(plt17.2$out)<jupyter_output><empty_output><jupyter_text>The outliers number did dramatically decreesed, as consequence of both the root transformation and the log transformation.  (18) open_acc_6m A count variable<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt28<- boxplot(loans$open_acc_6m)

head((plt28$out),70)
length(plt28$out)
sum(loans$open_acc_6m!=6,na.rm=TRUE)
sum(loans$open_acc_6m==0,na.rm=TRUE)<jupyter_output><empty_output><jupyter_text>this variable have just 6 different values, when just two sample from all of our data set got the value 6.Also we can see that almost for all loans this varaieble contains NA! (19) open_il_6m<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt19<- boxplot(loans$open_il_6m)

head((plt19$out),70)
length(plt19$out)
sum(loans$open_il_6m<50,na.rm=TRUE)<jupyter_output><empty_output><jupyter_text>we can see that almost for all loans this variable contains NA (20) open_il_12m<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt20<- boxplot(loans$open_il_12m)


head((plt20$out),70)
length(plt20$out)
sum(loans$open_il_12m<1000,na.rm=TRUE)<jupyter_output><empty_output><jupyter_text>we can see that almost for all loans this variable contains NA (21) open_il_24m<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt31<- boxplot(loans$open_il_24m)

head((plt31$out),70)
length(plt31$out)
sum(loans$open_il_24m<1000,na.rm=TRUE)<jupyter_output><empty_output><jupyter_text>we can see that almost for all loans this variable contains NA (22) mths_since_rcnt_il<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt25<- boxplot(loans$mths_since_rcnt_il)

head((plt25$out),70)
length(plt25$out)
sum(loans$mths_since_rcnt_il<1000,na.rm=TRUE)<jupyter_output><empty_output><jupyter_text>we can see that almost for all loans this variable contains NA  (23) total_bal_il<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt25<- boxplot(loans$total_bal_il)

head((plt25$out),70)
length(plt25$out)
sum(loans$total_bal_il<1000,na.rm=TRUE)<jupyter_output><empty_output><jupyter_text>we can see that almost for all loans this variable contains NA (24) il_util<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt25<- boxplot(loans$il_util)

head((plt25$out),70)
length(plt25$out)
sum(loans$il_util<1000,na.rm=TRUE)<jupyter_output><empty_output><jupyter_text>we can see that almost for all loans this variable contains NA (25) open_rv_12m<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt25<- boxplot(loans$open_rv_12m)

head((plt25$out),70)
length(plt25$out)
sum(loans$open_rv_12m<1000,na.rm=TRUE)<jupyter_output><empty_output><jupyter_text>we can see that almost for all loans this variable contains NA  (26) open_rv_24m<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt25<- boxplot(loans$open_rv_24m)

head((plt25$out),70)
length(plt25$out)
sum(loans$open_rv_24m<1000,na.rm=TRUE)<jupyter_output><empty_output><jupyter_text>we can see that almost for all loans this variable contains NA (27) max_bal_bc<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt25<- boxplot(loans$max_bal_bc)

head((plt25$out),70)
length(plt25$out)
sum(loans$max_bal_bc<100000,na.rm=TRUE)<jupyter_output><empty_output><jupyter_text>we can see that almost for all loans this variable contains NA (28) all_util As we have shown in the EDA notebook, this variable is normaly distributed<jupyter_code>hist(loans$all_util)
sum(loans$all_util<100000,na.rm=TRUE)<jupyter_output><empty_output><jupyter_text>For our disappointment, also for this variable, almost for all loans this variable contains NA. For educational purpose, we continue with our investigation, even though it is almost sure that this variable will be excluded in the missing data stage.Therefore, first we will use the IQR method and after the z method.<jupyter_code>#IQR
options(repr.plot.width = 4, repr.plot.height = 4)
plt25<- boxplot(loans$all_util)

head((plt25$out),70)
length(plt25$out)
#z
z_outlaiers<- function(x, outs_bound=2.7, na_rm=TRUE){
    z_x<-abs(scale(x))
    is_out<-z_x>outs_bound
    outs<-ifelse(sum(is_out,na.rm=TRUE)==0,0, x[is_out])
    
    outs<-ifelse(na_rm , outs[!is.na(outs)]  ,outs)
    outs<-ifelse(sum(is_out,na.rm=TRUE)==0,"There is no outliers!", outs)
    return (outs)
}

plt<-z_outlaiers(loans$all_util,2.7, na_rm=TRUE )
length(plt)
head(plt)<jupyter_output><empty_output><jupyter_text>We set the boundary to Z==2.7, which is the equivalent to IQR==2 in the theoretical normal distribution.Hence, it is not surprising that there is no outliers also according to this test. Acording to both methods, thare is no outlaiers (29) total_rev_hi_lim<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt29<- boxplot(loans$total_rev_hi_lim)

head((plt29$out),70)
length(plt29$out)
hist((loans$total_rev_hi_lim))
hist(log(loans$total_rev_hi_lim))
hist((loans$total_rev_hi_lim)^0.5)
plt29.1<- boxplot(log(loans$total_rev_hi_lim))

length(plt29.1$out)
plt29.2<- boxplot(loans$total_rev_hi_lim^0.5)

length(plt29.2$out)<jupyter_output><empty_output><jupyter_text>The outliers number did dramatically decreesed, as consequence of both the root transformation and the log transformation. but the log transformation did a better job. (30) inq_fi<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt25<- boxplot(loans$inq_fi)

head((plt25$out),70)
length(plt25$out)
sum(loans$inq_fi<100000,na.rm=TRUE)<jupyter_output><empty_output><jupyter_text>this variable contains NA almost for all loans and will be probably excluded in the missing data stage . (31) total_cu_tl<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt31<- boxplot(loans$total_cu_tl)

head((plt31$out),70)
length(plt31$out)
sum(loans$total_cu_tl<100000,na.rm=TRUE)<jupyter_output><empty_output><jupyter_text>this variable contains NA almost for all loans and will be probably excluded in the missing data stage . (32) inq_last_12m<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt32<- boxplot(loans$inq_last_12m)

head((plt32$out),70)
length(plt32$out)
sum(loans$inq_last_12m<100,na.rm=TRUE)<jupyter_output><empty_output><jupyter_text>this variable contains NA almost for all loans and will be probably excluded in the missing data stage . (33)acc_ratio<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt33<- boxplot(loans$acc_ratio)

head((plt33$out),70)
length(plt33$out)
sum(loans$acc_ratio<1,na.rm=TRUE)
sum(loans$acc_ratio==1,na.rm=TRUE)
sum(loans$acc_ratio>1,na.rm=TRUE)<jupyter_output><empty_output><jupyter_text>We found two people with more open accounts than total accounts, wich by definition is not possible.<jupyter_code>Lmax<-loans[loans$acc_ratio>1,]
Lmax[!is.na(Lmax$total_acc),]<jupyter_output><empty_output><jupyter_text>This two loans have the same issue date, what makes it more suspicious.For this reason we decided to replace this values with NA.<jupyter_code>loans$total_acc [(loans$acc_ratio>1)]<-NA
loans$open_acc[(loans$acc_ratio>1)]<-NA
loans$acc_ratio[(loans$acc_ratio>1)]<-NA<jupyter_output><empty_output><jupyter_text>(34)credit_hist [credit_hist_log, credit_hist_rt]<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt34<- boxplot(loans$credit_hist)

head((plt34$out),70)
length(plt34$out)
hist(loans$credit_hist)
hist(loans$credit_hist^0.5)
hist(log(loans$credit_hist))

plt34<- boxplot(loans$credit_hist^0.5)

length(plt34$out)
plt34<- boxplot(log(loans$credit_hist))

length(plt34$out)<jupyter_output><empty_output><jupyter_text>The outliers number did decreesed a bit, as consequence of both the root transformation and the log transformation. (35)poverty_rate_p<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt35<- boxplot(loans$poverty_rate_p)

head((plt35$out),70)
length(plt35$out)<jupyter_output><empty_output><jupyter_text>This variable is taken from official data of each country each year. When for each year and country varies it is the same.In addition, it can be seen that most of the outliers are located near the outliers boarder line.  (36) collections_12_mths_ex_med<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt36<- boxplot(loans$collections_12_mths_ex_med)

head((plt36$out),70)
length(plt36$out)
sum(is.na(loans$collections_12_mths_ex_med))<jupyter_output><empty_output><jupyter_text>Most of the values of this variable is 1 or 0, as we can see from the new variable we created at the feature engineering notebook<jupyter_code>summary(loans$cat_collections_12_mths_ex_med)<jupyter_output><empty_output><jupyter_text>(37) pub_rec<jupyter_code>options(repr.plot.width = 4, repr.plot.height = 4)
plt37<- boxplot(loans$pub_rec)

head((plt37$out),70)
length(plt37$out)
sum(is.na(loans$pub_rec))<jupyter_output><empty_output><jupyter_text>Most of the values of this variable is 1 or 0, as we can see from the new variable we created at the feature engineering notebook<jupyter_code>summary(loans$cat_pub_rec)<jupyter_output><empty_output><jupyter_text>Multivariate Outlier AnalysisFor multivariate outlier detection we can use DBSCAN. This algorithm search for aggregation of points that are combined and leave out of them the outlier points.<jupyter_code>##sample 
k<-loans

k_num<-NULL
k_cat<-NULL
k_num<-k[num_vars]
k_cat<-k[,cat_vars]<jupyter_output><empty_output><jupyter_text>preparing the data  numerics variables deminishing missingness<jupyter_code>sum(is.na(k_num),na.rm=TRUE)
ind <- is.na(k_num)
k_num[ind] <- rep(9999999, sum(ind))

head(k_num)<jupyter_output><empty_output><jupyter_text>scaling<jupyter_code>k.norm<-scale(k_num)
k.norm<-k.norm[, colSums(is.nan(k.norm)) != nrow(k.norm)]
head(k.norm)<jupyter_output><empty_output><jupyter_text>categorical variables get Dummys<jupyter_code>k_dummy<-createDummyFeatures(k[,cat_vars], cols = cat_vars)
k_dummy[is.na(k_dummy)]<-0.4116079
sum(is.na(k_dummy))
k_bin_vars<-k[,bin_vars]

k_bin_vars[is.na(k_bin_vars)]<- -0.4116079
k_data<-cbind(k.norm,k_dummy,k_bin_vars)
dim(k_data)
head(k_data)<jupyter_output><empty_output><jupyter_text>PCA visualisation<jupyter_code>#sample data 
set.seed(123)
k_data_semp<- k_data[sample(nrow(k_data), 10000), ]
#distance_matrix <- as.matrix(dist(scale(k_data_semp)))
#pca <-prcomp(distance_matrix)
#saveRDS(object =pca ,file = "PCA.rds" )
pca <- readRDS(paste0(getwd(),"/Models/PCA.rds"))
embedding <- data.table(pca$x[, 1:2])
embedding[, loans := rownames(k)]
ggplot(embedding, aes(x = PC1, y = PC2)) +
    geom_point(size = 2, colour = "steelblue", alpha = 0.3) +
    geom_text(aes(label = loans), check_overlap = TRUE) +
    theme_minimal()

#:3<jupyter_output>Warning message in `[.data.table`(embedding, , `:=`(loans, rownames(k))):
"Supplied 255720 items to be assigned to 10000 items of column 'loans' (245720 unused)"<jupyter_text>It is nice to have a look on some of the far loans.<jupyter_code>k_data[c(5852,740,8426,1621,2821),]<jupyter_output><empty_output><jupyter_text>finding outliers with dbscan model To determine eps value for the dbscan execution we will check the distances of our data.We will use knndist algorithm on the sample to check the mean distances in our data<jupyter_code>if(!require(knndist)){install.packages("knndist"); require(knndist)}
knnD<- kNNdist(k_data_semp, k=9, search="kd")
apply(knnD,2,min)
apply(knnD,2,max)
apply(knnD,2,mean)<jupyter_output><empty_output><jupyter_text>We see that the mean of distances for the knn are around 3.3 so we will determine the eps for DBSCAN as 4<jupyter_code>## dbscan clustering
#mod <-dbscan(k_data, eps = 4 ,minPts =1)
#saveRDS(mod, "db_model.rds")
mod <- readRDS(paste0(getwd(),"/Models/db_model.rds"))
table(mod$cluster)
head(k_data)
k_data$cluster<-as.vector(mod$cluster)
##sample with clusters
set.seed(123)
k_data_semp<- k_data[sample(nrow(k_data), 10000), ]

embedding[, DClusters := k_data_semp$cluster]
options(repr.plot.width=10, repr.plot.height=10  )
ggplot(embedding, aes(x = PC1, y = PC2)) +
    geom_point(aes(colour = factor(k_data_semp$cluster)), size = 2, alpha = 0.9) +
geom_text(aes(label = loans), check_overlap = TRUE)<jupyter_output><empty_output><jupyter_text>defining the outliers based on dbscan<jupyter_code>table(mod$cluster)[table(mod$cluster)>1]<jupyter_output><empty_output><jupyter_text>We see that except of cluster number 1 all other cluster contains a very low percentage of data therefore we will define outliers the loans from every cluster except of cluster 1<jupyter_code>k_data$is_out<-!(mod$cluster ==1)

k_data_semp$is_out<-!((k_data_semp$cluster ==1))
head(k_data)
sum(k_data$is_out)<jupyter_output><empty_output><jupyter_text>We can see that there is 926 loans that are labeled as outliers, based on DBSCAN clustering and on our labeling.<jupyter_code>k_data_outs<-k_data[k_data$is_out,]
k_outs<-k_data[k_data$is_out,]
k_data_in<-k_data[!k_data$is_out,]
k_in<-k_data[!k_data$is_out,]
#embedding[, DClusters := mod$k_data_semp]
ggplot(embedding, aes(x = PC1, y = PC2)) +
    geom_point(aes(colour = k_data_semp$is_out), size = 2, alpha = 0.8) 
#remove the support tag
k_data$cluster<-NULL<jupyter_output><empty_output><jupyter_text>It is nice to have a look on some of the outliers details<jupyter_code>head(k[k_data$is_out,])<jupyter_output><empty_output><jupyter_text>To drop or not to drop?we checked if there is a relation between the "defaulted" variable, which is our outcome variable, to the is_out variable. Meaning if there is a correlation between the outliers and the outcome variable<jupyter_code>##chi-sqared test
chisq.test(k_data$is_out,k$default, correct=F )<jupyter_output><empty_output><jupyter_text>There is no sagnificant correlation beween the is_out variable and the default variable. but it is not so far from being significant. second we checked if the relationship between the defaulted variable and the outcome variable did changed as a consequance of the outliers.<jupyter_code>#model <- glm(default.1~.,family=binomial,data=k_data_in)
#saveRDS(object = model, file = "log_data_in.rds")
model_in<-readRDS(file = paste0(getwd(),"/Models/log_data_in.rds"))
head(coef(summary(model_in)))
print(paste0("There is ",sum(coef(summary(model_in))[,4]<0.05)," significant variables"))<jupyter_output><empty_output><jupyter_text>we can see that there is no variables with significant correlation with the outcome variable, in the data set with no outliers<jupyter_code>#model <- glm(default.1~.,family=binomial,data=k_data)
#saveRDS(object = model, file = "log_data_with_outs.rds")
model_with_out<-readRDS(file = paste0(getwd(),"/Models/log_data_with_outs.rds"))
head(coef(summary(model_with_out)))
print(paste0("There is ",sum(coef(summary(model_with_out))[,4]<0.05)," significant variables"))<jupyter_output><empty_output><jupyter_text>We can see that there is no variables with significant correlation with the outcome variable, in the whole data set (data set include the outliers), therefore we can conclude that the outliers didn't created any correlation between the outcome variable to the other variables.<jupyter_code>#model <- glm(default.1~.,family=binomial,data=k_data_outs)
#saveRDS(object = model, file = "log_data_out.rds")
model_out<-readRDS(file = paste0(getwd(),"/Models/log_data_out.rds"))
head(coef(summary(model_out)))
print(paste0("There is ",sum(coef(summary(model_out))[,4]<0.05)," significant variables"))<jupyter_output><empty_output><jupyter_text>we  also can see that there is no variables with significant correlation with the outcome variable, in the data set with just the outliers For this reasons, we decided to exclude the outliers from the dataset<jupyter_code>dim(k)
sum(k_data$is_out)
k_ex<-k[!k_data$is_out,]
dim(k_ex)
loans<-k_ex
##saving table
writeSQL(loans, "loans_dataset_outliers")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11929226880597835, 'fraction_numerical': 0.03662289136765756, 'mean_word_length': 6.844612068965517, 'pattern_counts': {'":': 0, '<': 497, '<?xml version=': 0, '>': 349, 'https://': 5, 'lorem ipsum': 0, 'www.': 4, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 3, 'word_list_counts': {'cursed_substrings.json': 10, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '82', 'dir': 'jupyter-structured-clean-dedup', 'id': '165994', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 14490, 'n_tokens_neox': 13069, 'n_words': 3038, 'path': 'notebooks/4-Outlier-Treatment.ipynb', 'repo_name': 'kobiburkis/LendingClubAnalysis', 'size': '36398'}