Finding the companies when removing NAs in R

Ana J 0
Viewed 46

I am working with a panel data, which I am filtering by removing NAs for different variables step by step and creating new data-frames.

For example passing from a panel_data1 to panel_data2 while removing NAs for additional variables, 399 companies are removed. Is there a way I can retrieve these companies in a new dataset, as I want to have the companies (amlong with their respective data) that I am losing.

Panel_data

structure(list(ISIN = c("AEA000201011", "AEA000201011", "AEA000201011", 
"AEA000201011", "AEA000201011", "AEA000201011", "AEA000201011", 
"AEA000201011", "AEA000201011", "AEA000201011", "AEA000201011", 
"AEA000801018", "AEA000801018", "AEA000801018", "AEA000801018", 
"AEA000801018", "AEA000801018", "AEA000801018", "AEA000801018", 
"AEA000801018", "AEA000801018", "AEA000801018", "AEA001501013", 
"AEA001501013", "AEA001501013"), year = c(2010L, 2011L, 2012L, 
2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 2019L, 2020L, 2010L, 
2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 2019L, 
2020L, 2010L, 2011L, 2012L), full_company_name = c("Abu Dhabi Commercial Bank PJSC", 
"Abu Dhabi Commercial Bank PJSC", "Abu Dhabi Commercial Bank PJSC", 
"Abu Dhabi Commercial Bank PJSC", "Abu Dhabi Commercial Bank PJSC", 
"Abu Dhabi Commercial Bank PJSC", "Abu Dhabi Commercial Bank PJSC", 
"Abu Dhabi Commercial Bank PJSC", "Abu Dhabi Commercial Bank PJSC", 
"Abu Dhabi Commercial Bank PJSC", "Abu Dhabi Commercial Bank PJSC", 
"Abu Dhabi Islamic Bank PJSC", "Abu Dhabi Islamic Bank PJSC", 
"Abu Dhabi Islamic Bank PJSC", "Abu Dhabi Islamic Bank PJSC", 
"Abu Dhabi Islamic Bank PJSC", "Abu Dhabi Islamic Bank PJSC", 
"Abu Dhabi Islamic Bank PJSC", "Abu Dhabi Islamic Bank PJSC", 
"Abu Dhabi Islamic Bank PJSC", "Abu Dhabi Islamic Bank PJSC", 
"Abu Dhabi Islamic Bank PJSC", "Arabtec Holding PJSC", "Arabtec Holding PJSC", 
"Arabtec Holding PJSC"), refin_esg = c(NA, NA, NA, NA, 48.74, 
54.32, 63.81, 67.64, 71.74, 68.17, 63.8, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, 30.89, NA, NA, NA, NA), refin_e = c(NA, NA, NA, 
NA, 23.21, 53.02, 60.18, 18.64, 50.25, 52.21, 46.69, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, 20.52, NA, NA, NA, NA), esg_msci_hist = c(NA, 
NA, NA, NA, NA, 70, 84, 73, 74, 63, 71, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA), env_msci_hist = c(NA, NA, NA, 
NA, NA, 29, 17, 20, 19, 14, 26, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA), ESG_spg = c(NA, NA, NA, NA, NA, 35, 
36, 37, 26, 20, 15, NA, NA, NA, NA, NA, NA, NA, NA, NA, 8, 6, 
NA, NA, NA), E_spg = c(NA, NA, NA, NA, NA, 18, 26, 26, 15, 12, 
2, NA, NA, NA, NA, NA, NA, NA, NA, NA, 6, 0, NA, NA, NA)), row.names = c(NA, 
25L), class = "data.frame")

panel_data1

panel_data1 <- panel_data %>% filter(!is.na(refin_e) & !is.na(refin_esg) &
                                        !is.na(sc1_refin) & !is.na(sc2_refin) &
                                        !is.na(sct_refin) & !is.na(sc12inten_refin) &
                                        !is.na(E_spg) & !is.na(ESG_spg) & !is.na(mktcap) &
                                        !is.na(carbonscope1_trucost) &
                                        !is.na(carbonscope2_trucost) & !is.na(carbon_inten_scope1_trucost) &
                                        !is.na(carbon_inten_scope2_trucost) &
                                        !is.na(env_msci_hist) & !is.na(esg_msci_hist) &
                                        !is.na(scope1_msci_carb) & !is.na(scope2_msci_carb) &
                                        !is.na(scope12_msci_carb) & !is.na(scope12inten_msci_carb))

The output of this data is 2103 companies.

panel_data2

panel_data2 <- panel_data %>%
  filter(!is.na(refin_e) & !is.na(refin_esg) &
           !is.na(sc1_refin) & !is.na(sc2_refin) &
           !is.na(sct_refin) & !is.na(sc12inten_refin) &
           !is.na(E_spg) & !is.na(ESG_spg) & !is.na(mktcap) &
           !is.na(carbonscope1_trucost) &
           !is.na(carbonscope2_trucost) &
           !is.na(carbon_inten_scope1_trucost) &
           !is.na(carbon_inten_scope2_trucost) &
           !is.na(env_msci_hist) & !is.na(esg_msci_hist) &
           !is.na(scope1_msci_carb) & !is.na(scope2_msci_carb) &
           !is.na(scope12_msci_carb) &
           !is.na(scope12inten_msci_carb) &
           !is.na(carbonscope3_trucost) &
           !is.na(sc3_refin) & !is.na(scope3_msci_carb) &
           !is.na(carbon_inten_scope3_trucost))

