Conditional formatting of multiple columns in gt table

Question

This is my first post, so apologies if I messed something up. I am trying to apply conditional formatting to multiple columns (comparing results for samples SampA, SampB and SampB to Limit) of a gt table. Following the lead of one of the gt examples and a different stack overflow Q, I have managed to apply it to a single column (variable) using this code:

## Conditional Formatting of single column in gt table

samples = as_tibble(cbind("Chem"=c("Cd","Pb","Zn"),
                       "Limit"=c("0.005","0.05","0.007"),
                       "SampA" = c("0.001","0.15","0.003"),
                       "SampB" = c("0.002","0.04","0.005"),
                       "SampC" = c("0.009","0.23","0.03")))

gt(samples,rowname_col="Chem") %>% tab_style(
        style = list(
                cell_fill(color = "grey80"),
                cell_text(weight = "bold")
        ),
        locations = cells_body(
                columns = vars(SampA),
                rows = SampA >= Limit
        )
    )   %>% tab_spanner(
            label = "Samples",
            columns = vars(SampA,SampB,SampC))

However, I have not been successful in trying to expand this to multiple columns. I can get the 'columns' argument to work with 'vars(SampA,SampB,SampC)'. Leaving the 'rows' argument with 'SampA >= Limit', the formatting 'works' in the sense that all rows where SampA >= Limit are highlighted across the three Samp columns, but this is not what I'm after. Running the code below ends up with no formatting of any column.

        locations = cells_body(
                columns = vars(SampA,SampB,SampC),
                rows = vars(SampA,SampB,SampC) >= Limit
        )

I have been able to "brute force" what I'm after by repeating the 'style_tab()' tailored to each column, but know that there must be a better way to get to my goal. Help?

Gabriel

gt is pretty new, so there is no guarantee that there is a better way just yet. – Michail

Okay, thanks Mark. Sorry for the delay...it had been so long that I had given up on anyone even responding. I guess I'll just leave the post and see if someone has an answer at some point. Cheers,Gary. – Gabriel

Answer 1

I know this is probably too late to help you, but I was able to figure it out by defining a function that creates a list of cells_body calls with the correct parameters. This list is then passed to the locations parameter and applies the specified formatting to all the selected cells. Hopefully someone else finds this useful!

library(gt)
samples = as_tibble(cbind("Chem"=c("Cd","Pb","Zn"),
                          "Limit"=c("0.005","0.05","0.007"),
                          "SampA" = c("0.001","0.15","0.003"),
                          "SampB" = c("0.002","0.04","0.005"),
                          "SampC" = c("0.009","0.23","0.03")))

builder <- function(x, Limit){cells_body(columns = !!sym(x), rows = !!sym(x) > Limit)}

gt(samples,rowname_col="Chem") %>% 
  tab_style(style = list(cell_fill(color = "grey80"), cell_text(weight = "bold")),
            locations = lapply(c("SampA", "SampB", "SampC"), builder, Limit = sym(Limit))) %>%
  tab_spanner(label = "Samples", columns = c(SampA, SampB, SampC))

This will work with any vector of strings that match your column names, including what can be created through the colnames() function

names <- colnames(samples)[3:ncol(samples)]

gt(samples,rowname_col="Chem") %>% 
  tab_style(style = list(cell_fill(color = "grey80"), cell_text(weight = "bold")),
            locations = lapply(names, builder, Limit = sym(Limit))) %>%
  tab_spanner(label = "Samples", columns = c(SampA, SampB, SampC))

Meanie

That seems to work! Better late than never! Just a note that I got an error when I ran your code...then noticed that the "builder" and "test_fun" should have the same name. I changed both to "test_fun" and it worked. While your solution is intuitive at a high level, I haven't used !!sym or sym before and haven't used lapply in years. I appreciate you posting your answer...this will make gt much easier to use for this type of table (we could have tens of columns for some datasets). Thanks! – Gabriel

Good catch! I updated the comment. glad it was still useful! – Meanie

Answer 2

library(gt)
samples = as_tibble(cbind("Chem"=c("Cd","Pb","Zn"),
                          "Limit"=c("0.005","0.05","0.007"),
                          "SampA" = c("0.001","0.15","0.003"),
                          "SampB" = c("0.002","0.04","0.005"),
                          "SampC" = c("0.009","0.23","0.03")))

samples <- samples %>% 
  mutate(Limit = as.numeric(Limit))

gt(samples,
   rowname_col="Chem") %>% 
  tab_style(style = list(cell_fill(color = 'yellow'), 
                         cell_text(weight = 'bold')), 
            locations = cells_body(columns=vars(SampA), 
                                   rows = SampA >= Limit)) %>% 
  tab_style(style = list(cell_fill(color = 'yellow'), 
                         cell_text(weight = 'bold')), 
            locations = cells_body(columns=vars(SampB), 
                                   rows = SampB >= Limit)) %>% 
  tab_style(style = list(cell_fill(color = 'yellow'), 
                         cell_text(weight = 'bold')), 
            locations = cells_body(columns=vars(SampC), 
                                   rows = SampC >= Limit)) %>% 
  tab_spanner(
  label = "Samples",
  columns = vars(SampA,SampB,SampC))

Mora

Thanks for the response! I was on holidays, hence the delayed response. Your code is essentially what I did for the "brute force" method above. I was hoping for an option that could do multiple columns as the same time (imagine trying to do this for 100 samples). Nevertheless, your code will be helpful to others as I didn't explicitly show mine for the "brute force" method. Thanks, Gary – Gabriel

@Gabriel you can - see the first option of the accepted question in #55067216 using data_color – Farra

@Mooks. I am probably missing something. That example was for applying the same conditional structure to a single column (or all columns). I can't see how it would be applied to my example, where the conditions differ by row. As you can see by Susan's code above, the challenge to apply conditions by row was in specifying the location to apply the formatting...I can specify multiple columns but not rows (e.g., I could only get "SampA >= Limit" to work while it would be great to say "SampA:SampC >= Limit" or something similar. Thanks, – Gabriel

You’re right, I had been looking at so many examples all day - I’d missed that this one was a bit more subtle. – Farra