如何根据ireg与sector双列匹配从对照表向数据集添加值？

问题背景 #

现有一个15行的tibble数据集（包含nquest、nord、tpens、ireg、sector字段），需要根据ireg和sector的组合，从对应的对照表中匹配数值并添加为新列VA。之前使用case_when逐个手动匹配80个组合，操作过于繁琐，寻求高效实现方法。

原数据集示例 #

# A tibble: 15 × 5
# Groups:   nquest, nord [15]
   nquest  nord tpens  ireg sector
    <int> <int> <dbl> <int>  <dbl>
 1    173     1  1800    18      1
 2   2886     1  1211    13      4
 3   2886     2  2100    13      3
 4   5416     1   700     8      4
 5   7886     1  2000     9      2
 6  20297     1  1200     5      2
 7  20711     2  2000     4      3
 8  22169     1   880    15      4
 9  22276     1  1200     8      1
10  22286     1   850     8      4
11  22286     2   650     8      2
12  22657     1  1400    16      4
13  22657     2  1500    16      1
14  23490     1  1400     5      1
15  24147     1  1730     4      4

对照表示例 #

sector == 1   sector == 2  sector == 3   sector ==4
ireg == 1        1944.5       27973.4        5328.4      79542.3
ireg == 2          49.3         561.7         237.1       3132.8
ireg == 3        3785.3       73596.3       13563.1     240420.8
ireg == 4        1661.1        7157.5        2054.2      27174.2
ireg == 5        3016.1       37700.8        6429.1      89338.0
ireg == 6         504.5        8160.5        1386.9      22491.7
ireg == 7         442.6        6954.7        2160.0      32398.8
ireg == 8        3419.0       36172.4        5416.6      89411.6
ireg == 9        2266.7       20603.2        4269.7      74176.9
ireg == 10        551.7        4019.0        1060.2      13843.0
ireg == 11        642.8        9484.3        1569.9      23637.0
ireg == 12       1962.6       17565.5        5863.1     145318.8
ireg == 13        843.9        5562.1        1520.3      19911.1
ireg == 14        308.7         831.5         295.6       4021.4
ireg == 15       2524.2       12507.0        4441.0      75844.6
ireg == 16       2763.3        8485.3        3581.4      49263.3
ireg == 17        607.4        2481.2         555.3       6689.7
ireg == 18       1474.0        2272.0        1217.7      23005.5
ireg == 19       3303.8        6552.2        3444.8      63552.4
ireg == 20       1232.1        3212.3        1449.7      23478.9

原低效方法（手动case_when） #

dataset <- dataset %>%
  mutate(VA = case_when(
    (ireg == 1 & sector == 1) ~ 1944.5,
    (ireg == 1 & sector == 2 ) ~ 27973.4,
    (ireg == 1 & sector == 3 ) ~ 5328.4,
    (ireg == 1 & sector == 4 ) ~ 79542.3,
    (ireg == ... & sector == ... ) ~ ....
  ))

高效解决方案 #

使用数据连接替代手动匹配，步骤如下：

1. 将对照表转换为整洁格式的数据框 #

把宽格式的对照表转成ireg、sector、VA三列的长格式，方便后续连接：

library(tibble)

lookup_table <- tibble(
  ireg = rep(1:20, each = 4),
  sector = rep(1:4, times = 20),
  VA = c(1944.5, 27973.4, 5328.4, 79542.3,
         49.3, 561.7, 237.1, 3132.8,
         3785.3, 73596.3, 13563.1, 240420.8,
         1661.1, 7157.5, 2054.2, 27174.2,
         3016.1, 37700.8, 6429.1, 89338.0,
         504.5, 8160.5, 1386.9, 22491.7,
         442.6, 6954.7, 2160.0, 32398.8,
         3419.0, 36172.4, 5416.6, 89411.6,
         2266.7, 20603.2, 4269.7, 74176.9,
         551.7, 4019.0, 1060.2, 13843.0,
         642.8, 9484.3, 1569.9, 23637.0,
         1962.6, 17565.5, 5863.1, 145318.8,
         843.9, 5562.1, 1520.3, 19911.1,
         308.7, 831.5, 295.6, 4021.4,
         2524.2, 12507.0, 4441.0, 75844.6,
         2763.3, 8485.3, 3581.4, 49263.3,
         607.4, 2481.2, 555.3, 6689.7,
         1474.0, 2272.0, 1217.7, 23005.5,
         3303.8, 6552.2, 3444.8, 63552.4,
         1232.1, 3212.3, 1449.7, 23478.9)
)

如果对照表是从外部文件（如CSV/Excel）读取的宽格式数据，可自动转换：

library(dplyr)
library(tidyr)
library(stringr)

# 读取宽格式对照表
wide_lookup <- read.csv("your_lookup_file.csv")

# 转换为长格式
lookup_table <- wide_lookup %>%
  mutate(ireg = as.integer(str_remove(ireg, "ireg == "))) %>%
  pivot_longer(
    cols = starts_with("sector == "),
    names_to = "sector",
    values_to = "VA"
  ) %>%
  mutate(sector = as.integer(str_remove(sector, "sector == ")))

2. 用left_join连接数据集，自动匹配VA值 #

通过ireg和sector作为连接键，一次性完成所有匹配：

dataset <- dataset %>%
  left_join(lookup_table, by = c("ireg", "sector"))

方法优势 #

• 无需手动编写大量case_when条件，代码简洁易读
• 后续对照表更新时，只需修改lookup_table或重新读取外部文件，维护成本低
• 连接操作是dplyr核心功能，性能稳定，处理大规模数据也高效

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