How to name excel files as the names of the data frames in a list in R

Question

I have a large list with more than 100 data frames in it. For simplification I show a list with three data frames (dummy data). I would like to write each data frame into an excel file by using openxlsx. I already created a loop, in which each data frame becomes on excel file, but I want to name each excel file as the name of the data frame. How can I get each excel file named as the data frame in the list?? So DF1 should become DF1.xlsx. DF2 should become DF2.xlsx. DF should become DF3.xlsx.But at the moment each excel file is name 1,2 or 3. My loop:

for (i in (1:3)) {write.xlsx(listDF[i],file=paste(i,".xlsx"))}

# dummy data
listDF <- list(
  DF1 = data.frame(
    sample = c("TGX", "TGX", "TGX", "TGX"),
    RC = c(0, 1, 2, 3),
    medRC = c(0, 3, 4, 0),
    RC.norm = c(0, 3, 3, 3),
    medRC.norm = c(0, 3, 3, 3)
  ),
  DF2 = data.frame(
    sample = c("TBF", "TBF", "TBF", "TBF"),
    RC = c(2, 1, 2, 3),
    medRC = c(4, 3, 4, 0),
    RC.norm = c(1, 3, 3, 3),
    medRC.norm = c(0, 3, 3, 3)
  ),
  DF3 = data.frame(
    sample = c("TZW", "TZW", "TZW", "TZW"),
    RC = c(4, 3, 2, 3),
    medRC = c(1, 3, 2, 0),
    RC.norm = c(1, 1, 1, 1),
    medRC.norm = c(0, 7, 5, 3)
  )

    listDF 

    # $DF1
    #   sample  RC medRC RC.norm medRC.norm
    # 1  TGX    0     0       0          0
    # 2  TGX    1     3       3          3
    # 3  TGX    2     4       3          3
    # 4  TGX    3     0       3          3

    # $DF2
    #   sample RC medRC RC.norm medRC.norm
    # 1  TBF   2     4       1       0
    # 3  TBF   1     3       3       3 
    # 4  TBF   2     4       3       3
    # 5  TBF   3     0       3       3

    # $DF3
    #   sample  RC medRC RC.norm medRC.norm
    # 1  TZW    4     1       1          0
    # 2  TZW    3     3       1          7
    # 3  TZW    2     2       1          5
    # 4  TZW    3     0       1          3

What is your question? What is the issue? – Shan R Jun 01 '20 at 07:34 — Shan R, Jun 01 '20 at 07:34

score 3 · Accepted Answer · answered Jun 01 '20 at 07:41

Names of list elements can be accessed as using names()

names(listDF)
[1] "DF1" "DF2" "DF3"

Your loop needs to be modified to:

for (i in (1:3)) {write.xlsx(listDF[i],file=paste0(names(listDF)[i],".xlsx"))}

Also note that using paste generates a whitespace between your DF name and .xlsx. Using paste0 discards the whitespace.

score 2 · Answer 2 · answered Jun 01 '20 at 07:43

2

You can use purrr's imap using which you can access data and name.

purrr::imap(listDF, ~openxlsx::write.xlsx(.x, paste0(.y, '.xlsx')))

Or in base using Map :

Map(openxlsx::write.xlsx, listDF, paste0(names(listDF), '.xlsx'))

answered Jun 01 '20 at 07:43

Ronak Shah

377,200
20
156
213

score 2 · Answer 3 · 2020-06-01T08:29:42.777

To retrieve names from a list in R you can run:

df_names <- names(listDF)

Then you can use them in your loop:

for (i in (1:length(listDF))) {
  write.xlsx(listDF[[i]],
             file = paste0(df_names[i], ".xlsx"))
}

Note that you can refer to your data frames as listDF[[]] (to return a data.frame, not a list, see the documentation) and it's generally better to store files without spaces, so use paste0 instead of paste (see the documentation).

lapply

If you would like to use lapply, it would be probably necessary to write a function like lapply(listDF, function(i) {...}), where i's are your data frames.

Or, probably you can iterate over indexes and get data frames names inside lapply like that:

lapply(seq_along(listDF),
       function(i) {write.xlsx(listDF[[i]],
                      paste0(names(listDF)[[i]], ".xlsx"))
                   }
)

How to name excel files as the names of the data frames in a list in R

3 Answers3

lapply