如何在R中用Cross Join实现类似PROC SQL的关联结果？

我需要在R中编写代码，实现类似PROC SQL里Cross Join的效果，来得到指定的父-子匹配输出，但试了几次都没成功，求帮忙。

原始数据 #

data <- data.frame(
  all_names = c("PARENT1", "CHILD1", "CHILD2", "CHILD3", "PARENT2", "PARENT3", "CHILD4", "CHILD5", "CHILD6", "PARENT7", "CHILD7", "CHILD8")
)

数据预览：

all_names
1    PARENT1
2     CHILD1
3     CHILD2
4     CHILD3
5    PARENT2
6    PARENT3
7     CHILD4
8     CHILD5
9     CHILD6
10   PARENT7
11    CHILD7
12    CHILD8

预期输出 #

expected_output <- data.frame(
  parent= c("PARENT1", "PARENT1", "PARENT1", "PARENT2", "PARENT2", "PARENT2", "PARENT3", "PARENT3", "PARENT3", "PARENT7", "PARENT7"),
  child=c("CHILD1", "CHILD2", "CHILD3", "CHILD4", "CHILD5", "CHILD6", "CHILD4", "CHILD5", "CHILD6", "CHILD7", "CHILD8")
)

输出预览：

parent  child
1  PARENT1 CHILD1
2  PARENT1 CHILD2
3  PARENT1 CHILD3
4  PARENT2 CHILD4
5  PARENT2 CHILD5
6  PARENT2 CHILD6
7  PARENT3 CHILD4
8  PARENT3 CHILD5
9  PARENT3 CHILD6
10 PARENT7 CHILD7
11 PARENT7 CHILD8

我尝试的代码（未成功） #

data %>% mutate(seq=row_number())

child <- data %>% filter(stringr::str_detect(all_names,'CHILD'))
parent <- data %>% filter(stringr::str_detect(all_names,'PARENT'))

parent %>% cross_join(child) %>% filter(seq.x <= seq.y)

解决方案 #

核心思路是先给每个父节点标记分组，把后续的子节点归到最近的父节点分组中，再按分组关联父和子。用dplyr和tidyr就能实现：

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

# 1. 给数据添加分组标记，父节点作为分组起点，向下填充分组ID
data_processed <- data %>%
  mutate(
    is_parent = str_detect(all_names, "PARENT"),
    group_id = cumsum(is_parent)
  ) %>%
  fill(group_id, .direction = "down")

# 2. 拆分父、子数据，保留分组ID
parent_df <- data_processed %>%
  filter(is_parent) %>%
  select(group_id, parent = all_names)

child_df <- data_processed %>%
  filter(!is_parent) %>%
  select(group_id, child = all_names)

# 3. 按分组ID关联，得到结果
result <- parent_df %>%
  inner_join(child_df, by = "group_id") %>%
  select(parent, child)

print(result)

运行后输出和预期完全一致：

parent  child
1  PARENT1 CHILD1
2  PARENT1 CHILD2
3  PARENT1 CHILD3
4  PARENT2 CHILD4
5  PARENT2 CHILD5
6  PARENT2 CHILD6
7  PARENT3 CHILD4
8  PARENT3 CHILD5
9  PARENT3 CHILD6
10 PARENT7 CHILD7
11 PARENT7 CHILD8

问题原因说明 #

你之前的代码用cross_join后过滤seq.x <= seq.y，这种逻辑无法区分不同父节点对应的子组，会把所有父和子做无差别交叉匹配再过滤，不符合预期的分组匹配规则。我们需要先通过分组ID明确每个父对应的子范围，再进行关联。

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