Aim

Giving a different color to a table's cells according to numerical threshold(s).

R Package

gt

Reproducible example

mydata <- structure(list(none = c(4, 4, 25, 18, 10), light = c(2, 3, 10, 
24, 6), medium = c(3, 7, 12, 33, 7), heavy = c(2, 4, 4, 13, 2
)), row.names = c("SM", "JM", "SE", "JE", "SC"), class = "data.frame")

Using the above dataset, I can produce a table (however crude), using the following code:

mytable <- gt::gt(mydata)

Where I got stuck

It must be really easy, but I can wrap my head around how to assign (say) red to the cells where the value is (say) larger than 20 AND blue to cells whose value is (say) smaller than 10. It's days now that I am trying to do a little of google search (example HERE), but I could not find a solution. It must be pretty simple but no success so far. My best guess is using the tab_style() function, but I am at loss of understanding how to tune the parameters to get what I am after.

CodePudding user response：

This isn't ideal if you have an arbitrarily large data frame, but for an example of your size it's certainly manageable, imo. I generalized the tests as separate functions to reduce additional code duplication and make it easier to adjust your conditional parameters.

If you're looking for a more generalized solution it would be to look over a vector of columns, as described here.

library(gt)

isHigh <- function(x) {
  x > 20
}

isLow <- function(x) {
  x < 10
}

mydata %>% 
  gt() %>% 
  tab_style(
    style = list(
      cell_fill(color = 'red'), 
      cell_text(weight = 'bold')
    ), 
    locations = 
      list(
        cells_body(
          columns = none, 
          rows = isHigh(none)
        ),
        cells_body(
          columns = light, 
          rows = isHigh(light)
        ), 
        cells_body(
          columns = medium, 
          rows = isHigh(medium)
        ), 
        cells_body(
          columns = heavy, 
          rows = isHigh(heavy)
        )
      )
  ) %>% 
  tab_style(
    style = list(
      cell_fill(color = 'lightblue'), 
      cell_text(weight = 'bold')
    ), 
    locations = 
      list(
        cells_body(
          columns = none, 
          rows = isLow(none)
        ),
        cells_body(
          columns = light, 
          rows = isLow(light)
        ), 
        cells_body(
          columns = medium, 
          rows = isLow(medium)
        ), 
        cells_body(
          columns = heavy, 
          rows = isLow(heavy)
        )
      )
  )

CodePudding user response：

On the basis of the comment I got, and after having read the earlier post here on SO, I came up with the following:

Create a dataset to work with:

mydata <- structure(list(none = c(4, 4, 25, 18, 10), light = c(2, 3, 10, 
24, 6), medium = c(3, 7, 12, 33, 7), heavy = c(2, 4, 4, 13, 2
)), row.names = c("SM", "JM", "SE", "JE", "SC"), class = "data.frame")

Create a 'gt' table:

mytable <- gt::gt(mydata)

Create a vector of columns name to be later used inside the 'for' loops:

col.names.vect <- colnames(mydata)

Create two 'for' loops, one for each threshold upon which we want our values to be given different colors (say, a RED text for values > 20; a BLUE text for values < 5):

for(i in seq_along(col.names.vect)) {
      mytable <- gt::tab_style(mytable,
                               style = gt::cell_text(color="red"), 
                               locations = gt::cells_body(
                                 columns = col.names.vect[i],
                                 rows = mytable$`_data`[[col.names.vect[i]]] > 20)) 
}

for(i in seq_along(col.names.vect)) {
  mytable <- gt::tab_style(mytable,
                           style = gt::cell_text(color="blue"), 
                           locations = gt::cells_body(
                             columns = col.names.vect[i],
                             rows = mytable$`_data`[[col.names.vect[i]]] < 5)) 
}

This seems to achieve the goal I had in mind.