How can I merge grouped values in one column in R?

Question

If I have a table that looks like this:

How can I merge each group in Column 1 so that the values are not repeated like this:

I also need this to work with the gt table library

I have tried df %>% group_by(Column 1) %>% gt(), but that puts the group above the row instead of merging the cells

Does this answer your question? [Grouping Rows in GTSummary](https://stackoverflow.com/questions/65665465/grouping-rows-in-gtsummary) — Panagiotis Togias, Apr 22 '23 at 21:17
You are asking for Excel concepts to be replicated in R structures. For "merged cells" there is no equivalent. You might get a display that looks like a merged cell but there won't be an underlying structure that is faithful to the Excel concept. — IRTFM, Apr 22 '23 at 22:33

TarJae · Answer 1 · 2023-04-22T22:15:48.790

1

We could use flextable:

library(flextable)
library(dplyr)

flextable(df) %>% 
  merge_v(j="Column1") %>% 
  theme_box() %>% 
  align(j = 1, align = "center")

edited Apr 22 '23 at 22:15

answered Apr 22 '23 at 21:47

TarJae

72,363
6
19
66

1

Is there a way to convert that back to a data frame or something that can be used with gt()? I need it for gt() because I have a bunch of other functions and table formatting in gt already – TomJones Apr 22 '23 at 21:56
I will update a gt version! – TarJae Apr 22 '23 at 21:57
1

Sorry, but that's not what I am looking for. The flex table format is exactly what I am looking for with column 1 centered and merged. – TomJones Apr 22 '23 at 22:09
Please see my update. So far I can't find a way with gt! – TarJae Apr 22 '23 at 22:16
yeah, really need something that works with gt. Thanks anyways – TomJones Apr 22 '23 at 22:19
See `gt` still does not have the `rows_merge` function! So IMHO I think this is not possible with gt. – TarJae Apr 22 '23 at 22:32

score 1 · Answer 2 · answered Apr 22 '23 at 22:28

Closest thing I can give to what you want is with the below code by removing the top border in cases where the grouped variable is set to an empty character. What is left is to find a way to align each group to the center of each respective area but maybe you're ok with just that.

library(dplyr)
library(gt)

is_empty <- function(x){
  
  return(ifelse(x == "", TRUE, FALSE))
  
}

tibble(colA = c("A", "A", "B", "B", "C", "C"), 
       colB = c("1", "23", "245", "123", "134", "98"),
       colC = c("342", "34", "45", "23", "13", "982")) |> 
  group_by(colA) |> 
  mutate(colA = ifelse(row_number() == 1, colA, "")) |> 
  ungroup() |> 
  gt() |>
  tab_style(
    style = list(
      cell_borders(
        sides = c("top", "bottom"),
        weight = px(0)
      )),
    locations = list(
      cells_body(
        columns = colA,
        rows = is_empty(colA)
      )
    )
  )

How can I merge grouped values in one column in R?

2 Answers2