cols_merge: Add NA handling with custom pattern

[alex-lauer]

Hi there,

I would like to create an ordinary clinical summary table with columns for treatments, summarizing descriptive stats or model outputs. In my reprex, I would like to create a table with columns for label and treatment, where treatment contains the number of subjects n, some mean value, a CI, formatted as "[lower_bound; upper_bound]" and Min and Max, formatted as "Min - Max".

I achieved my goal for Min and Max using cols_merge_range() as opposed to cols_merge(). The latter wouldn't omit the missing values for rows with missing values.

library(gt)
library(tidyverse)

tbl <- tibble(label = c("n" ,"Mean", "95% CI", "Min - Max"),
              trt_n = c(15, 14, rep(NA, 2)),
              trt_ci_low = c(rep(NA, 2), 10, NA),
              trt_ci_up = c(rep(NA, 2), 18, NA),
              trt_min = c(rep(NA, 3), 4),
              trt_max = c(rep(NA, 3), 20))

tbl %>% 
  gt() %>% 
  sub_missing(missing_text = "") %>%
  cols_merge(columns = c("trt_ci_low", "trt_ci_up"), pattern = "[{1}; {2}]") %>% 
  cols_merge_range(col_begin = "trt_min", col_end = "trt_max", sep = " - ") 

This results in

Omitting the pattern argument in cols_merge() would lead to the desired NA handling, but doesn't yield the right aggregation.

Merging all remaining trt columns would then lead to [; ] being added to n, Mean and Min - Max. Is there a way to format the CI's as well?

[rich-iannone]

Hi Alex,

Sorry about the difficulty encountered here. Right now the pattern argument doesn't allow for more expressive arrangements of text that account for NA values (this is something that is on my radar). However, we can perform a more manual solution with text_transform(). Here is what I wrote:

library(gt)
library(tidyverse)

tbl <- tibble(label = c("n" ,"Mean", "95% CI", "Min - Max"),
              trt_n = c(15, 14, rep(NA, 2)),
              trt_ci_low = c(rep(NA, 2), 10, NA),
              trt_ci_up = c(rep(NA, 2), 18, NA),
              trt_min = c(rep(NA, 3), 4),
              trt_max = c(rep(NA, 3), 20))

tbl %>% 
  gt() %>% 
  sub_missing(missing_text = "") %>%
  text_transform(
    locations = cells_body(columns = trt_ci_low),
    fn = function(x) {
      
      out <- rep(NA_character_, length(x))
      
      for (i in seq_along(out)) {
        if (!is.na(x[i]) && !is.na(tbl$trt_ci_up[i])) {
          out[i] <- paste0(x[i], "; ", tbl$trt_ci_up[i])
        } else {
          out[i] <- ""
        }
      }  
      
      out
    }
  ) %>%
  cols_hide(columns = trt_ci_up) %>%
  cols_merge_range(col_begin = "trt_min", col_end = "trt_max", sep = " - ") 

The text_transform() function allows you to write a custom function that takes the inputs of a column and modify them in the anonymous function. The only condition is that what's returned is a character vector of the same length as in the input.

Not at all an ideal solution but I hope it can be used as a workaround until the merging functions become more feature complete.

[alex-lauer]

Thanks for your help @rich-iannone - you're awesome!