如何整理DataFrame使相同值固定在同一列（AT1/AT2列）

我有一个包含数千行、5列的DataFrame，希望对每行的AT1和AT2列进行调整，让相同值始终处于同一列。比如第10和11行，两个值在AT1和AT2列之间互换了，需要让所有记录的相同值都固定在同一列。

示例数据 #

mydata <- structure(list(record_id = 1:19, Loc = c("ROTT", "ROTT", "ROTT", 
"ROTT", "ROTT", "ROTT", "ROTT", "ROTT", "ROTT", "ROTT", "ROTT", 
"ROTT", "ROTT", "ROTT", "ROTT", "ROTT", "ROTT", "ROTT", "ROTT"
), repeat. = c(1L, 2L, 3L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L), AT1 = c("m.657+5G>A_NA", "m.657+5G>A_NA", 
"m.657+5G>A_NA", "m.1501C>T_NA", "m.1501C>T_NA", "m.1501C>T_NA", 
"m.687C>G_NA", "m.687C>G_NA", "m.687C>G_NA", "m.2512G>A_NA", 
"m.1538G>A_NA", "m.2840-2A>G_NA", "m.2840-2A>G_NA", "m.2840-2A>G_NA", 
"m.2111T>C_NA", "m.2111T>C_NA", "m.1549-2A>G_NA", "m.1549-2A>G_NA", 
"m.1549-2A>G_NA"), AT2 = c("<NA>", "<NA>", "<NA>", "<NA>", "<NA>", 
"<NA>", "<NA>", "<NA>", "<NA>", "m.1538G>A_NA", "m.2512G>A_NA", 
"<NA>", "<NA>", "<NA>", "<NA>", "<NA>", "<NA>", "<NA>", "<NA>"
)), class = "data.frame", row.names = c(NA, -19L))

尝试过的无效代码 #

library(data.table)
cols <- c("AT1", "AT2")
setDT(mydata)[, (cols) := lapply(.SD, sort), by = .(record_id, Loc), .SDcols = cols][]

问题原因 #

这段代码的错误在于：lapply(.SD, sort)是对AT1和AT2每列单独排序，而非对每行的两个值组成的向量排序。按record_id, Loc分组后，每一行是一个组，但lapply会分别处理AT1列和AT2列，无法实现每行内部两个值的顺序统一。

解决方案 #

方法1：data.table 实现 #

对每行的AT1和AT2值组成的向量进行排序，重新分配到AT1和AT2列，确保相同值的位置全局统一：

library(data.table)
setDT(mydata)

# 按每行分组，对当前行的AT1、AT2值排序后重新赋值
mydata[, c("AT1", "AT2") := {
  sorted_vals <- sort(c(AT1, AT2), na.last = TRUE)
  list(sorted_vals[1], sorted_vals[2])
}, by = record_id]

方法2：tidyverse 实现 #

用rowwise()按行处理，对每行的两个值排序后替换原列：

library(tidyverse)

mydata <- mydata %>%
  rowwise() %>%
  mutate(
    sorted_vals = sort(c(AT1, AT2), na.last = TRUE),
    AT1 = sorted_vals[1],
    AT2 = sorted_vals[2]
  ) %>%
  select(-sorted_vals) %>%
  ungroup()

两种方法都会将每行的AT1/AT2值按字典序排序，确保相同值始终出现在同一列（比如示例中的m.1538G>A_NA会固定在AT1，m.2512G>A_NA固定在AT2）。

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