How to export a nested regression output

Question

I have just started using map and broom to create a nested regression output that looks as follows:

  Source | Results
   <chr> |   <list>
    A    | <tibble [29 x 5]>
    B    | <tibble [29 x 5]>
    C    | <tibble [29 x 5]>
    D    | <tibble [29 x 5]>
    E    | <tibble [29 x 5]>

I want to export each source in to a separate excel sheet (within the same workbook).

I usually use a for loop using the xlsx package like this:

x <- example
for (i in x) {
  Models[[i]] <- dt[,as.list(summary(lm(.SD[[i]] ~ .SD$example 
                                           )))]
}    
Models <- lapply(Models,function(z) z$coefficients)        
    wb <- createWorkbook()
        sheet <- list()
        for (i in x) {
          sheet[[i]] <- createSheet(wb, sheetName = i)
          addDataFrame(Models[[i]], sheet[[i]],startColumn = 1, startRow = 1, row.names = TRUE,col.names = TRUE)
        }
        saveWorkbook(wb,'example.xlsx')

Is there an alternative way to do this in a tidy way?

Answer 1

From start to finish it might look something like the following. Using the iris dataset, group_split splits the data into three dataframes by Species, map iterates over the list of dataframes, applying lm, set_names gives the list elements names (necessary for sheet names), and write_xlsx turns the named list of dataframes into an XLSX with three sheets:

library(dplyr)   # for group_split
library(purrr)   # for map and set_names
library(broom)   # for augment
library(writexl) # for write_xlsx


group_split(iris, Species) %>% 
    map(~ lm(Sepal.Width ~ Sepal.Length, .) %>% augment) %>% 
    set_names(unique(iris$Species)) %>% 
    write_xlsx("iris.xlsx")

You'll have to forgive me for not using the xlsx package. I usually spend more time trying to get rJava to work than I do using the package itself, so I use writexl instead, which is also quite good. If you know your way around xlsx than it should be possible to adapt the above code to your work flow.