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?

Ronak Shah · Accepted Answer · 2021-05-12 03:39:09Z

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

answered May 12, 2021 at 3:39

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anup Over a year ago

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.

GordonShumway · Accepted Answer · 2021-05-12 03:31:16Z

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)

answered May 12, 2021 at 3:31

GordonShumway

2,05617 silver badges22 bronze badges

2 Comments

Anup Over a year ago

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.

GordonShumway Over a year ago

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

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related