R Packages
we gonna use dplyr, ggplot2, plotly, readxl and knitr in this example.
Functions that were used:
read_excel(): for read excel files.
filter(): in order to make subsets of our data.
mutate(): To create new variables.
as.factor(): To convert a variable in factor.
glimpse similar to str(): applied to a dataframe.
group_by(): takes an existing tbl and converts it into a grouped tbl where operations are performed “by group”.
summarise() o summarize(): creates a new data frame that will have one or more rows for each combination of grouping variables.
n(): used from within summarise, mutate and filter.
Import Data
datos<- read_excel(path = "/Users/Juan Alfredo.DESKTOP-ETJ31JC/Documents/Universidad/Termino 2020 2S/R Samples/Pagina/credit.xlsx", sheet = 2)Explore Data
glimpse(datos)## Rows: 30,548
## Columns: 25
## $ Sucursal <chr> "SUCURSAL MAYOR", "SATELITE 1", "SUCURSAL MAYO~
## $ `Nombre Agencia` <chr> "AGENCIA 6", "AGENCIA 7", "AGENCIA 1", "AGENCI~
## $ `Cod Operación` <chr> "00711", "00373", "01078", "00598", "00967", "~
## $ `No Cliente` <chr> "00562009438-9", "001227008773-1", "0036700963~
## $ Clase <chr> "NORMAL", "REFINANCIADO", "NORMAL", "NORMAL", ~
## $ `Tipo Producto` <chr> "Cartera", "Cartera", "Cartera", "Cartera", "C~
## $ `Tipo Crédito` <chr> "MICROEMPRESA", "COMERCIALES", "CONSUMO", "CON~
## $ `Tipo Garantía` <chr> "GARANTE PERSONAL", "HIPOTECARIA", "GARANTE PE~
## $ Garantía <dbl> NA, 606687.97, NA, NA, NA, NA, 28454.39, NA, N~
## $ `Monto Original` <dbl> 8300.00, 327816.50, 15100.00, 5500.00, 10300.0~
## $ `valor en mora` <dbl> 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00~
## $ `saldo capital` <dbl> 7085.65, 177825.56, 15100.00, 3410.21, 9552.40~
## $ `Días Mora` <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 20, 0, 0, ~
## $ `Provision Constituida` <dbl> 70.86, 2667.38, 151.00, 34.10, 95.52, 237.72, ~
## $ `Sector Economico` <chr> "COMERCIO AL POR MAYOR Y AL POR MENOR; REPARAC~
## $ Actividad <chr> "COMERCIO AL POR MENOR DE OTROS PRODUCTOS N.C.~
## $ Corte <dttm> 2019-09-30, 2019-09-30, 2019-09-30, 2019-09-3~
## $ ESTADO <chr> "POR VENCER", "POR VENCER", "POR VENCER", "POR~
## $ CALIFICACION <chr> "A1", "A1", "A1", "A1", "A1", "A1", "A1", "A1"~
## $ PD <dbl> 0.0707, 0.1088, 0.0848, 0.1105, 0.0851, 0.1016~
## $ `Rango de la calif` <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 4, 1, 1, 1~
## $ `Estado Actual` <chr> "NO DEFAULT", "NO DEFAULT", "NO DEFAULT", "NO ~
## $ `vencido US$` <dbl> 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00~
## $ `Improductivo us$` <dbl> 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00~
## $ `Categoria De cliente` <chr> "buen cliente", "buen cliente", "buen cliente"~Descriptive Statistic
summary(datos)## Sucursal Nombre Agencia Cod Operación No Cliente
## Length:30548 Length:30548 Length:30548 Length:30548
## Class :character Class :character Class :character Class :character
## Mode :character Mode :character Mode :character Mode :character
##
##
##
##
## Clase Tipo Producto Tipo Crédito Tipo Garantía
## Length:30548 Length:30548 Length:30548 Length:30548
## Class :character Class :character Class :character Class :character
## Mode :character Mode :character Mode :character Mode :character
##
##
##
##
## Garantía Monto Original valor en mora saldo capital
## Min. : 888.8 Min. : 0 Min. : 0.0 Min. : 0.1
## 1st Qu.: 10005.9 1st Qu.: 5200 1st Qu.: 0.0 1st Qu.: 3233.6
## Median : 88489.6 Median : 11000 Median : 0.0 Median : 7146.4
## Mean : 173127.9 Mean : 24801 Mean : 310.7 Mean : 16941.1
## 3rd Qu.: 169568.5 3rd Qu.: 24385 3rd Qu.: 0.0 3rd Qu.: 15398.4
## Max. :2265106.5 Max. :770000 Max. :160222.0 Max. :715000.0
## NA's :27464 NA's :85
## Días Mora Provision Constituida Sector Economico Actividad
## Min. : 0.00 Min. : 0.00 Length:30548 Length:30548
## 1st Qu.: 0.00 1st Qu.: 36.74 Class :character Class :character
## Median : 0.00 Median : 89.17 Mode :character Mode :character
## Mean : 19.32 Mean : 474.54
## 3rd Qu.: 0.00 3rd Qu.: 243.50
## Max. :1202.00 Max. :45869.47
##
## Corte ESTADO CALIFICACION
## Min. :2019-09-30 00:00:00 Length:30548 Length:30548
## 1st Qu.:2019-12-31 00:00:00 Class :character Class :character
## Median :2020-03-31 00:00:00 Mode :character Mode :character
## Mean :2020-04-06 22:53:26
## 3rd Qu.:2020-06-30 00:00:00
## Max. :2020-09-30 00:00:00
##
## PD Rango de la calif Estado Actual vencido US$
## Min. :0.0011 Min. :1.000 Length:30548 Min. : 0.0
## 1st Qu.:0.0800 1st Qu.:1.000 Class :character 1st Qu.: 0.0
## Median :0.0984 Median :1.000 Mode :character Median : 0.0
## Mean :0.1828 Mean :1.702 Mean : 246.3
## 3rd Qu.:0.1179 3rd Qu.:1.000 3rd Qu.: 0.0
## Max. :0.9801 Max. :9.000 Max. :160222.0
##
## Improductivo us$ Categoria De cliente
## Min. : 0 Length:30548
## 1st Qu.: 0 Class :character
## Median : 0 Mode :character
## Mean : 1179
## 3rd Qu.: 0
## Max. :230544
## Handle missing values NA
dt<- datos %>% filter(! is.na(.)) In this way we can’t drop Na’s because the rows have values.
dt<- datos %>% filter( `Tipo Producto`!="Sobregiro") If we filter by “Sobregiro” we drop the NA’s in this “case”.
Group By Class
cl<- dt %>% group_by(Clase) %>% mutate(Clase = as.factor(Clase)) %>% summarise(count= n())kable(cl)| Clase | count |
|---|---|
| EN FIDEICOMISO | 584 |
| LEGAL (COBRO JUDICIAL) | 2 |
| NORMAL | 29682 |
| REESTRUCTURADA | 26 |
| REFINANCIADO | 72 |
Group by Parent & branch bank
pb<-dt %>% group_by(Sucursal) %>%
mutate(`Tipo Crédito` = as.factor(`Tipo Crédito`)) %>% summarise(mean_mora=mean(`Días Mora`))kable(pb)| Sucursal | mean_mora |
|---|---|
| SATELITE 1 | 14.38060 |
| SATELITE 2 | 22.60097 |
| SUCURSAL MAYOR | 20.70807 |
Group by Credit types and Branches bank
cb<- dt %>% group_by(Sucursal,`Tipo Crédito`) %>% summarise(across(c("Monto Original","Días Mora"), ~ mean(.x, na.rm = TRUE)))## `summarise()` has grouped output by 'Sucursal'. You can override using the `.groups` argument.kable(cb)| Sucursal | Tipo Crédito | Monto Original | Días Mora |
|---|---|---|---|
| SATELITE 1 | COMERCIALES | 121996.719 | 18.87776 |
| SATELITE 1 | CONSUMO | 11816.921 | 12.58333 |
| SATELITE 1 | MICROEMPRESA | 7102.309 | 16.40196 |
| SATELITE 1 | VIVIENDA | 50573.942 | 16.11294 |
| SATELITE 2 | COMERCIALES | 79599.508 | 33.77559 |
| SATELITE 2 | CONSUMO | 11684.434 | 17.67924 |
| SATELITE 2 | MICROEMPRESA | 7905.255 | 40.43750 |
| SATELITE 2 | VIVIENDA | 59141.207 | 22.19106 |
| SUCURSAL MAYOR | COMERCIALES | 57738.304 | 38.58498 |
| SUCURSAL MAYOR | CONSUMO | 11938.943 | 10.50350 |
| SUCURSAL MAYOR | MICROEMPRESA | 9138.624 | 43.81461 |
| SUCURSAL MAYOR | VIVIENDA | 55759.853 | 34.32609 |
Average default by days
tb1<- dt %>% group_by(`Tipo Crédito`, CALIFICACION) %>% mutate(`Tipo Crédito` = as.factor(`Tipo Crédito`), CALIFICACION = (as.factor(CALIFICACION))) %>% summarise(count = n(),mean_mora=mean(`Días Mora`) )Table
kable(tb1)| Tipo Crédito | CALIFICACION | count | mean_mora |
|---|---|---|---|
| COMERCIALES | A1 | 3242 | 0.000000 |
| COMERCIALES | A2 | 225 | 6.640000 |
| COMERCIALES | A3 | 136 | 24.485294 |
| COMERCIALES | B1 | 179 | 41.418994 |
| COMERCIALES | B2 | 105 | 70.971429 |
| COMERCIALES | C1 | 48 | 100.020833 |
| COMERCIALES | C2 | 34 | 146.764706 |
| COMERCIALES | D | 51 | 277.333333 |
| COMERCIALES | E | 106 | 865.018868 |
| CONSUMO | A1 | 16376 | 0.000000 |
| CONSUMO | A2 | 468 | 4.393162 |
| CONSUMO | A3 | 388 | 12.134021 |
| CONSUMO | B1 | 613 | 23.406199 |
| CONSUMO | B2 | 306 | 37.745098 |
| CONSUMO | C1 | 315 | 56.625397 |
| CONSUMO | C2 | 141 | 80.964539 |
| CONSUMO | D | 125 | 103.296000 |
| CONSUMO | E | 478 | 327.916318 |
| MICROEMPRESA | A1 | 2938 | 0.000000 |
| MICROEMPRESA | A2 | 60 | 5.100000 |
| MICROEMPRESA | A3 | 118 | 12.491525 |
| MICROEMPRESA | B1 | 174 | 23.488506 |
| MICROEMPRESA | B2 | 80 | 37.512500 |
| MICROEMPRESA | C1 | 112 | 56.633929 |
| MICROEMPRESA | C2 | 54 | 81.240741 |
| MICROEMPRESA | D | 42 | 106.238095 |
| MICROEMPRESA | E | 240 | 427.583333 |
| VIVIENDA | A1 | 2252 | 0.000000 |
| VIVIENDA | A2 | 467 | 11.929336 |
| VIVIENDA | A3 | 220 | 40.527273 |
| VIVIENDA | B1 | 129 | 78.023256 |
| VIVIENDA | B2 | 36 | 153.500000 |
| VIVIENDA | C1 | 22 | 196.227273 |
| VIVIENDA | C2 | 16 | 235.937500 |
| VIVIENDA | D | 5 | 313.200000 |
| VIVIENDA | E | 65 | 772.261539 |
Data visualization
ggplotly(p1)ggplotly(p2)