Conditional coloring and outer borders in pdf KableExtra table in R

Question

i have a simulated data frame in R that i want to conditional format the background of the cells (ie if the value is less than 30 to be red if is between 31 and 75 to be blue and 76 and above green). I tried to add outer borders in the table but as you can see from the photo there are empty spaces with the header at the upper right and the left vertical border does not work.

the setup chunk is :

{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
options(knitr.table.format = "latex")

and the YAML section :

output:
  pdf_document:
    latex_engine: xelatex

Data

library(tibble)
library(kableExtra)

set.seed(123)  # For reproducibility
YEARS = c(1990, 2000, 2010, 2020)
# Generate columns
Year <- rep(YEARS, each = 48)
Category <- rep(sapply(1:16, function(x) paste0(sample(letters, 10, replace = TRUE), collapse = "")), each = 3)
Category <- rep(Category, times = 4)
Favor <- rep(c("Good", "Mediocre", "Bad"), each = 1,times =64)
Percentage <- sample(50:99, 192, replace = TRUE)

# Create tibble
df <- tibble(Year, Category, Favor, Percentage)

df = df%>%
  arrange(
    Category,
    factor(Favor, levels = c("Bad", "Mediocre", "Good")),
    Year
  ) %>%
  pivot_wider(
    names_from = c(Favor, Year),
    values_from = Percentage,
    names_sep = "-"
  )


header_values = c("Category", rep(c(
  paste(YEARS[1]), paste(YEARS[2]), paste(YEARS[3]), paste(YEARS[4])
), 3))
colnames(df)=header_values

df %>%
  kableExtra::kbl(align = "lcccccccccccccc") %>%
  kableExtra::column_spec(1, border_left = TRUE) %>%
  kableExtra::column_spec(ncol(df), border_right = TRUE) %>%
  kableExtra::add_header_above(header = c(" " = 1,
                                          "Good" = 4,
                                          "Mediocre" = 4,
                                          "Bad" = 4)) %>%
  kableExtra::column_spec(1, width = "6cm") 

Answer 1

You can solve the conditional coloring using this great answer and the borders left & right using ?add_header_above -> there are arguments for border_left & border_right.

border_left T/F option for border on the left side in latex.

border_right T/F option for border on the right side in latex.

Please note that the background-color codes are not the same as in R!. So green might work, but green4 not! Don't quote me on this, but I think these colors are fine. You could define your own colors in a LaTeX yaml header \definecolor{orange}{HTML}{ff7f00} or \usepackage[dvipsnames]{xcolor} for more color names as described here. How to add rmarkdown latex header

A character string for background color. Here please pay attention to the differences in color codes between HTML and LaTeX. Also note that in HTML, background defined in cell_spec won't cover the whole cell.

giving

Code

---
title: "Untitled"
output:
  pdf_document:
    latex_engine: xelatex
---

```{r table, echo=F, warning=F}
library(tibble)
library(kableExtra)
library(tidyverse)

set.seed(123)  # For reproducibility
YEARS = c(1990, 2000, 2010, 2020)
# Generate columns
Year <- rep(YEARS, each = 48)
Category <- rep(sapply(1:16, function(x) paste0(sample(letters, 10, replace = TRUE), collapse = "")), each = 3)
Category <- rep(Category, times = 4)
Favor <- rep(c("Good", "Mediocre", "Bad"), each = 1,times =64)
Percentage <- sample(50:99, 192, replace = TRUE)

# Create tibble
df <- tibble(Year, Category, Favor, Percentage)

df = df%>%
  arrange(
    Category,
    factor(Favor, levels = c("Bad", "Mediocre", "Good")),
    Year
  ) %>%
  pivot_wider(
    names_from = c(Favor, Year),
    values_from = Percentage,
    names_sep = "-"
  )


header_values = c("Category", rep(c(
  paste(YEARS[1]), paste(YEARS[2]), paste(YEARS[3]), paste(YEARS[4])
), 3))
colnames(df)=header_values

for (col in 2:ncol(df)) { # iterate cell_spec over columns 2 until end
  df[[col]] <- cell_spec(
    df[[col]], 
    background = case_when(
      df[[col]] < 30 ~ "red",
      df[[col]] >= 30 & df[[col]] <= 75 ~ "blue",
      .default = "teal" # use teal
    ),
    color = "white"  # White text 
  )
}

kbl(df, escape = FALSE, align = "lcccccccccccccc") %>%
  add_header_above(header = c(" " = 1,"Good" = 4,"Mediocre" = 4,"Bad" = 4),border_left = T,border_right = T) %>%
  kable_styling(bootstrap_options = c("bordered"))%>%
  column_spec(1, border_left = TRUE, width = "2cm") %>%
  column_spec(ncol(df), border_right = TRUE)
```

ADD

If I increase the left col to 6cm, the outer bound does not disappear, but the table gets cutoff