r语言 - 拆分列，绑定 dfs 并保留大多数重复值

Question

我有一个看起来像这样的df：

> df
Names   Symbol  GeneID  Description    Paths                Colors
IL-1    CP1     3553    Receptor       Path1|Path2|Path5    Green|Blue|Pink
IL-6    CFT5    3569    Receptor       Path3|Path1|Path2    Red|Green|Blue
TNF     DFR4    7124    Receptor       Path4|Path3|Path1    Yellow|Red|Green
CCL2    FGTZ    6347    Receptor       Path4|Path5|Path2    Yellow|Pink|Blue
IL-1    SED     3552    Receptor       Path6|Path5|Path3    Purple|Pink|Red
PAI1    SWA     5054    Receptor       Path1                Green 
IL-12   SSS     3593    Receptor       Path1|Path2          Green|Blue 
IL-8    SDE     3576    Receptor       Path1|Path3|Path5    Green|Red|Pink
CTGF    SDFR    1490    Receptor       Path4|Path5|Path1    Yellow|Pink|Green
TGF     FDGT    7046    Receptor       Path5|Path3          Pink|Red

我想拆分名为Paths和Colors的cols，然后计算每个Path#在名为Paths的列上出现的次数。所以我可以获得如下所示的df，其中Path1出现 7 次，其对应的颜色为Green.Path5出现 5 次，这就是为什么以其相应的颜色(pink(显示在第二位的原因，依此类推。

> df2
Paths Colors
Path1 Green
Path5 Pink
Path3 Red
Path2 Blue
Path4 Yellow
Path6 Purple

我尝试用这段代码做到这一点：

Paths <- data.frame(do.call('rbind', strsplit(as.character(df$paths), '|', fixed = TRUE)))
df2 <- table(unlist(Paths))
df2 <- data.frame(sort(df2, decreasing = T))

但这只适用于一条线，而不是将Paths和Colors列分开。

有什么建议吗？优选使用base R

Answer 1

你的问题有两个部分。首先，我们需要将数据重塑为整洁的格式：每条路径一行，具有相应的颜色。

df %>% tidyr::separate_rows(Paths, Colors, sep = "\|") 
Names Symbol GeneID Description Paths Colors
1   IL-1    CP1   3553    Receptor Path1  Green
2   IL-1    CP1   3553    Receptor Path2   Blue
3   IL-1    CP1   3553    Receptor Path5   Pink
...

现在我们需要计算最受欢迎的路径：

counts <- df %>% count(Paths) %>% arrange(desc(n))
# A tibble: 6 x 2
Paths     n
<chr> <int>
1 Path1     7
2 Path5     6
3 Path3     5
4 Path2     4
5 Path4     3
6 Path6     1

最后，我们可以连接相应的颜色并删除n列。这是一种方法。

counts %>% inner_join(df) %>% distinct(Paths, Colors)
Joining, by = "Paths"
# A tibble: 6 x 2
Paths Colors
<chr> <chr> 
1 Path1 Green 
2 Path5 Pink  
3 Path3 Red   
4 Path2 Blue  
5 Path4 Yellow
6 Path6 Purple

Answer 2

在base R中，答案是：

## This part of the script is to separate the Paths and Colors columns
Paths <- data.frame(do.call('rbind', strsplit(as.character(df$Paths), '|', fixed = TRUE)))
Colors <- data.frame(do.call('rbind', strsplit(as.character(df$Colors), '|', fixed = TRUE)))
## Here Paths and Colors are pasted together and separated by underscore
Pathways = data.frame(matrix(NA, ncol = ncol(Paths), nrow = nrow(Colors)))
for (i in 1:ncol(Paths)){
Pathways[,i] = paste(Paths[,i], Colors[,i], sep = " ")
}
## Then, the number of times that each Path appears is counted.
occurrences <- table(unlist(Pathways))
occurrences <- data.frame(sort(occurrences, decreasing = T))
occurrences