How to export a list of dataframes generated as output within R to a single excel sheet

Question

below is a reproducible sample dataframe generated in R. The number of colums is the same for each investor(given by ID). What is different is the number of rows. I would like to export this multiple list of data frames(for each investor) into a single excel sheet. There are over 3000 investors(with differeing number of rows). Please help out

 [[993]]   
   investor asset quantity    price   datetime    RG_count  RL_count  PG_count  PL_count
1    1011     MC   2200          8     2016-03-02        0        0        0        0
2    1011     NIJL  100          50     2016-02-22       NA       NA       NA       NA
3    1011     RPAL   300          2     2016-02-16        0        0        0        0
[[994]]
  investor  asset  quantity    price    datetime            RG_count RL_count PG_count PL_count
1    1156   LOYV   1400        10.54   2010-09-15 01:00:00        0        0        1        0

[[995]]
  investor asset quantity   price            datetime RG_count RL_count PG_count PL_count
1    1140   LPC    13272 551.302 2017-03-27 01:00:00        0        0        1        0

[[996]]
  investor asset quantity    price      datetime             RG_count RL_count PG_count PL_count
1    1941   MBK    2700     62.20     2017-04-24 01:00:00        0        0        0        3

[[997]]
  investor asset quantity price   datetime              RG_count RL_count PG_count PL_count
1    1944   JFM   -79040  17.00   2011-07-14 01:00:00        0        0        1        0
2    1944   MC  -221490  3.00     2010-10-20 01:00:00        0        0        1        0
3    1944  RAPL   -59340  1.20    2012-03-13 00:00:00        0        0        0        0
4    1944   XT   -56300  1.75     2012-03-22 00:00:00       NA       NA       NA       NA

You could start by reading up on the excellent `openxlsx` package. Then you can add sheets and content procedurally. Not sure how usable an excel with 3000 sheets will be thou — dario, Oct 08 '21 at 15:46
You could use `bind_rows()` from the `dplyr` package to create a single data frame and then output that to Excel. — norie, Oct 08 '21 at 15:50

SourceCoda · Answer 1 · 2021-10-08T23:17:27.397

As was mentioned in the comments, I recommend using bind_rows() from the dplyr package to append your data frames, and write.xlsx from the openxlsx package to create your new file.

install.packages("dplyr", "openxlsx")
library("dplyr", "openxlsx")

If all of your data exist in a single data frame, good news! Your adventure ends here.

example <- bind_rows(investors_dataframe)
write.xlsx(example, "/Users/Username/Documents/filename.xlsx")

However, if you are trying to combine multiple data frames, a good approach would be to create a list of all your data frames, then use that list as an argument for bind_rows:

example2 <- bind_rows(list_of_dataframes)
write.xlsx(example2, "/Users/Username/Documents/filename.xlsx")

score 0 · Answer 2 · answered Oct 08 '21 at 23:23

The 3 most common solutions to bind lists are:

# Base R
do.call("rbind", myList)
# dplyr
dplyr::bind_rows(myList)
# data.table
data.table::rbindlist(myList)

Benchmark is here

The openxlsx or writexl package can be a good choice to write out your data.

Benchmark is here

How to export a list of dataframes generated as output within R to a single excel sheet

2 Answers2