EN VI

write xlsx files from a list of dataframes in R?

How to write xlsx files from a list of dataframes in R

Suppose we have a list of data frames called list_data.

Our goal is to write a separate .xlsx file maybe using openxlsx::write.xlsx() for each District where each file has two subsheets (aka tabs) named after the data.frames in list_data (in this case, these names are one and two).

Because some Districts are only included in one OR two, for such Districts one of the subsheets will be empty which is OK.

File names can be the names of the Districts.

I tried something below without success. Is this even possible in R?

see a desired file output below

library(tidyverse)
library(openxlsx)

list_data<- list(one= data.frame(District=LETTERS[1:3], V1 = 1:3),
                 two= data.frame(District=LETTERS[3:8], V7 = 3:8, V3=8:3))

lapply(list_dat,\(j) 
    group_by(j,District) %>% 
      write.xlsx(file =paste0(j$District,".xlsx"), sheetName = ???)) # Not sure how to add subsheet names here

Here is one desired file for District C with 2 subsheets:

Solution:

I rewrite the code as follows

library(tidyverse)
library(openxlsx)

list_data<- list(one= data.frame(District=LETTERS[1:3], V1 = 1:3),
                 two= data.frame(District=LETTERS[3:8], V7 = 3:8, V3=8:3))

rv <- bind_rows(list_data) %>%
  distinct(District) %>%
  pull(District) %>%
  lapply(function(x) {
    sub_list <- lapply(names(list_data), function(y) {
      list_data[[y]] %>%
        mutate(subsheet = y) %>%
        relocate(subsheet) %>%
        filter(District == x)
    })
    names(sub_list) <- names(list_data)
    write.xlsx(sub_list, file =paste0(x,".xlsx"))
  })