how to write a list of list to excel using imap and map

Question

I have a list of list and I would like to input them into muti excel files, wherer excel name will be the sublist name, and name of each sheet in the excel will be the df name in each sublist. I tried to utilize imap and map to reach my goal. However I am new to those and still confuse how to set it up correctly.

The sample list can be build using

lst1<-list(`101-01-101` = list(Demographics = structure(list(SubjectID = c("Subject ID", 
"101-01-101"), BRTHDTC = c("Birthday", "1953-07-07"), SEX = c("Gender", 
"Female")), row.names = c(NA, -2L), class = c("tbl_df", "tbl", 
"data.frame")), DiseaseStatus = structure(list(SubjectID = c("Subject ID", 
"101-01-101"), DSDT = c("DS Date", "2016-03-14"), DSDT_P = c("DS Date Prob", 
NA)), row.names = c(NA, -2L), class = c("tbl_df", "tbl", "data.frame"
))), `101-02-102` = list(Demographics = structure(list(SubjectID = c("Subject ID", 
"101-02-102"), BRTHDTC = c("Birthday", "1963-07-02"), SEX = c("Gender", 
"Female")), row.names = c(NA, -2L), class = c("tbl_df", "tbl", 
"data.frame")), DiseaseStatus = structure(list(SubjectID = c("Subject ID", 
"101-02-102"), DSDT = c("DS Date", "2017-04-04"), DSDT_P = c("DS Date Prob", 
NA)), row.names = c(NA, -2L), class = c("tbl_df", "tbl", "data.frame"
))), `101-03-103` = list(Demographics = structure(list(SubjectID = c("Subject ID", 
"101-03-103"), BRTHDTC = c("Birthday", "1940-09-11"), SEX = c("Gender", 
"Male")), row.names = c(NA, -2L), class = c("tbl_df", "tbl", 
"data.frame")), DiseaseStatus = structure(list(SubjectID = c("Subject ID", 
"101-03-103"), DSDT = c("DS Date", NA), DSDT_P = c("DS Date Prob", 
"UN-UNK-2015")), row.names = c(NA, -2L), class = c("tbl_df", 
"tbl", "data.frame"))), `101-04-104` = list(Demographics = structure(list(
    SubjectID = c("Subject ID", "101-04-104"), BRTHDTC = c("Birthday", 
    "1955-12-31"), SEX = c("Gender", "Male")), row.names = c(NA, 
-2L), class = c("tbl_df", "tbl", "data.frame")), DiseaseStatus = structure(list(
    SubjectID = c("Subject ID", "101-04-104"), DSDT = c("DS Date", 
    "2016-05-02"), DSDT_P = c("DS Date Prob", NA)), row.names = c(NA, 
-2L), class = c("tbl_df", "tbl", "data.frame"))), `104-05-201` = list(
    Demographics = structure(list(SubjectID = c("Subject ID", 
    "104-05-201"), BRTHDTC = c("Birthday", "1950-12-04"), SEX = c("Gender", 
    "Female")), row.names = c(NA, -2L), class = c("tbl_df", "tbl", 
    "data.frame")), DiseaseStatus = structure(list(SubjectID = c("Subject ID", 
    "104-05-201"), DSDT = c("DS Date", "2018-07-06"), DSDT_P = c("DS Date Prob", 
    NA)), row.names = c(NA, -2L), class = c("tbl_df", "tbl", 
    "data.frame"))))

The codes I have so far are:

lst1 %>% imap ( ~  wb = createWorkbook()
  Map(function(data, nameofsheet){     
    addWorksheet(wb, nameofsheet)
    writeData(wb, nameofsheet, data)
}, .x, names(.x))

saveWorkbook(wb, file.path("C:/Users/SQ/Documents/",
                sprintf("subject_%s.xlsx", .y)))
)

My codes doesn't work, but hopfully can give you some idea what I try to do. I need to go through this in this way as I need to add some format to the sheets. Would anyone give me some guidance on how to do this?

I probably did not use .x & .y right. I am still confused on how to define them when we work with list.

Many thanks.

user_3732271 · Answer

We just need a simple tweak to the code

If we are using multiple expressions, block it inside the {}
The data going into the Map should be .x and not .y as .y will be names of the list

library(openxlsx)
library(purrr)
library(dplyr)
lst1 %>% 
   imap ( ~ { 
   wb <- createWorkbook()
   Map(function(data, nameofsheet){     
       addWorksheet(wb, nameofsheet)
       writeData(wb, nameofsheet, data)
      }, .x, names(.x))

   saveWorkbook(wb, file.path("C:/Users/SQ/Documents/",
                sprintf("subject_%s.xlsx", .y)))
                }
  )

-output files generated

-file content

how to write a list of list to excel using imap and map

1 Answers