基于R语言dplyr对相似前缀列执行行级聚合操作

针对你提到的按前缀列组聚合的需求，分两种场景给出具体实现：

构造示例数据 #

先模拟你描述的多前缀列数据：

library(dplyr)
library(tidyr)
library(stringr)

df <- tibble(
  id = 1:5,
  p001_i1 = c(1, NA, 3, NA, 5),
  p001_i2 = c(NA, 2, NA, 4, NA),
  p501_i1 = c(NA, 10, NA, 40, NA),
  p501_i2 = c(20, NA, 30, NA, 50),
  p501_i3 = c(15, 25, NA, 35, 45)
)

场景1：取每行非缺失值的均值 #

通过长表转换-分组聚合-宽表还原的流程实现：

df_mean <- df %>%
  # 将同前缀列转成长格式，拆分前缀和优先级标记
  pivot_longer(
    cols = starts_with(c("p001", "p501")),
    names_to = c("prefix", "priority"),
    names_sep = "_",
    values_drop_na = FALSE
  ) %>%
  # 按id和前缀分组，计算非缺失值均值
  group_by(id, prefix) %>%
  summarise(agg_value = mean(value, na.rm = TRUE), .groups = "drop") %>%
  # 转回宽格式，匹配原id
  pivot_wider(names_from = prefix, values_from = agg_value) %>%
  left_join(df %>% select(id), ., by = "id")

场景2：按i3>i2>i1优先级取第一个非缺失值 #

有两种实现方式：

方式1：长表转换+排序取首值 #

df_priority <- df %>%
  pivot_longer(
    cols = starts_with(c("p001", "p501")),
    names_to = c("prefix", "priority"),
    names_sep = "_",
    values_drop_na = FALSE
  ) %>%
  # 按优先级从高到低排序
  mutate(priority = factor(priority, levels = c("i3", "i2", "i1"), ordered = TRUE)) %>%
  arrange(id, prefix, priority) %>%
  # 取每组第一个非缺失值
  group_by(id, prefix) %>%
  summarise(agg_value = first(na.omit(value)), .groups = "drop") %>%
  pivot_wider(names_from = prefix, values_from = agg_value) %>%
  left_join(df %>% select(id), ., by = "id")

方式2：用coalesce直接按优先级组合列（更简洁） #

利用coalesce返回第一个非NA值的特性，直接针对每个前缀组按优先级顺序组合列：

prefixes <- c("p001", "p501")

df_priority_simple <- df %>%
  mutate(
    across(
      all_of(prefixes),
      # 按i3>i2>i1顺序取非缺失值
      ~ coalesce(!!!syms(str_c(.x, c("_i3", "_i2", "_i1")))),
      .names = "{.col}"
    )
  ) %>%
  # 保留id和聚合后的列
  select(id, all_of(prefixes))

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