在dplyr多对多连接中均匀选取行的通用解决方案

场景与问题 #

需要将表df2连接到df1，两者通过key字段关联，属于多对多关系：

• df1的key存在大量重复值
• df2的部分key对应多个source值

使用常规多对多左连接会生成所有组合，行数远超df1原数据，但需求是均匀分配df2中对应key的source值，最终结果保留df1的原始行数。

示例数据 #

library(dplyr)

# 原始数据
df1 <- data.frame(key = c("A", "B", "C", "C", "C", "B", "C"),
                  sample = c("s1", "s2", "s3", "s4", "s5", "s6","s7"))
df2 <- data.frame(key = c("A", "B", "C", "C"),
                  source = c("p1", "p2", "p3", "p4"))

常规多对多连接的结果（不符合需求） #

执行left_join(df1, df2, relationship = 'many-to-many')会得到所有组合，行数膨胀：

key sample source
1    A     s1     p1
2    B     s2     p2
3    C     s3     p3
4    C     s3     p4
5    C     s4     p3
6    C     s4     p4
7    C     s5     p3
8    C     s5     p4
9    B     s6     p2
10   C     s7     p3
11   C     s7     p4

理想结果 #

key sample source
1    A     s1     p1
2    B     s2     p2
3    C     s3     p3
4    C     s4     p4
5    C     s5     p3
6    B     s6     p2
7    C     s7     p4 

通用解决方案 #

核心思路是给df2的每个key下的source生成循环索引，同时给df1的每个key分组生成行号，通过key+索引的方式实现均匀分配：

# 处理df2：为每个key下的source生成索引序列
df2_expanded <- df2 %>%
  group_by(key) %>%
  mutate(source_idx = row_number()) %>%
  ungroup()

# 处理df1：为每个key分组生成组内行号
df1_with_idx <- df1 %>%
  group_by(key) %>%
  mutate(row_idx = row_number()) %>%
  ungroup()

# 统计每个key对应的source数量
key_source_count <- df2_expanded %>%
  count(key, name = "source_count")

# 给df1生成循环匹配的索引，对应df2的source数量
df1_matched <- df1_with_idx %>%
  left_join(key_source_count, by = "key") %>%
  mutate(match_idx = ((row_idx - 1) %% source_count) + 1) %>%
  select(-row_idx, -source_count)

# 连接得到均匀分配的结果
result <- df1_matched %>%
  left_join(df2_expanded, by = c("key", "match_idx" = "source_idx")) %>%
  select(key, sample, source)

print(result)

方案说明 #

• 无论df2中每个key对应多少个source值（1个或多个），都会自动循环分配给df1中对应key的每一行
• 严格保留df1的原始行数，实现source值的均匀覆盖，适配任意重复次数的场景

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