formatStyle over multiple columns DT R

Question

I have a data.frame with one ID column and multiple numeric columns, the amount of numeric columns can differ. Of these numeric columns I want to color all values above the column mean green and all values below the column mean red. The code below give my desired outcome, but it is not a generic code for data frames with more or less numeric columns.

library(DT)
    
data2 <- cbind(ID = "some ID",iris[,1:4])
        
datatable(
  data2, rownames = FALSE, class = 'cell-border stripe',
  options = list(
    dom = 't', pageLength = -1, lengthMenu = list(c(-1), c('All'))
  )
) %>%
  formatStyle(colnames(data)[2], backgroundColor = styleInterval(mean(data2[,2]), c("red","green"))) %>%
  formatStyle(colnames(data)[3], backgroundColor = styleInterval(mean(data2[,3]), c("red","green"))) %>%
  formatStyle(colnames(data)[4], backgroundColor = styleInterval(mean(data2[,4]), c("red","green"))) %>%
  formatStyle(colnames(data)[5], backgroundColor = styleInterval(mean(data2[,5]), c("red","green")))

I would like to replace the code above with the code below but that does not work. The code below will also work when the number of numeric columns changes.

datatable(
  data2, rownames = FALSE, class = 'cell-border stripe',
  options = list(
    dom = 't', pageLength = -1, lengthMenu = list(c(-1), c('All'))
  )
) %>%
  formatStyle(colnames(data2)[2:ncol(data2)], backgroundColor = styleInterval(colMeans(data2[,2:ncol(data2)]), c("red","green")))

Is this possible? So yes, how?

Batanichek · Accepted Answer · 2016-06-30T13:00:58.547

You can do it with addition calculation like

(Not work with same value in different column)

hepl_1=sapply(2:ncol(data2),function(i)  ifelse(data2[[i]]>=mean(data2[[i]]),"rgb(255,0,0)","rgb(0,255,0)"))
help_3=as.matrix(data2[2:ncol(data2)])

datatable(
  data2, rownames = FALSE, class = 'cell-border stripe',
  options = list(
    dom = 't', pageLength = -1, lengthMenu = list(c(-1), c('All'))
  )
) %>%
  formatStyle(colnames(data2)[2:ncol(data2)], backgroundColor = styleEqual(help_3, hepl_1))

Update

You can generate rowCallback like

datatable(
  data2, rownames = FALSE, class = 'cell-border stripe',
  options = list(
    dom = 't', pageLength = -1, lengthMenu = list(c(-1), c('All')),
    rowCallback=JS(paste0("function(row, data) {\n",
                          paste(sapply(2:ncol(data2),function(i) paste0("var value=data[",i-1,"]; if (value!==null) $(this.api().cell(row,",i-1,").node()).css({'background-color':value <=", mean(data2[[i]])," ? 'red' : 'green'});\n")
                          ),collapse = "\n"),"}" ))
  )
)

The idea of having a matrix with the numbers and a matrix with the corresponding colors, does not work if you have the same value in different columns when it is in one column red and in the other green. It will then give the color of the first column — Berecht, Jun 30 '16 at 12:15
You can jitter or randomize it after determining the colors. Only you need to be sure that the created random number are unique — Berecht, Jun 30 '16 at 12:44

formatStyle over multiple columns DT R

1 Answers1

Update

Linked