Take list of tibbles in R, summarize them two different ways, then write each summary as a separate sheet on an excel file named after each tibble

Question

I have a list of tibbles. I want to summarize these tibbles in different ways and write these summaries to an excel workbook for each tibble.

 For example, I want the total of each type of item in colA (how many ABs, how many BAs etc.) I also want the total of each type of item in colC.  I then want these totals for each type as a kind of table (or df).

 I want to take these summary tables and have them be written as separate sheets on an excel workbook named by each tibble. In this example I would want 3 different work books (one for tibble 1 , one for tibble 2, one for tibble 3). Each tibble would have two sheets (one for a totals summary from colA and one that's a totals summary from colC). Each sheet would be named corresponding to the tibble's title (1,2,3). How do I do this?

Thank you for your time and thoughts.

`1` <- tibble(colA = c("AB", "AB", "BA", "BA"), colB = c("K", "K", "K", "K"), colC=c("TP", "TP", "ND", "ND") )
`2` <- tibble(colA = c("CD", "AB", "BA", "BA"), colB = c("L", "L", "L", "L"), colC=c("TP", "ND", "ND", "ND") )
`3` <- tibble(colA = c("CD", "AB", "BA"), colB = c("M", "M", "M"), colC=c("TP", "ND", "ND"))

MyList <- list(`1`, `2`, `3`)

MyList

MyList
[[1]]# A tibble: 4 × 3 
colA colB colC 
<chr> <chr> <chr>
1 AB K TP 
2 AB K TP 
3 BA K ND 
4 BA K ND 

[[2]]# A tibble: 4 × 3 
colA colB colC 
<chr> <chr> <chr>
1 CD L TP 
2 AB L ND 
3 BA L ND 
4 BA L ND 

[[3]]# A tibble: 3 × 3 
colA colB colC 
<chr> <chr> <chr>
1 CD M TP 
2 AB M ND 
3 BA M ND 

 

Answer 1

We loop over the list (after naming it), select the columns of interest, split the data into a list of tibbles with split.default, loop over the inner list, get the count of the column, store the output in an object 'out'. Loop over the sequence of the output, write the inner list elements as sheets in the work book created with write.xlsx. As these are already named, the sheets will be named with the corresponding column name

library(purrr)
library(dplyr)
library(openxlsx)
names(MyList) <- seq_along(MyList)
 out <- map(MyList, ~ .x %>%
    select(colA, colC) %>%
    split.default(names(.)) %>%
     map(~ .x %>%
        count(across(everything()))) )
for(i in seq_along(out))
   write.xlsx(out[[i]], paste0(names(MyList)[i], ".xlsx"))