在R中获取每行Top3最高值对应列名，求tidyverse实现方案

需求说明 #

• 处理数据集，为每行提取A:N列（原数据第5至9列）对应的三个列名：
  • 最高值对应的列名（命名为row_max）
  • 第二大非零值对应的列名（命名为row_min）
  • 第三高值对应的列名

现有尝试 #

已实现row_max的提取：

tmp$row_max = colnames(tmp[,5:9])[apply(tmp[,5:9],1,which.max)]

但使用which.min会返回包含0的列，不符合需求；仅能通过排序获取第二高数值，无法关联对应列名：

apply(tmp[,5:9], 1, FUN = function(x) sort(x)[4])

当前数据集输出：

contig  pos ref cov  A  T  C  G N row_max 
1: NW_017095466.1  130   N  41 39  2  0  0 0       A 
2: NW_017095466.1  166   N  48  0 46  2  0 0       T 
3: NW_017095466.1  427   N  52 50  0  0  2 0       A 
4: NW_017095466.1 1736   N  54 44  0 10  0 0       A 
5: NW_017095466.1 1918   N  46  0  0  3 43 0       G 
6: NW_017095466.1 2688   N  52  5  0 47  0 0       C 

数据集dput #

dput(tmp)
structure(list(contig = c("NW_017095466.1", "NW_017095466.1", 
"NW_017095466.1", "NW_017095466.1", "NW_017095466.1", "NW_017095466.1"
), pos = c(130L, 166L, 427L, 1736L, 1918L, 2688L), ref = c("N", 
"N", "N", "N", "N", "N"), cov = c(41L, 48L, 52L, 54L, 46L, 52L
), A = c(39L, 0L, 50L, 44L, 0L, 5L), T = c(2L, 46L, 0L, 0L, 0L, 
0L), C = c(0L, 2L, 0L, 10L, 3L, 47L), G = c(0L, 0L, 2L, 0L, 43L, 
0L), N = c(0L, 0L, 0L, 0L, 0L, 0L), row_max = c("A", "T", "A", 
"A", "G", "C"), row_min = c("C", "A", "T", "T", "A", "T")), row.names = c(NA, 
-6L), class = c("data.table", "data.frame"), .internal.selfref = <pointer: 0x7f94c30168e0>)

tidyverse解决方案 #

结合pivot_longer、group_by、arrange和pivot_wider实现，兼顾第二大非零值的需求：

方案1：保留0值但优先非零排序 #

library(tidyverse)

tmp_processed <- tmp %>%
  # 将A:N列转为长表，保留行标识列
  pivot_longer(cols = A:N, names_to = "base", values_to = "count") %>%
  group_by(contig, pos) %>%
  # 排序规则：非零值优先，再按数值降序
  arrange(desc(count != 0), desc(count), .by_group = TRUE) %>%
  # 生成行内排名
  mutate(rank = row_number()) %>%
  # 提取Top3记录
  filter(rank %in% 1:3) %>%
  # 转回宽表并命名列
  pivot_wider(names_from = rank, values_from = base, names_prefix = "row_") %>%
  rename(row_min = row_2, row_third = row_3) %>%
  # 合并回原数据集
  right_join(tmp, by = c("contig", "pos")) %>%
  # 调整列顺序与原数据对齐
  select(contig, pos, ref, cov, A, T, C, G, N, row_max, row_min, row_third)

print(tmp_processed)

方案2：严格排除0值（第二值必须非零，无符合条件则为NA） #

library(tidyverse)

tmp_processed <- tmp %>%
  pivot_longer(cols = A:N, names_to = "base", values_to = "count") %>%
  group_by(contig, pos) %>%
  filter(count > 0) %>% # 过滤所有0值
  arrange(desc(count), .by_group = TRUE) %>%
  mutate(rank = row_number()) %>%
  filter(rank %in% 1:3) %>%
  pivot_wider(names_from = rank, values_from = base, names_prefix = "row_") %>%
  rename(row_min = row_2, row_third = row_3) %>%
  right_join(tmp, by = c("contig", "pos")) %>%
  select(contig, pos, ref, cov, A, T, C, G, N, row_max, row_min, row_third)

print(tmp_processed)

内容的提问来源于stack exchange，提问作者R-MASHup