library(readxl)
## Warning: package 'readxl' was built under R version 3.4.4
read_excel_allsheets <- function(filename) {
# Zdroj: https://stackoverflow.com/questions/12945687/read-all-worksheets-in-an-excel-workbook-into-an-r-list-with-data-frames
sheets <- readxl::excel_sheets(filename)
x <- lapply(sheets, function(X) readxl::read_excel(filename, sheet = X))
x <- lapply(x, as.data.frame)
names(x) <- sheets
return(x)
}
subory.zoznam <- paste0("data/hist_panel_EURIBOR_",
rep(c("Dec","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov"), 2),
c(rep("_2016",1), rep("_2017", 12), rep("_2018", 11)),
".xls"
)
subory.zoznam
## [1] "data/hist_panel_EURIBOR_Dec_2016.xls"
## [2] "data/hist_panel_EURIBOR_Jan_2017.xls"
## [3] "data/hist_panel_EURIBOR_Feb_2017.xls"
## [4] "data/hist_panel_EURIBOR_Mar_2017.xls"
## [5] "data/hist_panel_EURIBOR_Apr_2017.xls"
## [6] "data/hist_panel_EURIBOR_May_2017.xls"
## [7] "data/hist_panel_EURIBOR_Jun_2017.xls"
## [8] "data/hist_panel_EURIBOR_Jul_2017.xls"
## [9] "data/hist_panel_EURIBOR_Aug_2017.xls"
## [10] "data/hist_panel_EURIBOR_Sep_2017.xls"
## [11] "data/hist_panel_EURIBOR_Oct_2017.xls"
## [12] "data/hist_panel_EURIBOR_Nov_2017.xls"
## [13] "data/hist_panel_EURIBOR_Dec_2017.xls"
## [14] "data/hist_panel_EURIBOR_Jan_2018.xls"
## [15] "data/hist_panel_EURIBOR_Feb_2018.xls"
## [16] "data/hist_panel_EURIBOR_Mar_2018.xls"
## [17] "data/hist_panel_EURIBOR_Apr_2018.xls"
## [18] "data/hist_panel_EURIBOR_May_2018.xls"
## [19] "data/hist_panel_EURIBOR_Jun_2018.xls"
## [20] "data/hist_panel_EURIBOR_Jul_2018.xls"
## [21] "data/hist_panel_EURIBOR_Aug_2018.xls"
## [22] "data/hist_panel_EURIBOR_Sep_2018.xls"
## [23] "data/hist_panel_EURIBOR_Oct_2018.xls"
## [24] "data/hist_panel_EURIBOR_Nov_2018.xls"
df <- data.frame(Bank = character(), Rate = numeric(), Date = character())
for (subor in subory.zoznam){
mysheets <- read_excel_allsheets(subor)
for (i in 1:length(mysheets)){
colnames(mysheets[[i]])[1] <- "Bank"
Date <- names(mysheets)[i]
if (grepl("2016", subor)) Date <- paste0(Date, "_2016")
if (grepl("2017", subor)) Date <- paste0(Date, "_2017")
if (grepl("2018", subor)) Date <- paste0(Date, "_2018")
df <- rbind(df, data.frame(mysheets[[i]]["Bank"],
Rate = unname(mysheets[[i]]["3 Months"]),
Date
)
)
}
}
head(df)
## Bank Rate Date
## 1 BNP-Paribas -0.40 0112_2016
## 2 Banca Monte Dei Paschi Di Siena -0.31 0112_2016
## 3 Banco Bilbao Vizcaya Argentaria -0.31 0112_2016
## 4 Banco Santander -0.32 0112_2016
## 5 Banque et Caisse d'Épargne de l'État -0.32 0112_2016
## 6 Barclays Bank -0.30 0112_2016
library(reshape2)
df2 <- dcast(df, Date ~ Bank, value.var = "Rate" )
head(df2)
## Date Banca Monte Dei Paschi Di Siena
## 1 0112_2016 -0.31
## 2 0212_2016 -0.31
## 3 0512_2016 -0.31
## 4 0612_2016 -0.31
## 5 0712_2016 -0.32
## 6 0812_2016 -0.32
## Banco Bilbao Vizcaya Argentaria Banco Santander
## 1 -0.31 -0.32
## 2 -0.31 -0.32
## 3 -0.31 -0.32
## 4 -0.31 -0.32
## 5 -0.31 -0.32
## 6 -0.32 -0.32
## Banque et Caisse d'?pargne de l'?tat
## 1 NA
## 2 NA
## 3 NA
## 4 NA
## 5 NA
## 6 NA
## Banque et Caisse d'Épargne de l'État Barclays Bank Belfius BNP-Paribas
## 1 -0.32 -0.30 -0.32 -0.4
## 2 -0.32 -0.30 -0.32 -0.4
## 3 -0.32 -0.30 -0.32 -0.4
## 4 -0.32 -0.33 -0.32 -0.4
## 5 -0.32 -0.33 -0.32 -0.4
## 6 -0.32 -0.33 -0.32 -0.4
## Caixa Geral De Dep?sitos Caixa Geral De Depósitos CaixaBank S.A.
## 1 NA -0.29 -0.31
## 2 NA -0.29 -0.31
## 3 NA -0.29 -0.31
## 4 NA -0.29 -0.31
## 5 NA -0.29 -0.31
## 6 NA -0.29 -0.31
## CECABANK Cr?dit Agricole s.a. Crédit Agricole s.a. Deutsche Bank DZ Bank
## 1 -0.31 NA -0.32 -0.17 -0.35
## 2 -0.31 NA -0.32 -0.17 -0.35
## 3 -0.31 NA -0.32 -0.17 -0.35
## 4 -0.31 NA -0.32 -0.17 -0.35
## 5 -0.32 NA -0.32 -0.17 -0.35
## 6 -0.32 NA -0.32 -0.17 -0.35
## HSBC France ING Bank Intesa Sanpaolo National Bank of Greece Natixis
## 1 -0.31 -0.32 -0.31 -0.31 -0.31
## 2 -0.31 -0.32 -0.31 -0.31 -0.31
## 3 -0.31 -0.32 -0.31 -0.31 -0.31
## 4 -0.31 -0.32 -0.31 -0.31 -0.31
## 5 -0.31 -0.32 -0.31 -0.31 -0.31
## 6 -0.31 -0.32 -0.31 -0.31 -0.32
## Soci?t? G?n?rale Société Générale UniCredit
## 1 NA -0.34 -0.3
## 2 NA -0.34 -0.3
## 3 NA -0.33 -0.3
## 4 NA -0.33 -0.3
## 5 NA -0.33 -0.3
## 6 NA -0.33 -0.3
nahradit.na <- function(from, to){
ind <- is.na(df2[,to])
df2[ind, to] <<- df2[ind, from]
}
names(df2)
## [1] "Date"
## [2] "Banca Monte Dei Paschi Di Siena"
## [3] "Banco Bilbao Vizcaya Argentaria"
## [4] "Banco Santander"
## [5] "Banque et Caisse d'?pargne de l'?tat"
## [6] "Banque et Caisse d'Épargne de l'État"
## [7] "Barclays Bank"
## [8] "Belfius"
## [9] "BNP-Paribas"
## [10] "Caixa Geral De Dep?sitos"
## [11] "Caixa Geral De Depósitos"
## [12] "CaixaBank S.A."
## [13] "CECABANK"
## [14] "Cr?dit Agricole s.a."
## [15] "Crédit Agricole s.a."
## [16] "Deutsche Bank"
## [17] "DZ Bank"
## [18] "HSBC France"
## [19] "ING Bank"
## [20] "Intesa Sanpaolo"
## [21] "National Bank of Greece"
## [22] "Natixis"
## [23] "Soci?t? G?n?rale"
## [24] "Société Générale"
## [25] "UniCredit"
nahradit.na(from = 5, to = 6)
nahradit.na(from = 10, to = 11)
nahradit.na(from = 14, to = 15)
nahradit.na(from = 23, to = 24)
df2 <- df2[, -c(1, 5, 10, 14, 23)]
any(is.na(df2))
## [1] FALSE
# PRIPRAVENE DATA :)
head(df2)
## Banca Monte Dei Paschi Di Siena Banco Bilbao Vizcaya Argentaria
## 1 -0.31 -0.31
## 2 -0.31 -0.31
## 3 -0.31 -0.31
## 4 -0.31 -0.31
## 5 -0.32 -0.31
## 6 -0.32 -0.32
## Banco Santander Banque et Caisse d'Épargne de l'État Barclays Bank
## 1 -0.32 -0.32 -0.30
## 2 -0.32 -0.32 -0.30
## 3 -0.32 -0.32 -0.30
## 4 -0.32 -0.32 -0.33
## 5 -0.32 -0.32 -0.33
## 6 -0.32 -0.32 -0.33
## Belfius BNP-Paribas Caixa Geral De Depósitos CaixaBank S.A. CECABANK
## 1 -0.32 -0.4 -0.29 -0.31 -0.31
## 2 -0.32 -0.4 -0.29 -0.31 -0.31
## 3 -0.32 -0.4 -0.29 -0.31 -0.31
## 4 -0.32 -0.4 -0.29 -0.31 -0.31
## 5 -0.32 -0.4 -0.29 -0.31 -0.32
## 6 -0.32 -0.4 -0.29 -0.31 -0.32
## Crédit Agricole s.a. Deutsche Bank DZ Bank HSBC France ING Bank
## 1 -0.32 -0.17 -0.35 -0.31 -0.32
## 2 -0.32 -0.17 -0.35 -0.31 -0.32
## 3 -0.32 -0.17 -0.35 -0.31 -0.32
## 4 -0.32 -0.17 -0.35 -0.31 -0.32
## 5 -0.32 -0.17 -0.35 -0.31 -0.32
## 6 -0.32 -0.17 -0.35 -0.31 -0.32
## Intesa Sanpaolo National Bank of Greece Natixis Société Générale
## 1 -0.31 -0.31 -0.31 -0.34
## 2 -0.31 -0.31 -0.31 -0.34
## 3 -0.31 -0.31 -0.31 -0.33
## 4 -0.31 -0.31 -0.31 -0.33
## 5 -0.31 -0.31 -0.31 -0.33
## 6 -0.31 -0.31 -0.32 -0.33
## UniCredit
## 1 -0.3
## 2 -0.3
## 3 -0.3
## 4 -0.3
## 5 -0.3
## 6 -0.3