Write list of data frames to Excel in multiple sheets

Question

I have a data frame that I run a few analyses on and I want to export the results to Excel files. One workbook per group and each analysis results on a separate tab. I would prefer to use openxlsx for the exporting to take java out of the equation.

library(plyr)
library(dplyr)
library(openxlsx)

df <- iris

# Analysis 1
results1 <- df %>%
  group_by(Species) %>%
  summarise(count = n())

# Analysis 2
results2 <- df %>%
  group_by(Species) %>%
  summarise(mean.sl = mean(Sepal.Length),
            mean.sw = mean(Sepal.Width))

My desired export output would be three Excel workbooks, setosa.xlsx, versicolor.xlsx, and virginica.xlsx; each with two sheets "results1" and "results2" containing only their within-group results. Meaning no setosa rows in the versicolor Excel file.

I tried to split results1 and results2 into lists of data frames in order to use lappy with write.xlsx but I'm not able to make it work.

r1_list <- dlply(results1, .(Species))
r2_list <- dlply(results2, .(Species))

Other suggestions?

Also, I believe the xlsx package is what you're looking for — alexwhitworth
– alexwhitworth, Commented Jul 28, 2016 at 19:57
xlsx depends on java, which I'm trying to avoid, if possible — Jake Russ
– Jake Russ, Commented Jul 28, 2016 at 20:13
The WriteXLS package worked fine for me. Just pass it a named list of data.frames and those will be tabs in the result. — Frank
– Frank, Commented Jul 28, 2016 at 20:27

PhilC · Accepted Answer · 2016-07-28 21:19:40Z

Sample code

library(plyr)
library(dplyr)
library(openxlsx)


setwd("c:/r")
df <- iris

# Analysis 1
results1 <- df %>%
  group_by(Species) %>%
  summarise(count = n())

# Analysis 2
results2 <- df %>%
  group_by(Species) %>%
  summarise(mean.sl = mean(Sepal.Length),
            mean.sw = mean(Sepal.Width))

#get the unique species
sp <- unique(df$Species)

createSpreadsheets <- function(species,r1,r2){
  ## Create new workbooks
  wb <- createWorkbook() 

  ## Create the worksheets
  addWorksheet(wb, sheetName = "Results1" )
  addWorksheet(wb, sheetName = "Results2" )

  ## Write the data
  writeData(wb, "Results1", r1)
  writeData(wb, "Results2", r2)

  ## Save workbook to working directory 
  saveWorkbook(wb, file = paste(species,".xlsx", sep=""), overwrite = TRUE)
}

## create spreadsheets by calling our function for each species
for(s in sp){
  createSpreadsheets(s,results1[results1$Species==s,],results2[results2$Species==s,])
}

Collectives™ on Stack Overflow

Write list of data frames to Excel in multiple sheets

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related