1-Processing.Rmd

---
title: "II-1. Ïîäãîòîâêà"
output: html_notebook
---

- Óïàêîâêà ôàéëîâ ïî áóìàãàì â åäèíûé ÷èñòûé ôàéë
- Ãåíåðàöèÿ ïðèçíàêîâ è èõ óïîðÿäî÷èâàíèå

×èñëà áåç ýêñïîíåíöèàëüíîãî ïðåäñòàâëåíèÿ
```{r}
options(scipen=999)
```

Áèáëèîòåêè è ïîëüçîâàòåëüñêèå ôóíêöèè
```{r include=FALSE}
source("Libraries.R")
source("Functions.R")
```





## 1. Äàííûå

Çàãðóçèòü äàííûå ïî âñåì áóìàãàì
```{r} 
path_all <- "Data/all/"
files_all_names <- list.files(path = path_all, pattern = "*.csv")
files_all_path <- sapply(files_all_names, {function(x) paste(path_all, x, sep = "")})
length(files_all_path)
```


Ñöåïëÿåì è ÷èñòèì ïàìÿòü
```{r}
d <- as_tibble(
  do.call(rbind, lapply(files_all_path, read_csv_ru))
)
gc();
```


Ïåðåèìåíîâûâàåì ïðàâèëüíî + ñíîñèì 

```{r}
colnames(d)[colnames(d)=="RTNG_SB_TYPE"] <- "RISK_SB_TYPE"
colnames(d)[colnames(d)=="RTNG_MI_TYPE"] <- "RISK_MI_TYPE"

drop_cols <- c(
  "RTNG_MA_NUM","RTNG_SB_NUM","RTNG_MI_NUM",
  "RTNG_S_NUM","RTNG_M_NUM","RTNG_F_NUM"
)

d <- d[, !(names(d) %in% drop_cols)]
gc();
```

Âûãðóçêà áîëüøîãî ôàéëà
```{r}
# write.table(d, "Data/clean_final_big.csv", sep = ";", dec = ",", row.names = F, quote = F)
# d <- read.csv2("Data/clean_final_big.csv");
```


Îïèñàíèå ïîëåé
```{r}
# Field description
d_head <- read.xlsx(file = "info.xlsx", sheetIndex = 1, encoding = "UTF-8")
labelz <- d_head[,1:2]
labelz
```


## 2. Ïðåîáðàçîâàíèÿ



Äàòû â äàòû
```{r}
d$DATE <- as.Date(d$DATE, format="%d/%m/%Y");
d$DATE_TR <- as.Date(d$DATE_TR, format="%d/%m/%Y");
d$DATE_M <- as.Date(d$DATE_M, format="%d/%m/%Y");
d$DATE_C <- as.Date(d$DATE_C, format="%d/%m/%Y");
d$DATE_O <- as.Date(d$DATE_O, format="%d/%m/%Y");
```



Òåêñò â ôàêòîðû
```{r}
d$INN <- as.factor(d$INN)
d$CBID <- as.factor(d$CBID)
d$CBIDI <- as.factor(d$CBIDI)

d$RISK_MA <- as.factor(d$RISK_MA)
d$RISK_SB <- as.factor(d$RISK_SB)
d$RISK_MI <- as.factor(d$RISK_MI)
```

```{r}
summary(d$RISK_MA)
summary(d$RISK_SB)
summary(d$RISK_MI)
```


Ðèñêè ïî ïîðÿäêó
```{r}
risk_type_levels <- c(" 0-2 ", " 3-4 ", " 5-6 ", " 7-9 ", "10", "")

d$RISK_MA_TYPE <- factor(d$RISK_MA_TYPE, levels = risk_type_levels)
d$RISK_SB_TYPE <- factor(d$RISK_SB_TYPE, levels = risk_type_levels)
d$RISK_MI_TYPE <- factor(d$RISK_MI_TYPE, levels = risk_type_levels)
```



```{r}
summary(d$RISK_MA_TYPE)
summary(d$RISK_SB_TYPE)
summary(d$RISK_MI_TYPE)
```



Ðåéòèíãè ïî ïîðÿäêó
```{r}
rtng_levels <- c("NR","D","C","CC","CCC-","CCC","CCC+","B-","B","B+","BB-","BB","BB+","BBB-","BBB","BBB+","A", "")
d$RTNG_MA <- factor(d$RTNG_MA, levels = rtng_levels)
d$RTNG_SB <- factor(d$RTNG_SB, levels = rtng_levels)
d$RTNG_MI <- factor(d$RTNG_MI, levels = rtng_levels)
```


```{r}
summary(d$RTNG_SB)
```

