如何有条件地合并R中的两个数据框（公共列，条件）

我已经尝试这样做了大约 2-3 天，但仍然找不到答案。我想做的是我有两个数据框 x,y （下面给出了它们的示例）

X
     Response.No Tab.No Survey.Date AC.Name Mandal.Name Village.Name
1         9530      1  2015-05-26      NA          NA           NA
2         6702      1  2015-05-30      NA          NA           NA
3        26744      1  2015-05-31      NA          NA           NA
4         8925      1  2015-06-03      NA          NA           NA
5        20242      1  2015-06-04      NA          NA           NA
6        21316      1  2015-06-04      NA          NA           NA
7        28056      1  2015-06-04      NA          NA           NA
8        12661      1  2015-06-05      NA          NA           NA
9        17187      1  2015-06-05      NA          NA           NA
10       28795      1  2015-06-05      NA          NA           NA

Y
     AC.Name   Mandal.Name      Village.Name Tab.No Survey.Start.Date Survey.End.Date
1  Nandigama Chanderlapadu        Punnavalli      1        2015-05-23      2015-05-27
2  Nandigama Chanderlapadu        Kasarabada      1        2015-05-30      2015-06-07
3  Nandigama Chanderlapadu     Kodavatikallu      1        2015-06-09      2015-06-28
4  Nandigama Chanderlapadu        Thurlapadu      1        2015-06-29      2015-07-13
5  Nandigama Chanderlapadu     Chanderlapadu      1        2015-07-14      2015-07-25
6  Nandigama Chanderlapadu            Popuru      2        2015-05-23      2015-05-27
7  Nandigama Chanderlapadu        Kandrapadu      2        2015-05-30      2015-06-08
8  Nandigama Chanderlapadu Vibhareethalapadu      3        2015-05-30      2015-06-04
9  Nandigama Chanderlapadu             Eturu      3        2015-06-10      2015-06-23
10 Nandigama Chanderlapadu      Bobbillapadu      3        2015-06-26      2015-07-03

即我想通过 Tab.No 列匹配 x 和 y，但还要确保 x$Survey.Date 位于 y$Survey.Start.Date 和 y$Survey.End.Date 之间。如果这两个条件都不满足，则该行必须具有 N.A 值。我尝试并搜索了 google stackoverflow 和 R-Studio 帮助，但无法获得所需的结果。

Z
     Response.No Tab.No Survey.Date AC.Name      Mandal.Name   Village.Name
1         9530      1  2015-05-26      Nandigama Chanderlapadu Punnavalli
2         6702      1  2015-05-30      Nandigama Chanderlapadu Kasarabada
3        26744      1  2015-05-31      Nandigama Chanderlapadu Kasarabada
4         8925      1  2015-06-03      Nandigama Chanderlapadu Kasarabada
5        20242      1  2015-06-04      Nandigama Chanderlapadu Kasarabada
6        21316      1  2015-06-04      Nandigama Chanderlapadu Kasarabada
7        28056      1  2015-06-04      Nandigama Chanderlapadu Kasarabada
8        12661      1  2015-06-05      Nandigama Chanderlapadu Kasarabada
9        17187      1  2015-06-05      Nandigama Chanderlapadu Kasarabada
10       28795      1  2015-06-05      Nandigama Chanderlapadu Kasarabada

我已经检查过了： 1.如何根据匹配列和范围内的两个条件合并R中的两个数据框？ 2. 带有开始/结束窗口的滚动连接 3. R 中的条件合并/替换

我一直试图使用 merge()、cbind() 和 match() 来解决这个问题，但没有成功。 我可以仅使用序列号进行合并，但不使用日期条件。

感谢您的帮助

Data:

x <- data.table(Tab.No = c(1,1,2), Survey.Date = as.Date(c("2015-5-26","2015-6-15","2015-4-03")))
y <- data.table(AC.Name = c("abc","xyz","qwe"),
                Mandal.Name = c("def","pqr","rty"),
                Village.Name = c("def","pqr","rty"),
                Tab.No = c(1,1,2), 
                Survey.Start.Date = as.Date(c("2015-5-30","2015-5-01","2015-5-05")), 
                Survey.End.Date = as.Date(c("2015-6-30","2015-5-29","2015-6-30")))

我首先将 X 合并到 Y 上，测试条件，然后将左连接返回到 x 数组上：

使用数据表：

merge(x,merge(y,x,by = "Tab.No")[Survey.Date >= Survey.Start.Date & Survey.Date <= Survey.End.Date, list(Tab.No,AC.Name,Mandal.Name,Village.Name,Survey.Date)], by = c("Tab.No","Survey.Date"), all.x = T)
   serial       date add1 add2
1:      1 2015-05-26  xyz  pqr
2:      1 2015-06-15  abc  def
3:      2 2015-04-03   NA   NA

如果您不熟悉 data.table，可以更清楚地分解一下：

z <- merge(y,x,by = "Tab.No")[Survey.Date >= Survey.Start.Date & Survey.Date <= Survey.End.Date, list(Tab.No,AC.Name,Mandal.Name,Village.Name,Survey.Date)]
merge(x,z, by = c("Tab.No","Survey.Date"), all.x = T)

请注意，我忽略了NAx 框架中的列，它们在开始时是不必要的