如何连接两张表查找缺失行并填充NA值？

问题说明 #

我有两份表格：

• Table A是完整的样本列表，每个以Name、treat.、n为组合的样本组包含3条重复行
• Table B包含相同样本组合的部分行及对应数值，存在行缺失

表格示例 #

Table A                                 Table B
------------------------                ----------------------------------
Name     treat.      n                  Name     treat.      n       Value
AA         H         1                  AA         H         1         x1
AA         H         1                  AA         H         1         x2
AA         H         1                  AA         H         1         x3
BB         C         2                  BB         C         2         y1
BB         C         2                  BB         C         2         y2
BB         C         2                  CC         H         3         z1
CC         H         3                  
CC         H         3
CC         H         3

期望结果 #

Name     treat.      n     Value    
----------------------------------              
AA         H         1       x1           
AA         H         1       x2           
AA         H         1       x3           
BB         C         2       y1           
BB         C         2       y2           
BB         C         2       NA           
CC         H         3       z1           
CC         H         3       NA
CC         H         3       NA

解决方案（R语言） #

核心思路是给每个样本组内的行添加序号，通过序号匹配完成行对齐，缺失行自动填充NA。

方法1：使用tidyverse包 #

library(tidyverse)

# 构造示例数据（实际使用时替换为你的数据读取代码，如read.csv）
table_a <- tibble(
  Name = c("AA", "AA", "AA", "BB", "BB", "BB", "CC", "CC", "CC"),
  treat. = c("H", "H", "H", "C", "C", "C", "H", "H", "H"),
  n = c(1,1,1,2,2,2,3,3,3)
)

table_b <- tibble(
  Name = c("AA", "AA", "AA", "BB", "BB", "CC"),
  treat. = c("H", "H", "H", "C", "C", "H"),
  n = c(1,1,1,2,2,3),
  Value = c("x1", "x2", "x3", "y1", "y2", "z1")
)

# 给两个表格添加组内序号
table_a <- table_a %>%
  group_by(Name, treat., n) %>%
  mutate(row_id = row_number()) %>%
  ungroup()

table_b <- table_b %>%
  group_by(Name, treat., n) %>%
  mutate(row_id = row_number()) %>%
  ungroup()

# 左连接并整理结果
result <- table_a %>%
  left_join(table_b, by = c("Name", "treat.", "n", "row_id")) %>%
  select(-row_id)

# 输出结果
print(result)

方法2：使用Base R #

# 构造示例数据
table_a <- data.frame(
  Name = c("AA", "AA", "AA", "BB", "BB", "BB", "CC", "CC", "CC"),
  treat. = c("H", "H", "H", "C", "C", "C", "H", "H", "H"),
  n = c(1,1,1,2,2,2,3,3,3),
  stringsAsFactors = FALSE
)

table_b <- data.frame(
  Name = c("AA", "AA", "AA", "BB", "BB", "CC"),
  treat. = c("H", "H", "H", "C", "C", "H"),
  n = c(1,1,1,2,2,3),
  Value = c("x1", "x2", "x3", "y1", "y2", "z1"),
  stringsAsFactors = FALSE
)

# 添加组内序号
table_a$row_id <- ave(rep(1, nrow(table_a)), table_a$Name, table_a$treat., table_a$n, FUN = seq_along)
table_b$row_id <- ave(rep(1, nrow(table_b)), table_b$Name, table_b$treat., table_b$n, FUN = seq_along)

# 左连接并整理
result <- merge(table_a, table_b, by = c("Name", "treat.", "n", "row_id"), all.x = TRUE)
result <- result[, !colnames(result) %in% "row_id"]
# 恢复原顺序
result <- result[order(result$Name, result$treat., result$n), ]
rownames(result) <- NULL

# 输出结果
print(result)

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