R语言中如何基于另一数据框子集变量平均值创建新变量？

问题原因 #

你原来的代码无法正常运行，核心问题是在mutate中直接用df1$To/df1$From做向量匹配时，mean()会对整个筛选后的向量计算全局均值，而非逐行匹配对应Zone的均值。

正确实现步骤 #

1. 先预计算df2中每个Zone的Value平均值，生成一个均值映射表
2. 将该映射表与df1分别按From和To做连接，获取每行对应的From、To均值
3. 计算两者差值得到Diff

完整代码 #

library(dplyr)

# 原始数据框
df1 <- data.frame("From" = c("X", "Y", "Z"),
                  "To" = c("Z", "Y", "X"))

df2 <- data.frame("Zone" = c("X", "X", "X", "Y", "Y", "Y", "Z", "Z", "Z"),
                  "Value" = seq(1, 9, 1))

# 步骤1：计算每个Zone的均值
zone_means <- df2 %>% 
  group_by(Zone) %>% 
  summarise(Mean_Value = mean(Value, na.rm = TRUE))

# 步骤2-3：连接并计算Diff
df3 <- df1 %>%
  left_join(zone_means, by = c("From" = "Zone")) %>%
  rename(From_Mean = Mean_Value) %>%
  left_join(zone_means, by = c("To" = "Zone")) %>%
  rename(To_Mean = Mean_Value) %>%
  mutate(Diff = To_Mean - From_Mean) %>%
  select(From, To, Diff)

# 输出结果
print(df3)

简化写法（无需中间表） #

也可以用pull()结合match()直接在mutate中获取对应均值，代码更紧凑：

df3 <- df1 %>%
  mutate(
    From_Mean = pull(zone_means, Mean_Value)[match(From, zone_means$Zone)],
    To_Mean = pull(zone_means, Mean_Value)[match(To, zone_means$Zone)],
    Diff = To_Mean - From_Mean
  ) %>%
  select(From, To, Diff)

运行后会得到预期结果：

From To Diff
1    X  Z    6
2    Y  Y    0
3    Z  X   -6

内容的提问来源于stack exchange，提问作者nico.sch