CODEWITHSUNDEEP
rfunctioncolorsconditional-formattingformattable
JRP
JRP

Reputation: 135

How to conditionally color table elements using formattable and color_tile?

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?

Upvotes: 3

Views: 3978

Answers (2)

Ruth Gg
Ruth Gg

Reputation: 51

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!

Upvotes: 5

demarsylvain
demarsylvain

Reputation: 2185

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)))

enter image description here

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

enter image description here

Upvotes: 1

Related Questions