R语言实现类Excel VLOOKUP功能 匹配填充表格Cost列

需求说明 #

• 基于Table1的取值填充Table2的Cost列
• 匹配逻辑：同时匹配两个维度：① Table2的FROM字段和Table1的出发地对应；② Table2的Weight值转为字符串后和Table1的列名一致。匹配成功则提取Table1对应位置的数值填入Cost列，无匹配则留空。

示例原始数据 #

# Table1原始结构
Table1 <- structure(list(`0` = c("SYD", "MEL", "BRIS"), `1` = c(0.29, 0.4, 0.46), 
                         `3` = c(0.4, 0.29, 0.87), `5` = c(0.46, 0.85, 0.29)), 
                    class = "data.frame", row.names = c(NA, -3L))

# Table2原始结构
Table2 <- structure(list(FROM = c("SYD", "SYD", "SYD", "MEL", "MEL"), 
                         Weight = c(1L, 3L, 4L, 1L, 5L)), 
                    class = "data.frame", row.names = c(NA, -5L))

实现步骤 #

首先预处理Table1：原始Table1第一列是出发地值，列名为0，先将出发地设为行名，方便后续按行索引匹配：

rownames(Table1) <- Table1$`0`
Table1 <- Table1[, -1] # 删除原出发地列

方法1：基础R实现（无需安装第三方包） #

利用R矩阵的双索引取值逻辑，直接构造行列位置索引匹配，运行效率高：

# 构造行索引：匹配FROM对应的行位置
row_loc <- match(Table2$FROM, rownames(Table1))
# 构造列索引：匹配Weight对应的列位置，注意把Weight转为字符型和Table1列名格式对齐
col_loc <- match(as.character(Table2$Weight), colnames(Table1))
# 按索引批量取值，无匹配的位置自动返回空值NA
Table2$COST <- as.numeric(Table1[cbind(row_loc, col_loc)])

运行后输出结果如下，示例里标注?的位置会自动填充正确计算值：

FROM Weight COST
1  SYD      1 0.29
2  SYD      3 0.40
3  SYD      4   NA
4  MEL      1 0.40
5  MEL      5 0.85

方法2：tidyverse实现（语法易读） #

如果习惯用tidyverse的流处理语法，可以先把宽格式的Table1转为长格式，再通过左连接匹配，代码逻辑更直观：

# 加载tidyverse包，首次使用需先运行install.packages("tidyverse")安装
library(tidyverse)

# 把Table1从宽格式转为长格式，统一字段类型
Table1_long <- Table1 %>%
  rownames_to_column("FROM") %>%
  pivot_longer(cols = -FROM, names_to = "Weight", values_to = "COST") %>%
  mutate(Weight = as.integer(Weight))

# 左连接匹配得到结果
Table2_result <- Table2 %>%
  left_join(Table1_long, by = c("FROM", "Weight"))

该方法得到的结果和基础R方法完全一致。

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