[FIXED] How to create efficiently multiple tables (and flextable) using function/loop in R?

Issue

I'm trying to do a very simple task in R which is creating a Flextable (called Flextable_1, Flextable_2,...,Flextable_35) foreach dataset that I have (dataset_1, dataset_2,...,dataset_35).

The code for the first Flextable_1 is:

Flextable_1 <- 
  flextable(dataset_1)  %>% 
  add_header_row(top = TRUE, values = c("", "Total","CountyA","CountyB"), colwidths = c(1,4,4,4)) %>% 
  theme_vanilla()%>% 
  color(dataset_1, part = "footer", color = "#666666")%>%
  autofit(part = "all")%>%
  align(align = "center", part = "all") %>% 
  merge_h(part = "header")%>%
  merge_v(part = "header")%>%
  set_caption(caption = "Summary Statistics for 0.1Miles Buffer Area")%>%
  add_footer_lines("")%>%
  fontsize(size = 10, part = "all")%>%
  fontsize(size = 10, part = "header", i = 2) %>%
  fontsize(size = 14, part = "header", i = 1) 

So the code for all datasets from 2 to 35 will be (just showing the 35th):

Flextable_35 <- 
  flextable(dataset_35)  %>% 
  add_header_row(top = TRUE, values = c("", "Total","CountyA","CountyB"), colwidths = c(1,4,4,4)) %>% 
  theme_vanilla()%>% 
  color(dataset_35, part = "footer", color = "#666666")%>%
  autofit(part = "all")%>%
  align(align = "center", part = "all") %>% 
  merge_h(part = "header")%>%
  merge_v(part = "header")%>%
  set_caption(caption = "Summary Statistics for 0.35Miles Buffer Area")%>%
  add_footer_lines("")%>%
  fontsize(size = 10, part = "all")%>%
  fontsize(size = 10, part = "header", i = 2) %>%
  fontsize(size = 14, part = "header", i = 1) 

How can I do this in an efficient way so I don't have to write all of these lines for every dataset (from 1 to 35)? Please also note that the caption foreach table changes according to the number of the dataset I'm working with. So the caption for Flextable_1 is "Summary Statistics for 0.1Miles Buffer Area", while the caption for Flextable_35 is "Summary Statistics for 0.35Miles Buffer Area"

Many thanks in advance. Any help is really appreciated!!

Solution

I have created a function for your task. The function finishes the task and assigns the result to the global environment. There is one parameter in the function, the number of tables; this will be used in the loop to iterate through the different tables.

library(dplyr)
library(flextable)

# Create a function that constructs tables
create_flextable <- function(number_table = 1) {
  
  # Get the table you need
  start <- "dataset_"
  table <- get(paste0(start, number_table))
  
  # Run Process
  Flextable <- 
    flextable(table)  %>% 
    add_header_row(top = TRUE, values = c("", "Total","CountyA","CountyB"), colwidths = c(1,4,4,4)) %>% 
    theme_vanilla()%>% 
    color(table, part = "footer", color = "#666666")%>%
    autofit(part = "all")%>%
    align(align = "center", part = "all") %>% 
    merge_h(part = "header")%>%
    merge_v(part = "header")%>%
    set_caption(caption = paste0("Summary Statistics for 0.", number_table, "Miles Buffer Area"))%>%
    add_footer_lines("")%>%
    fontsize(size = 10, part = "all")%>%
    fontsize(size = 10, part = "header", i = 2) %>%
    fontsize(size = 14, part = "header", i = 1) 
  
  # Assigned constructed table to global environment
  assign(paste0("Flextable_", number_table), Flextable, envir = .GlobalEnv)
  
}

# Run loop to create multiple tables
for (i in 1:35) {
  
  create_flextable(number_table = i)
  
}

Answered By - rodrigo franco

Answer Checked By - Pedro (PHPFixing Volunteer)