conditional formatting of cells using if else? or hiding columns in DT package in R

Question

I have a table that looks like this.

data_wide <-data.frame(id=c(1,2,3,4),book1=c("","","","age"),book2=c("covid","alive","",""),book3=c("","income","","location"))

I am trying to render this table in html using the DT package in R so that all values are hidden and can be viewed with mouse hover. To clarify further, my goal is to keep the background of the cells different depending on the value, for example, cells with no value can be light blue, while a cell with a value can be green and when I hover over the green cells, I am able to view this value. Below is my very naive attempt to accomplish my goal:

library(DT)
library(stringr)

data_wide <- data_wide %>% 
  mutate(b1 = as.numeric(str_detect(string = book1, pattern = ""))) %>% 
  mutate(b2 = as.numeric(str_detect(string = book2, pattern = ""))) %>% 
  mutate(b3 = as.numeric(str_detect(string = book3, pattern = ""))) 


datatable(data_wide, options = list(columnDefs = list(list(
  targets = 2:4,
  render = JS(
    "function(data, type, row, meta) {",
    "return type === 'display' && data.length > 6?",
    "'<span title=\"' + data + '\">' + data.substr(0, 20) + '...</span>' : data;",
    "}")
))), callback = JS('table.page(3).draw(false);'))%>%
formatStyle(
'book1',"b1",
color = "lightgreen",
border = '2px solid #FFFFFF',
backgroundColor = styleEqual(c(0, 1), c('lightblue', 'lightgreen'))) %>%
formatStyle(
'book2',"b2",
border = '2px solid #FFFFFF',
color = "lightgreen",
backgroundColor = styleEqual(c(0, 1), c('lightblue', 'lightgreen'))) %>%
formatStyle(
'book3',"b3",
border = '2px solid #FFFFFF',
color = "lightgreen",
backgroundColor = styleEqual(c(0, 1), c('lightblue', 'lightgreen'))) 

For explanation, I have added the b1,b2,b3 columns so that I can borrow values from them to color code the cells for book1,book2 and book3. However I am unable to drop them (b1,b2,b3),since they are no longer needed. I have tried adding list(visible = FALSE, target = c(5,6,7) in the options but that doesnt seem to work, maybe since that option has already been used to enable mouse hover? I have looked into conditional formatting as well on this forum and other resources but looks like that only works with integers (0,1..etc) and I have strings in this table. I am at a loss and hoping to find a solution.

Would appreciate any help on this and open to other ideas as well.

Thank you.

Answer 1

Okay so I will answer my own question here, the code needed another list within a list using "visible = FALSE" as @LocoGris pointed. Without adding a list within the list, ellipsis was getting compromised.

datatable(
  data_wide, 
  plugins = "ellipsis",
  options = list(
    columnDefs = list(list(
      targets = c(2,3,4),
      render = JS("$.fn.dataTable.render.ellipsis( 17, false )")
    ),list(visible=FALSE,targets = c(5,6,7)))
  )
)%>%
  formatStyle(
    'book1',"b1",
    color = "lightgreen",
    border = '2px solid #FFFFFF',
    backgroundColor = styleEqual(c(0, 1), c('lightblue', 'lightgreen'))) %>%
  formatStyle(
    'book2',"b2",
    border = '2px solid #FFFFFF',
    color = "lightgreen",
    backgroundColor = styleEqual(c(0, 1), c('lightblue', 'lightgreen'))) %>%
  formatStyle(
    'book3',"b3",
    border = '2px solid #FFFFFF',
    color = "lightgreen",
    backgroundColor = styleEqual(c(0, 1), c('lightblue', 'lightgreen')) )

Answer 2

May you try this:

data_wide <-data.frame(id=c(1,2,3,4),book1=c("","","","age"),book2=c("covid","alive","",""),book3=c("","income","","location"))

library(DT)
library(stringr)
library(dplyr)

data_wide <- data_wide %>% 
  mutate(b1 = as.numeric(str_detect(string = book1, pattern = ""))) %>% 
  mutate(b2 = as.numeric(str_detect(string = book2, pattern = ""))) %>% 
  mutate(b3 = as.numeric(str_detect(string = book3, pattern = ""))) 


datatable(data_wide, options = list(columnDefs = list(list(
  visible=FALSE, targets=c(5,6,7),
  render = JS(
    "function(data, type, row, meta) {",
    "return type === 'display' && data.length > 6?",
    "'<span title=\"' + data + '\">' + data.substr(0, 20) + '...</span>' : data;",
    "}")
))), callback = JS('table.page(3).draw(false);'))%>%
  formatStyle(
    'book1',"b1",
    color = "lightgreen",
    border = '2px solid #FFFFFF',
    backgroundColor = styleEqual(c(0, 1), c('lightblue', 'lightgreen'))) %>%
  formatStyle(
    'book2',"b2",
    border = '2px solid #FFFFFF',
    color = "lightgreen",
    backgroundColor = styleEqual(c(0, 1), c('lightblue', 'lightgreen'))) %>%
  formatStyle(
    'book3',"b3",
    border = '2px solid #FFFFFF',
    color = "lightgreen",
    backgroundColor = styleEqual(c(0, 1), c('lightblue', 'lightgreen')) )