How to style cells in DT:datatable based on pattern matching of strings?

user_5149847 0
Viewed 321

I have a shiny app that renders a DT table that has formatted numeric values. The table contains data that is both percentages and data that represents currency. The data is not tidy in that the table is effectively transposed with each row representing a feature and each column being an observation. Ultimately, my goal is to encode logic that anytime a cell has a negative value (the first character of the string is a '-'), the cell is colored red. Here is my reproducible example:

library(tidyverse)
library(DT)

example_df <- data.frame(
  x = c("$1", "-2%", "$3"),
  y = c("$10", "10%", "$20")
)


determine_cell_color <- function(x) {
  
  if (str_sub(x, 1, 1) == "-") {
    return("red")
  }
  
  else {
    return("white")
  }
  
}

# how to get an individual cell to be a different color 
# if first character in string value is '-'
example_df %>% 
  datatable() %>% 
  formatStyle(target = 'row', backgroundColor = styleEqual(~determine_cell_color))
dt shiny r
1 Answers

This approach can be generalized to many more columns. The main idea is to style one column with another column, the only downside is that it doubles the number of cols in the data frame. Fortunately we can hide those columns.

library(tidyverse)
library(DT)

example_df <- data.frame(
  x = c("$1", "-2%", "$3"),
  y = c("$10", "10%", "$20"),
  z = c("$10", "-10%", "$20"),
  w = c("-$10", "-10%", "$20")
)

pattern <- names(example_df) %>% str_c(collapse = "|")

determine_cell_color <- function(vector) {
  str_detect(vector, "-") %>% as.numeric()
}

example_df <- example_df %>% mutate(across(matches(pattern), determine_cell_color, .names = "cell_color_{.col}"))

DT <- datatable(example_df, options = list(
  columnDefs = list(list(targets = (ncol(example_df) / 2 + 1):ncol(example_df), visible = FALSE))
))

walk(str_subset(names(example_df), "^.$"), ~ {
  DT <<- DT %>% formatStyle(
    .x, str_c("cell_color_", .x),
    backgroundColor = styleEqual(c(1, 0), c("red", "white"))
  )
})

DT

Created on 2022-01-07 by the reprex package (v2.0.1)

user_111687660
Related