Comparing elements of data.frames in R

Question

I have 2 data frames

> a1
v1  v2  v3
ABCA1   --> GIF
ACTA1   --| CSNK2A1
ACTN4   --| HDAC7
ACTN4   --> RARA

> a2
v1  v2  v3
ACTA1   --| CSNK2A1
ABCD2   --| HDAC7
ABCA1   --| GIF
ACTN4   --> XYZ1

I want output where a1$element1 == a2$element1 & a1$element3 == a2$element3 & a1$element2 != a2$element2. So, the outcome will be:

> a3
ABCA1   --> GIF

As all elements in this row fulfills our all 3 conditions. It is row 1 in a1 and row 3 in a2. As you can see (row 1 of a1 and row 3 of a2) ABCA1 == ABCA1 & --> != --| & GIF == GIF

Pierre L · Accepted Answer · 2015-10-07T12:30:08.727

3

You can try merge:

m2 <- merge(a1, a2, by=c("v1", "v3"))
m2[m2[,3] != m2[,4],][c(1,3,2)]
#     v1 v2.x  v3
#1 ABCA1  --> GIF

We first merge on columns 1 and 3. That will connect the cases of the first two conditions. For the last condition, test for the cases where the column symbols are different.

Also stringsAsFactors should be set to FALSE when creating the data frames.

With data.table, you can merge using its shallow copying efficiency.

setDT(a1)[a2, on = c("v1", "v3")][v2 != i.v2]
      v1  v2  v3 i.v2
1: ABCA1 --> GIF  --|

edited Oct 07 '15 at 12:30

answered Oct 06 '15 at 15:37

Pierre L

28,203
6
47
69

Thanks.. It did the job. – user3253470 Oct 06 '15 at 15:45
Maybe also `library(data.table) ; setDT(a1)[a2, on = c("v1", "v3"), nomatch = 0L][v2 != i.v2]` just for fun – David Arenburg Oct 06 '15 at 15:46
@DavidArenburg Can you help me with this question: [http://stackoverflow.com/questions/35484595/data-frame-merge-and-selection-of-values-which-are-common-in-2-data-frames] – user3253470 Feb 18 '16 at 16:15

LyzandeR · Answer 2 · 2015-10-06T15:58:09.473

2

This seems to work for me:

a1[ intersect(which((paste0(a1$v1, a1$v3) %in% paste0(a2$v1, a2$v3))),
              which(a1$v2 != a2$v2)) ,]

#     v1  v2  v3
#1 ABCA1 --> GIF

First, find which rows in a1 and a2 have common columns 1 and 3. Then find which rows do not have the second column in common. Then get the intersect of the two and use this to subset a1.

Data:

a1 <- read.table(header=T,text='v1  v2  v3
ABCA1   --> GIF
ACTA1   --| CSNK2A1
ACTN4   --| HDAC7
ACTN4   --> RARA', stringsAsFactors=F)

a2 <- read.table(header=T,text='v1  v2  v3
ACTA1   --| CSNK2A1
ABCD2   --| HDAC7
ABCA1   --| GIF
ACTN4   --> XYZ1', stringsAsFactors=F)

edited Oct 06 '15 at 15:58

answered Oct 06 '15 at 15:38

LyzandeR

37,047
12
77
87

Can you help me with this question: [http://stackoverflow.com/questions/35484595/data-frame-merge-and-selection-of-values-which-are-common-in-2-data-frames] – user3253470 Feb 18 '16 at 16:14

Comparing elements of data.frames in R

2 Answers2

Linked