Načitanie tej cybnej databazy

data_raw <- read.csv2("ChybnaDatabaza.csv", stringsAsFactors = FALSE)
data_raw <- data_raw %>%
  mutate(across(c(TOBIN.Q, MARKET.CAPITALIZATION, RETURN.ON.ASSETS, 
                  DEBT.TO.ASSET, FIRM.SIZE, SOCIAL.DISCLOSURE.INDEX, 
                  ENVIRONMENTAL.DISCLOSURE.INDEX, GOVERNANCE.DISCLOSURE.INDEX, ESG.INDEX), as.numeric))
head(data_raw) %>% kable(caption = "Ukážka neupravených dát")
Ukážka neupravených dát
YEARS COMPANIES EXCHANGE.SECTOR PRIMARY.BUSINESS TOBIN.Q MARKET.CAPITALIZATION RETURN.ON.ASSETS DEBT.TO.ASSET FIRM.SIZE SOCIAL.DISCLOSURE.INDEX ENVIRONMENTAL.DISCLOSURE.INDEX GOVERNANCE.DISCLOSURE.INDEX ESG.INDEX
2013 Industrial & Medical Gases Nigeria Natural Resources Industrial Gases 1.08 6.44 14.41 36.85 6.46 0.36 NA 0.38 0.25
2014 Industrial & Medical Gases Nigeria Natural Resources Industrial Gases 0.96 6.36 11.19 41.01 6.53 0.29 0 0.38 0.22
2015 Industrial & Medical Gases Nigeria Natural Resources Industrial Gases 0.75 6.20 5.74 34.32 6.51 0.29 0 0.38 0.22
NA Industrial & Medical Gases Nigeria Natural Resources Industrial Gases 0.63 6.17 3.51 40.16 6.56 0.29 0 0.38 0.22
2017 Industrial & Medical Gases Nigeria Natural Resources Industrial Gases 0.60 NA 9.78 43.69 6.63 0.29 0 0.38 0.22
2018 Industrial & Medical Gases Nigeria Natural Resources Industrial Gases 0.70 6.24 13.43 40.72 6.65 0.29 0 0.38 0.22 
na_summary <- colSums(is.na(data_raw))
data.frame(Stlpec = names(na_summary), Pocet_Chybajucich_Hodnot = na_summary) %>%
  kable(caption = "Prehľad chýbajúcich hodnôt podľa premenných")
Prehľad chýbajúcich hodnôt podľa premenných
Stlpec Pocet_Chybajucich_Hodnot
YEARS YEARS 1
COMPANIES COMPANIES 0
EXCHANGE.SECTOR EXCHANGE.SECTOR 0
PRIMARY.BUSINESS PRIMARY.BUSINESS 0
TOBIN.Q TOBIN.Q 0
MARKET.CAPITALIZATION MARKET.CAPITALIZATION 1
RETURN.ON.ASSETS RETURN.ON.ASSETS 0
DEBT.TO.ASSET DEBT.TO.ASSET 1
FIRM.SIZE FIRM.SIZE 1
SOCIAL.DISCLOSURE.INDEX SOCIAL.DISCLOSURE.INDEX 1
ENVIRONMENTAL.DISCLOSURE.INDEX ENVIRONMENTAL.DISCLOSURE.INDEX 1
GOVERNANCE.DISCLOSURE.INDEX GOVERNANCE.DISCLOSURE.INDEX 1
ESG.INDEX ESG.INDEX 0 
data_raw %>%
  slice(1:50) %>%
  mutate(Riadok = row_number()) %>%
  mutate(across(everything(), as.character)) %>%
  pivot_longer(cols = -Riadok, names_to = "Premenna", values_to = "Hodnota") %>%
  mutate(Chyba = is.na(Hodnota)) %>%
  ggplot(aes(x = Premenna, y = Riadok, fill = Chyba)) +
  geom_tile(color = "white", linewidth = 0.2) +
  scale_fill_manual(values = c("FALSE" = "#2ecc71", "TRUE" = "#e74c3c"),
                    labels = c("OK (Prítomná)", "Chýba (NA)")) +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1, size = 8)) +
  labs(title = "Detekcia chýbajúcich hodnôt v databáze",
       x = "Premenná", y = "Číslo riadku", fill = "Stav hodnota")