Âûÿâëÿåì íåñîñòûêîâêè ðåéòèíãà è ðèñêà
```{r}
d[!is.na(d$RISK_MA_TYPE) & !is.na(d$RTNG_MA) & d$RISK_MA_TYPE=="" & d$RTNG_MA!="" ,]$PAPER %>% unique
d[!is.na(d$RISK_SB_TYPE) & !is.na(d$RTNG_SB) & d$RISK_SB_TYPE=="" & d$RTNG_SB!="" ,]$PAPER %>% unique
d[!is.na(d$RISK_MI_TYPE) & !is.na(d$RTNG_MI) & d$RISK_MI_TYPE=="" & d$RTNG_MI!="" ,]$PAPER %>% unique

d[!is.na(d$RISK_MA_TYPE) & !is.na(d$RTNG_MA) & d$RISK_MA_TYPE=="" & d$RTNG_MA!="" ,]$RTNG_MA %>% unique
d[!is.na(d$RISK_SB_TYPE) & !is.na(d$RTNG_SB) & d$RISK_SB_TYPE=="" & d$RTNG_SB!="" ,]$RTNG_SB %>% unique
d[!is.na(d$RISK_MI_TYPE) & !is.na(d$RTNG_MI) & d$RISK_MI_TYPE=="" & d$RTNG_MI!="" ,]$RTNG_MI %>% unique
```




Âîññòàíàâëèâàåì ïðîïóñêè
```{r}

  d[!is.na(d$RISK_MA_TYPE) & !is.na(d$RTNG_MA) &  d$RISK_MA_TYPE=="" & d$RTNG_MA == "BB-",]$RISK_MA = 6
  d[!is.na(d$RISK_MA_TYPE) & !is.na(d$RTNG_MA) &  d$RISK_MA_TYPE=="" & d$RTNG_MA == "BB-",]$RISK_MA_TYPE = " 5-6 "

  d[!is.na(d$RISK_MA_TYPE) & !is.na(d$RTNG_MA) &  d$RISK_MA_TYPE=="" & d$RTNG_MA == "B",]$RISK_MA = 4
  d[!is.na(d$RISK_MA_TYPE) & !is.na(d$RTNG_MA) &  d$RISK_MA_TYPE=="" & d$RTNG_MA == "B",]$RISK_MA_TYPE = " 3-4 "

  d[!is.na(d$RISK_MA_TYPE) & !is.na(d$RTNG_MA) &  d$RISK_MA_TYPE=="" & d$RTNG_MA == "B+",]$RISK_MA = 5
  d[!is.na(d$RISK_MA_TYPE) & !is.na(d$RTNG_MA) &  d$RISK_MA_TYPE=="" & d$RTNG_MA == "B+",]$RISK_MA_TYPE = " 5-6 "

  
  d[!is.na(d$RISK_SB_TYPE) & !is.na(d$RTNG_SB) &  d$RISK_SB_TYPE=="" & d$RTNG_SB == "BB-",]$RISK_SB = 6
  d[!is.na(d$RISK_SB_TYPE) & !is.na(d$RTNG_SB) &  d$RISK_SB_TYPE=="" & d$RTNG_SB == "BB-",]$RISK_SB_TYPE = " 5-6 "

  d[!is.na(d$RISK_SB_TYPE) & !is.na(d$RTNG_SB) &  d$RISK_SB_TYPE=="" & d$RTNG_SB == "B",]$RISK_SB = 4
  d[!is.na(d$RISK_SB_TYPE) & !is.na(d$RTNG_SB) &  d$RISK_SB_TYPE=="" & d$RTNG_SB == "B",]$RISK_SB_TYPE = " 3-4 "

  d[!is.na(d$RISK_SB_TYPE) & !is.na(d$RTNG_SB) &  d$RISK_SB_TYPE=="" & d$RTNG_SB == "B-",]$RISK_SB = 3
  d[!is.na(d$RISK_SB_TYPE) & !is.na(d$RTNG_SB) &  d$RISK_SB_TYPE=="" & d$RTNG_SB == "B-",]$RISK_SB_TYPE = " 3-4 "


  d[!is.na(d$RISK_MI_TYPE) & !is.na(d$RTNG_MI) &  d$RISK_MI_TYPE=="" & d$RTNG_MI == "BB-",]$RISK_MI = 6
  d[!is.na(d$RISK_MI_TYPE) & !is.na(d$RTNG_MI) &  d$RISK_MI_TYPE=="" & d$RTNG_MI == "BB-",]$RISK_MI_TYPE = " 5-6 "

  d[!is.na(d$RISK_MI_TYPE) & !is.na(d$RTNG_MI) &  d$RISK_MI_TYPE=="" & d$RTNG_MI == "B",]$RISK_MI = 4
  d[!is.na(d$RISK_MI_TYPE) & !is.na(d$RTNG_MI) &  d$RISK_MI_TYPE=="" & d$RTNG_MI == "B",]$RISK_MI_TYPE = " 3-4 "

  d[!is.na(d$RISK_MI_TYPE) & !is.na(d$RTNG_MI) &  d$RISK_MI_TYPE=="" & d$RTNG_MI == "B-",]$RISK_MI = 3
  d[!is.na(d$RISK_MI_TYPE) & !is.na(d$RTNG_MI) &  d$RISK_MI_TYPE=="" & d$RTNG_MI == "B-",]$RISK_MI_TYPE = " 3-4 "

```

