Saving Excel files in loop in r

Question

I have data frame and created a subset of it. I split the data frame and its subset by a variable factors. I want to save it in excel file. I want to write a loop to create multiple excel files data frame and subset files are in sheets by a variable factor.

I had written a code its just saving the last kind of variable workbook. How to create all the workbooks.

rm(list = ls())

mtcars


split_mtcars <- split(mtcars, mtcars$cyl)
split_mtcars_subset <- split(mtcars[,2:4], mtcars$cyl)

cyl_type <- names(split_mtcars)


for(i in length(cyl_type)){
wb <- createWorkbook()
addWorksheet(wb, "raw")
addWorksheet(wb, "subset")

writeData(wb, 1, split_mtcars[[i]])
writeData(wb, 2, split_mtcars_subset[[i]])

saveWorkbook(wb, file = paste0(cyl_type[i],".xlsx"), overwrite = TRUE)
}

Thanks In advance

You are not sequencing through the names, but only getting the length nr. Make a sequence by writting ‘for(i in 1:length(cyl_type))’ — Jelle, Feb 15 '19 at 23:29
No need for second split. Use first `split_mtcars[[i]][,2:5]` — Parfait, Feb 16 '19 at 01:04

score 0 · Answer 1 · answered Feb 16 '19 at 01:14

Consider by to split your data frame by factor(s) to avoid the need of intermediate objects and hide the loop. Below outputs your workbook and builds a list of data frames.

split_mtcars <- by(mtcars, mtcars$cyl, function(sub) {
    wb <- createWorkbook()
    addWorksheet(wb, "raw")
    addWorksheet(wb, "subset")

    writeData(wb, 1, sub)
    writeData(wb, 2, sub[,2:5])

    saveWorkbook(wb, file = paste0(sub$cyl[1],".xlsx"), overwrite = TRUE)

    return(sub)   # TO REPLICATE split()
})

Saving Excel files in loop in r

1 Answers1