flextable: How to merge rows with duplicate values for some columns

2
votes

I'm making a table using flextable(). I would like to merge cells with duplicated srdr_id.

Given these data:

test <- structure(list(srdr_id = c("175124", "175124", "172545", "172545", 
"172609", "172609", "172609"), full_name = c("TAU", "MI", "TAU", 
"MI", "TAU", "MI", "MI"), article_arm_name = c("Control", "WISEteens", 
"Assessed control", "Intervention", "Control", "Computer BI", 
"Therapist BI"), arm_number = c(1L, 2L, 1L, 2L, 1L, 2L, 3L)), row.names = c(NA, 
-7L), class = c("tbl_df", "tbl", "data.frame"))


  srdr_id full_name article_arm_name arm_number
  <chr>   <chr>     <chr>                 <int>
1 175124  TAU       Control                   1
2 175124  MI        WISEteens                 2
3 172545  TAU       Assessed control          1
4 172545  MI        Intervention              2
5 172609  TAU       Control                   1
6 172609  MI        Computer BI               2
7 172609  MI        Therapist BI              3

I can do it with the following code - but would like to simplify the multiple merge_at() statements.

flextable(test) %>% theme_box() %>% 
           merge_at(i = 1:2, j = 1) %>% 
           merge_at(i = 3:4, j = 1) %>% 
           merge_at(i = 5:7, j = 1)

<code>enter image description here</code>

Unfortunately, the simple solution, merge_v(j = ~srdr_id), no longer is sufficient when I add another column which is duplicated across common srdr_id's.

test_2 <- structure(list(srdr_id = c("175124", "175124", "172525", "172525", 
"172545", "172545", "172609", "172609", "172609"), substances = c("alcohol", 
"alcohol", "alcohol", "alcohol", "cannabis", "cannabis", "alcohol\n cannabis\n other drugs", 
"alcohol\n cannabis\n other drugs", "alcohol\n cannabis\n other drugs"
), full_name = c("TAU", "MI", "TAU", "MI (parent)", "TAU", "MI", 
"TAU", "MI", "MI"), article_arm_name = c("Control", "WISEteens", 
"Treatment as usual", "Brief MI (b-MI)", "Assessed control", 
"Intervention", "Control", "Computer BI", "Therapist BI")), row.names = c(NA, 
-9L), class = c("tbl_df", "tbl", "data.frame"))

# A tibble: 9 x 4
  srdr_id substances                         full_name   article_arm_name  
  <chr>   <chr>                              <chr>       <chr>             
1 175124  alcohol                            TAU         Control           
2 175124  alcohol                            MI          WISEteens         
3 172525  alcohol                            TAU         Treatment as usual
4 172525  alcohol                            MI (parent) Brief MI (b-MI)   
5 172545  cannabis                           TAU         Assessed control  
6 172545  cannabis                           MI          Intervention      
7 172609  "alcohol\n cannabis\n other drugs" TAU         Control           
8 172609  "alcohol\n cannabis\n other drugs" MI          Computer BI       
9 172609  "alcohol\n cannabis\n other drugs" MI          Therapist BI
2
rflextable
I added a more realistic table, where the simple solution no longer works, i.e. 'substances' is repeated across srdr_id. – user25494
what do you want to get as result? Is this solution ok for you: flextable(test_2) %>% theme_box() %>% merge_v(j = 1:2) – David Gohel
Unfortunately, with merge_v(j = 1:2), the first 4 rows where substances == "alcohol" are merged, rather than rows 1 and 2, and rows 3 and 4. I would like to merge duplicated cells only within a given srdr_id. – user25494

2 Answers

4
votes

Wow - I missed the obvious solution for the simple case. Question has been edited with a more realistic (nested) dataset.

test_ft <- flextable(test) %>% theme_box() %>%  merge_v(j = ~srdr_id)
2
votes

You can create a custom function for that as flextable does not have this option. 

test_2 <- structure(list(srdr_id = c("175124", "175124", "172525", "172525", 
"172545", "172545", "172609", "172609", "172609"), substances = c("alcohol", 
"alcohol", "alcohol", "alcohol", "cannabis", "cannabis", "alcohol\n cannabis\n other drugs", 
"alcohol\n cannabis\n other drugs", "alcohol\n cannabis\n other drugs"
), full_name = c("TAU", "MI", "TAU", "MI (parent)", "TAU", "MI", 
"TAU", "MI", "MI"), article_arm_name = c("Control", "WISEteens", 
"Treatment as usual", "Brief MI (b-MI)", "Assessed control", 
"Intervention", "Control", "Computer BI", "Therapist BI")), row.names = c(NA, 
-9L), class = c("tbl_df", "tbl", "data.frame"))

merge_custom <- function(ft, x, columns){
  z <- rle(x)
  rows_at <- cumsum(z$lengths) - z$lengths + 1

  for(i in seq_along(rows_at)){
    for(j in columns)
      ft <- merge_at(x = ft, i = seq( rows_at[i], rows_at[i] + z$lengths[i] - 1), j = j)
  }

  ft
}


flextable(test_2) %>% merge_custom(x = test_2$srdr_id, columns=1:2) %>% theme_box()

enter image description here