R语言含缺失值的数据聚合：保持团队顺序与关联团队名称

核心问题分析 #

• 问题1：team3中无数据的成员Bill未在输出中保留，仅显示Mary
• 问题2：team1成员顺序从输入的"Mary + Frank"变为输出的"Frank + Mary"，无法和原团队对应

实现目标输出1：保留原始团队成员顺序并显示全部成员 #

直接复用teams中的原始成员字符串，基于原始成员列表计算总和（无数据成员贡献0）：

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

input = structure(list(V1 = c("Team_2022", "Team_2022", "Team_2022"), V2 = c("Frank", "Mary", "John"), V3 = c("Sydney", "Sydney", "Sydney"), V4 = c(55, 76, 14)), row.names = c(NA, -3L), class = c("data.table", "data.frame"))

teams = structure(list(V1 = c("team1", "team2", "team3"), V2 = c("Mary + Frank","Mary + John", "Mary + Bill")), class = "data.frame", row.names = c(NA, -3L))

result_v1 = function(data, team_row) {
  original_team_str = team_row[2]
  members = str_split(team_row[2], " \\+ ", simplify = TRUE)[1,]
  
  # 匹配成员数据，无数据的成员补0
  member_values = map_dbl(members, ~ifelse(.x %in% data$V2, data$V4[data$V2 == .x], 0))
  total = sum(member_values)
  
  tibble(
    V1 = unique(data$V1),
    V2 = original_team_str,
    V3 = unique(data$V3),
    V4 = total
  )
}

output1 = map_dfr(1:nrow(teams), ~result_v1(input, teams[.x, ]))
output1

输出与目标输出1完全一致，保留原始成员顺序，team3完整显示"Mary + Bill"。

实现目标输出2：关联团队名称并保留全部成员 #

将团队名称加入输出，同时保留原始成员字符串，计算总和包含无数据成员的0贡献：

result_v2 = function(data, team_row) {
  team_name = team_row[1]
  original_team_str = team_row[2]
  members = str_split(team_row[2], " \\+ ", simplify = TRUE)[1,]
  
  member_values = map_dbl(members, ~ifelse(.x %in% data$V2, data$V4[data$V2 == .x], 0))
  total = sum(member_values)
  
  tibble(
    V1 = team_name,
    V2 = unique(data$V1),
    V3 = original_team_str,
    V4 = unique(data$V3),
    V5 = total
  )
}

output2 = map_dfr(1:nrow(teams), ~result_v2(input, teams[.x, ]))
output2

输出与目标输出2一致，明确关联团队名称，所有成员完整显示。

实现目标输出3：关联团队名称，仅显示有数据的成员 #

仅保留input中存在的成员，总和仅计算有数据成员的数值：

result_v3 = function(data, team_row) {
  team_name = team_row[1]
  members = str_split(team_row[2], " \\+ ", simplify = TRUE)[1,]
  
  # 筛选存在数据的成员
  existing_members = members[members %in% data$V2]
  existing_str = paste(existing_members, collapse = " + ")
  
  total = sum(data$V4[data$V2 %in% existing_members])
  
  tibble(
    V1 = team_name,
    V2 = unique(data$V1),
    V3 = existing_str,
    V4 = unique(data$V3),
    V5 = total
  )
}

output3 = map_dfr(1:nrow(teams), ~result_v3(input, teams[.x, ]))
output3

注：原目标输出3中team1的V5值69为笔误，实际计算应为55+76=131，上述代码输出正确结果。

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