I have one dataframe (X) that looks like

ID1  ID2
1     2
2     3
3     4

And another (Y) that looks like

ID  GROUP
1    5
2    5     
3    6
4    6

I am trying to compare and compute the ID's in dataframe X that are in the same row, are they in the same group listed in dataframe Y?

For example:

ID1  ID2  SAME.GROUP
1     2    YES
2     3    NO
3     4    YES

I have done this before matching one column but can't figure out how to do both. Thanks in advance

CodePudding user response：

You can use match:

transform(X, SAME.GROUP = Y$GROUP[match(X$ID1, Y$ID)] == Y$GROUP[match(X$ID2, Y$ID)])

  ID1 ID2 SAME.GROUP
1   1   2       TRUE
2   2   3      FALSE
3   3   4       TRUE

data

X <- read.table(header = T, text = "ID1  ID2
1     2
2     3
3     4")


Y <- read.table(header = T, text = "ID  GROUP
1    5
2    5     
3    6
4    6
")

CodePudding user response：

We could use a join

library(data.table)
setDT(X)[dcast(setDT(Y), GROUP ~ paste0("ID", rowid(GROUP)),
     value.var = "ID"), SAME.GROUP := "YES", on = .(ID1, ID2)]
X[is.na(SAME.GROUP), SAME.GROUP := "NO"]

-output

> X
     ID1   ID2 SAME.GROUP
   <int> <int>     <char>
1:     1     2        YES
2:     2     3         NO
3:     3     4        YES