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.

score 1 · Answer 1 · answered Oct 16 '20 at 16:29

1

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)))

answered Oct 16 '20 at 16:29

M--

25,431
8
61
93

1

With the current version of `dplyr`, it would be `mutate(across(where(is.factor), as.character))` – akrun Oct 16 '20 at 18:28
@akrun I haven't read the update log yet, you mean `mutate_...` are deprecated? – M-- Oct 16 '20 at 18:31
It will be deprecated in the future. Now, it is all in `mutate` with `across` – akrun Oct 16 '20 at 18:33

hc_haha · Accepted Answer · 2020-10-16T16:54:08.603

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
         )
       })

Loop to create multiple sheets in multiple Excel workbooks

2 Answers2