The output of this data is 1704.

I want to be able to have a new data frame with the 399 that were dropped, if possible

na dplyr r
1 Answers

It's unclear from your code whether you are testing all columns or just a subset. Either way, though, it is far more efficient to use complete.cases(.) somehow.

dat1 <- dat %>%
  filter(complete.cases(cur_data()))
dat2 <- dat %>%
  filter(!complete.cases(cur_data()))
dat1
#           ISIN year              full_company_name refin_esg refin_e esg_msci_hist env_msci_hist ESG_spg E_spg
# 1 AEA000201011 2015 Abu Dhabi Commercial Bank PJSC     54.32   53.02            70            29      35    18
# 2 AEA000201011 2016 Abu Dhabi Commercial Bank PJSC     63.81   60.18            84            17      36    26
# 3 AEA000201011 2017 Abu Dhabi Commercial Bank PJSC     67.64   18.64            73            20      37    26
# 4 AEA000201011 2018 Abu Dhabi Commercial Bank PJSC     71.74   50.25            74            19      26    15
# 5 AEA000201011 2019 Abu Dhabi Commercial Bank PJSC     68.17   52.21            63            14      20    12
# 6 AEA000201011 2020 Abu Dhabi Commercial Bank PJSC     63.80   46.69            71            26      15     2
dat2
#            ISIN year              full_company_name refin_esg refin_e esg_msci_hist env_msci_hist ESG_spg E_spg
# 1  AEA000201011 2010 Abu Dhabi Commercial Bank PJSC        NA      NA            NA            NA      NA    NA
# 2  AEA000201011 2011 Abu Dhabi Commercial Bank PJSC        NA      NA            NA            NA      NA    NA
# 3  AEA000201011 2012 Abu Dhabi Commercial Bank PJSC        NA      NA            NA            NA      NA    NA
# 4  AEA000201011 2013 Abu Dhabi Commercial Bank PJSC        NA      NA            NA            NA      NA    NA
# 5  AEA000201011 2014 Abu Dhabi Commercial Bank PJSC     48.74   23.21            NA            NA      NA    NA
# 6  AEA000801018 2010    Abu Dhabi Islamic Bank PJSC        NA      NA            NA            NA      NA    NA
# 7  AEA000801018 2011    Abu Dhabi Islamic Bank PJSC        NA      NA            NA            NA      NA    NA
# 8  AEA000801018 2012    Abu Dhabi Islamic Bank PJSC        NA      NA            NA            NA      NA    NA
# 9  AEA000801018 2013    Abu Dhabi Islamic Bank PJSC        NA      NA            NA            NA      NA    NA
# 10 AEA000801018 2014    Abu Dhabi Islamic Bank PJSC        NA      NA            NA            NA      NA    NA
# 11 AEA000801018 2015    Abu Dhabi Islamic Bank PJSC        NA      NA            NA            NA      NA    NA
# 12 AEA000801018 2016    Abu Dhabi Islamic Bank PJSC        NA      NA            NA            NA      NA    NA
# 13 AEA000801018 2017    Abu Dhabi Islamic Bank PJSC        NA      NA            NA            NA      NA    NA
# 14 AEA000801018 2018    Abu Dhabi Islamic Bank PJSC        NA      NA            NA            NA      NA    NA
# 15 AEA000801018 2019    Abu Dhabi Islamic Bank PJSC     30.89   20.52            NA            NA       8     6
# 16 AEA000801018 2020    Abu Dhabi Islamic Bank PJSC        NA      NA            NA            NA       6     0
# 17 AEA001501013 2010           Arabtec Holding PJSC        NA      NA            NA            NA      NA    NA
# 18 AEA001501013 2011           Arabtec Holding PJSC        NA      NA            NA            NA      NA    NA
# 19 AEA001501013 2012           Arabtec Holding PJSC        NA      NA            NA            NA      NA    NA

It would actually be a bit shorter code to use base R, as in

dat1 <- dat[complete.cases(date),]
dat2 <- dat[!complete.cases(date),]

but I was continuing with the apparent preference for dplyr. They both produce the same results.

If you're using a subset, you can use them specifically instead of the full frame. For example, if I only care about the last two columns of your sample data, then

dat %>%
  filter(complete.cases(ESG_spg, E_spg))
#           ISIN year              full_company_name refin_esg refin_e esg_msci_hist env_msci_hist ESG_spg E_spg
# 1 AEA000201011 2015 Abu Dhabi Commercial Bank PJSC     54.32   53.02            70            29      35    18
# 2 AEA000201011 2016 Abu Dhabi Commercial Bank PJSC     63.81   60.18            84            17      36    26
# 3 AEA000201011 2017 Abu Dhabi Commercial Bank PJSC     67.64   18.64            73            20      37    26
# 4 AEA000201011 2018 Abu Dhabi Commercial Bank PJSC     71.74   50.25            74            19      26    15
# 5 AEA000201011 2019 Abu Dhabi Commercial Bank PJSC     68.17   52.21            63            14      20    12
# 6 AEA000201011 2020 Abu Dhabi Commercial Bank PJSC     63.80   46.69            71            26      15     2
# 7 AEA000801018 2019    Abu Dhabi Islamic Bank PJSC     30.89   20.52            NA            NA       8     6
# 8 AEA000801018 2020    Abu Dhabi Islamic Bank PJSC        NA      NA            NA            NA       6     0
r2evans0
Related