rstringconditional-formattinggt

Conditional wrapping inside of a gt table


I have the following table surveys :

structure(list(QUEST = c("Q1", "Q1", "Q1", "Q2", "Q2", "Q2", 
"Q3", "Q3", "Q3"), VALUE = c("REF_YEAR", "year_submitted", "Month_submitted", 
"REF_YEAR", "year_submitted", "Month_submitted", "REF_YEAR", 
"year_submitted", "Month_submitted"), `2018` = c("2017", "2018", 
"Feb", "2016", "2018", "Feb", "2017", "2018", "Mar"), `2019` = c("2018", 
"2019", "Apr", "2017", "2019", "Feb", "2018", "2019", "Apr"), 
    `2020` = c("2019", "2020", "Apr", "2018", "2020", "Feb", 
    "2019", "2020", "Apr"), `2021` = c("2020", "2021", "Feb", 
    "2019", "2021", "Feb", "2020", "2021", "Feb"), `2022` = c("2021", 
    "2022", "Apr", "2020", "2022", "Mar", "2021", "2022", "Apr"
    ), `2023` = c("2022", "2023", "Apr", "2021/2022", "2023/2023", 
    "Dec/Dec", "2022", "2023", "Mar"), `2024` = c("2023", "2024", 
    "Apr", NA, NA, NA, "2023", "2024", "Apr")), row.names = c(NA, 
-9L), class = "data.frame")

It gives a table with survey round years in columns and for each questionnaire (Q1,Q2,Q3) the reference year of the data submitted in the questionnaire, as well as the year and month when the data where submitted. For instance for the survey round 2021, Q1 could have been submitted in June 2023 (i.e., very late) and with the reference year for the data as 2019 (i.e., two years lag in comparison to the survey round year). Sometimes a respondent will send additional questionnaires corresponding to a different reference year. When this is the case, the values are separated by a "/" as with the example of Q2 for the survey round 2023.

I would like to find a visually acceptable solution that accommodates this variations. Ideally, I would find a way to:

  1. detect when a cell includes a "/"
  2. have one line per year or month: e.g.:
2021/
2022
  1. reduce the font size only when a cell has more than one 'value'

I tried playing around with gt table but without much success. I think I should find a way to integrate some kind of conditional formatting based on the cell content but I've been struggling to figure it just to reduce the font size, not even mentioning forcing the display on two lines. The below is the kind of code I am trying which I got from this post https://stackoverflow.com/questions/76435371/styling-cells-in-a-gt-table-based-on-detection-of-a-string-in-their-contents but even this does not work.

surveys_tb <- surveys %>%
  gt(groupname_col = 'QUEST') %>%
  cols_label(VALUE = "") %>%
  tab_spanner(label = md("**Survey round**"), columns = starts_with("20")) %>%
  tab_style(cell_text(size=3),
            locations = names(surveys)[grep('20', names(surveys))] %>%
              lapply(FUN = \(col_name){
                cells_body(columns = col_name,
                           rows = grepl('/', surveys[[col_name]]))
                }))

Solution

  • You can achieve this with using tab_style_body() to format the text, and text_transform() to substitute the slashes for breaks (you could also do this part earlier in the pipeline before converting to a gt table).

    library(gt)
    
    surveys |>
      gt(groupname_col = 'QUEST') |>
      cols_label(VALUE = "") |>
      tab_spanner(label = md("**Survey round**"), columns = starts_with("20")) |>
      tab_style_body(style = cell_text(size = "small"),
                     columns = -c(QUEST, VALUE),
                     pattern = "/") |>
      text_transform(fn = \(x) sub("/", "<br>", x),
                     locations = cells_body(columns = starts_with("20")))
    

    enter image description here