Using loop to write excel files in R

February 11, 2022

I have a list of data frames that I would like to export to excel. Exporting each data frame would be very tedious and error prone. Is there a way to achieve this using for loop or map functions? The desire would also be export all the data frames into one workbook.

library(tidyverse)
library(writexl)


df <- tribble(
  ~var1, ~var2, ~group,
  12, 11, "A",
  15, 10, "B",
  16, 18, "A",
  18, 14, "C",
  18, 3, "A",
  10, 17, "B",
  20, 1, "A",
  10, 13, "A"
)

nest(df, data = c(var1, var2))

sheet1 <- df %>% filter(group == "A")
sheet2 <- df %>% filter(group == "B")
sheet3 <- df %>% filter(group == "C")

write_xlsx(sheet1, "sheet1.xlsx")
write_xlsx(sheet2, "sheet2.xlsx")
write_xlsx(sheet3, "sheet3.xlsx")

>Solution :

In write_xlsx "to create an xlsx with (multiple) named sheets, simply set x to a named list of data frames." So you can do the following to achieve what you are asking for:

groups <- distinct(df, group) %>% pull(group)

list_of_dfs <-  groups %>% 
  map(~ df %>% filter(group == .x)) 

names(list_of_dfs) <- groups

write_xlsx(list_of_dfs, "out.xlsx")