Search code examples
rgt

R gt summary_rows Ratio Total Row

I am using the great gt package to help make nice looking tables in R. I really like how I can use it to make Total columns, but I am struggling in trying to do anything beyond a simple sum or mean. I need do something more like a SUMPRODUCT for a couple of my columns. Let's say that I have the following data. It has a denom column which I want to divide a couple of numerators by.

library(dplyr)
library(gt)
set.seed(1)
df <- data.frame(some_letter = sample(letters, size = 10, replace = FALSE),
                 denom = sample(1:100, size = 10, replace = FALSE), 
                 num1 = sample(100:200, size = 10, replace = FALSE),
                 num2 = sample(100:200, size = 10, replace = FALSE)) %>% 
  mutate(rat1 = round(num1 / denom, 2), 
         rat2 = round(num2 / denom, 2))

some_letter denom num1 num2  rat1  rat2
1            y    54  188  119  3.48  2.20
2            d    74  143  127  1.93  1.72
3            g     7  178  200 25.43 28.57
4            a    73  132  143  1.81  1.96
5            b    79  183  186  2.32  2.35
6            k    85  134  169  1.58  1.99
7            n    37  169  139  4.57  3.76
8            r    89  173  197  1.94  2.21
9            w    94  141  124  1.50  1.32
10           j    34  137  195  4.03  5.74

So I would like to get a TOTAL column that is the sum for denom, num1 and num2. This is super easy with gt using the summary_rows function. But the TOTAL I need for the two ratio columns is not just that sum. I can't just add up rat1 and rat2 to get the correct ratio. The TOTAL column for those needs to be the sum(num1) / sum(denom) and sum(num1) / sum(denom).

So this works for the easy columns.

gt(df) %>% 
  summary_rows(fns = list(TOTAL = "sum"), 
               columns = vars(denom, num1, num2))

enter image description here

For those other columns, I have tried the following to no avail.

gt(df) %>% 
  summary_rows(fns = list(TOTAL = "sum"), 
               columns = vars(denom, num1, num2)) %>% 
  summary_rows(fns = list(TOTAL = ~sum(.) / sum(denom)), columns = vars(rat1, rat2))

gt(df) %>% 
  summary_rows(fns = list(TOTAL = "sum"), 
               columns = vars(denom, num1, num2)) %>% 
  summary_rows(fns = list(TOTAL = sum(.) / sum(denom)), columns = vars(rat1, rat2))

And I know that I can compute this separately and rbind it onto the bottom, but the summary_rows function provides really fantastic formatting without doing anything extra.

Solution

  • I'm having the same problem, and have found no better solution, other than to tidy up the way the code passes r1_total and r2_total to the summary_rows( ) function:

    r1_total <- sum(df$num1) /  sum(df$denom)
r2_total <- sum(df$num2) /  sum(df$denom)

gt(df) %>% 
  summary_rows(fns = list(TOTAL = "sum"), columns = vars(denom, num1, num2)) %>% 
  summary_rows(fns = list(TOTAL = ~ c(r1_total), columns = vars(rat1)) %>% 
  summary_rows(fns = list(TOTAL = ~ c(r2_total), columns = vars(rat2))

    OR

    gt(df) %>% 
  summary_rows(fns = list(TOTAL = "sum"), columns = vars(denom, num1, num2)) %>% 
  summary_rows(fns = list(TOTAL = ~ sum(df$num1) /  sum(df$denom) ), columns = vars(rat1)) %>% 
  summary_rows(fns = list(TOTAL = ~ sum(df$num2) /  sum(df$denom) ), columns = vars(rat2))