Čistenie databazy

data_cleaned <- data_raw %>%
  group_by(COMPANIES) %>%
  fill(YEARS, PRIMARY.BUSINESS, .direction = "downup") %>%
  mutate(
    MARKET.CAPITALIZATION = ifelse(is.na(MARKET.CAPITALIZATION), 
                                   mean(MARKET.CAPITALIZATION, na.rm = TRUE), MARKET.CAPITALIZATION),
    DEBT.TO.ASSET = ifelse(is.na(DEBT.TO.ASSET), 
                           mean(DEBT.TO.ASSET, na.rm = TRUE), DEBT.TO.ASSET),
    FIRM.SIZE = ifelse(is.na(FIRM.SIZE), 
                       mean(FIRM.SIZE, na.rm = TRUE), FIRM.SIZE),
    ENVIRONMENTAL.DISCLOSURE.INDEX = ifelse(is.na(ENVIRONMENTAL.DISCLOSURE.INDEX), 
                                            0, ENVIRONMENTAL.DISCLOSURE.INDEX)
  ) %>%
  ungroup()
head(data_cleaned) %>% kable(caption = "Očistená a doplnená databáza")
Očistená a doplnená databáza
YEARS COMPANIES EXCHANGE.SECTOR PRIMARY.BUSINESS TOBIN.Q MARKET.CAPITALIZATION RETURN.ON.ASSETS DEBT.TO.ASSET FIRM.SIZE SOCIAL.DISCLOSURE.INDEX ENVIRONMENTAL.DISCLOSURE.INDEX GOVERNANCE.DISCLOSURE.INDEX ESG.INDEX
2013 Industrial & Medical Gases Nigeria Natural Resources Industrial Gases 1.08 6.440000 14.41 36.85 6.46 0.36 0 0.38 0.25
2014 Industrial & Medical Gases Nigeria Natural Resources Industrial Gases 0.96 6.360000 11.19 41.01 6.53 0.29 0 0.38 0.22
2015 Industrial & Medical Gases Nigeria Natural Resources Industrial Gases 0.75 6.200000 5.74 34.32 6.51 0.29 0 0.38 0.22
2015 Industrial & Medical Gases Nigeria Natural Resources Industrial Gases 0.63 6.170000 3.51 40.16 6.56 0.29 0 0.38 0.22
2017 Industrial & Medical Gases Nigeria Natural Resources Industrial Gases 0.60 6.393333 9.78 43.69 6.63 0.29 0 0.38 0.22
2018 Industrial & Medical Gases Nigeria Natural Resources Industrial Gases 0.70 6.240000 13.43 40.72 6.65 0.29 0 0.38 0.22

Zaklad štatistika

data_cleaned %>%
  group_by(EXCHANGE.SECTOR) %>%
  summarise(
    Pocet_Pozorovani = n(),
    Priemer_ESG = round(mean(as.numeric(ESG.INDEX), na.rm = TRUE), 3),
    Priemer_ROA = round(mean(as.numeric(RETURN.ON.ASSETS), na.rm = TRUE), 2)
  ) %>%
  kable(caption = "Súhrnná štatistika podľa sektorov po očistení")
Súhrnná štatistika podľa sektorov po očistení
EXCHANGE.SECTOR Pocet_Pozorovani Priemer_ESG Priemer_ROA
Agriculture 40 0.220 -27.59
Conglomerate 50 0.275 -17.61
Construction & Real Estate 40 0.253 -19.30
Consumer Goods 160 0.316 7.07
Healthcare 60 0.292 -12.80
ICT 40 0.244 -43.92
Industrial Goods 110 0.307 6.24
Natural Resources 40 0.162 2.56
Oil & Gas 70 0.302 -35.89
Services 150 0.253 62.92