使用R将一行与文件中的所有其他行进行比较 [英] Compare one row to all other rows in a file using R

问题描述

我有一个如下的文件：

P1 A,B,C    
P2 B,C,D,F    
P3 C,D,E,F  

到所有其他行，得到如下所示的交叉元素的计数：

and I need to compare each row to all other rows to get a count of intersecting elements like below:

P1 P2 2    
P1 P3 1    
P2 P3 3   

谢谢

S

Thank you,
S

推荐答案

阅读示例数据。

txt <- "P1 A,B,C
        P2 B,C,D,F
        P3 C,D,E,F"
tc <- textConnection(txt)
dat <- read.table(tc,as.is=TRUE)
close(tc)

转换为长格式并使用自连接与聚合函数。

Transform to long format and use self join with aggregating function.

dat_split <- strsplit(dat$V2,",")
dat_long <- do.call(rbind,lapply(seq_along(dat_split),
            function(x) data.frame(id=x,x=dat_split[[x]], stringsAsFactors=FALSE)))

result <- sqldf("SELECT t1.id AS id1,t2.id AS id2,count(t1.x) AS N 
                 FROM dat_long AS t1  INNER JOIN dat_long AS t2 
                 WHERE (t2.id>t1.id) AND (t1.x=t2.x) GROUP BY t1.id,t2.id")

结果

> result
  id1 id2 N
1   1   2 2
2   1   3 1
3   2   3 3

这篇关于使用R将一行与文件中的所有其他行进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！