1

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")
procerus
  • 194
  • 8
  • What do you mean by *split in a weird way*? – Onyambu Mar 31 '23 at 09:32
  • I am sorry, in line "b<-rep(letters[1:10], 100)", the default is "times", so it made perfect sense what was happening, I corrected it to each, as well as edited the question. – procerus Mar 31 '23 at 09:53

1 Answers1

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)
Julian
  • 6,586
  • 2
  • 9
  • 33
  • Hi, thanks for response, you think you could modify this to use some other package other than xlsx? I am getting Error: package or namespace load failed for ‘xlsx’: .onLoad failed in loadNamespace() for 'rJava', details: call: fun(libname, pkgname) error: JAVA_HOME cannot be determined from the Registry – procerus Mar 31 '23 at 09:58
  • I made an edit with `openxlsx` which does not have the java dependency. – Julian Mar 31 '23 at 10:55