Using a For-loop to create multiple tables-CodePudding

I'm trying to figure out if there's a way to use for-loops to create multiple tables in one go, all using the same dataset - I'll clarify that in a second. I'm using R Markdown, because I need to compile it in a PDF in the end, if that makes any difference.

So I'm working with one database and trying to separate it into multiple tables (using gt) based on the values of one of the variables. Let's say that my dataset is about towns' annual budgets.

data <- data.frame(x=1, y=1:15, z=16:30)
colnames(data) <- c("County", "Town", "Budget")
sample.space <- c(1:5)
number.samples <- 15
data$County <- sample(sample.space, number.samples, replace = TRUE)
sample.space2 <- c(2500:5000)
data$Budget <- sample(sample.space2, number.samples, replace = FALSE)

(Please excuse my somewhat clumsy creation, I don't often deal with random numbers these days.)

So what I'd like to do is create separate tables that show the annual budget for all the towns within County X. I've been able to do that just fine by creating new datasets for each county and then making a table from the new dataset. That's fine when you're only looking at 5 counties, but when it's 50, that gets to be a bit cumbersome.

I feel like there should be some way to do this using a for-loop. My gut instinct is to do it like this, but when I compile, nothing prints on the PDF.

library(gt)

for (i in 1:5) {
  data[data$County == i, ] %>%
    gt() %>%
    tab_header(
      title = "Annual Budget by County",
    ) 
}

If I pick a county and try the same thing without the loop, I have no problem.

data[data$County == 1, ] %>%
    gt() %>%
    tab_header(
      title = "Annual Budget for County 1",
    )

It's not the end of the world if I can't make it work, but I'm hoping someone more savvy than me can figure it out!

Thanks :)

CodePudding user response：

It is just that in for loop the result is not stored based on the OP's code. We can create a NULL list of length 5 and update the list with the output based on the sequence looped

lst1 <- vector('list', 5)
for (i in 1:5) {
 lst1[[i]] <- data[data$County == i, ] %>%
    gt() %>%
    tab_header(
      title = "Annual Budget by County",
    ) 
}

If we want to create multiple objects, it is possible with assign but not recommended i.e. it is better to keep it in a list

Or another option is split by 'County' and loop with lapply to convert to gt

lst1 <- lapply(split(data, data$County), function(x) 
              gt(x) %>%
      tab_header(
      title = "Annual Budget by County",
    ) )

CodePudding user response：

Edit: Since it appears you also want to output each of these in an Rmarkdown document, I've added instructions on how to do that. However, this solution may only work for knitting an html document with the gt package. Knitting to pdf raises a whole host of other issues outside the scope of this question, but overall you will probably have a better experience using kables instead of gt for knitting to pdf.

Using the purrr package from the tidyverse for iteration:

library(gt)
library(htmltools)
library(tidyverse)

counties <- data$County %>% 
  unique() %>%
  sort()

gt_list <- map(.x = counties,  .f = function(x) {
  data %>%
    filter(County == x) %>%
    gt() %>%
    tab_header(
      title = paste("Annual Budget for County", x),
    ) 
})

Then you can wrap your list of gt tables in a tagList from the htmltools package to render each of them in your Rmarkdown document:

tagList(gt_list)

If you absolutely need to knit to pdf, try using kables instead of gt, substituting map with walk (since you are now only iterating for the side-effect of printing a kable). Also, make sure set the chunk option results = "asis":

```{r, results = "asis"}
walk(.x = counties,  .f = function(x) {
  df <- data %>%
    filter(County == x) 
    
  print(knitr::kable(df, caption = paste("Annual Budget for County", x)))
  cat('\n\n\n\n')
})
```

And finally, if you simply wanted to just output each gt to its own pdf file (or similar), you could do as follows:

walk(.x = counties,  .f = function(x) {
  data %>%
    filter(County == x) %>%
    gt() %>%
    tab_header(
      title = paste("Annual Budget for County", x),
    ) %>%
    gtsave(paste0("County_", x, ".pdf"))
})

CodePudding user response：

After some digging, I was able to find two extremely helpful links that solved my Knit to PDF issues with gt tables created within a loop. YMMV:

Use as_latex()
Modify your Rmarkdown yaml

So, your entire Rmarkdown file might look something like this:

---
title: "gt package pdf output"
output:
  pdf_document: default
header-includes:
- \usepackage{caption} # Insert the package used by gt
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = FALSE)
```

```{r, include=FALSE}

library(tidyverse)
library(gt)

data <- data.frame(x=1, y=1:15, z=16:30)
colnames(data) <- c("County", "Town", "Budget")
sample.space <- c(1:5)
number.samples <- 15
data$County <- sample(sample.space, number.samples, replace = TRUE)
sample.space2 <- c(2500:5000)
data$Budget <- sample(sample.space2, number.samples, replace = FALSE)

counties <- data$County %>% 
  unique() %>%
  sort()

```

```{r, results="asis"}

walk(.x = counties,  .f = function(x) {
  tab_latex <- data %>%
    filter(County == x) %>%
    gt() %>%
    tab_header(
      title = paste("Annual Budget for County", x),
    ) %>%
    as_latex() %>% 
    as.character() %>%
    cat()
})

```