Export a list of data frames in multiple Excel files containing each multiple sheets

Question

Apologies in advance if this has already been asked elsewhere, but I've tried different attempts and nothing has worked so far.

I have a list (named Results) of 26 data frames (DF). I export each DF into 26 Excel files using writexl and lapply with an anonyous function :

>   head(Results)
$Paris.Aquatic_moss
    Sample_type Locality Chemicals mean_Mesure max_Mesure min_Mesure 
1  Aquatic_moss   Paris   AG-110m        NA         NA         NA   
2  Aquatic_moss   Paris     BE-7      123.275      177.0      52.60  
3  Aquatic_moss   Paris      NH3         NA         NA         NA   
4  Aquatic_moss   Paris      CO         ...        ...        ...  
5  Aquatic_moss   London  AG-110m        NA         NA         NA   
6  Aquatic_moss   London     BE-7       ...        ...        ...  
7  Aquatic_moss   London     NH3        ...        ...        ...   
8  Aquatic_moss   London     CO         ...        ...        ... 

library(writexl)
lapply(names(Results), 
       function (x) write_xlsx(Results[[x]], path=paste(x, "2019.xlsx", sep="_")))

I obtain 26 Excel files named "nameoftheDF1_2019", "nameoftheDF2_2019",... Each Excel has one sheet named "Sheet1". But each Excel is huge. I realise that it would be much easier that each DF is itself split by the column called "Locality" and results in 26 Excel files each with different sheets.

I could split the list of 26 DF Results (see below) before exporting to Excel. But in this case, I do not know how to re-combine during exporting so that I remain with 26 Excel files containing each several sheets

Results_Locality <- split(Results,list(Results$Locality), drop=TRUE)

So I am wondering whether it is possible to add with lapply another function to split Results by the column "Localization" so that each DF will be exported in an Excel file with different sheets (sheet1 = Localization1 in DF1, sheet2 = Localization 2 in DF1, ...) ? Otherwise is it possible to create a loop to export the data of each localization in each DF in the same sheet ?

take alook at data.table::split().. it can split a data-table by column-values and outputs to a list you can loop over with. — Wimpel
I am not sure to quite understand. R returns me an error :Error: 'split' is not an exported object from 'namespace:data.table' — Sylvain
my apologies... data.table comes with it's own split methods, but it is not a separate function, you can just call 'split' on a data.table... split.data.table will then be called. — Wimpel

stefan stefan · Accepted Answer · 2020-06-25T12:14:59

This can be achieved by splitting the df and passing the splitted df to writexl::write_xlsx. By default write_xlsx will put the elements of the list in different sheets:

library(writexl)

xlexport <- function(x, var = "Locality") {
  d <- split(Results[[x]], Results[[x]][[var]])
  writexl::write_xlsx(d, path = paste(x, "2019.xlsx", sep = "_"))
}

Results <- list(iris1 = iris, iris2 = iris)

lapply(names(Results), xlexport, var = "Species")

Export a list of data frames in multiple Excel files containing each multiple sheets

1 Answers