R kable/kableExtra, conditional formatting by percentage (with missing values)

Question

I am using kable and kableExtra and want to display a table with columns of percentages (in format "95%"). I would also like to do conditional formatting of the cell backgrounds based on the percent value (with multiple colors and cut points). In addition, there are a few NAs mixed in.

I have tried some of the methods in this post, but don't like the cell_spec background shading that does not shade the full cell, so would prefer to use column_spec within kable. However all the options I have found to display the percent convert the percent to a character variable, which then limits my ability to do the conditional formatting.

As I was simplifying my example code to post here (after several hours of errors), I did finally get it working using startWith (which is an OK solution in this case because my cutpoints are at 80% and 90%, but not ideal for all cases).

df <- tibble(
          x=c(1:6),
          percents =c (1, .95, .82, .77, .62, NA)
          )
    
df$percents <- percent(df$percents, accuracy = 1)

kable(df, booktabs=TRUE, escape=FALSE ) %>% 
   kable_styling() %>% 
  column_spec(2, background = if_else(startsWith(df$percents, '100'), "Darkgrey", 
                              if_else(startsWith(df$percents, '9'), "Darkgrey", 
                              if_else(startsWith(df$percents, '8'), "silver", "lightgray", 
                              "white"), "white"), "white"))

I'm curious if anyone has another solution for this that preserves the ability to work with the percent values as numbers instead of characters.

Thank You!

score 5 · Accepted Answer · answered May 15 '21 at 19:06

We could use the formattable::percent which will keep the type intact

df$percents = formattable::percent(df$percents, digits = 0) 
kable(df, booktabs=TRUE, escape=FALSE ) %>% 
   kable_styling() %>% 
   column_spec(2, background = case_when(df$percents > 0.9 ~ 'darkgrey', 
                                    df$percents > 0.8 ~ 'silver', 
                                    is.na(df$percents) ~ 'white', 
                                    TRUE ~ 'lightgray'))

data

df <- tibble::tibble(
  x=c(1:6),
   percents =c (1, .95, .82, .77, .62, NA)
   )

score 4 · Answer 2 · answered May 15 '21 at 05:06

Create a separate column for labels and use case_when to compare with numbers.

library(knitr)
library(kableExtra)

df <- tibble::tibble(
  x=c(1:6),
  percents =c (1, .95, .82, .77, .62, NA)
)

df$percent_label <- scales::percent(df$percents, accuracy = 1)

kable(df[-2], booktabs=TRUE, escape=FALSE ) %>% 
  kable_styling() %>% 
  column_spec(2, background = case_when(df$percents > 0.9 ~ 'darkgrey', 
                                        df$percents > 0.8 ~ 'silver', 
                                        is.na(df$percents) ~ 'white', 
                                        TRUE ~ 'lightgray'))

R kable/kableExtra, conditional formatting by percentage (with missing values)

2 Answers2

data