如何基于Permno多列匹配合并两个R数据集？

要实现基于所有Permno相关列的匹配合并，核心思路是把多列Permno的宽格式数据转为长格式，让每个有效Permno对应唯一的记录，再和单Permno列的数据集合并。以下是具体实现步骤：

步骤1：加载所需工具包并导入数据 #

首先加载tidyverse包（包含数据处理所需的dplyr和tidyr），然后导入你提供的示例数据集：

# 加载包
library(tidyverse)

# 导入示例数据集B
df_b <- structure(list(permno = structure(c(17743, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 18092), label = "Historical CRSP PERMNO Link to COMPUSTAT Record", format.stata = "%12.0g"), permno1 = structure(c(NA, 83443, 83443, 83443, 83443, 83443, 83443, 83443, 83443, 83443, 17778, 17778, 17778, 17778, 17778, 17778, 17778, 17778, 17778, 17778, NA), label = "1 permno", format.stata = "%12.0g"), permno2 = structure(c(NA, 17778, 17778, 17778, 17778, 17778, 17778, 17778, 17778, 17778, 83443, 83443, 83443, 83443, 83443, 83443, 83443, 83443, 83443, 83443, NA), label = "2 permno", format.stata = "%12.0g"), permno3 = structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), label = "3 permno", format.stata = "%12.0g"), permno4 = structure(c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), label = "4 permno", format.stata = "%12.0g"), execid = structure(c(124, 125, 125, 125, 125, 125, 125, 125, 125, 125, 125, 125, 125, 125, 125, 125, 125, 125, 125, 125, 133), label = "(firstnm) execid", format.stata = "%10.0g"), exec_lname = structure(c("Berkley", "Buffett", "Buffett", "Buffett", "Buffett", "Buffett", "Buffett", "Buffett", "Buffett", "Buffett", "Buffett", "Buffett", "Buffett", "Buffett", "Buffett", "Buffett", "Buffett", "Buffett", "Buffett", "Buffett", "Miller"), label = "(firstnm) exec_lname", format.stata = "%20s"), exec_fname = structure(c("William", "Warren", "Warren", "Warren", "Warren", "Warren", "Warren", "Warren", "Warren", "Warren", "Warren", "Warren", "Warren", "Warren", "Warren", "Warren", "Warren", "Warren", "Warren", "Warren", "Dane"), label = "(firstnm) exec_fname", format.stata = "%26s")), row.names = c(NA, -21L), class = c("tbl_df", "tbl", "data.frame"))

# 创建示例数据集A（仅含Permno列）
df_a <- tibble(permno = c(17743, 83443, 17778, 18092))

步骤2：将数据集B转为长格式并清洗 #

把5个Permno列转为单列，过滤掉空值（NA），并去重避免重复匹配：

df_b_long <- df_b %>%
  # 选择需要保留的非Permno列，以及所有Permno相关列
  select(execid, exec_fname, exec_lname, starts_with("permno")) %>%
  # 把多列Permno转为长格式，列名统一为permno
  pivot_longer(cols = starts_with("permno"), 
               names_to = "permno_col", 
               values_to = "permno") %>%
  # 过滤掉Permno为空的记录
  filter(!is.na(permno)) %>%
  # 去重，避免同一个execid和permno出现多次
  distinct(execid, permno, .keep_all = TRUE) %>%
  # 移除临时的permno_col列（可选）
  select(-permno_col)

步骤3：合并两个数据集 #

现在可以用left_join（保留数据集A的所有记录）或inner_join（只保留双方匹配的记录）完成合并：

# 左连接：保留df_a的所有Permno，匹配到对应的exec信息
merged_df <- df_a %>%
  left_join(df_b_long, by = "permno")

# 查看合并结果
print(merged_df)

合并后的结果会显示每个Permno对应的所有匹配到的exec记录，比如Permno=83443会匹配到execid=125的Warren Buffett，Permno=17778也会匹配到同一exec。

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