在R中按V1分组查找公共值并输出最左侧匹配值的方法

需求说明 #

按数据框的V1列分组，在每组的剩余列中找出所有行共有的非空公共值，选取这些值在原始列顺序中出现位置最靠左的那个值，添加为新列V7；若没有公共值则填充"No match"。

例如V1="Red"的组中，公共值为Vienna和Oslo，需选取列顺序更早的Vienna作为结果。

输入数据 #

input = structure(list(V1 = c("Red", "Red", "Red", "Red", "Green", 
"Green"), V2 = c("Vienna", "London", "Budapest", "Vienna", "Oslo ", 
"Kyiv"), V3 = c("Lisbon", "Milan", "Vienna", "Oslo", "Rome", 
"Madrid"), V4 = c("Rome", "Barcelona", "Prague", "", "", "Dublin"
), V5 = c("Oslo", "Vienna", "Oslo", "", "", ""), V6 = c("", "Oslo", 
"", "", "", "")), row.names = c(NA, 6L), class = "data.frame")

解决方案（Tidyverse版本） #

使用dplyr分组结合purrr的函数式操作实现，逻辑清晰易维护：

library(dplyr)
library(purrr)

# 定义处理单组数据的函数
get_leftmost_common <- function(group_data) {
  # 收集每行的非空唯一值
  row_values <- group_data %>% 
    select(-V1) %>% 
    pmap(function(...) {
      vals <- c(...)
      vals[vals != ""] %>% unique()
    })
  
  # 提取所有行的公共值
  common_vals <- reduce(row_values, intersect)
  
  if (length(common_vals) == 0) {
    return("No match")
  }
  
  # 按原始列顺序，找到每个公共值最早出现的位置
  col_order <- colnames(group_data)[-1]
  val_positions <- map_int(common_vals, ~{
    min(which(map_lgl(group_data[, col_order], ~.x %in% .)))
  })
  
  # 返回位置最靠前的公共值
  common_vals[which.min(val_positions)]
}

# 分组计算并添加新列
output <- input %>% 
  group_by(V1) %>% 
  mutate(V7 = get_leftmost_common(cur_data())) %>% 
  ungroup()

# 查看结果
output

解决方案（Base R版本） #

无需额外依赖包，纯基础R实现：

# 初始化新列
input$V7 <- ""

# 按V1分组处理
groups <- split(input, input$V1)

for (g in names(groups)) {
  group <- groups[[g]]
  # 提取每行的非空唯一值集合
  row_sets <- apply(group[, -1], 1, function(x) {
    unique(x[x != ""])
  })
  
  # 计算所有行的公共值
  common_vals <- Reduce(intersect, row_sets)
  
  if (length(common_vals) == 0) {
    res <- "No match"
  } else {
    # 找到每个公共值最早出现的列索引
    col_order <- colnames(group)[-1]
    val_pos <- sapply(common_vals, function(val) {
      min(which(sapply(group[, col_order], function(col) val %in% col)))
    })
    res <- common_vals[which.min(val_pos)]
  }
  
  # 为该组赋值新列
  input$V7[input$V1 == g] <- res
}

# 查看结果
input

结果验证 #

两种方法均可得到符合预期的输出：

#   V1      V2    V3        V4    V5    V6       V7
# 1 Red  Vienna Lisbon      Rome  Oslo          Vienna
# 2 Red  London  Milan Barcelona Vienna  Oslo Vienna
# 3 Red Budapest Vienna    Prague  Oslo          Vienna
# 4 Red  Vienna  Oslo                  Vienna
# 5 Green Oslo   Rome                 No match
# 6 Green   Kyiv Madrid    Dublin                 No match

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