R DT conditional formatting using non visible column

Question

This question relates to this but the value that I want to condition on is not the value of the cell but an external column which is available but not shown in the DT.

My example is simple:

DT::datatable(
  iris[,1:4],
  editable = TRUE,
  filter = c("bottom"),
  rownames = FALSE,
  extensions = 'Buttons',
  options = list(
    pageLength=21, scrollY='400px',
    dom = 'Brt'
))%>% formatStyle('Sepal.Length', fontWeight = styleInterval(5, c('normal', 'bold')))

How would I color-code or apply formatting to a column Sepal.Length depending on the value of iris$Species

• If Species is setosa then blue and
• If Species is versicolor then red and
• If Species is virginica then green

Answer 1

This should do the job

library(DT)
DT::datatable(
  iris,
  editable = TRUE,
  filter = c("bottom"),
  rownames = FALSE,
  extensions = 'Buttons',
  options = list(
    columnDefs = list(list(targets = 4, visible = F)),
    pageLength= 150, scrollY='400px',
    dom = 'Brt'
  )) %>% formatStyle(
    'Sepal.Length', 'Species',
    backgroundColor = styleEqual(c("setosa", "versicolor","virginica"), c('steelblue', 'red', "green"))
  )

Answer 2

Here is a solution:

• use the full dataset;
• hide the desired column with the option columnDefs;
• use some Javascript in the option initComplete to set the colors.

---

jscode <- "function(settings) {
var table = settings.oInstance.api();
var nrows = table.rows().count();
for(var i=0; i<nrows; i++){
var cell0 = table.cell(i,0);
var cell4 = table.cell(i,4);
var species = cell4.data();
var bgcolor;
if(species == 'setosa'){
bgcolor = 'blue';
}else if(species == 'versicolor'){
bgcolor = 'red';
}else{
bgcolor = 'green'
}
cell0.node().style.backgroundColor = bgcolor;
}
}"

DT::datatable(
  iris,
  editable = TRUE,
  filter = c("bottom"),
  rownames = FALSE,
  extensions = 'Buttons',
  options = list(
    pageLength=21, scrollY='400px',
    dom = 'Brtp',
    columnDefs = list(list(visible=FALSE, targets=4)),
    initComplete = JS(jscode)
  ))%>% formatStyle('Sepal.Length', 
                    fontWeight = styleInterval(5, c('normal', 'bold')))