在R中实现同组行列数据匹配的单元格颜色标记

需要处理数据框df1，针对每组相同subject_id和visit_point的两行数据，检查ipv、epds、consent_date、bisq列的内容一致性：

• 同一组内列内容完全一致的单元格标记为绿色
• 内容存在不一致的单元格标记为红色
  无需新增匹配状态列，直接在原单元格上标注颜色。

示例数据 #

df1 <- structure(list(
  subject_id = c("191-3456", "191-3456", "191-0021", "191-0021", "191-0741", "191-0741"), 
  visit_point = c("3-month","3-month","6-month","6-month","3-month","3-month"),
  source_name = c("6730.csv", "0956.csv", "6730.csv", "0956.csv", "6730.csv", "0956.csv"), 
  ipv = c("6", "1", "1", "1", "2", "2"), 
  epds = c("0", "4", "0", "0", "3", "3"), 
  consent_date = c("2022-10-27", "2022-10-20", "2023-01-19", "2023-01-19", "2023-01-19", "2023-01-17"), 
  bisq = c("17:00","18:00","16:00","16:00","02:00", "02:00")), 
  class = "data.frame", row.names = c(NA, -6L))

期望效果 #

实现方法（使用gt包） #

gt包可以轻松实现单元格的条件格式设置，步骤如下：

1. 安装并加载gt包：

install.packages("gt")
library(gt)

2. 生成匹配状态标记逻辑：
   按subject_id+visit_point分组，判断组内目标列内容是否一致，再应用颜色格式：

df1 %>%
  gt() %>%
  # 组内值一致时填充绿色
  tab_style(
    style = cell_fill(color = "#90EE90"), # 浅绿色
    locations = cells_body(
      columns = c(ipv, epds, consent_date, bisq),
      rows = {
        group_indices <- dplyr::group_indices(., subject_id, visit_point)
        sapply(seq_len(nrow(.)), function(i) {
          group <- group_indices[i]
          col <- cur_column()
          all(.[[col]][group_indices == group] == .[[col]][group_indices == group][1])
        })
      }
    )
  ) %>%
  # 组内值不一致时填充红色
  tab_style(
    style = cell_fill(color = "#FFCCCB"), # 浅红色
    locations = cells_body(
      columns = c(ipv, epds, consent_date, bisq),
      rows = {
        group_indices <- dplyr::group_indices(., subject_id, visit_point)
        sapply(seq_len(nrow(.)), function(i) {
          group <- group_indices[i]
          col <- cur_column()
          !all(.[[col]][group_indices == group] == .[[col]][group_indices == group][1])
        })
      }
    )
  )

说明 #

• 代码通过group_indices对数据按分组键分组
• 对目标列的每个单元格，判断所在组内该列所有值是否完全一致：
  • 组内值全一致时，单元格填充浅绿色
  • 组内值存在差异时，单元格填充浅红色
• 该方法直接在原表格上标记颜色，无需新增额外列

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