如何用dplyr在R中匹配数据框字符值并执行对应计算？

完整代码 #

library(tidyverse)

# 构建原始数据框
date = c(rep(as.Date("2022-01-01"),4),rep(as.Date("2022-02-01"),4),rep(as.Date("2022-03-01"),3))
A = seq(1,11,1)
B = seq(12,22,1)
C = seq(23,33,1)
symbol = c(rep("A",4),rep("B,C",4),rep("D,A",3))
df1 = tibble(date,A,B,C,symbol)

first = c("A","A","B","B","B")
second = c("D","F","A","C","D")
value = c(2,3,4,3,5)
df2 = tibble(first,second,value)

# 生成双向匹配的映射表
df2_matched = df2 %>%
  bind_rows(df2 %>% rename(first = second, second = first)) %>%
  mutate(pair = str_c(first, ",", second))

# 处理df1并计算目标值
result = df1 %>%
  group_by(symbol) %>%
  mutate(row_num = row_number()) %>%
  mutate(
    s1 = str_split(symbol, ",", simplify = TRUE)[,1],
    s2 = str_split(symbol, ",", simplify = TRUE)[,2]
  ) %>%
  mutate(pair = str_c(s1, ",", s2)) %>%
  left_join(df2_matched, by = "pair") %>%
  group_by(symbol) %>%
  summarise(
    calc = if_else(
      any(!is.na(value)),
      min(pick(A,B,C)[which(row_num == first(na.omit(value))),]),
      max(pick(A,B,C))
    )
  )

# 查看结果
result

代码解释 #

1. 处理匹配映射表（df2）：

   • 将df2中的first和second列互换后与原表合并，生成支持双向匹配的映射关系（比如A,D和D,A都能被识别为同一匹配对）。
   • 生成pair列作为后续匹配的键。

2. 处理数据框df1：

   • 按symbol分组，给每组内的行添加row_num编号，用于后续根据df2的value值定位目标行。
   • 拆分symbol列得到单个字符，生成对应的pair列，与处理后的df2进行左连接，获取匹配的value值。

3. 计算目标值calc：

   • 再次按symbol分组，判断当前组是否存在匹配的value：
     • 若存在匹配，取对应value编号行的A、B、C列最小值。
     • 若不存在匹配，取当前组A、B、C列的最大值。

运行结果 #

# A tibble: 3 × 2
  symbol calc
  <chr> <dbl>
1 A        26
2 B,C       7
3 D,A      10

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