Search code examples
rr-markdownkableformattable

Change color_bar color based on odd or even rows, R table

I currently have a formattable table such as the following example:

library(data.table)
library(dplyr)
library(formattable)
library(tidyr)
customGreen0 = "#DeF7E9"
customGreen = "#71CA97"
customRed = "#ff7f7f"

austinData= fread('https://raw.githubusercontent.com/lgellis/MiscTutorial/master/Austin/Imagine_Austin_Indicators.csv', data.table=FALSE, header = TRUE, stringsAsFactors = FALSE)
attach(austinData)

i1 <- austinData %>%
  filter(`Indicator Name` %in% 
           c('Prevalence of Obesity', 'Prevalence of Tobacco Use', 
             'Prevalence of Cardiovascular Disease', 'Prevalence of Diabetes')) %>%
  select(c(`Indicator Name`, `2011`, `2012`, `2013`, `2014`, `2015`, `2016`)) %>%
  mutate (Average = round(rowMeans(
    cbind(`2011`, `2012`, `2013`, `2014`, `2015`, `2016`), na.rm=T),2), 
    `Improvement` = round((`2011`-`2016`)/`2011`*100,2))
i1

color_bar3 <- function (color = "#49CA69", fun = "proportion", ...)
{
  fun <- match.fun(fun)
  formatter("span", style = function(x) style(display = "inline-block",
                                              `border-radius` = "5px", `padding-left` = "3px",
                                              `background-color` = csscolor(color),
                                              width = percent(fun(as.numeric(gsub(",", "", x)), ...))))
}


formattable(i1, align =c("l","c","c","c","r", "c", "l", "l", "r"), list(
  `Indicator Name` = formatter("span", style = ~ style(color = "grey",font.weight = "bold")), 
  `2011`= color_tile(customGreen, customGreen0),
  `2012`= color_tile(customGreen, customGreen0),
  `2013`= color_tile(customGreen, customGreen0),
  `2014`= color_bar(customRed),
  `2015`= color_tile(customGreen, customGreen0),
  `2016`= color_bar(customGreen),
  `Average` = color_bar3(customRed)
))

having this table:

enter image description here

What I want to do but I have not found the way is: As you can see the Average column is using a redish color....

How do I change the bar colors of the Average column only if I am in row 2 (Prevalence of Tobacco Use) or row 4 (Prevalence of Diabetes) to blue?

In other words i want the Average bars in red if I am in row 1 and 3 or in blue if I am in rows 2 and 4. Something like the following:

enter image description here

thanks!

Solution

  • We may use the row/col in area

    formattable(i1, align =c("l","c","c","c","r", "c", "l", "l", "r"), list(
  `Indicator Name` = formatter("span", style = ~ style(color = "grey",font.weight = "bold")), 
  `2011`= color_tile(customGreen, customGreen0),
  `2012`= color_tile(customGreen, customGreen0),
  `2013`= color_tile(customGreen, customGreen0),
  `2014`= color_bar(customRed),
  `2015`= color_tile(customGreen, customGreen0),
  `2016`= color_bar(customGreen),
  area(row = c(1, 3), col = `Average`) ~ color_bar3(customRed),
  area(row = c(2, 4), col = `Average`) ~ color_bar3("lightblue")
))

    -output

    enter image description here