如何用group_by和summarize为配对行添加mean_blast高值标记

我有如下数据框：

> dput(for_stack)
structure(list(id = c("20230420-01", "20230420-02", "2023042110-01", 
"2023042110-02", "2023042112-01", "2023042112-02", "2023042114-01", 
"2023042114-02", "2023042214-01", "2023042214-02"), pair_id = c(20230420L, 
20230420L, 2023042110L, 2023042110L, 2023042112L, 2023042112L, 
2023042114L, 2023042114L, 2023042214L, 2023042214L), mean_blast = c(3.82352941176471, 
4.46153846153846, 1.71428571428571, 1.0625, 4.8125, 4, 3.5, 1.25, 
4.9375, 4.5)), row.names = c(NA, 10L), class = "data.frame")

每个pair_id对应两行数据，我需要为每行添加higher列，标记该行是否为对应pair_id中mean_blast值更高的行，最终效果如下：

> dput(for_stack)
structure(list(id = c("20230420-01", "20230420-02", "2023042110-01", 
"2023042110-02", "2023042112-01", "2023042112-02", "2023042114-01", 
"2023042114-02", "2023042214-01", "2023042214-02"), pair_id = c(20230420L, 
20230420L, 2023042110L, 2023042110L, 2023042112L, 2023042112L, 
2023042114L, 2023042114L, 2023042214L, 2023042214L), mean_blast = c(3.82352941176471, 
4.46153846153846, 1.71428571428571, 1.0625, 4.8125, 4, 3.5, 1.25, 
4.9375, 4.5), higher = c(FALSE, TRUE, TRUE, FALSE, TRUE, FALSE, 
TRUE, FALSE, TRUE, FALSE)), row.names = c(NA, 10L), class = "data.frame")

输出表格：

id    pair_id mean_blast higher
1    20230420-01   20230420   3.823529  FALSE
2    20230420-02   20230420   4.461538   TRUE
3  2023042110-01 2023042110   1.714286   TRUE
4  2023042110-02 2023042110   1.062500  FALSE
5  2023042112-01 2023042112   4.812500   TRUE
6  2023042112-02 2023042112   4.000000  FALSE
7  2023042114-01 2023042114   3.500000   TRUE
8  2023042114-02 2023042114   1.250000  FALSE
9  2023042214-01 2023042214   4.937500   TRUE
10 2023042214-02 2023042214   4.500000  FALSE

我原本想通过group_by和summarize实现，但没找到具体方法，求解决方案。

解决方案 #

使用dplyr包（推荐） #

你提到的group_by是正确方向，但不需要用summarize（它会聚合分组数据，丢失行信息），应该用mutate来为每组的每行添加标记：

library(dplyr)

for_stack <- for_stack %>%
  group_by(pair_id) %>%
  mutate(higher = mean_blast == max(mean_blast)) %>%
  ungroup()

• group_by(pair_id)：按pair_id分组
• mutate(higher = mean_blast == max(mean_blast))：在每组内，判断当前行的mean_blast是否等于该组的最大值，返回逻辑值（TRUE/FALSE）
• ungroup()：取消分组，恢复普通数据框结构

使用base R实现 #

如果不想用dplyr，也可以用base R的ave函数：

for_stack$higher <- with(for_stack, ave(mean_blast, pair_id, FUN = function(x) x == max(x)))

• ave函数会按pair_id分组处理mean_blast，对每组应用匿名函数判断是否为最大值，返回与原数据长度一致的逻辑向量

验证结果 #

运行上述代码后，查看数据框即可得到你想要的higher列，与示例输出一致。

内容的提问来源于stack exchange，提问作者grace.cutler