Multiple split and export to separate excel tabs

[Slavek]

Hi,
I have this simple df:

df <- data.frame(
  stringsAsFactors = FALSE,
               URN = c("aaa","bbb","ccc","ddd",
                       "eee","fff","ggg","hhh","iii","jjj","kkk","lll",
                       "mmm"),
           Country = c("xxx","xxx","yyy","yyy",
                       "yyy","zzzz","zzzz","yyy","zzzz","xxx","zzzz","yyy",
                       "zzzz"),
              Type = c("Sales","Service","Sales",
                       "Sales","Service","Service","Sales","Sales","Service",
                       "Sales","Service","Service","Sales")
)

I need to split this df by Country and Type having 6 separate data frames as a result which should be exported into one Excel workbook with 6 tabs. I know I can do it manually:

df.xxx.Sales <- subset(df, Country=='xxx' & Type=='Sales')
df.xxx.Service <- subset(df, Country=='xxx' & Type=='Service')
df.yyy.Sales <- subset(df, Country=='yyy' & Type=='Sales')
df.yyy.Service <- subset(df, Country=='yyy' & Type=='Service')
df.zzzz.Sales <- subset(df, Country=='zzzz' & Type=='Sales')
df.zzzz.Service <- subset(df, Country=='zzzz' & Type=='Service')

require(openxlsx)
tables <- list("xxx Sales" = df.xxx.Sales, "xxx Service" = df.xxx.Service, 
               "yyy Sales" = df.yyy.Sales, "yyy Service" = df.yyy.Service, 
               "zzzz Sales" = df.zzzz.Sales, "zzzz Service" = df.zzzz.Service)
write.xlsx(tables, file = "C:/Users/.../Multiple Export table.xlsx")

but I cannot do it with a real, big data frame.
Is any way of splitting by Country and Type without manual specifications.
Is it possible to do export to excel without listing all data frames and tab names?

[nirgrahamuk]

gsrc <- group_by(df,
          Country,
          Type)

keyvar <- gsrc %>% 
  group_keys() %>% 
  mutate(key=paste(Country, Type)) %>% 
  pull(key)

gsrc %>% 
group_split()%>%
 as.list() %>% 
setNames(keyvar)%>% 
write.xlsx(file="out2.xlsx")

[Slavek]

Thank you very much! Very useful!
I know the question was answered but what thick should be used to export the data frames to separate work books instead of separate tabs of one workbook? This option might be useful for large data frames...

[Slavek]

I guess, the change should be in the last line of your code...