# r - Match two matrices by columns

I have two matrices that looks like this:

A

``ColumnA ColumnB ColumnC ColumnDA D N FDF N A SP F K lqw AS O Wn H QD E``

B

`` ColumnA ColumnB ColumnC ColumnDA DH K FSnp N A SAS Q O lmP n N WEAS PV QNQ 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.

Best

Desired output:

C

``ColumnA ColumnB ColumnC ColumnDA N N SP AS A Qn K EO``

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
``````

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