如何用dplyr的full_join按k_Name关联补全缺失Class行？

问题背景 #

给定两个tibble数据集：

k <- tibble::tribble(
  ~k_Name, ~Class, ~p_k,
  "IJP",   "A",    0.66,
  "IJP",   "B",    0.25,
  "IJP",   "C",    0.09,
)

xi <- tibble::tribble(
  ~xi_Name, ~Class, ~p_xi, ~k_Name,
  "1",      "A",    0.5,   "IJP",
  "1",      "B",    0.5,   "IJP",
  "2",      "A",    0.25,  "IJP",
  "2",      "B",    0.25,  "IJP",
  "2",      "C",    0.25,  "IJP",
  "2",      "D",    0.25,  "IJP",
)

执行full_join(k, xi)时，会缺失xi_Name = "1"且Class = "C"的行——该Class在原始xi表中不存在，但需要基于匹配的k_Name（值为"IJP"）生成该行，对应p_xi字段为NA。期望输出如下：

tibble::tribble(
  ~xi_Name, ~Class, ~p_xi, ~k_Name, ~p_k,
  "1",      "A",    0.5,   "IJP",   .66,
  "1",      "B",    0.5,   "IJP",   .25,
  "1",      "C",    NA,    "IJP",   .09,
  "2",      "A",    0.25,  "IJP",   .66,
  "2",      "B",    0.25,  "IJP",   .25,
  "2",      "C",    0.25,  "IJP",   .09,
  "2",      "D",    0.25,  "IJP",   NA
)

解决方案 #

直接使用full_join无法实现需求，因为它仅匹配两个表中同时存在的连接键组合。需先构建所有需要的k_Name+xi_Name+Class组合，再关联两个表的数据：

方法一：构建全组合后关联 #

library(dplyr)
library(tidyr)

# 生成所有k_Name、xi_Name、Class的可能组合
all_combinations <- expand_grid(
  k_Name = unique(c(k$k_Name, xi$k_Name)),
  xi_Name = unique(xi$xi_Name),
  Class = unique(c(k$Class, xi$Class))
)

# 关联两个表的数据并整理列顺序
result <- all_combinations %>%
  left_join(k, by = c("k_Name", "Class")) %>%
  left_join(xi, by = c("k_Name", "xi_Name", "Class")) %>%
  select(xi_Name, Class, p_xi, k_Name, p_k)

方法二：交叉连接核心组合后关联 #

library(dplyr)
library(tidyr)

# 先交叉xi的k_Name+xi_Name组合与k的k_Name+Class组合
result <- crossing(
  xi %>% select(k_Name, xi_Name),
  k %>% select(k_Name, Class, p_k)
) %>%
  left_join(xi, by = c("k_Name", "xi_Name", "Class")) %>%
  select(xi_Name, Class, p_xi, k_Name, p_k)

两种方法均可生成符合期望的输出：既包含xi_Name="1"+Class="C"的行，也保留xi中独有的Class="D"行（对应p_k为NA）。

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