Conditional Cell Formatting using KableExtra in R

Question

I'm trying to format cell's within a column using cell_spec in the KableExtra library. My data and code looks like this:

df<-data.frame(Category=c("A","B","C","D"),Q1=c(46540.6541,4779.1654,798984.648,987454.144586),Q2=c(98742.06543,78993.0641,6418.016,48974.041684),QoQ=c("1.02%","8.05%","5.04","6.87"))
df$QoQ<-ifelse(
  df$QoQ > 0.00,
  cell_spec(df$QoQ, color = "green", bold = T),
  cell_spec(df$QoQ, color = "red", bold = T))
kbl(df)

When I execute the code above, the table's QoQ column returns the following: 1.02%. I suspect this is because I'm filtering the values numerically but my data class is character. My question is, can I keep my data as is, meaning I keep the % sign in the QoQ column, and still write a conditional statement where if the QoQ cell is greater than 0.00% then it is green, otherwise red?

user_3732271 · Answer

We could convert to numeric with formattable::percent

library(kableExtra)
library(formattable)
library(dplyr)
df1 <- df %>%
  mutate(QoQ  = formattable::percent(readr::parse_number(QoQ))/100,
         QoQ = cell_spec(QoQ, color = ifelse(QoQ > 0.0, "green", 
            "red"), bold = TRUE))
kbl(df1, escape = FALSE) %>%
  kable_styling

-output

data

df <- structure(list(Category = c("A", "B", "C", "D"), Q1 = c(46540.6541, 
4779.1654, 798984.648, 987454.144586), Q2 = c(98742.06543, 78993.0641, 
6418.016, 48974.041684), QoQ = c("1.02%", "8.05%", "5.04", "6.87"
)), class = "data.frame", row.names = c(NA, -4L))

user_13321647 · Answer

We could use parse_number in combination with scales::percent

library(readr)
library(dplyr)
library(scales)

df %>%
  mutate(QoQ = parse_number(QoQ)) %>% 
  mutate(QoQ = scales::percent(QoQ))


df$QoQ<-ifelse(
  df$QoQ > 0.00,
  cell_spec(df$QoQ, color = "green", bold = T),
  cell_spec(df$QoQ, color = "red", bold = T))

kbl(df, escape = FALSE) %>% 
  kable_styling

Output:

Conditional Cell Formatting using KableExtra in R

2 Answers

data