如何用R的dplyr包匹配生产数据框组合与参考数据框并赋值

解决方案代码 #

library(tidyverse)

# 初始化数据
first = c("Alpha","Alpha","Alpha","Beta","Beta","Corn","Hat")
second = c("Beta","Corn","Desk","Corn","Desk","Desk","Ian")
val = c(10,20,30,40,50,60,70)
df1 = tibble(first,second,val)

date = c(as.Date("2022-01-01"),as.Date("2022-02-01"))
var = c("Alpha","Beta,Corn,Fanta,Epsilon,George,Hat,Ian")
df2  = tibble(date,var)

# 预处理参考数据框df1：生成标准化的组合字符串（排序后拼接，保证顺序不影响匹配）
df1_processed <- df1 %>%
  rowwise() %>%
  mutate(comb = str_c(sort(c(first, second)), collapse = ",")) %>%
  ungroup() %>%
  select(comb, val) %>%
  distinct()

# 处理生产线数据框df2，生成结果
result <- df2 %>%
  # 拆分var列为变量列表
  mutate(vars = str_split(var, ",")) %>%
  rowwise() %>%
  # 生成所有两两组合的标准化字符串，同时标记是否有匹配的组合
  mutate(
    pairs = list(combn(vars, 2, FUN = function(x) str_c(sort(x), collapse = ","))),
    has_match = any(pairs %in% df1_processed$comb)
  ) %>%
  ungroup() %>%
  # 展开所有两两组合
  unnest_longer(pairs) %>%
  # 筛选出在df1中存在的组合
  filter(pairs %in% df1_processed$comb) %>%
  # 匹配对应的val值
  left_join(df1_processed, by = c("pairs" = "comb")) %>%
  rename(comb = pairs) %>%
  select(date, comb, val) %>%
  # 补充没有匹配组合的日期：添加单独变量行，val设为0
  bind_rows(
    df2 %>%
      mutate(vars = str_split(var, ",")) %>%
      rowwise() %>%
      mutate(has_match = any(combn(vars, 2, FUN = function(x) str_c(sort(x), collapse = ",")) %in% df1_processed$comb)) %>%
      ungroup() %>%
      filter(!has_match) %>%
      unnest_longer(vars) %>%
      rename(comb = vars) %>%
      mutate(val = 0) %>%
      select(date, comb, val)
  ) %>%
  # 按日期排序
  arrange(date)

# 查看结果
result

关键步骤说明 #

1. 预处理df1：将每个first和second的组合按字母排序后用逗号拼接，生成标准化的comb列，这样不管df2中变量组合的顺序如何，都能准确匹配（比如Corn,Beta也能匹配到Beta,Corn对应的val）。
2. 拆分df2的变量：用str_split把每个日期的拼接变量拆分为列表形式，方便后续生成组合。
3. 生成两两组合：用combn生成所有可能的两两变量组合，同样转换为标准化字符串，然后筛选出在df1中存在的组合，匹配对应的val值。
4. 处理无匹配的情况：对于没有任何匹配组合的日期（比如2022-01-01），将单独变量展开为行，val设为0，补充到结果中。

运行结果 #

执行代码后得到的result数据框与需求完全一致：

# A tibble: 3 × 3
  date       comb      val
  <date>     <chr>   <dbl>
1 2022-01-01 Alpha       0
2 2022-02-01 Beta,Corn   40
3 2022-02-01 Hat,Ian     70

内容的提问来源于stack exchange，提问作者Homer Jay Simpson