在R中实现Excel的VLOOKUP等效功能

我在R中有两个数据框：
第一个名为sports_countries，结构如下：

sports_countries <- structure(list(Sport = c("Basketball", "Baseball", "Badminton", 
"Cricket", "Hockey", "Gymnastics", "Rugby", "Football", "Tennis", 
"Volleyball"), Country = c("Turkey", "Nicaragua", "South Korea", 
"Botswana", "Oman", "Brazil", "Samoa", "Saudi Arabia", "Belarus", 
"Latvia"), Score = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA)), row.names = c(NA, 
-10L), class = "data.frame")

第二个名为sports_countries_score，结构如下：

sports_countries_score <- structure(list(Country = c("Belarus", "Botswana", "Brazil", "Latvia", 
"Nicaragua", "Oman", "Samoa", "Saudi Arabia", "South Korea", 
"Turkey"), Basketball = c(100L, 100L, 13L, 25L, 88L, 100L, 100L, 
68L, 12L, 11L), Baseball = c(49L, 44L, 18L, 100L, 17L, 100L, 
100L, 100L, 5L, 51L), Badminton = c(100L, 100L, 52L, 100L, 100L, 
100L, 100L, 100L, 2L, 48L), Cricket = c(100L, 40L, 30L, 100L, 
100L, 18L, 44L, 32L, 100L, 100L), Hockey = c(14L, 100L, 35L, 
10L, 100L, 27L, 66L, 100L, 9L, 28L), Gymnastics = c(100L, 100L, 
4L, 100L, 100L, 100L, 100L, 100L, 8L, 11L), Rugby = c(100L, 54L, 
10L, 50L, 100L, 100L, 3L, 100L, 30L, 13L), Football = c(57L, 
100L, 3L, 100L, 100L, 73L, 97L, 53L, 17L, 41L), Tennis = c(2L, 
100L, 13L, 17L, 100L, 100L, 100L, 100L, 99L, 100L), Volleyball = c(100L, 
76L, 1L, 9L, 53L, 59L, 100L, 60L, 30L, 1L)), class = "data.frame", row.names = c(NA, 
-10L))

如果把它们看作Excel的不同工作表，我原本会用以下VLOOKUP公式填充sports_countries的Score列（从单元格C2开始）：

=VLOOKUP(B2,sports_countries_score!A:K,2,FALSE)
=VLOOKUP(B3,sports_countries_score!A:K,3,FALSE)
=VLOOKUP(B4,sports_countries_score!A:K,4,FALSE)
=VLOOKUP(B5,sports_countries_score!A:K,5,FALSE)
=VLOOKUP(B6,sports_countries_score!A:K,6,FALSE)
=VLOOKUP(B7,sports_countries_score!A:K,7,FALSE)
=VLOOKUP(B8,sports_countries_score!A:K,8,FALSE)
=VLOOKUP(B9,sports_countries_score!A:K,9,FALSE)
=VLOOKUP(B10,sports_countries_score!A:K,10,FALSE)
=VLOOKUP(B11,sports_countries_score!A:K,11,FALSE) 

但我尝试用以下R代码未能实现该功能：

final_table <- sports_countries %>%
  left_join(sports_countries_score, by = "Country") %>%
  select(Sport, Country, Score = Basketball:Volleyball)

请问在R中实现该等效功能的最佳方式是什么？

你之前的代码问题在于select(Sport, Country, Score = Basketball:Volleyball)会把所有运动列都选出来并试图统一命名为Score，这显然不符合需求——我们需要根据每一行的Sport值，匹配对应的列提取分数。

推荐用重塑分数表为长格式再匹配的方式实现，这是tidyverse风格的标准解法：

library(dplyr)
library(tidyr)

final_table <- sports_countries %>%
  left_join(
    # 将宽格式的分数表转为长格式，让每个运动成为单独行
    sports_countries_score %>%
      pivot_longer(
        cols = Basketball:Volleyball,  # 选中所有运动列
        names_to = "Sport",            # 原列名转为Sport字段
        values_to = "Score"            # 原列值转为Score字段
      ),
    by = c("Country", "Sport")         # 按国家和运动双重匹配
  )

执行后final_table的Score列结果，完全对应你用Excel VLOOKUP实现的效果。

如果不想引入tidyr包，也可以用基础R的mapply逐行匹配：

final_table <- sports_countries
# 逐行匹配对应国家和运动的分数
final_table$Score <- mapply(
  function(country, sport) {
    sports_countries_score[sports_countries_score$Country == country, sport]
  },
  final_table$Country,
  final_table$Sport
)

两种方法都能达成目标，tidyverse的写法可读性更强，也更便于后续数据处理。

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