CODEWITHSUNDEEP
rwritexl
Helen
Helen

Reputation: 617

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)"

Upvotes: 1

Views: 994

Answers (2)

ha-pu
ha-pu

Reputation: 581

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)

Upvotes: 1

RLave
RLave

Reputation: 8374

You don't need a for loop for this probably.

Just use names() on the list object as.character() in order to name the sheets.

l <- lapply(1:2, function(i) iris[i, ]) #creates separated df for each row
names(l) <- as.character(1:2) # sets the names of each element in the list
# these will be the sheets name


file="path_to_file.xlsx" # path to file name
library(writexl)

write_xlsx(l, file)

Upvotes: 1

Related Questions