R语言DT包DataTable条件格式化：高亮行列匹配单元格

问题描述 #

给定如下结构的R数据框：

data <- data.frame(
  Time = c(1, 2, 3, 4, 5, 6, 7, 8),
  Rider = c("Rider1", "Rider2", "Rider3", "Rider4", "Rider1", "Rider2", "Rider3", "Rider4"),
  Rider1 = c(10, 20, 15, 25, 30, 45, 30, 43),
  Rider2 = c(12, 22, 14, 24, 32, 30, 56, 75),
  Rider3 = c(9, 19, 11, 21, 29, 40, 20, 19),
  Rider4 = c(11, 21, 13, 23, 31, 29, 40, 20)
)

需要在R Markdown中用DT包将其展示为交互式表格，并实现条件格式化：高亮Rider列值与列名匹配的单元格（修改背景色）。原尝试代码未成功：

datatable(data) %>%
  formatStyle(
    columns = c("Rider1", "Rider2", "Rider3", "Rider4"),
    valueColumns = "Rider",
    backgroundColor = styleEqual(data$Rider, c("Rider1" = "yellow", "Rider2" = "lightblue", "Rider3" = "lightgreen", "Rider4" = "lightpink"))
  )

问题原因 #

原代码的styleEqual用法不符合需求：styleEqual是将valueColumns中的值直接映射到样式，但这里需要对比当前列的列名与对应行的Rider值，属于更复杂的条件判断，无法通过简单的等值映射实现。

DT包解决方案 #

方法1：使用formatStyle结合JS函数

通过自定义JavaScript函数，动态判断当前列名是否与行内Rider值匹配，再设置对应背景色：

library(DT)

datatable(data) %>%
  formatStyle(
    columns = c("Rider1", "Rider2", "Rider3", "Rider4"),
    backgroundColor = JS(
      "function(value, row, index) {",
      "  // 获取当前列的列名",
      "  const colName = this.api().column(this.index()).header().innerText;",
      "  // 对比列名与Rider值，返回对应背景色",
      "  if (colName === row.Rider) {",
      "    switch(row.Rider) {",
      "      case 'Rider1': return 'yellow';",
      "      case 'Rider2': return 'lightblue';",
      "      case 'Rider3': return 'lightgreen';",
      "      case 'Rider4': return 'lightpink';",
      "    }",
      "  }",
      "  // 不匹配则返回默认背景",
      "  return '';",
      "}"
    )
  )

方法2：使用rowCallback遍历行

通过rowCallback逐行处理，找到与Rider值匹配的列并设置样式：

datatable(data, 
          rowCallback = JS(
            "function(row, data) {",
            "  const targetCol = data.Rider;",
            "  // 找到列名匹配的索引",
            "  const colIdx = table.columns().indexes().filter(function(idx) {",
            "    return table.column(idx).header().innerText === targetCol;",
            "  });",
            "  // 为对应单元格设置背景色",
            "  if (colIdx.length > 0) {",
            "    let bgColor = '';",
            "    switch(targetCol) {",
            "      case 'Rider1': bgColor = 'yellow'; break;",
            "      case 'Rider2': bgColor = 'lightblue'; break;",
            "      case 'Rider3': bgColor = 'lightgreen'; break;",
            "      case 'Rider4': bgColor = 'lightpink'; break;",
            "    }",
            "    $('td', row).eq(colIdx[0]).css('background-color', bgColor);",
            "  }",
            "}"
          ))

替代方案：formattable包 #

如果更倾向于静态格式化表格，可以使用formattable包，为每列单独设置条件样式：

library(formattable)

formattable(data, list(
  Rider1 = formatter("span", style = function(x, row) {
    if (row[["Rider"]] == "Rider1") style("background-color", "yellow") else NULL
  }),
  Rider2 = formatter("span", style = function(x, row) {
    if (row[["Rider"]] == "Rider2") style("background-color", "lightblue") else NULL
  }),
  Rider3 = formatter("span", style = function(x, row) {
    if (row[["Rider"]] == "Rider3") style("background-color", "lightgreen") else NULL
  }),
  Rider4 = formatter("span", style = function(x, row) {
    if (row[["Rider"]] == "Rider4") style("background-color", "lightpink") else NULL
  })
))

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