R语言合并行数不同的数据集：按指定列匹配并生成NA值

问题背景 #

需要合并两个行数不同的数据集：4行的df_main和6行的df_add，要求按df_main$match_id与df_add$other_id匹配，无匹配的位置填充NA。之前尝试的merge(df_main, df_add, by = 0, all = TRUE)[-1]能得到6行和NA值，但未按指定列正确匹配，期望得到的目标结构如下：

df_goal <- data.frame (match_id  = c("1", "1", "1", "2", "2", "2"),
                       index_date = c("2006-09-13", "2006-09-13", NA, "2006-09-13", "2006-09-13", NA),
                       type =  c("Good", "Good", NA, "Bad", "Bad", NA),
                       measure_date = c("2005-01-01", "2005-03-13", "2005-04-19", "2005-06-22", "2005-09-29", "2005-11-03"),
                       wt =  c(10, 11, 15, 60, 42, 33)
                  )

解决方案 #

核心思路是给两个数据集的同组内的行添加序号，确保同match_id/other_id下的行能按顺序对应，再进行合并。

方法1：基础R实现 #

# 假设你的原始数据集结构如下（可替换为实际数据）
df_main <- data.frame(
  match_id = c("1", "1", "2", "2"),
  index_date = rep("2006-09-13", 4),
  type = c("Good", "Good", "Bad", "Bad")
)

df_add <- data.frame(
  other_id = c("1", "1", "1", "2", "2", "2"),
  measure_date = c("2005-01-01", "2005-03-13", "2005-04-19", "2005-06-22", "2005-09-29", "2005-11-03"),
  wt = c(10, 11, 15, 60, 42, 33)
)

# 1. 给df_main添加组内行号
df_main <- transform(df_main, row_num = ave(match_id, match_id, FUN = seq_along))

# 2. 给df_add添加组内行号，并将other_id重命名为match_id
df_add <- transform(df_add, row_num = ave(other_id, other_id, FUN = seq_along))
names(df_add)[names(df_add) == "other_id"] <- "match_id"

# 3. 按match_id和row_num合并，保留df_add的所有行（确保最终6行）
df_result <- merge(df_main, df_add, by = c("match_id", "row_num"), all.y = TRUE)

# 4. 移除辅助的row_num列
df_result <- df_result[, !names(df_result) %in% "row_num"]

方法2：dplyr包实现（更简洁） #

library(dplyr)

# 假设原始数据集同上
df_result <- df_add %>%
  # 重命名匹配列，统一为match_id
  rename(match_id = other_id) %>%
  # 按match_id分组，添加组内行号
  group_by(match_id) %>%
  mutate(row_num = row_number()) %>%
  # 左连接处理后的df_main（同样添加组内行号）
  left_join(
    df_main %>%
      group_by(match_id) %>%
      mutate(row_num = row_number()),
    by = c("match_id", "row_num")
  ) %>%
  # 调整列顺序为目标结构
  select(match_id, index_date, type, measure_date, wt) %>%
  ungroup() %>%
  # 移除辅助行号列
  select(-row_num)

说明 #

之前使用by=0的错误在于：它是按全局行号合并，而非按指定的match_id/other_id分组匹配。通过添加组内行号，能保证同组内的行按顺序一一对应，多余的行自动填充NA，最终得到符合预期的结构。

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