Export a data frame as multiple worksheets in a single Excel file in R

Question

I have a dataframe like this dummy data:

df <- data.frame(
  stringsAsFactors = FALSE,
  id = c(
    "100-1",
    "100-1",
    "100-2",
    "100-2",
    "100-5",
    "100-5",
    "100-5",
    "100-9",
    "100-9",
    "100-9"
  ),
  name = c(
    "Aggi",
    "Aggi",
    "Nina",
    "Nina",
    "Katrin",
    "Katrin",
    "Katrin",
    "Tom",
    "Tom",
    "Tom"
  ),
  date = c(
    "5/15/2019",
    "5/15/2019",
    "5/15/2019",
    "5/15/2019",
    "5/15/2019",
    "5/15/2019",
    "5/15/2019",
    "5/15/2019",
    "5/15/2019",
    "5/15/2019"
  ),
  start_time = c(
    "11:32:00",
    "11:37:30",
    "12:04:00",
    "12:08:00",
    "13:53:00",
    "13:55:00",
    "17:28:00",
    "17:29:00",
    "17:31:00",
    "17:34:45"
  ),
  end_time = c(
    "11:37:30",
    "12:04:00",
    "12:08:00",
    "13:53:00",
    "13:55:00",
    "17:28:00",
    "17:29:00",
    "17:31:00",
    "17:34:45",
    "17:38:45"
  )
)

After series of data wrangling I want to export the data to a single Excel file that contains multiple worksheets for each variable name. I found a workaround here with openxlsx library.

# export one .xlsx file with sheet for each name:

    library(tidyverse)
    library(lubridate)
    library(openxlsx)

# make a list of names
        names <-
          as.list(
            df$name) %>%
          as.character() %>% 
          unique()

 # create wordbook       
        wb <- createWorkbook()

        for (d in names) {
           addWorksheet(wb, sheetName = d)
          writeData(wb, d, df)
        }

        saveWorkbook(wb, 'data.xlsx')

My script is working and will export a single file with multiple worksheets, but each worksheets contains all the data not sub-group of data. I want one sheet for data belongs to Aggi (work sheet name = Aggi) and so on. What am I missing? Apparently my for statement has a problem.

Answer 1

In your writeData function you need to subset the data for each corresponding worksheet.

So in your for loop, you need something like:

for (d in names) {
  addWorksheet(wb, sheetName = d)
  writeData(wb, d, df[df$name == d, ]) ## Subsets on the name
}