Ïðîâåðêà
```{r}
d[!is.na(d$RISK_MA_TYPE) & !is.na(d$RTNG_MA) & d$RISK_MA_TYPE=="" & d$RTNG_MA!="" ,]$PAPER %>% unique
d[!is.na(d$RISK_SB_TYPE) & !is.na(d$RTNG_SB) & d$RISK_SB_TYPE=="" & d$RTNG_SB!="" ,]$PAPER %>% unique
d[!is.na(d$RISK_MI_TYPE) & !is.na(d$RTNG_MI) & d$RISK_MI_TYPE=="" & d$RTNG_MI!="" ,]$PAPER %>% unique

d[!is.na(d$RISK_MA_TYPE) & !is.na(d$RTNG_MA) & d$RISK_MA_TYPE=="" & d$RTNG_MA!="" ,]$RTNG_MA %>% unique
d[!is.na(d$RISK_SB_TYPE) & !is.na(d$RTNG_SB) & d$RISK_SB_TYPE=="" & d$RTNG_SB!="" ,]$RTNG_SB %>% unique
d[!is.na(d$RISK_MI_TYPE) & !is.na(d$RTNG_MI) & d$RISK_MI_TYPE=="" & d$RTNG_MI!="" ,]$RTNG_MI %>% unique
```


```{r}
summary(d$RTNG_MA)
summary(d$RTNG_SB)
summary(d$RTNG_MI)
```



Âðåìÿ ïî ïîðÿäêó
```{r}
t_levels <- c(" 0-1 "," 1-3 "," 3-5 ", " 5+ ")
d$T_TYPE <- factor(d$T_TYPE, levels = t_levels)
d$T_M_TYPE <- factor(d$T_M_TYPE, levels = t_levels)
summary(d$T_TYPE)
```

Îòñòàâøèåñÿ òåêñòîâûå - êàê ôàêòîðû
```{r}
chr_names <- names(d)[which(sapply(d, is.character))]
d[chr_names] <- lapply(d[chr_names], factor)
```





Ñ÷èòàëîñü è íåò çíà÷åíèÿ => íåëèêâèä
Îñòàëüíîå â NA
```{r}
d[((!is.na(d$LIQ_LABEL) & d$LIQ_LABEL=="")) & d$FLAG_NOCALC==0,]$LIQ_LABEL = "N"

d[is.na(d$LIQ_LABEL) & d$FLAG_NOCALC==0,]$LIQ_LABEL = "N"

d[((!is.na(d$LIQ_LABEL) & d$LIQ_LABEL=="")),]$LIQ_LABEL = NA

d$LIQ_LABEL <- factor(d$LIQ_LABEL)

summary(d$LIQ_LABEL)
```


Îïöèîíû: îòñóòñòâèå îïöèîíà - â NA
```{r}
d[!is.na(d$OPTION) & d$OPTION=="",]$OPTION = NA
d$OPTION <- factor(d$OPTION)

```

```{r}
summary(d$OPTION)
```



Ãîñóäàðñòâåííûì îáëèãàöèÿì ïðèñâîèì îöåíêó "10"
```{r}

d[d$ISSUER_TYPE=="Ãîñóäàðñòâåííûå",]$RISK_SB_TYPE = "10"
d[d$ISSUER_TYPE=="Ãîñóäàðñòâåííûå",]$RISK_MA_TYPE = "10"
d[d$ISSUER_TYPE=="Ãîñóäàðñòâåííûå",]$RISK_MI_TYPE = "10"

```


Ðåéòèíã â ÷èñëî
```{r}
#d$RTNG_SB_NUM = max(as.numeric(d$RTNG_SB),na.rm=T)-(as.numeric(d$RTNG_SB))
dt$RTNG_SB_NUM = max(as.numeric(dt$RTNG_SB),na.rm=T)-(as.numeric(dt$RTNG_SB))
```



## 3. Íîâûå ïåðåìåííûå

VAR äîõîäíîñòè ê ïîãàøåíèþ
```{r}
d$VAR_Y <- -(d$VAR)/(d$DUR)
d$VAR_Y_DC = 100*((d$DUR-sqrt(d$DUR^2+2*d$CNV*d$VAR/100))/d$CNV)
```
```{r}
cbid_list <- d$CBID %>% unique
```




