How to add totals by group using gt

Question

With the small reproducible example below, how can I add total by group, specifically, by year in my example? I can add grand total by using adorn_totals from the janitor package but I am wondering about totals by year (2012, 2013) Thanks in advance

library(dplyr)
library(tidyr)
library(janitor)
library(gt)

a <- structure(list(SampleDate = structure(c(15710, 15713, 15713, 
15710, 15710, 15713, 15713, 15710, 15708, 15713, 15712, 15708, 
15708, 15713, 15712, 15708), class = "Date"), year = c("2012", 
"2013", "2013", "2012", "2013", "2013", "2013", "2013", "2013", 
"2012", "2013", "2013", "2013", "2013", "2013", "2013"), F = c(0, 
1, 0, 0, 0, 1, 0, 0, 0, 22, 0, 0, 0, 65, 0, 0), W = c(0, 0, 1, 
0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0), S = c(0, 0, 0, 0, 1, 
0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0), LF = c(1, 0, 0, 1, 0, 0, 0, 
1, 0, 0, 0, 0, 1, 0, 0, 1)), class = "data.frame", row.names = c(NA, 
-16L))

   SampleDate year  F W S LF
1  2013-01-05 2012  0 0 0  1
2  2013-01-08 2013  1 0 0  0
3  2013-01-08 2013  0 1 0  0
4  2013-01-05 2012  0 0 0  1
5  2013-01-05 2013  0 0 1  0
6  2013-01-08 2013  1 0 0  0
7  2013-01-08 2013  0 1 0  0
8  2013-01-05 2013  0 0 0  1
9  2013-01-03 2013  0 0 1  0
10 2013-01-08 2012 22 0 0  0
11 2013-01-07 2013  0 1 0  0
12 2013-01-03 2013  0 0 1  0
13 2013-01-03 2013  0 0 0  1
14 2013-01-08 2013 65 0 0  0
15 2013-01-07 2013  0 1 0  0
16 2013-01-03 2013  0 0 0  1

a %>% 
  group_by(year,SampleDate) %>% 
  summarise(W = sum(W), F = sum(F), LF = sum(LF), S = sum(S)) %>% 
  adorn_totals() %>% 
  gt(groupname_col = "year") %>% 
  tab_header(title = md("Fish Observations"), subtitle = md("Total number of fish by year"))

Answer 1

You can use summary_rows() for the group totals and grand_summary_rows() for the grand total. Groups are inherited from the data frame.

a |>
  mutate(SampleDate = as.character(SampleDate)) |>
  group_by(year, SampleDate) |>
  summarise(across(c(W, F, LF, S), sum)) |>
  gt() |>
  summary_rows(
    columns = -SampleDate,
    fns =  list(label = "Total", fn = "sum"),
    side = "bottom"
  ) |>
  grand_summary_rows(columns = -SampleDate,
                     fns = list(label = "Grand Total", fn = "sum")) |>
  tab_header(
    title = "Fish Observations",
    subtitle = "Total number of fish by year"
  )

Answer 2

Perhaps there is a more straightforward approach using gt::summary_rows but here is one option to get you close to your desired result using some bind_rows, summarise and a group_split + map step:

library(dplyr, warn = FALSE)
library(purrr)
library(gt)

a %>%
  mutate(SampleDate = as.character(SampleDate)) %>%
  group_by(year, SampleDate) %>%
  summarise(across(c(W, F, LF, S), sum)) %>%
  group_split() %>%
  map(
    ~ bind_rows(.x, summarise(.x,
      year = unique(year),
      SampleDate = "Total", across(c(W, F, LF, S), sum)
    ))
  ) %>%
  bind_rows() %>%
  bind_rows(
    .,
    summarise(.,
      year = "Grand Total",
      across(c(W, F, LF, S), ~ sum(.x[SampleDate == "Total"])),
      SampleDate = "-"
    )
  ) %>% 
  gt(groupname_col = "year") %>%
  tab_header(
    title = md("Fish Observations"),
    subtitle = md("Total number of fish by year")
  )