Getting NA on a merge that incorporates a second pair of columns in an either/or type of match

user_14672819 0
Viewed 59

I would like to merge two data frames matching on either/or of two pairs of columns. The idea is to give me a merge if a either Date1 or Date2 in the first dataframe match Date3 in the second data frame. First I duplicated the Date3 into Date3A and Date3B, because matching 2 to 1 gave an error. And while it runs here in the sample data, it only appended NA to each row in my real data.

IDx<-c("A","B","C","D")
Date1<-c("04-04-2004","05-05-2005","06-06-2006", "07-07-2007")
Date2<-c("04-04-2005","05-05-2005","06-06-2007","05-05-2005")
dfx<-data.frame(IDx,Date1,Date2)
# dfx looks like
#  IDx      Date1      Date2
#1   A 04-04-2004 04-04-2005
#2   B 05-05-2005 05-05-2005
#3   C 06-06-2006 06-06-2007
#4   D 07-07-2007 05-05-2005

IDy<-c("E","F","G","H")
Date3A<-c("04-04-2008","05-05-2005","06-06-2006","07-07-2007")
Date3B<-c("04-04-2008","05-05-2005","06-06-2006","07-07-2007")
dfy<-data.frame(IDy, Date3A,Date3B)
#note that Date3A and Date3B are exact matches.
# dfy
#  IDy     Date3A     Date3B
#1   E 04-04-2008 04-04-2008
#2   F 05-05-2005 05-05-2005
#3   G 06-06-2006 06-06-2006
#4   H 07-07-2007 07-07-2007

Here is what I tried

NewDF<-merge(x=dfx, y=dfy, by.x =c("Date1", "Date2"), by.y=c("Date3A","Date3B"), all.x=TRUE)

#NewDF looks like 
#       Date1 IDx      Date2  IDy     Date3B
#1 04-04-2004   A 04-04-2005 <NA>       <NA>
#2 05-05-2005   B 05-05-2005    F 05-05-2005
#3 06-06-2006   C 06-06-2007    G 06-06-2006
#4 07-07-2007   D 05-05-2005    H 07-07-2007

I should be getting a fifth row to match D with either H or F.
Here it runs, but when I tried it with my real data, it only appended NA to each row. So, for now, I am running them twice, like so

NewDF<-merge(x=dfx, y=dfy, by.x =c("Date1"), by.y=c("Date3A"), all.x=TRUE)
NewDF2<-merge(x=NewDF, y=dfy, by.x =c("Date2"), by.y=c("Date3A"), all.x=TRUE)

Now I get the right answer but the matches appear twice in each row (wide) and I want the matches to be one to each row (long).

#> NewDF2
#       Date2      Date1 IDx IDy.x   Date3B.x IDy.y   Date3B.y
#1 04-04-2005 04-04-2004   A  <NA>       <NA>  <NA>       <NA>
#2 05-05-2005 05-05-2005   B     F 05-05-2005     F 05-05-2005
#3 05-05-2005 07-07-2007   D     H 07-07-2007     F 05-05-2005
#4 06-06-2007 06-06-2006   C     G 06-06-2006  <NA>       <NA>

Can anyone help figure out what is going wrong? There are no NA's in the Date3 column(s).

r merge
1 Answers

If I understand correctly, the OP wants to find for all rows of dfx those IDy in dfy where Date3 matches either Date1 or Date2. The result is expected in long format.

Here are 3 different approaches:

  1. Reshape and join
  2. Two separate joins and a union
  3. Recursive joins and reshape (similar to OP's approach)

1. Reshape and join

dfx is reshaped from wide to long format so that there is only one date column which is used to join with dfy. Finally, this intermediate result is joined again with the dfx (to include IDx == A which does not have any matching date in dfy in the final result):

library(data.table)
melt(setDT(dfx), id.var = "IDx", value.name = "Date3")[
  setDT(dfy), on = "Date3", nomatch = NULL][
    , unique(.SD), .SDcols = !"variable"][
      dfx, on = "IDx"]

   IDx      Date3  IDy      Date1      Date2
1:   A       <NA> <NA> 04-04-2004 04-04-2005
2:   B 05-05-2005    F 05-05-2005 05-05-2005
3:   C 06-06-2006    G 06-06-2006 06-06-2007
4:   D 05-05-2005    F 07-07-2007 05-05-2005
5:   D 07-07-2007    H 07-07-2007 05-05-2005

2. Two separate joins and a union

One join is on the first date column Date1, the other join is on the second date column Date2. Both intermediate results are then combined rowwise (a union in SQL) and reordered:

library(data.table)
rbind(
  setDT(dfy)[setDT(dfx), on = "Date3==Date1", .(IDx, Date1, Date2, IDy, Date3)], 
  setDT(dfy)[setDT(dfx), on = "Date3==Date2", .(IDx, Date1, Date2, IDy, Date3)]
)[order(IDx)]

   IDx      Date1      Date2  IDy      Date3
1:   A 04-04-2004 04-04-2005 <NA> 04-04-2004
2:   A 04-04-2004 04-04-2005 <NA> 04-04-2005
3:   B 05-05-2005 05-05-2005    F 05-05-2005
4:   B 05-05-2005 05-05-2005    F 05-05-2005
5:   C 06-06-2006 06-06-2007    G 06-06-2006
6:   C 06-06-2006 06-06-2007 <NA> 06-06-2007
7:   D 07-07-2007 05-05-2005    H 07-07-2007
8:   D 07-07-2007 05-05-2005    F 05-05-2005

3. Recursive joins and reshape

dfx is right-joined with dfy to find matching dates in Date2. The intermediate result is again right-joined with dfy to find matching dates in Date1. Finally, the result is reshaped from wide to long format.

This is similar to OP's approach where the result of the first merge() is merged again.

library(data.table)
setDT(dfy)[dfy[setDT(dfx), on = "Date3==Date2"], on = "Date3==Date1"][
  , melt(.SD, id.vars = "IDx", measure.vars = patterns(IDy ="IDy$", Date = "Date3$"))][
    order(IDx), !"variable"]

   IDx  IDy       Date
1:   A <NA> 04-04-2004
2:   A <NA> 04-04-2005
3:   B    F 05-05-2005
4:   B    F 05-05-2005
5:   C    G 06-06-2006
6:   C <NA> 06-06-2007
7:   D    H 07-07-2007
8:   D    F 05-05-2005

For each row in dfx the result has two rows.

  • In case IDx == A there is no matching date in dfy which is indicated by the two NA
  • In case IDx == B there is one matching date in dfy but appears duplicated because Date1 and Date2 contain the same date value
  • In case IDx == C there is one matching date in dfy. For Date2 there is no match which is indicated by NA
  • In case IDx == D there are two matching dates in dfy

Data

IDx <- c("A", "B", "C", "D")
Date1 <- c("04-04-2004", "05-05-2005", "06-06-2006", "07-07-2007")
Date2 <- c("04-04-2005", "05-05-2005", "06-06-2007", "05-05-2005")
dfx <- data.frame(IDx, Date1, Date2)

IDy <- c("E", "F", "G", "H")
Date3 <- c("04-04-2008", "05-05-2005", "06-06-2006", "07-07-2007")
dfy <- data.frame(IDy, Date3)
user_38170040
Related