How to conditionally color table elements using formattable and color_tile?

Question

I am trying to set up a table with colored tiles that are colored conditionally based on the average of each column. Basically, the tile would be red if the value is below the average, and green if above the average. I used a simple if else statement in my function below.

I will eventually use the "formattable" package and color_tile function within the package.

I have attempted to write my own function to pick the colors, but it is only successfully labelling the first row in my data frame.

#library(formattable) # not used in this example
green <- "#71CA97"
red <- "#ff7f7f"

col1 <- c(1.2, 4.2, 5.6, 7.1)
col2 <- c(5.0, 1.3, 10.3, 6.0)
col3 <- c(4.7, 6.3, 1.5, 6.3)
mydata <- data.frame(col1, col2, col3)

colorPicker <- function(x) {
     if(x <= 5) {return("red")}
     else {return("green")}
}

tile.colors <- lapply(c(mydata), colorPicker)

Warning message: In if (x <= 5) { : the condition has length > 1 and only the first element will be used

"tile.colors" returns the correct colors, but only for the first row.

I would eventually call "tile.colors" in the formattable function, but for now I am just trying to get the color picking function correct.

Is there a more efficient way to accomplish this task?

Ruth Gg · Answer 1 · 2020-03-11T09:51:07.050

I adapted this answer to another question related to color_tile to make this function that makes tiles colored according to the column mean when called:

 color_tile_mean <- function (...) {
  formatter("span", style = function(x) {
    style(display = "block",
          padding = "0 4px", 
          `border-radius` = "4px", 
          `background-color` = ifelse(x < mean(x) , "lightpink", "lightgreen"))
  })}

I don't know how to make it accept custom colors, so to change them, just modify the ifelse condition in the function. In your case:

library(formattable)

# Set custom colors
green <- "#71CA97"
red <- "#ff7f7f"

# Make example table
col1 <- c(1.2, 4.2, 5.6, 7.1)
col2 <- c(5.0, 1.3, 10.3, 6.0)
col3 <- c(4.7, 6.3, 1.5, 6.3)
mydata <- data.frame(col1, col2, col3)

# Define color_tile_mean function
 color_tile_mean <- function (...) {
  formatter("span", style = function(x) {
    style(display = "block",
          padding = "0 4px", 
          `border-radius` = "4px", 
          `background-color` = ifelse(x < mean(x) , red, green)) # Remember to change the colors!
  })}

# Use it just like color_tile but without colors
formattable(mydata, align=c("c", "c", "c"),list(
  col1=color_tile_mean(),
  col2=color_tile_mean(),
  col3=color_tile_mean()
  )
)

Result

I hope this was useful!

I've been looking for something like this everywhere! Thanks for sharing! It works beautifully! :) — Piera, Sep 29 '20 at 23:07

demarsylvain · Answer 2 · 2019-06-26T16:59:47.743

1

Did you know the package DT ?

It allows to format tables, and there are functions like formatStyle() and styleInterval() you can use in order to color values according interval or average probably.

library(DT)
library(dplyr)

datatable(mydata) %>% 
  formatStyle(columns = colnames(mydata), color = styleInterval(c(5), c(green, red)))

datatable(mydata) %>% 
  formatStyle(columns = colnames(mydata), backgroundColor = styleInterval(c(5), c(green, red)))

edited Jun 26 '19 at 16:59

answered Jun 26 '19 at 16:40

demarsylvain

2,103
2
14
33

I would like to color the "tiles" behind the value, not the value itself. Do you know if this is reproducible using "formattable", or am I better off using DT? My only concern is that I like the way the tables look in formattable. – JRP Jun 26 '19 at 16:47
you can use `backgroundColor =` instead of `color =`. Here are some examples https://rstudio.github.io/DT/010-style.html. I don't know about library formattable. – demarsylvain Jun 26 '19 at 16:58

How to conditionally color table elements using formattable and color_tile?

2 Answers2

Linked