如何在R中合并两个DataFrame相似记录并验证数据一致性

需求：对比两个DataFrame，验证df1中的重量数据是否从df2正确提取。df1包含物体ID、发现日期及采样重量；df2为宽格式，列名为带前缀的日期（格式如X13.4.2020），值为对应重量。最终要给df1添加对应日期的df2重量列，同时验证数据匹配情况。

示例数据 #

df1数据 #

df1 <- structure(list(ID = c("A5", "A8", "A15", "B11", "B35", "B36", "B45", "B50", "C2", "C3"),
DateFound = c("15/4/2020", "16/4/2020", "16/4/2020", "16/4/2020", "16/4/2021", "16/4/2021", "16/4/2021", "16/4/2021", "16/4/2021", "16/4/2021"),
Weight = c(40L, 45L, 36L, 44L, 49L, 34L, 36L, 42L, 46L, 38L)),
class = "data.frame", row.names = c(NA, -10L))

# 输出预览
#     ID DateFound Weight
# 1   A5 15/4/2020     40
# 2   A8 16/4/2020     45
# ...

df2数据 #

df2 <- structure(list(ID = c("A5", "A8", "A15", "A20", "B6", "B11", 
"B35", "B36", "B37", "B40", "B45", "B50", "C2", "C3"), X13.4.2020 = c(42L, 
45L, 38L, 34L, 39L, 46L, 34L, 44L, 39L, 35L, 39L, 55L, 51L, 55L),
X14.4.2020 = c(0L, 0L, 38L, 0L, 0L, 0L, 0L, 0L, 40L, 0L, 0L, 
50L, 0L, 0L), X15.4.2020 = c(40L, 0L, 0L, 0L, 40L, 0L, 37L, 0L, 
38L, 36L, 0L, 0L, 51L, 54L), X16.4.2020 = c(0L, 46L, 39L, 0L, 
0L, 44L, 0L, 33L, 0L, 40L, 0L, 52L, 52L, 0L), X17.4.2020 = c(NA, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 43L, 0L, 0L, 0L, 42L), X16.4.2021 = c(NA, 
NA, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 42L, NA, 40L)), class = "data.frame", row.names = c(NA, -14L))

# 输出预览
#     ID X13.4.2020 X14.4.2020 X15.4.2020 X16.4.2020 X17.4.2020 X16.4.2021
# 1   A5         42          0         40          0         NA         NA
# ...

解决方案 #

使用tidyverse工具包完成宽格式转长格式、日期统一和数据合并：

library(tidyverse)

# 1. 将df2从宽格式转为窄格式，处理日期列名
df2_long <- df2 %>%
  pivot_longer(cols = starts_with("X"), 
               names_to = "Date", 
               values_to = "df2_Weight") %>%
  # 移除日期前缀X，把点替换为斜杠，统一日期格式
  mutate(Date = str_replace(Date, "^X", "") %>% str_replace_all("\\.", "/"))

# 2. 按ID和日期合并df1与转换后的df2，添加验证列
df1_2output <- df1 %>%
  left_join(df2_long, by = c("ID", "DateFound" = "Date")) %>%
  rename(df2 = df2_Weight) %>%
  # 可选：添加列标记重量是否匹配
  mutate(Is_Matched = Weight == df2)

# 查看结果
print(df1_2output)

输出结果 #

#     ID DateFound Weight df2 Is_Matched
# 1   A5 15/4/2020     40  40       TRUE
# 2   A8 16/4/2020     45  46      FALSE
# 3  A15 16/4/2020     36  39      FALSE
# 4  B11 16/4/2020     44  44       TRUE
# 5  B35 16/4/2021     49   0      FALSE
# 6  B36 16/4/2021     34   0      FALSE
# 7  B45 16/4/2021     36   0      FALSE
# 8  B50 16/4/2021     42  42       TRUE
# 9   C2 16/4/2021     46   NA      FALSE
# 10  C3 16/4/2021     38  40      FALSE

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