```{r}
HORIZON = 260
```


```{r}
d_dy <- list()

for (id in cbid_list) {
  d %>% filter(CBID == id) %>% dplyr::select(DATE_TR, CBID, D_Y, FLAG_NOCALC)->d_i
  if(nrow(d_i) < HORIZON){
  d_i$D_Y_VAR = rep(NA, nrow(d_i))
  }
  else {
  d %>% filter(CBID == id) %>% dplyr::select(DATE_TR, CBID, D_Y, FLAG_NOCALC)->d_i
  d_it <- as.xts((d_i$D_Y), order.by=d_i$DATE_TR)
  roll <- rollapply(data = d_it, width = HORIZON, align = "right", fill = NA, FUN = quantile1)
  d_i$D_Y_VAR = roll
  }
  d_i[d_i$FLAG_NOCALC==1,"D_Y_VAR"] = NA
  d_i <- d_i %>% dplyr::select(DATE_TR, CBID, D_Y_VAR)
  d_dy <- rbind(d_dy, d_i)
}
```



```{r}
d$D_Y_VAR = d_dy$D_Y_VAR
```



```{r}
d$ISSUER_GOS = (d$ISSUER_TYPE == "Ãîñóäàðñòâåííûå")
d$RTNG_BMINUS = (d$RTNG_SB == "B-")
```
Êàòåãîðèàëüíûå ïåðåìåííûå
```{r}
cat_vars <- names(d)[which(sapply(d, is.factor))]
d[cat_vars] <- lapply(d[cat_vars], factor)
cat_vars
```
×Èñëåííûå ïåðåìåííûå
```{r}
num_vars <- names(d)[which(sapply(d, is.numeric))]
num_vars
```
Ëîãè÷åñêèå ïåðåìåííûå
```{r}
log_vars <- names(d)[which(sapply(d, is.logical))]
log_vars

```


Âñå ëè ïåðåìåííûå ó÷ëè (+5 äàò)
```{r}
length(num_vars)+length(cat_vars)+length(log_vars)+5 == ncol(d)
```

```{r}
d <- d[d$DATE_TR>="2011-02-11",]
gc();
```


```{r}
# rtng_sb_gos <- d %>% filter(ISSUER_TYPE == "Ãîñóäàðñòâåííûå", RTNG_SB_NUM>0) %>% select(DATE_TR, RTNG_SB_NUM) %>% unique %>% arrange((DATE_TR))
# d$RTNG_SB_GOS = (right_join(rtng_sb_gos, d[,c("DATE_TR", "CBID")]) %>% select(RTNG_SB_NUM))[,] 
# d$RTNG_DIFF = (d$RTNG_SB_NUM - d$RTNG_SB_GOS)
```

## 4. Èòîã


```{r}
head(d)
```


```{r}
tail(d)
```

```{r}
str(d)
```

Çàïèñàòü â csv ôàéë äëÿ BI-ñðåäû (quote = F - áåç êàâû÷åê)
```{r}
#write.table(d, "Data/clean_final.csv", sep = ";", dec = ",", row.names = F, quote = F)
```

## Ðàçíîå

Âûÿâëåíèå ïðîïóñêîâ
```{r}
# col_vec <- c("DATE_TR","RTNG_MA","RTNG_SB","RTNG_MI", "RISK_MA","RISK_SB","RISK_MI","RISK_MA_TYPE","RISK_SB_TYPE","RISK_MI_TYPE")
# d[d$CBID == list_blank_rtng_ncns[[1]],col_vec] %>% View

# list_blank_rtng=d[!is.na(d$RTNG_SB) & d$RTNG_SB == "",]$CBID %>% unique
# list_na_rtng=d[is.na(d$RTNG_SB),]$CBID %>% unique
# 
# diff_na <- list()
# for (i in 1:length(list_na_rtng)) {
#   diff_na[[i]] <- d[d$CBID==list_na_rtng[[i]] & is.na(d$RTNG_SB),] %>% rownames %>% as.numeric %>% as.matrix %>% diff %>% data.frame
#   colnames(diff_na[[i]]) <-"value" 
# }
# 
# 
# 
# not_1_na <- list()
# for (i in 1:length(diff_na)) {
#   not_1_na[[i]] <- (diff_na[[i]][diff_na[[i]]$value!=1,])  
# }
# 
# diff_blank <- list()
# for (i in 1:length(list_blank_rtng)) {
#   diff_blank[[i]] <- d[d$CBID==list_blank_rtng[[i]] & d$RTNG_SB == "",] %>% rownames %>% as.numeric %>% as.matrix %>% diff %>% data.frame
#   colnames(diff_blank[[i]]) <-"value" 
# }
```