How can you add group percentages to tables using the gt( ) package?

user_13976677 0
Viewed 531

In a separate post I outline a method for adding overall percentages to a table in the gt( ) package (How can you automate the addition of overall percentages to the row_summary in the gt( ) package?) The solution I identified involved a separate invocation of the row_summary( ) function for each overall row percentage being added. But even this rather clunky solution doesn't work if applied to overall group percentages, as illustrated through the worked example below. Solutions?

# Create baseline data
set.seed(1)
df <- tibble(some_letter = sample(letters, size = 10, replace = FALSE),
             some_group = sample(c("A", "B"), size = 10, replace = TRUE),
             num1 = sample(100:200, size = 10, replace = FALSE),
             num2 = sample(100:200, size = 10, replace = FALSE),
             n = num1 + num2) %>% 
  mutate(across(starts_with("num"), ~(.x)/(n), .names = "pct_{col}"))

> df
# A tibble: 10 x 7
   some_letter some_group  num1  num2     n pct_num1 pct_num2
   <chr>       <chr>      <int> <int> <int>    <dbl>    <dbl>
 1 g           A            194   148   342    0.567    0.433
 2 j           A            121   159   280    0.432    0.568
 3 n           B            164   200   364    0.451    0.549
 4 u           A            112   118   230    0.487    0.513
 5 e           B            125   180   305    0.410    0.590
 6 s           A            137   164   301    0.455    0.545
 7 w           B            101   175   276    0.366    0.634
 8 m           B            135   110   245    0.551    0.449
 9 l           A            180   167   347    0.519    0.481
10 b           B            131   137   268    0.489    0.511

# Target: the weighted group percentages to be added to the table in gt( )
df %>% group_by(some_group) %>%
  summarise_at(vars(num1, num2, n), funs(sum)) %>%
  mutate(across(starts_with("num"), ~(.x)/(n), .names = "pct_{col}"))

# A tibble: 2 x 6
  some_group  num1  num2     n pct_num1 pct_num2
  <chr>      <int> <int> <int>    <dbl>    <dbl>
1 A            744   756  1500    0.496    0.504
2 B            656   802  1458    0.450    0.550


# Create table in gt( ), attempting to use the summary_rows( ) function to pass 
# group-specific percentages for pct_num1, the result of which is that the last
# passed value is recycled across all groups...

gt(df, groupname_col = "some_group", rowname_col="some_letter") %>%
  summary_rows(groups = TRUE, columns = vars(num1, num2, n), fns = list( TOTAL = "sum" ) ) %>%
  summary_rows(groups = TRUE,
               columns = vars(pct_num1),
               fns = list(TOTAL = ~ c(0.493,0.454) )
  )

Output from gt( )

gt r
1 Answers

As I answered in your other question "How can you automate the addition of overall percentages to the row summary in gt() package?", package gt allows you to control cell by cell all information shown in summary rows. The disadvantage is that the code for the table becomes pretty verbose.

I've used a shorter example than yours, for the sake of space, but the solution can be applied to your question

library(dplyr)
library(gt)

df2_ex <-  tribble(
  ~some_letter, ~some_group, ~num1, ~num2,
  "c"         ,         "A",     1,     2,
  "d"         ,         "A",     3,     4,
  "x"         ,         "B",     5,     6,  
  "y"         ,         "B",     7,     8
  ) %>%
  rowwise() %>% 
  mutate(pct_num1 = num1 / sum(c_across(starts_with("num"))), 
    pct_num2 = num2 / sum(c_across(starts_with("num"))))

df2_ex 
#> # A tibble: 4 x 6
#> # Rowwise: 
#>   some_letter some_group  num1  num2 pct_num1 pct_num2
#>   <chr>       <chr>      <dbl> <dbl>    <dbl>    <dbl>
#> 1 c           A              1     2    0.333    0.667
#> 2 d           A              3     4    0.429    0.571
#> 3 x           B              5     6    0.455    0.545
#> 4 y           B              7     8    0.467    0.533

The summary rows for the grouped table based on some_group column will read

df2_ex_grouped <- df2_ex %>% 
  group_by(some_group) %>%
  summarise_at(vars(num1, num2), sum) %>%
  rowwise() %>%
  mutate(pct_num1 = num1 / sum(c_across(starts_with("num"))), 
    pct_num2 = num2 / sum(c_across(starts_with("num"))))

df2_ex_grouped
#> # A tibble: 2 x 5
#> # Rowwise: 
#>   some_group  num1  num2 pct_num1 pct_num2
#>   <chr>      <dbl> <dbl>    <dbl>    <dbl>
#> 1 A              4     6    0.4      0.6  
#> 2 B             12    14    0.462    0.538

Finally, I've included a grand summary using the same methodology for the sake of completeness

df2_ex_total <- df2_ex %>%
  ungroup() %>%
  summarise_at(vars(num1, num2), sum) %>%
  rowwise() %>%
  mutate(pct_num1 = num1 / sum(c_across(starts_with("num"))), 
    pct_num2 = num2 / sum(c_across(starts_with("num"))))
df2_ex_total
#> # A tibble: 1 x 4
#> # Rowwise: 
#>    num1  num2 pct_num1 pct_num2
#>   <dbl> <dbl>    <dbl>    <dbl>
#> 1    16    20    0.444    0.556

The code to get the table you wanted is shown below. Note that I used two ways to identify the value that should appear in the right cell of the summary row:

  1. Using base R to get the value from df2_ex_grouped
  2. Using pull()

Pick the one you prefer.

The piece that was missing in your code was to specify which value of the some_groups column you were applying the summary_rows function instead of using groups = TRUE. Hope this answer solve your question.

df2_ex %>%
  gt(groupname_col = "some_group", rowname_col="some_letter") %>%
  summary_rows(groups = TRUE, columns = vars(num1, num2), fns = list(TOTAL = "sum"),
    formatter = fmt_number, decimals = 0) %>%
  summary_rows(groups = TRUE, columns = vars(num1, num2), fns = list(TOTAL = "sum"),
    formatter = fmt_number, decimals = 0) %>%
  summary_rows(groups = "A", columns = vars(pct_num1), 
    fns = list(TOTAL = ~ df2_ex_grouped$pct_num1[1]),
    formatter = fmt_number, decimals = 4) %>%
  summary_rows(groups = "A", columns = vars(pct_num2), 
    fns = list(TOTAL = ~ df2_ex_grouped$pct_num2[1]),
    formatter = fmt_number, decimals = 4) %>%
  summary_rows(groups = "B", columns = vars(pct_num1), 
    fns = list(TOTAL = ~ df2_ex_grouped$pct_num1[2]),
    formatter = fmt_number, decimals = 4) %>%
  summary_rows(groups = "B", columns = vars(pct_num2), 
    fns = list(TOTAL = ~ (
        df2_ex_grouped %>% 
          filter(some_group == "B") %>%
          select(pct_num2) %>%
          pull())),
    formatter = fmt_number, decimals = 4) %>%
  grand_summary_rows(columns = vars(num1, num2), fns = list(`grand TOTAL` = "sum"),
    formatter = fmt_number, decimals = 0) %>%
  grand_summary_rows(columns = vars(pct_num1), 
    fns = list(
      `grand TOTAL` = ~ (df2_ex_total$pct_num1)),
    formatter = fmt_number, decimals = 3) %>%
  grand_summary_rows(columns = vars(pct_num2), 
    fns = list(
      `grand TOTAL` = ~ (df2_ex_total$pct_num2)),
    formatter = fmt_number, decimals = 3)

Created on 2020-11-14 by the reprex package (v0.3.0)

user_66790660
Related