Loop to create multiple sheets in multiple Excel workbooks

Question

I have a dataset with two different groups and their values, something like this:

example <- data.frame('Group' = c('building 1', 'building 1', 
                                  'building 2', 'building 2'),
                      'Subgroup' = c('Active','Inactive','Active','Inactive'),
                      'Value' = c('abc','def','ghi','jkl'))

I know I can use lapply to output a workbook for each 'building':

buildings <- split(example, example$Group)

lapply(1:length(buildings), 
        function(x) write.xlsx(buildings[[x]], 
                               file = paste0(names(buildings[x]), '.xlsx'), 
                               row.names = FALSE))

Similarly, here would be the code to create multiple sheets within 1 workbook based on a column:

activity <- split(example, example$Subgroup)

lapply(1:length(activity), 
        function(x) write.xlsx(activity[[x]], file = 'All values.xlsx',
                               sheetName = paste0(names(activity[x])), 
                               append = TRUE, row.names = FALSE))

My question is, is there a way in R where these can these be combined (or is there another way) to create separate Workbooks by Group, with multiple sheets by Subgroup? What I'd want is a file for Building 1 that has 2 sheets: Active and Inactive.

Answer 1

I modify your code as follows. Since openxlsx::write.xlsx() will automatically use the name of the named list, such as activity, as the name of the sheets. The only thing you have to do is split the buildings in the lapply() function.

example <- data.frame('Group' = c('building 1', 'building 1', 'building 2', 'building 2'),
                      'Subgroup' = c('Active','Inactive','Active','Inactive'),
                      'Value' = c('abc','def','ghi','jkl'))


buildings <- split(example, example$Group)

lapply(seq_along(buildings), 
       function(x) {
         activity <- split(buildings[[x]], buildings[[x]]$Subgroup)
         openxlsx::write.xlsx(
           activity,
           file = paste0(names(buildings[x]), '.xlsx'),
           row.names = FALSE
         )
       })

Answer 2

You can split the data into nested lists which first level is split by Group and the second level is by Subgroup. Then you can write them into workbooks with separate sheets in a loop. See below;

library(dplyr)
library(xlsx)

example %>% 
  mutate_if(is.factor, as.character) %>% 
  split(., .$Group) %>% 
  lapply(., function(x) split(x, x$Subgroup)) %>% 
  lapply(., function(dat) 
    lapply(dat, function(dat.sub) 
      write.xlsx(dat.sub, 
                 file = paste0(as.character(unique(dat.sub$Group)), ".xlsx"),
                 sheetName = paste0(as.character(unique(dat.sub$Subgroup))), 
                 append = TRUE, row.names = FALSE)))