0

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?

Anup
  • 239
  • 2
  • 11

2 Answers2

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')
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • thanks. But I accepted GordonShumway's answer since his solution sticks to my basic codes built on dplyr and is easily extendable to my particular situation. – Anup May 12 '21 at 20:08
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)
GordonShumway
  • 1,980
  • 13
  • 19
  • this solution is great! I just have one follow-up question. Is there any way I can have the Excel tab names from the vector carriers but filter based on another character variable? For example, the character variable may be a subset of the variable `tailnum` that I will pre-define. – Anup May 12 '21 at 22:34
  • The names of the tabs are taken from the names of the list element so you can set them to whatever you want using `set_names()` – GordonShumway May 13 '21 at 20:54