1

I have two dataframes. one is raw data, the other one is aggregated.

My two dataframes are variants of the iris datasets (iris & iris_summary)

iris$ID <- 1:150

iris_summary <- iris %>% 
  group_by(Species) %>% 
  summarize(
    "Count of Species" = n(),
    "Average Sepal.Length" = mean(Sepal.Length),
    "Average Sepal.Width" = mean(Sepal.Width),
    "Average Petal.Length" = mean(Petal.Length),
    "Average Petal.Width" = mean(Petal.Width)
    )

Now I have an excel workbook that I would like to load into my environment that has preset formatting and 4 sheets.

Sheet 1: Instructions Sheet 2: Summary Table Sheet 3: Raw Data Sheet 4: Contact Info

I need to create 3 separate excel files for each "Species" (setosa, veriscolor, and virginica).

In Sheet 2: I need to include the summary stats for each species. This is an example for "setosa" stats are in the Summary Table enter image description here

And in Sheet 3: I need to include the raw data of only "setosa".

enter image description here

Sheets 1 & 4 can be left alone.

What would a loop that does look like? I would need an excel file - using this formatted work book for all 3 species in the Iris dataset. My actual data has about 30 categorical variables (compared to 3 'species' in the iris datasets) and copy and pasting them manually would take hours.

Update Code tried

wb <- loadWorkbook("Sample Workbook.xlsx")

for (i in iris$Species){
  print(i)
  FilterData <- iris %>% filter(Species == i) 
  filename <- paste0(i, "-data.xlsx")
  
  writeData(wb, sheet="Summary Table", i)
}
RL_Pug
  • 697
  • 7
  • 30
  • Can you share some code please of what you already tried in terms of creating/updating your Excel file? – deschen Dec 02 '20 at 21:17
  • Possible duplicate of https://stackoverflow.com/q/34172353/680068 ? – zx8754 Dec 02 '20 at 21:23
  • @deschen I didn't code the excel file, I just made it in excel. – RL_Pug Dec 02 '20 at 21:23
  • @zx8754 that looks like its going the right direction, I can load the excel file into my enviroment, but I'm unsure how to make a loop that will let me create multiple excel files from it in the manner I need. – RL_Pug Dec 02 '20 at 21:24
  • Use forloop, save file with different names. (not tested) – zx8754 Dec 02 '20 at 21:25
  • @FruityPebblePug: I didn't mean that you coded the Excel file yourself, I was just interested in what you already tried to achieve your desired result? So what R code did you already run from the openxlsx package to get the Excel output? – deschen Dec 02 '20 at 21:31
  • @deschen My bad, I've added the code I tried using but its not correct. I can't figure out how to specify that i need to create a new excel file for each "species". – RL_Pug Dec 02 '20 at 21:43
  • @zx8754 Do you have an idea of how that would work? – RL_Pug Dec 02 '20 at 21:47

1 Answers1

1

I was able to solve my question using this code

#Code Works for sheet 2 & 3
for (i in iris_summary$Species){
  print(i)
  FilterData <- iris_summary %>% filter(Species == i) 
  FilterData2 <- iris %>% filter(Species == i)
  
  wb <- loadWorkbook("Sample Workbook.xlsx")
  
  writeData(wb, 2, FilterData)
  writeData(wb, 3, FilterData2)
  saveWorkbook(wb, file = paste0(i, "-data.xlsx"), overwrite = TRUE)
}
RL_Pug
  • 697
  • 7
  • 30