3

I created hundreds of data frames in R, and I want to export them to a local position. All the names of the data frames are stored in a vector :

name.vec<-c('df1','df2','df3','df4','df5','df5')

each of which in name.vec is a data frame . what I want to do is to export those data frames as excel file, but I did not want to do it the way below :

library("xlsx")
write.xlsx(df1,file ="df1.xlsx")
write.xlsx(df2,file ="df2.xlsx")
write.xlsx(df3,file ="df3.xlsx")

because with hundreds of data frames, it's tedious and dangerous. I want some thing like below instead :

library('xlsx')
for (k in name.vec) {
  write.xlsx(k,file=paste0(k,'.xlsx'))
}

but this would not work.

Anyone know how to achieve this? your time and knowledge would be deeply appreciated. Thanks in advance.

www
  • 4,124
  • 1
  • 11
  • 22
Jia Gao
  • 1,172
  • 3
  • 13
  • 26
  • you've stored hundreds of data frames in the global environment? eek! `?get` shld be a solid hint to help you on your way. – hrbrmstr Sep 11 '17 at 11:39
  • that' horrible but true, my current work need to run hundreds of regressions in one loop, and I got 108 loops, also looking forward to improve my code.I'll try your suggestion – Jia Gao Sep 11 '17 at 11:51
  • 2
    store them in a list then you can `purrr::walk` for `for` or `lapply` across it. – hrbrmstr Sep 11 '17 at 11:51
  • Why doesn't your for-loop work? What's the error message? – lebelinoz Sep 11 '17 at 12:23
  • It will produce 'xlsx' files with the correct name I want, but the content in the file is not what passed from data frame with the same name but only one cell as 'df1', for illustration's sake. Exactly like @Runge said in the beginning of his answer. – Jia Gao Sep 13 '17 at 01:29

1 Answers1

4

The first reason the for loop doesn't work is that the code is attempting to write a single name, 'df1' for example, as the xlsx file contents, instead of the data frame. This is because you're not storing the data frames themselves in the "name.vec" you have. So to fix the for loop, you'd have to do something more like this:

df.list<-list(df1,df2,df3)
name.vec<-c('df1','df2','df3')

library('xlsx')

for (k in 1:length(name.list)){
  write.xlsx(df.list[[k]],file=paste0(name.vec[k],'.xlsx'))
}

However, for loops are generally slower than other options. So here's another way:

sapply(1:length(df.list),
       function(i) write.xlsx(df.list[[i]],file=paste0(name.vec[i],'.xlsx')))

Output is 3 data frames, taken from the df list, named by the name vector.

It may also be best to at some point switch to the newer package for this: writexl.

www
  • 4,124
  • 1
  • 11
  • 22
  • Excellent answer, always looking for ways other than `for loop`, that's the really last choice. – Jia Gao Sep 13 '17 at 01:34
  • @JasonGoal - Thanks, yeah, sapply can save a lot of time. – www Sep 13 '17 at 01:36
  • This seems like it is in error. Did you mean `name.vec` where you wrote `name.list` ? – G5W Sep 18 '17 at 23:52
  • @G5W - Thanks for checking this. The OP had originally referred to name.list as a vector which stored the names of their dfs, so I referenced name.list in my paragraph intro. Then I changed it to name.vec in my code because it's a vector, not technically a list. Meanwhile df.list is a list, so named accordingly. This was meant to clarify, but I see now how it might confuse. Is that what you were asking about? – www Sep 19 '17 at 01:01
  • Yes, that helps make things clearer. But since you code (in this answer) never defines `name.list` it might be better to stick purely with `name.vec` – G5W Sep 19 '17 at 01:05
  • @G5W - Fair enough. Thanks for the suggestion. I'll edit that now. – www Sep 19 '17 at 01:08