基于ID列合并多行：R语言DataFrame行合并问题求助

问题背景 #

现有如下分组后的DataFrame：

df<- structure(list(X18.digit.contact.id = c("0034y00002kIZ3rAAG", 
"0034y00002kIZ3rAAG", "0034y00002kIZ3rAAG", "0034y00002PpX11AAF", 
"0034y00002PpX11AAF", "0034y00002PpX11AAF", "0034y00002jHjYKAA0", 
"0034y00002jHjYKAA0", "0034y00002jHjYKAA0"), `Fitness Goal` = c(2L, 
NA, NA, -1L, NA, NA, NA, 1L, NA), `Nutrition/Hydration Goal` = c(NA, 
NA, NA, NA, 0L, NA, 2L, NA, NA), `Lifestyle Goal` = c(NA, NA, 
2L, NA, NA, 0L, NA, NA, 1L)), class = c("grouped_df", "tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -9L), groups = structure(list(
    X18.digit.contact.id = c("0034y00002PpX11AAF", "0034y00002jHjYKAA0", 
    "0034y00002kIZ3rAAG"), .rows = structure(list(4:6, 7:9, 1:3), ptype = integer(0), class = c("vctrs_list_of", 
    "vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -3L), .drop = TRUE))

需求是基于X18.digit.contact.id合并行，最终得到3行（对应3个唯一ID）、4列（ID+三个目标列）的DataFrame。尝试过summarize和lapply方法未成功，用pivot_longer+pivot_wider时出现警告Values from 'value' are not uniquely identified; output will contain list-cols，导致后续操作受限。

解决方法 #

方法一：dplyr 分组聚合 #

利用每个分组中单一目标列仅存在一个非NA值的特点，通过na.omit提取有效值：

library(dplyr)

df_merged <- df %>%
  group_by(X18.digit.contact.id) %>%
  summarize(
    `Fitness Goal` = first(na.omit(`Fitness Goal`)),
    `Nutrition/Hydration Goal` = first(na.omit(`Nutrition/Hydration Goal`)),
    `Lifestyle Goal` = first(na.omit(`Lifestyle Goal`))
  )

也可以用max(..., na.rm = TRUE)简化批量处理，因为每个分组目标列只有一个非NA整数，极值就是唯一有效值：

df_merged <- df %>%
  group_by(X18.digit.contact.id) %>%
  summarize(
    across(c(`Fitness Goal`, `Nutrition/Hydration Goal`, `Lifestyle Goal`), 
           ~ max(., na.rm = TRUE))
  )

方法二：data.table 高效处理 #

用data.table按ID分组后直接提取各列非NA值，自动压缩为单行：

library(data.table)

setDT(df)
df_merged <- df[, lapply(.SD, function(x) x[!is.na(x)]), 
                by = X18.digit.contact.id]

方法三：修复pivot方法的警告 #

在pivot_wider中指定values_fn确保每个组合取唯一值，消除列表列警告：

library(tidyr)

df_merged <- df %>%
  pivot_longer(cols = -X18.digit.contact.id, 
               names_to = "goal_type", values_to = "goal_value") %>%
  drop_na(goal_value) %>%
  pivot_wider(names_from = goal_type, values_from = goal_value,
              values_fn = first)

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