Is there a package than create merged tables with merged row summaries?

[ML_Rookie_2021]

Is there a 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.

[AbdelSameaAhmed]

Hi, I hope this may be useful for you
DT package may generate the table for you
I think you made excellent code,
I just add the DT package code for you code as follow:

library(tidyverse)
library(DT)
mtcars1 <- 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)) 
mtcars1 <- mtcars1 %>% 
  DT::datatable(mtcars1)

Thanks,

[ML_Rookie_2021]

@AbdelSameaAhmed Thanks. However data table does not do what I'm trying to do. I am not able to merge rows in datatable.

[technocrat]

Every R problem can be thought of with advantage as the interaction of three objects— an existing object, x , a desired object,y , and a function, f, that will return a value of y given x as an argument. In other words, school algebra— f(x) = y. Any of the objects can be composites.

Keeping the focus on what, rather than how is the key to this approach. The post is off to a good start by identifying mtcars as x and made some progress with the screenshot as y. But then there is a problem.

There are no carb values in mtcars for 1, 5 or 7 and records with 6 and 8 are assigned the category Small, which reflects a logic not apparent from the code. If carb is omitted, the remaining data frame achieves the stated goal in the illustration.

As it stands, if some relationship is to be illustrated between carb and size, a footnote to the table might be considered along the lines

For Size: Large vehicles are those with 1 or 2 carburetors, and Medium have 4. Small have any other number.