[SOLVED] R: write_xlsx with evaluation problem in argument

R: write_xlsx with evaluation problem in argument

I have a loop like this:

library(writexl)
file_output="C:/test/test.xlsx"
for(i in 1:2){
    df<-iris[i,]
    write_xlsx(list(i=df),file_output)
    }

I would like to store each iteration in a separate sheet named the value of i (or in my real program: the value of some variable). Is this possible with write_xlsx, or in general, is it possible to make write_xlsx (or some other function) to interpret:

"list(i=df)" as "list(1=df)" and "list(2=df)"

Solution

Instead of i = df you must name the list elements. For your loop this would mean:

file_output = "C:/test/test.xlsx"
for(i in 1:2){
  df <- iris[i,]
  out <- list(df)
  names(out) <- i
  write_xlsx(out, file_output)
}

However, this will result in one file per data.frame, since write_xlsx does not append to existing files (at least to my knowledge). If you want to have only one file with sheets for the various data.frames, you'd have to adapt your code:

file_output = "C:/test/test.xlsx"
vars <- 1:2
out <- vector(mode = "list", length = length(vars))
for(i in vars){ # if you use variable names in vars, use seq_along(vars) instead of vars
  out[[i]] <- iris[i,]
}
names(out) <- vars
write_xlsx(out, file_output)

Since I do not see any merit in using a loop here, I'd even suggest to use map from the purrr package or lapply:

file_output = "C:/test/test.xlsx"
vars <- 1:2
out <- map(vars, ~ iris[.x,])
names(out) <- vars
write_xlsx(out, file_output)