如何对DataFrame按KO_DEFINITION分组并提取A/B/C列唯一值拼接

我有一个包含A、B、C、KO_DEFINITION列的DataFrame（共23000+行），数据结构如下：

structure(list(A = c("KEGG 09100 PATHWAY Metabolism", "KEGG 09100 PATHWAY Metabolism", "KEGG 09100 PATHWAY Metabolism", "KEGG 09100 PATHWAY Metabolism", "KEGG 09100 PATHWAY Metabolism", "KEGG 09100 PATHWAY Metabolism", "KEGG 09100 PATHWAY Metabolism", "KEGG 09100 PATHWAY Metabolism"), B = c("KEGG 09101 PATHWAY Carbohydrate metabolism", "KEGG 09101 PATHWAY Carbohydrate metabolism", "KEGG 09103 PATHWAY Lipid metabolism", "KEGG 09105 PATHWAY Amino acid metabolism", "KEGG 09108 PATHWAY Metabolism of cofactors and vitamins", "KEGG 09111 PATHWAY Xenobiotics biodegradation and metabolism", "KEGG 09111 PATHWAY Xenobiotics biodegradation and metabolism", "KEGG 09111 PATHWAY Xenobiotics biodegradation and metabolism"), C = c("KEGG 00010 PATHWAY Glycolysis / Gluconeogenesis", "KEGG 00620 PATHWAY Pyruvate metabolism", "KEGG 00071 PATHWAY Fatty acid degradation", "KEGG 00350 PATHWAY Tyrosine metabolism", "KEGG 00830 PATHWAY Retinol metabolism", "KEGG 00625 PATHWAY Chloroalkane and chloroalkene degradation", "KEGG 00626 PATHWAY Naphthalene degradation", "KEGG 00980 PATHWAY Metabolism of xenobiotics by cytochrome P450"), KO_DEFINITION = c("KO K00001 DEFINITION E1.1.1.1, adh; alcohol dehydrogenase [EC:1.1.1.1]", "KO K00001 DEFINITION E1.1.1.1, adh; alcohol dehydrogenase [EC:1.1.1.1]", "KO K00001 DEFINITION E1.1.1.1, adh; alcohol dehydrogenase [EC:1.1.1.1]", "KO K00001 DEFINITION E1.1.1.1, adh; alcohol dehydrogenase [EC:1.1.1.1]", "KO K00001 DEFINITION E1.1.1.1, adh; alcohol dehydrogenase [EC:1.1.1.1]", "KO K00001 DEFINITION E1.1.1.1, adh; alcohol dehydrogenase [EC:1.1.1.1]", "KO K00001 DEFINITION E1.1.1.1, adh; alcohol dehydrogenase [EC:1.1.1.1]", "KO K00001 DEFINITION E1.1.1.1, adh; alcohol dehydrogenase [EC:1.1.1.1]"), row.names = c(NA, -8L), class = c("tbl_df", "tbl", "data.frame"))

需要生成一个新的DataFrame，其中每行对应一个唯一的KO_DEFINITION，新增列包含该分组下A、B、C列的所有唯一值拼接成的字符串，示例输出如下：

structure(list(KO_DEFINITION = c("KO K00001 DEFINITION E1.1.1.1, adh; alcohol dehydrogenase [EC:1.1.1.1]", "KO K00002 DEFINITION AKR1A1, adh; alcohol dehydrogenase (NADP+) [EC:1.1.1.2]"), new_string = c("KEGG 09100 PATHWAY Metabolism KEGG 09101 PATHWAY Carbohydrate metabolism KEGG 09103 PATHWAY Lipid metabolism KEGG 09105 PATHWAY Amino acid metabolism KEGG 09108 PATHWAY Metabolism of cofactors and vitamins KEGG 09111 PATHWAY Xenobiotics biodegradation and metabolism KEGG 00010 PATHWAY Glycolysis / Gluconeogenesis KEGG 00620 PATHWAY Pyruvate metabolism KEGG 00071 PATHWAY Fatty acid degradation KEGG 00350 PATHWAY Tyrosine metabolism KEGG 00830 PATHWAY Retinol metabolism KEGG 00625 PATHWAY Chloroalkane and chloroalkene degradation KEGG 00626 PATHWAY Naphthalene degradation KEGG 00980 PATHWAY Metabolism of xenobiotics by cytochrome P450 KEGG 00982 PATHWAY Drug metabolism - cytochrome P450", "KEGG 09100 PATHWAY Metabolism KEGG 09160 PATHWAY Human Diseases KEGG 09180 PATHWAY Brite Hierarchies KEGG 09101 PATHWAY Carbohydrate metabolism KEGG 09103 PATHWAY Lipid metabolism KEGG 09111 PATHWAY Xenobiotics biodegradation and metabolism KEGG 09161 PATHWAY Cancer: overview KEGG 09183 PATHWAY Protein families: signaling and cellular processes KEGG 00010 PATHWAY Glycolysis / Gluconeogenesis KEGG 00040 PATHWAY Pentose and glucuronate interconversions KEGG 00053 PATHWAY Ascorbate and aldarate metabolism KEGG 00620 PATHWAY Pyruvate metabolism KEGG 00561 PATHWAY Glycerolipid metabolism KEGG 00930 PATHWAY Caprolactam degradation KEGG 05208 PATHWAY Chemical carcinogenesis - reactive oxygen species KEGG 04147 PATHWAY Exosome [BR:ko04147]"), row.names = 1:2, class = "data.frame"))

目前思路是合并A、B、C列文本，用dplyr的group_by按KO_DEFINITION分组后去重，想寻求更简洁高效的实现方法。

方法一：使用dplyr + tidyr（简洁易读） #

利用pivot_longer把A、B、C列转为长格式，按KO_DEFINITION分组后提取唯一值再拼接：

library(dplyr)
library(tidyr)

result_df <- df %>%
  pivot_longer(cols = c(A, B, C), names_to = NULL, values_to = "pathway") %>%
  group_by(KO_DEFINITION) %>%
  summarise(new_string = paste(unique(pathway), collapse = " ")) %>%
  ungroup()

步骤说明：

• pivot_longer将A、B、C三列合并为单列pathway，消除列维度差异
• 按KO_DEFINITION分组，对每组的pathway取唯一值后用空格拼接成字符串
• ungroup取消分组，得到最终的DataFrame

方法二：使用data.table（高效处理大数据量） #

针对23000+行的数据，data.table的内存效率和运算速度更具优势：

library(data.table)

setDT(df)
result_dt <- df[, .(new_string = paste(unique(c(A, B, C)), collapse = " ")), by = KO_DEFINITION]

步骤说明：

• setDT将DataFrame转为data.table格式
• 按KO_DEFINITION分组，每组内合并A、B、C列的所有值，取唯一后拼接成字符串
• 直接得到分组聚合后的结果，无需额外转换

这两种方法都比先合并列再分组去重的思路更简洁，且能保证结果中只保留每组A、B、C的唯一值拼接字符串。

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