How to create a loop with openxslx and R?

Question

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

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

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)
}

Can you share some code please of what you already tried in terms of creating/updating your Excel file? — deschen
@deschen I didn't code the excel file, I just made it in excel. — RL_Pug
@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

RL_Pug RL_Pug · Accepted Answer · 2020-12-02T23:50:29

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)
}

How to create a loop with openxslx and R?

1 Answers