CODEWITHSUNDEEP
r
NewUsr_stat
NewUsr_stat

Reputation: 2587

Match two matrices by columns

I have two matrices that looks like this:

A

ColumnA   ColumnB   ColumnC    ColumnD     
   A         D         N          F            
   DF        N         A          S    
   P         F         K          l      
   qw        AS        O          W    
             n         H          Q      
                       D          E

B 

  ColumnA   ColumnB   ColumnC    ColumnD     
     A         DH        K          FS            
    np         N         A           S    
    AS         Q         O          lm      
     P         n         N          WE    
               AS        PV          Q      
                         NQ          E

I would like a third matrix C containing the common elements column by column between the two matrices. I tried to do this work by using R but it seems impossible since the two matrices are too large: ~5000 rows and 1500 columns. The two matrices have the same number of columns ad the same column names.

Can anyone help me please?

Best

Desired output: C

ColumnA   ColumnB   ColumnC    ColumnD     
   A         N         N          S            
   P         AS        A          Q    
             n         K          E      
                       O

Upvotes: 0

Views: 315

Answers (2)

akrun
akrun

Reputation: 887991

You could try

library(stringi)
#Here `A` and `B` are "data.frames"
m1 <- stri_list2matrix( Map(`intersect`, A, B), fill='')
C <- setNames(as.data.frame(m1, stringsAsFactors=FALSE), colnames(A))
C
#   ColumnA ColumnB ColumnC ColumnD
# 1       A       N       N       S
# 2       P      AS       A       Q
# 3               n       K       E
# 4                       O

Or

lst <- lapply(rbind(A,B), function(x) x[duplicated(x)& x!=''] )
m2 <- sapply(lst, `length<-`, max(sapply(lst, length)))
m2[is.na(m2)] <- ''
as.data.frame(m2, stringsAsFactors=FALSE)
#  ColumnA ColumnB ColumnC ColumnD
#1       A       N       K       S
#2       P       n       A       Q
#3              AS       O       E
#4                       N

Upvotes: 2

isebarn
isebarn

Reputation: 3950

Do you know how to use sqlite?

In sqlite you could try something like

SELECT DISTINCT newtable
FROM A 
WHERE newtable Not IN (SELECT DISTINCT newtable FROM B)

it shoulnt be too much hassle to create a .db file

Note: if you're running linux you have sqlite or sqlite3 installed already

Upvotes: 0

Related Questions