arrays - 在 df1 中的键数组中查找 df2 中的键并合并相应的值

Question

我有df1

df1 <- data.frame(states = c("wash", "mont", "oreg", "cali", "michi"), key1 = c(1,3,5,7,9), key2 = c(2,4,6,8,10))

看起来像这样(key1 和 key2 是一个键数组):

  states key1 key2
1   wash    1    2
2   mont    3    4
3   oreg    5    6
4   cali    7    8
5  michi    9   10

df2 有附加信息

df2 <- data.frame(sample = c(9,8,5,4,1), value = c("steel", "gold", "blue", "grey", "green"))

看起来像这样:

  sample value
1      9 steel
2      8  gold
3      5  blue
4      4  grey
5      1 green

df2 中的样本需要与 df1 中的 key1 或 key2 匹配才能生成 df3

  states key1 key2 sample value
1   wash    1    2      1 green
2   mont    3    4      4  grey
3   oreg    5    6      5  blue
4   cali    7    8      8  gold
5  michi    9   10      9 steel

然后我可以删除示例列...不是问题。如果 sample 的值可以在 key1 或 key2 中，我如何将 df2 扩展到 df3？

谢谢!

Answer 1

您可以使用一对merge调用并绑定(bind)它们的输出:

rbind(transform(merge(df1, df2, by.x = "key1", by.y = "sample"), sample = key1),
      transform(merge(df1, df2, by.x = "key2", by.y = "sample"), sample = key2))
#   key1 states key2 value sample
# 1    1   wash    2 green      1
# 2    5   oreg    6  blue      5
# 3    9  michi   10 steel      9
# 4    3   mont    4  grey      4
# 5    7   cali    8  gold      8

另一种方法:

match.idx <- pmax(match(df1$key1, df2$sample),
                  match(df1$key2, df2$sample), na.rm = TRUE)
cbind(df1, df2[match.idx, ])
#   states key1 key2 sample value
# 5   wash    1    2      1 green
# 4   mont    3    4      4  grey
# 3   oreg    5    6      5  blue
# 2   cali    7    8      8  gold
# 1  michi    9   10      9 steel