Write multiple data frames to correct excel sheets using for loops

Question

I have a nested for loop situation where I am looping through several excel work sheets, applying several functions, generating a single data.frame from each work sheet, and writing all of this back to a new work book whose sheet names are the same as the original work book.

At the moment the sheet names are correct in the new work book, but the data frames keep writing over each other so that every sheet has the same data frame. How can I get each data frame to write to its respective excel sheet?

library(xlsx)
library(agricolae)

#Read all worksheets in one file
read_excel_allsheets <- function(filename) {
 sheets <- readxl::excel_sheets(filename)
 x <-    lapply(sheets, function(X) readxl::read_excel(filename, sheet = X))
 names(x) <- sheets
 x
  }

mysheets <- read_excel_allsheets("Loop_help.xls")`

for (i in 1:length(mysheets)){  #loop through sheets in workbook

file<-as.data.frame(mysheets[i])

  for(i in 2:ncol(file)){    #loop through columns in sheets

if(var(file[,i]) > 0){   #exclude columns that are all zeros

#create data frame (df)  
#do stuff

}
  #dostuff

  }

  #do more stuff

  n<-excel_sheets("Loop_help.xls") #sheet names

  for (i in 1:length(mysheets)) {#write new sheets to excel
    if (i == 1)
      write.xlsx(df, file="Loop.Help.xls", sheetName = n[i])
    else write.xlsx(df, file="Loop.Help.xls", sheetName = n[i],
                    append = TRUE)       
    }
}

At the end with `write.xlsx`, where is `objects` being created? — Parfait, Jul 12 '16 at 02:54
You nest the same loop at the end, reusing `i`. Try removing second `for (i in 1:length(mysheets))` but keep the if/else statement. — Parfait, Jul 12 '16 at 12:54

score 0 · Accepted Answer · answered Jul 13 '16 at 01:31

Thank you to @Parfait for leading me to this answer.

Not only did I have a superfluous for loop in there, but I was calling i in both loops, when I needed to use a different letter (k).

Here is the final code:

for (k in 1:length(mysheets)){#loop through sheets in workbook
  file<-as.data.frame(mysheets[k]) 

  for(i in 2:ncol(file)){#loop through columns in sheets

if(var(file[,i]) > 0){#exclude columns that are all zeros

  #create data frame (df)
  #do stuff
  }
  }
  #do more stuff

  n<-excel_sheets("Loop_help.xls") #sheet names

          if (k == 1)
      write.xlsx(df, file="Loop.help.xls", sheetName = n[k])#write first sheet
    else write.xlsx(df, file="Loop.help.xls", sheetName = n[k],
                    append = TRUE) #append additional sheets

  }

How/where would I add in the filepath if I want it saved somewhere besides my wd? — jesstme, Jan 10 '17 at 02:42

Write multiple data frames to correct excel sheets using for loops

1 Answers1