Display dataframe as an HTML table with conditional colouring based on another column

Question

I have a dataframe (mtcars). It has columns with names: mpg cyl disp hp drat wt qsec vs I want to compare each column with vs ( column 1 vs column 8, column 2 vs column 8 and etc) and colour each column from 1: 7 as red (if the element is less than that of one in column8), yellow (if equal) or green (if greater).

I have used KableExtra package and used the below code:

library(dplyr)
library(kableExtra)
mtcars %>%
 mutate(
   car = row.names(.),
   mpg = cell_spec(mpg, "html", background = ifelse(mpg > vs, "green", "red")),
   cyl = cell_spec(cyl, "html", background = ifelse(cyl > vs,"green", "red" )),
 ) %>%
 kable(format = "html", escape = F) %>%
 kable_styling("striped", full_width = F)

I could achieve somehow but have two major issues:

KableExtra does not produce cell background as a tile/fill in Html it just highlights text in cell. I see latex can produce cell background but I can not use latex need only html solution.
In my actual data frame there are 70 dynamic columns and therefore need to access columns without names.

Tried with condformat and tableHTML too...but could not achieve comparing columns dynamically.

Perhaps check out the DT package: rstudio.github.io/DT. See this link for (conditional) row and column styling: rstudio.github.io/DT/010-style.html — Joris C.
Unfortunately DT doesn't seem to have function to compare between columns but to with a constant number... — doctshind s
@doctshinds: a workaround is to add a set of (hidden) columns to the data used for formatting, see the answer below — Joris C.

Joris C. Joris C. · Accepted Answer · 2019-06-25T07:52:07

A possible way to use column styling with the DT package is by adding a set of dummy columns used to set the background colors in formatStyle:

library(dplyr)
library(DT)

## add dummy columns for formatting
mtcarsColor <- mutate_all(mtcars, list(color = ~case_when(
            . < mtcars$vs ~ -1,
            . > mtcars$vs ~  1,
            TRUE ~ 0
        )
    )
)

## relevant column indices
dataCols <- grep("color", names(mtcarsColor), invert = TRUE) 
colorCols <- grep("color", names(mtcarsColor))

## datatable formatted by (hidden) dummy columns
datatable(mtcarsColor,
        rownames = FALSE,
        options = list(columnDefs = list(list(visible = FALSE, targets = colorCols - 1)))    
    ) %>%
    formatStyle(columns = dataCols,
        valueColumns = colorCols,
        backgroundColor = styleEqual(c("-1", "0", "1"), c("#FF000080", "#FFFF0080", "#00FF0080"))
    )

Display dataframe as an HTML table with conditional colouring based on another column

1 Answers