How to ignore a missing sheet in a rbind function?

user_16597745 0
Viewed 76

I need help with troubleshooting this an error I keep getting.

I am trying to build a combined data.frame of 4 excel files containing various sheet.

However, because a sheet named "j" in the excel 'Mal' excel file is missing, the code wont run and it keeps returning

Error: Sheet 'j' not found

How do I fix the error please?

Thanks.

import_partnersubmissions_ALT <- function(){
  filelist <- list.files(path = "./Partner Submissions/", pattern = "*.xlsx")
  print(filelist)
  master <- data.frame()
  for(x in filelist){
    if(str_detect(x,"Bru")){
      print("Reading Bru...")
      master <- rbind(master,
                      read_partnerALT(x, "a"),
                      read_partnerALT(x, "b"),
                      read_partnerALT(x, "c"),
                      read_partnerALT(x, "d"),
                      read_partnerALT(x, "e"),
                      read_partnerALT(x, "f"),
                      read_partnerALT(x, "g")
      )
    }else if(str_detect(x,"Mal")){
      print("Reading Mal...")
      master <- rbind(master,
                      read_partnerALT(x, "h"),
                      read_partnerALT(x, "i"),
                      read_partnerALT(x, "j"),
                      read_partnerALT(x, "k")
      )
    }else if(str_detect(x,"Sgp")){
      print("Reading Sgp...")
      master <- rbind(master,
                      read_partnerALT(x, "l"),
                      read_partnerALT(x, "m"),
                      read_partnerALT(x, "n"),
                      read_partnerALT(x, "o")
      )
    }else if(str_detect(x,"Mld")){
      print("Reading Mld...")
      master <- rbind(master,
                      read_partnerALT(x, "p"),
                      read_partnerALT(x, "q"),
                      read_partnerALT(x, "r"),
                      read_partnerALT(x, "s")
      )
    }
  }
openxlsx rbind data.table if-statement r
1 Answers

(Up front, you tagged this with data.table, so I assume you are using the data.table package. If not, then most of the below still applies, though you'll need to use do.call(rbind, list(...)) in place of rbindlist(list(...)).)

rbind is fine with NULL arguments, so wrap with tryCatch. However, writing this each time will get onerous, I suggest using rbindlist:

master <- rbindlist(
  c(list(master), 
    lapply(c("h", "i", "j", "k"),
           function(S) tryCatch(read_partnerALT(x, S), error = function(e) NULL)))
)

I don't know the definition of read_partnerALT, so it's possible that that can be simplified a little, but the premise remains:

  • iterate over your sheet names c("h", "i", ...);
  • pass that to an anon-func that makes the real call to read_partnerALT but wrapped in tryCatch(.);
  • if there is any kind of error with reading that sheet, it will instead return NULL for that sheet and silently continue

I'm not always a fan of doing things like this silently, so at the cost of brevity, we can make it a little more noteworthy:

func <- function(S) {
  tryCatch(
    read_partnerALT(x, S),
    error = function(e) {
      warning(conditionMessage(e))
      NULL
    })
}
master <- rbindlist( c(list(master), lapply(c("h", "i", "j", "k"), func)) )

Demonstration:

library(data.table)
MT <- as.data.table(mtcars)

Demonstrating the error condition:

rbindlist(lapply(c("cyl", "gear", "quux"), function(S) MT[get(S) == 4,]))
# Error in .checkTypos(e, names_x) : 
#   Object 'quux' not found amongst mpg, cyl, disp, hp, drat and 6 more

Silent fix:

rbindlist(lapply(c("cyl", "gear", "quux"), function(S) tryCatch(MT[get(S) == 4,], error = function(e) NULL)))
#       mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear
#     <num> <num> <num> <num> <num> <num> <num> <num> <num> <num>
#  1:  22.8     4 108.0    93  3.85 2.320 18.61     1     1     4
#  2:  24.4     4 146.7    62  3.69 3.190 20.00     1     0     4
#  3:  22.8     4 140.8    95  3.92 3.150 22.90     1     0     4
# ...

Warning fix:

rbindlist(lapply(c("cyl", "gear", "quux"), function(S) tryCatch(MT[get(S) == 4,], error = function(e) {warning(conditionMessage(e)); NULL;})))
# Warning in value[[3L]](cond) :
#   Object 'quux' not found amongst mpg, cyl, disp, hp, drat and 6 more
#       mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear
#     <num> <num> <num> <num> <num> <num> <num> <num> <num> <num>
#  1:  22.8     4 108.0    93  3.85 2.320 18.61     1     1     4
#  2:  24.4     4 146.7    62  3.69 3.190 20.00     1     0     4
#  3:  22.8     4 140.8    95  3.92 3.150 22.90     1     0     4
# ...
user_33582720
Related