如何用mutate()和ifelse()基于多数据框匹配生成合并注释列

需要基于datatossedtest$StationID与datasummary$StationID的匹配关系关联两个数据框，将datatossedtest中匹配行的指定列信息合并到datasummary的单个comment列中，同一StationID的多条匹配信息需用AND连接。

测试数据：

datatossedtest <- tibble(`WID` = c("10A", "11A", "11A", "12A", "10A"), 
                         `StationID` = c("A", "B", "B", "AB", "C"), 
                         `Issue` = c("Bad", "Not Good", "Bad", "Meh", "Meh"), 
                         'n' = c(7, 3, 6, 5, 4))

datasummary <- tibble(`WID` = c("10A", "11A","12A", "10A", "13A"), 
                      `StationID` = c("A", "B","AB","C","D"))

原尝试代码仅返回单条匹配结果，无法合并同一StationID的多条记录：

datasummary <- datasummary %>% 
  mutate(comment = ifelse(datasummary$StationID %in% datatossedtest$StationID, 
                          glue("{datatossedtest$n} sample(s) were thrown out because of {datatossedtest$Issue}"),
                          "Lookin good"))

期望输出：

> datasummary
# A tibble: 5 × 3
  WID   StationID comment                                         
  <chr> <chr>     <chr>                                           
1 10A   A         7 sample(s) were thrown out because of Bad     
2 11A   B         3 sample(s) were thrown out because of Not Good AND 6 sample(s) were thrown out because of Bad
3 12A   AB        6 sample(s) were thrown out because of Bad     
4 10A   C         5 sample(s) were thrown out because of Meh     
5 13A   D         Lookin good  

原代码问题在于直接用glue引用整列，只会取对应位置的单条值，无法聚合同一StationID的多条记录。以下两种方法可实现需求：

方法一：先聚合再关联（高效推荐） #

先对datatossedtest按StationID聚合生成合并后的comment文本，再关联到datasummary：

library(dplyr)
library(glue)
library(stringr)

# 聚合生成各StationID对应的合并comment
comment_df <- datatossedtest %>%
  group_by(StationID) %>%
  summarise(comment = str_c(glue("{n} sample(s) were thrown out because of {Issue}"), collapse = " AND "))

# 关联到datasummary，填充无匹配的情况
datasummary_final <- datasummary %>%
  left_join(comment_df, by = "StationID") %>%
  mutate(comment = ifelse(is.na(comment), "Lookin good", comment))

方法二：逐行处理（逻辑直观） #

用purrr::map_chr逐行筛选并合并对应StationID的记录：

library(dplyr)
library(purrr)
library(glue)
library(stringr)

datasummary_final <- datasummary %>%
  mutate(comment = map_chr(StationID, function(id) {
    matched_rows <- filter(datatossedtest, StationID == id)
    if (nrow(matched_rows) == 0) {
      "Lookin good"
    } else {
      str_c(glue_data(matched_rows, "{n} sample(s) were thrown out because of {Issue}"), collapse = " AND ")
    }
  }))

说明 #

• 方法一先聚合再关联，数据处理效率更高，适合大数据量场景；
• 方法二逐行处理逻辑更直观，适合小数据量或需要灵活调整单条记录格式的场景；
• 两种方法均使用str_c实现文本拼接，glue/glue_data用于格式化单条记录的文本内容。

内容的提问来源于stack exchange，提问作者nps-randy