r语言 - 基于另一个数据框中的列重新排序数据框中的行，但具有非唯一值

Question

我有2个数据帧

第一个数据帧df1有1246行，看起来像这样:

gene1         gene2          gene3
AAAB.P1           1.23          2.28          -2.85
AABC.P1           4.06         -0.59          -2.42
ABCD.P1           3.26          2.19          -3.01
ABCD.R1           1.23          0.15          -2.30
DBCA.P1           1.67         -0.51          -3.24

第二个数据帧df2也有1246行，看起来像这样:

id      primary_diagnosis
1   ABCD    carcinoma
2   ABCD    carcinoma
3   AAAB    AS
4   DBCA    carcinoma
5   EFGH    other
6   LMNO    AS

我需要根据id列重新排列第二个df，使其与第一个df的顺序相同。我试着用

df2<-df2[order(match(df2$id, substr(rownames(df1), 1, 4))), ]

但是它只给了我21行处于正确位置的数据。我认为问题是df2包含一些重复项。例如，ABCD同时具有ABCD和ABCD。P1和ABCD。R1在df1中，但是在df2中只有一个元素。它们的排列顺序并不重要因为P1和R1都与癌相匹配。我如何重新排序df2以匹配df1已经在的顺序?

Answer 1

您可以使用df2[rank(rownames(df1), ties.method = "random"), ]

ties.method = "random"参数确保在存在平局的情况下每个秩是唯一的。

Answer 2

我们可以这样使用简单的arrange:

library(dplyr)
df2 %>% 
arrange(id, df1$id)

id primary_diagnosis
3 AAAB                AS
1 ABCD         carcinoma
2 ABCD         carcinoma
4 DBCA         carcinoma
5 EFGH             other
6 LMNO                AS

#df1
df1 <- structure(list(gene1 = c(1.23, 4.06, 3.26, 1.23, 1.67), gene2 = c(2.28, 
-0.59, 2.19, 0.15, -0.51), gene3 = c(-2.85, -2.42, -3.01, -2.3, 
-3.24)), class = "data.frame", row.names = c("AAAB.P1", "AABC.P1", 
"ABCD.P1", "ABCD.R1", "DBCA.P1"))
#df2
df2 <- structure(list(id = c("ABCD", "ABCD", "AAAB", "DBCA", "EFGH", 
"LMNO"), primary_diagnosis = c("carcinoma", "carcinoma", "AS", 
"carcinoma", "other", "AS")), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6"))

Answer 3

这个怎么样:

df1 <- read.table(text = "id gene1 gene2 gene3
AAAB.P1 1.23 2.28 -2.85
AABC.P1 4.06 -0.59 -2.42
ABCD.P1 3.26 2.19 -3.01
ABCD.R1 1.23 0.15 -2.30
DBCA.P1 1.67 -0.51 -3.24", sep=" ", header=TRUE)
rownames(df1) <- df1$id
df1 <- df1[,-1]

df2 <- read.table(text="id primary_diagnosis
ABCD carcinoma
ABCD carcinoma
AAAB AS
DBCA carcinoma
EFGH other
LMNO AS", sep=" ", header=TRUE)

df2[match(substr(rownames(df1), 1, 4), df2$id), ]
#>       id primary_diagnosis
#> 3   AAAB                AS
#> NA  <NA>              <NA>
#> 1   ABCD         carcinoma
#> 1.1 ABCD         carcinoma
#> 4   DBCA         carcinoma

^{在2022-05-23由reprex包(v2.0.1)创建}