Using write_xlsx in a for loop to export dataframes into multiple sheets

Question

I want to export some dataframes that I create through a for loop. These should be exported to different Excel sheets in the same file. While exporting, I want each sheet to have a different name. For instance, the sheet names in my following example should be coming from the character vector carriers.

I cannot use the package xlsx as this does not run on my R. I have tried using the writexl::write_xlsx function, but I could not make it work. Here is a simple example to reproduce my problem:

library(nycflights13)
library(writexl)

carriers <- c( "UA", "AA", "B6", "DL")

for(i in 1:length(carriers)){
dframe_flt <- flights %>% filter(carrier == carriers[i]) %>% summarize(distance_m = mean(distance))
write_xlsx(dframe, sheet = !! paste(carriers[i]), excel = "flights_data.xlsx", col_names = TRUE)
}

It produces the following error:

Error in write_xlsx(dframe, sheet = !!paste(carriers[i]), excel = "flights_data.xlsx",  : 
  unused arguments (sheet = !!paste(carriers[i]), excel = "flights_data.xlsx")

How do I fix this error?

Answer 1

you are getting the error as sheet and excel are not arguments in write_xlsx. To write multiple sheets to a single xlsx you want to first put them all in named list and then supply that list to write_xlsx.

library(nycflights13)
library(writexl)
library(tidyverse)
carriers <- c( "UA", "AA", "B6", "DL")

Create the list using map (from tidyr):

output <- map(set_names(carriers),function(cr){
  dframe_flt <- flights %>% 
    filter(carrier == cr) %>% 
    summarize(distance_m = mean(distance))
})

Write to xlsx:

write_xlsx(output, path = "flights_data.xlsx", col_names = TRUE)

Answer 2

subset, aggregate, split and write :

carriers <- c( "UA", "AA", "B6", "DL")
data <- subset(flights, carrier %in% carriers)
data <- aggregate(distance~carrier, data, mean, na.rm = TRUE)
writexl::write_xlsx(split(data, data$carrier), 'data.xlsx')