Render in PDF via Rmarkdown a table with conditional colouring

Question

I have a table in R with 4 columns.1st column names with John Helen and George. 2nd column name pizza with values 0.7, 0.5, 0.33.3rd column name pasta with values 0.4,0.7,0.2. 4th column name sweet with values 0.9, 0.3, 0.2.

df <- data.frame(
  Name = c("John", "Helen", "George"),
  Pizza = c(0.7, 0.5, 0.33),
  Pasta = c(0.4, 0.7, 0.2),
  Sweet = c(0.9, 0.3, 0.2)
)

print(df)

Now I want to color each cell according to 3) criteria: if value >=0.7 then color it green, if the cell value is <= 0.3 and >= 0.15 yellow and otherwise white.

library(formattable)


rules <- formatter(
  "span",
  style = x ~ style(
    color = ifelse(x >= 0.7, "green", ifelse(x <= 0.3 & x >= 0.15, "yellow", "white"))
  )
)


formattable_df <- as.data.frame(lapply(df, function(x) as.character(rules(x))))


print(formattable_df)

Now I want to put them all in a rmarkdown file to render a PDF latex style preferably latex table but to keep the conditional colouring in the resulting PDF file.How can I do it?

Answer 1

I like to do this by creating a function which formats the data appropriately using kableExtra::cell_spec():

set_background <- function(x, color = "white", format = "latex") {
    kableExtra::cell_spec(x, format = format, background = color)
}

You can then apply this to the relevant columns of your data using dplyr::across():

library(dplyr)
df |>
    mutate(
        across(Pizza:Sweet, \(x)
            case_when(
                x > 0.7 ~ set_background(x, "green"),
                x >= 0.15 & x <= 0.3 ~ set_background(x, "yellow"),
                TRUE ~ set_background(x)
            )
        )
    ) |>
    kableExtra::kbl(format="latex", booktabs=TRUE, escape=FALSE)

Make sure that your rmarkdown yaml header contains the appropriate packages:

---
output: pdf_document
header-includes:
  - \usepackage{booktabs}
  - \usepackage[table]{xcolor}
---

Output: