Writing multiple data frames from a list into excel, split 1 factor across different sheets and order by another factor

Question

Here is some data.

a<-rnorm(1000, 1,1)
b<-rep(letters[1:10], each = 100)
d<-rep(c("x","y","z","q"),250) 

abd<-cbind.data.frame(a,b,d)

I would like to split this data frame into list of data frames based on a factor (b in this case) and than write this list of data frame into excel in a way that each factor "b" will be placed on a new sheet and factor "d" will be ordered in a specified way (e.g. first comes x, than y, than z, than q). I first tried to split by factor:

abdlist<-split(abd, abd$b)
abdlist[1]

This looks fine for now, I would like to specify the order of factor "d" when I am writing the list to excel.

library(dplyr)
library(writexl)
abdlist %>%:write_xlsx(path = "path.xlsx")

Answer 1

You could use purrr for this:

library(purrr)
library(dplyr)
library(openxlsx)

wb <- createWorkbook()
abdlist |> 
  map(~.x |> 
        mutate(d = factor(d, levels = c("x","y","z","q"))) |> 
        arrange(d)) |> 
  iwalk(function(.data, sheet_name) {
                addWorksheet(wb = wb, sheetName = sheet_name)
                writeData(wb = wb, x = .data, sheet = sheet_name)
              }
  )

saveWorkbook(wb = wb, file = "text.xlsx", overwrite = TRUE)