Conditional formatting with column_spec within a dplyr chain

[joseepoirier]

How do I specify a column within a dplyr chain to define the condition to do conditional formatting?

I am looking to have different background colors in a kable depending on the cell value. Per the 2020-10-06 documentation for knitr:kable and kableExtra by Hao Zhu, I'm trying to define the colors using column_spec and an ifelse statement but the latter requires I explicitly name the column to state the condition:

mtcars[1:8, 1:8] %>%
      arrange(wt) %>%
      kbl(booktabs = T, linesep = "") %>% 
      kable_paper(full_width = F) %>%
      column_spec(6, color = "white", background = ifelse(mtcars[1:8,1:8]$drat > 3, "red", "green"))

returns a formatting reflecting the original order of the dataframe, not the arranged one:

If I use . instead of referring to the dataframe by name:

mtcars[1:8, 1:8] %>%
     arrange(wt) %>%
     kbl(booktabs = T, linesep = "") %>% 
     kable_paper(full_width = F) %>%
     column_spec(6, color = "white", background = ifelse(.$drat > 3, "red", "green"))

I get the error: Error in .$drat : $ operator is invalid for atomic vectors. Removing .$ returns Error in ifelse(drat > 3, "red", "green") : object 'drat' not found.

How can I define the condition by referring to the internal dataframe derived through the dplyr chain?

Thanks.

[joels]

I'm not certain, but it looks like the piped data frame isn't available to column_spec. Another option would be to use cell_spec before the call to kbl, as follows:

mtcars[1:8, 1:8] %>%
  rownames_to_column("model") %>% 
  arrange(wt) %>%
  mutate(drat = cell_spec(drat, background=ifelse(drat > 3, "red", "green"))) %>% 
  kbl(booktabs = T, linesep = "", escape=FALSE) %>% 
    kable_paper(full_width = F) %>%
    column_spec(6, color = "white")

Which gives the following PDF output:

[joseepoirier]

Thanks joels.

I won't accept it yet as the solution since it's a workaround and going against the author's recommendation not to use cell_spec for conditional formatting with kableExtra 1.2. However it does do what I need it to, so thanks for your input.

[haozhu233]

Thanks @cderv, for inviting me!

Hi, @joseepoirier, the . symbol in pipes always refers to the last object before the current pipe symbol. For example, in

mtcars %>% summarise(mean = mean(mpg)) %>% mutate(new = .$mean)

. means the new data frame after summarise instead of mtcars. That says, in this case, we won't be able to use . to refer to either mtcars[1:8, 1:8] or the arranged version because column_spec is not directly connected to either of those.

The solution is quite simple: save the final data before you create your table. Well, I understand the joy of piping from top to bottom. However, in cases like this one, it's just easier and cleaner to break them into two pieces.

mydt <- mtcars[1:8, 1:8] %>%
     arrange(wt) 

kbl(mydt, booktabs = T, linesep = "", escape=FALSE) %>% 
    kable_paper(full_width = F) %>%
    column_spec(6, color = ifelse(mydt$drat > 3, "red", "green"))