Illustration of a function to make summary tables with totals

library(tidyverse)

regions <- c("USA", "Australia", "China")
years <- 2017:2023L
markets <- letters[1:4]

nn <- 1000
sample_n <- function(var, n = nn) {
  var <- rlang::ensym(var)
  eval(expr(sample(!!var, n, replace = TRUE)))
}

df <- tibble(region = sample_n(regions),
             year = sample_n(years),
             market = sample_n(markets),
             value = rnorm(n = nn, mean = 5))

head(df)
#> # A tibble: 6 × 4
#>   region     year market value
#>   <chr>     <int> <chr>  <dbl>
#> 1 USA        2021 a       7.35
#> 2 Australia  2017 d       5.55
#> 3 Australia  2017 b       3.22
#> 4 China      2019 b       5.47
#> 5 China      2019 d       5.71
#> 6 Australia  2022 c       7.06

make_table <- function(df, val_var, row_var, col_var, 
                       fun = \(x) sum(!is.na(x))) {
  
  rows <-
    df |> 
    group_by({{ row_var }}, {{ col_var }}) |>
    summarize(.val = {{ fun }} ({{ val_var }}), .groups = "drop") |>
    pivot_wider(names_from = {{ col_var }}, values_from = .val)
  
  total_col <- 
    df |> 
    group_by({{ row_var }}) |>
    summarize(Total = {{ fun }} ({{ val_var }}), .groups = "drop")
  
  total_row <- 
    df |> 
    group_by({{ col_var }}) |>
    summarize(.val = {{ fun }} ({{ val_var }}), .groups = "drop") |>
    mutate({{ row_var }} := "Total") |>
    pivot_wider(names_from = {{ col_var }}, values_from = .val)
  
  total_total <-
    df |> 
    summarize(Total = {{ fun }} ({{ val_var }}), .groups = "drop") 
  
  rows |>
    inner_join(total_col, join_by({{ row_var }})) |>
    bind_rows(
      total_row |>
        bind_cols(total_total))
}

df |> 
  make_table(val_var = value, 
             row_var = region, 
             col_var = market)
#> # A tibble: 4 × 6
#>   region        a     b     c     d Total
#>   <chr>     <int> <int> <int> <int> <int>
#> 1 Australia    78    97    80   110   365
#> 2 China        73    74    84    74   305
#> 3 USA          76    80    92    82   330
#> 4 Total       227   251   256   266  1000

df |> 
  make_table(val_var = value, 
             row_var = region, 
             col_var = market,
             fun = mean)
#> # A tibble: 4 × 6
#>   region        a     b     c     d Total
#>   <chr>     <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 Australia  4.77  4.72  5.20  5.05  4.93
#> 2 China      4.94  4.96  5.02  4.80  4.93
#> 3 USA        5.34  5.05  4.94  5.16  5.11
#> 4 Total      5.02  4.90  5.05  5.01  4.99

df |> 
  make_table(val_var = value, 
             row_var = market, 
             col_var = year)
#> # A tibble: 5 × 9
#>   market `2017` `2018` `2019` `2020` `2021` `2022` `2023` Total
#>   <chr>   <int>  <int>  <int>  <int>  <int>  <int>  <int> <int>
#> 1 a          36     42     28     25     31     32     33   227
#> 2 b          41     39     37     31     47     31     25   251
#> 3 c          33     21     37     50     32     49     34   256
#> 4 d          35     44     36     32     38     41     40   266
#> 5 Total     145    146    138    138    148    153    132  1000

df |> 
  make_table(val_var = value, 
             row_var = market, 
             col_var = year,
             fun = mean)
#> # A tibble: 5 × 9
#>   market `2017` `2018` `2019` `2020` `2021` `2022` `2023` Total
#>   <chr>   <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl> <dbl>
#> 1 a        5.19   5.07   4.93   4.77   5.10   4.77   5.18  5.02
#> 2 b        4.99   4.86   4.97   4.76   5.02   4.76   4.80  4.90
#> 3 c        5.06   5.05   5.04   5.13   5.00   5.02   4.99  5.05
#> 4 d        5.11   5.09   4.80   4.93   5.00   4.99   5.12  5.01
#> 5 Total    5.09   5.02   4.94   4.94   5.03   4.91   5.04  4.99

^{Created on 2024-06-18 with reprex v2.1.0}

iangow/summ_table.md