1

My Global Environment in R Studio consists of multiple data frames. I would like to write the data within each data frame to each tab within a single excel file.

To make this question reproducible, let us consider the following data frames in the Global Environment:

df1 <- data.frame(ID = c("001", "002", "003"), scores = c(5, 7, 6))
df2 <- data.frame(ID = c("001", "002", "003"), scores = c(3, 6, 4))
df3 <- data.frame(ID = c("001", "002", "003"), scores = c(7, 6, 7))
df4 <- data.frame(ID = c("001", "002", "003"), scores = c(4, 3, 7))

The desired output is one excel file, with 4 tabs, with the first tab named "df1" and containing the data of df1, and so and so forth.

I have tried the following, but they all do not work:

lapply(ls(), function(x) write.xlsx(ls(), "Overall_Output.xlsx", sheetName = x, append = TRUE, col.names = TRUE, row.names = FALSE, showNA = FALSE))

lapply(names(ls()), function(x) write.xlsx(ls(), "Overall_Output.xlsx", sheetName = x, append = TRUE, col.names = TRUE, row.names = FALSE, showNA = FALSE))

lapply(names(ls()), function(x) write.xlsx(ls[[x]], "Overall_Output.xlsx", sheetName = x, append = TRUE, col.names = TRUE, row.names = FALSE, showNA = FALSE))

I know on Stackoverflow there are solutions to my query using a for loop. However, there are no solutions where the apply family of functions (such as lapply) are used to arrive at the answer.

Appreciate any help using lapply to solve this problem. Thanks!

DTYK
  • 1,098
  • 1
  • 8
  • 33

2 Answers2

4

The problem is that ls() returns character values, not the data frames. So none of the code you use actually accesses the data frames itself. Plus, it's very dangerous to use ls(), as it will add each and every object in your global environment.

Start with placing your data frames in a list:

alldf <- list(
  df1 = data.frame(ID = c("001", "002", "003"), scores = c(5, 7, 6)),
  df2 = data.frame(ID = c("001", "002", "003"), scores = c(3, 6, 4)),
  df3 = data.frame(ID = c("001", "002", "003"), scores = c(7, 6, 7)),
  df4 = data.frame(ID = c("001", "002", "003"), scores = c(4, 3, 7))
)

Once you've done that, you can simply use write.xlsx() of the openxlsx package like this:

library(openxlsx)
write.xlsx(alldf, "text.xlsx")

It will automatically set the names as well.

If you need to construct that list from your global environment, you can do something like this:

allobj <- ls()
alldf <- sapply(allobj, get, simplify = FALSE)
alldf <- alldf[sapply(alldf, inherits, "data.frame")]
Joris Meys
  • 106,551
  • 31
  • 221
  • 263
1

First we create an empty .xlsx to append our sheets:

library(xlsx)
wb = xlsx::createWorkbook(type='xlsx')
saveWorkbook(wb,"Overall_Output.xlsx")
rm(wb)#remove wb to avoid it in ls()

Then we call each dataframe by their name, using get0() function:

lapply(ls(), function(x) write.xlsx(get0(x), "Overall_Output.xlsx", sheetName = x, append = TRUE, col.names = TRUE, row.names = FALSE, showNA = FALSE))
tyumru
  • 417
  • 3
  • 13