How do I create a table with merged rows in R

Question

Is there an R package that lets me produce tables such as this -

I am using excel to show my desired output. I'm using the mtcars dataset as an example. Here I group Carb sizes into small, medium and large and show the count and mean mpg by those sizes. I've been exploring the gt package but still struggling to produce something like this exactly. What I can see from this tutorial is that gt table can add summary lines below each group, however I would like to add the summaries (count, mean etc) as columns, and not rows below each group.

Lastly when I try to replicate the the tutorial in the link above using the mtcars dataset, I get the following error -

Error in rlang::eval_tidy(expr = var_expr, data = data_tbl, env = emptyenv()) : object mean.mpg not found

Here is my code -

mtcars %>% 
  select(mpg, carb) %>% 
  mutate(size = case_when(
    carb %in% c(1, 2) ~ "Large",
    carb %in% c(4, 4) ~ "Medium",
    TRUE ~ "Small"
  )) %>% 
  group_by(carb, size) %>% 
  summarise(count = n(),
            mean.mpg = mean(mpg)) %>% 
  ungroup() %>% 
  gt(groupname_col = "size") %>% 
  summary_rows(groups = T, 
               columns = mean.mpg,
               fns = list(
                 average = "mean"
               ))

I appreciate any help.

Answer 1

If you put quotes around the new column name you don't get an error in R 4.04 running gt Version: 0.2.2 , so try: columns = 'mean.mpg'. From the @lag_rat_kid comment, and our joint experience, I'm guessing this infelicity was remedied in the more recent version of pkg:gt.

 #quit current session; restart
 install.packages("gt")
 library(gt); library(dplyr)
 # your code now succeeds

Although I modified it a bit to fix the error in the definition of the "medium" case

I used the Rstudio Export as PNG file and was agast at the size it created ... 1.8 MB, so I then saved as a jpg file and then resaved as a PNG file before including it here). I then adopted the practice of saving as a jpg file and then converting it with an image viewing program for upload here.

If you drop the 'card' column from the grouping, do some different labeling and don't include the summary rows (which you've already caluclated) you get much closer to your desires:

my_gt_tbl <- mtcars %>%
select(mpg, carb) %>%
mutate(size = case_when(
carb %in% c(1, 2) ~ "Large = 1,2",
carb %in% c(3, 4) ~ "Medium = 3,4",
TRUE ~ "Small = 6,8"
)) %>%
group_by(size) %>%
summarise(count = n(),
mean.mpg = mean(mpg))  %>%
gt(groupname_col = "size") 
my_gt_tbl