如何在kableExtra的cell_spec()调用中忽略NA值？

问题 #

我希望在对特定值做条件格式设置时忽略NA值，让NA显示为空单元格。但使用cell_spec()后，NA值会被单元格样式覆盖，导致options(knitr.kable.NA = '')无法生效。示例代码如下：

library(tidyverse)
library(kableExtra)

df <- data.frame(
  name = paste("Name", LETTERS[1:10]),
  var1 = c(0.5, -1.3, NA, 3.4, -2.6, 0.8, NA, 0.3, 2, -1.6)
)

df_fill <- df %>%
  mutate(var1 = cell_spec(
    var1,
    background = case_when(
      var1 >= 1 ~ "#FEDCDA",
      var1 <= -1 ~ "#E5F8E0",
      .default = "white")
    )
  )

options(knitr.kable.NA = '')

kbl(df_fill,
    booktabs = TRUE,
    escape = FALSE,
    linesep = "") %>%
  kable_styling(latex_options = c("HOLD_position"),
                font_size = 8)

解决方案 #

问题核心是cell_spec()会为NA值生成带样式的单元格代码，直接覆盖了knitr.kable.NA的设置。只需在处理时单独过滤NA值，让其返回空字符串即可：

library(tidyverse)
library(kableExtra)

df <- data.frame(
  name = paste("Name", LETTERS[1:10]),
  var1 = c(0.5, -1.3, NA, 3.4, -2.6, 0.8, NA, 0.3, 2, -1.6)
)

df_fill <- df %>%
  mutate(var1 = ifelse(
    is.na(var1),
    "",
    cell_spec(
      var1,
      background = case_when(
        var1 >= 1 ~ "#FEDCDA",
        var1 <= -1 ~ "#E5F8E0",
        .default = "white"
      )
    )
  ))

kbl(df_fill,
    booktabs = TRUE,
    escape = FALSE,
    linesep = "") %>%
  kable_styling(latex_options = c("HOLD_position"),
                font_size = 8)

逻辑说明 #

• 用ifelse(is.na(var1), "", cell_spec(...))优先判断NA值，直接返回空字符串，避免cell_spec为NA生成无效样式代码
• 非NA值正常执行cell_spec的条件格式逻辑，不影响原有样式效果

内容的提问来源于stack exchange，提问作者user13973103