如何在RStudio中合并含重复TrackerID的全球能源监测数据集

需求说明 #

• Dataset1包含全部电厂的TrackerID；Dataset2中部分TrackerID缺失，部分ID重复出现，同一ID对应Signatory的不同取值
• 合并目标：Dataset1中在Dataset2有多条观测的ID需复制行，对应Dataset2的每条记录；Dataset2中没有的ID，Signatory补NA

示例数据集 #

Dataset1 <- data.frame(TrackerID = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))

Dataset2 <- data.frame(TrackerID = c(1, 3, 3, 6, 7, 7, 7, 10),
                       Signatory= c(1, 1, 0, 0, 1, 0, 1, 1))

期望合并结果 #

Dataset.wished <- data.frame(TrackerID = c(1, 2, 3, "3_1", 4, 5, 6, 7, "7_1", "7_2", 8, 9, 10),
                             Signatory = c(1, NA, 1, 0, NA, NA, 0, 1, 0, 1, NA, NA, 1))

实现代码（用dplyr包） #

# 未安装dplyr的话先运行：install.packages("dplyr")
library(dplyr)

# 给Dataset2的重复TrackerID添加区分后缀
Dataset2_processed <- Dataset2 %>%
  group_by(TrackerID) %>%
  mutate(
    row_idx = row_number(),
    TrackerID_new = ifelse(row_idx > 1, paste0(TrackerID, "_", row_idx - 1), as.character(TrackerID))
  ) %>%
  ungroup() %>%
  select(-row_idx)

# 左连接数据集并整理最终输出
Dataset_merged <- Dataset1 %>%
  mutate(TrackerID = as.character(TrackerID)) %>%
  left_join(Dataset2_processed, by = "TrackerID") %>%
  mutate(
    TrackerID_final = coalesce(TrackerID_new, TrackerID),
    Signatory = ifelse(is.na(Signatory), NA, Signatory)
  ) %>%
  select(TrackerID = TrackerID_final, Signatory)

# 查看合并结果
print(Dataset_merged)

代码说明 #

1. 处理重复ID：通过group_by和row_number()为每个TrackerID的重复行编号，对第2行及以后的行添加_1、_2后缀，区分同一ID的不同记录
2. 左连接：确保Dataset1的所有TrackerID都被保留，Dataset2中没有的ID，Signatory自动填充为NA
3. 整理输出：用coalesce优先使用带后缀的ID，无后缀时保留原ID，最终只保留需要的两列

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