R数据框中观测对各变量成对差值的计算方法

示例数据构造（可直接运行测试） #

# 配对表
df1 <- data.frame(
  PairID = 1:6,
  PairElement1 = c("A", "C", "E", "A", "B", "G"),
  PairElement2 = c("B", "D", "F", "C", "D", "H")
)

# 元素数值表
df2 <- data.frame(
  PairElement = c("A", "B", "C", "D", "E", "F", "G", "H"),
  var1 = c(8,8,10,1,10,5,1,8),
  var2 = c(4,8,1,2,10,6,2,0),
  var3 = c(3,7,0,3,6,8,6,9),
  var4 = c(8,6,1,10,4,6,5,0)
)

方法1：dplyr（tidyverse）实现（推荐，逻辑清晰易扩展） #

library(dplyr)

result <- df1 %>%
  # 关联第一个配对元素的所有变量值
  left_join(df2, by = c("PairElement1" = "PairElement"), suffix = c("", "_1")) %>%
  # 关联第二个配对元素的所有变量值
  left_join(df2, by = c("PairElement2" = "PairElement"), suffix = c("_1", "_2")) %>%
  # 批量计算所有变量的差值，变量数量多的时候不用逐个写逻辑
  mutate(across(ends_with("_1"), 
                ~.x - get(sub("_1$", "_2", cur_column())),
                .names = "{sub('_1$', 'd', .col)}")) %>%
  # 选择需要的列并重命名
  select(PairID,
         Pair1 = PairElement1,
         Pair2 = PairElement2,
         ends_with("d"))

如果变量数量少，也可以手动写差值逻辑，和批量计算效果一致：

mutate(
  var1d = var1_1 - var1_2,
  var2d = var2_1 - var2_2,
  var3d = var3_1 - var3_2,
  var4d = var4_1 - var4_2
)

方法2：纯基础R实现 #

# 两次合并匹配两个配对元素的数值
merge_tmp <- merge(merge(df1, df2, by.x = "PairElement1", by.y = "PairElement"),
                   df2, by.x = "PairElement2", by.y = "PairElement",
                   suffixes = c("_1", "_2"), all.x = TRUE)
# 计算差值
merge_tmp$var1d <- merge_tmp$var1_1 - merge_tmp$var1_2
merge_tmp$var2d <- merge_tmp$var2_1 - merge_tmp$var2_2
merge_tmp$var3d <- merge_tmp$var3_1 - merge_tmp$var3_2
merge_tmp$var4d <- merge_tmp$var4_1 - merge_tmp$var4_2
# 调整列顺序和列名，按PairID排序
result <- merge_tmp[order(merge_tmp$PairID), 
                    c("PairID", "PairElement1", "PairElement2", "var1d", "var2d", "var3d", "var4d")]
colnames(result)[2:3] <- c("Pair1", "Pair2")
rownames(result) <- NULL

两种方法输出的result和你给出的预期结构完全一致。

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