r - 连接以提取表 Y 中大于表 X 中每个项目 ID 的日期的最小日期

Question

我找到了很多类似问题的答案，但没有找到这个问题的答案。我觉得这应该很容易，但这让我的大脑受伤。

模式是我有一张租金表和一张返回表。每件元素都可以多次出租和归还。对我来说幸运的是，它们存储在不同的表中。

表 X(租金)

ID    Type   Date Rented
0001  A       2017-02-01
0001  A       2017-07-01
0001  A       2017-09-01
0002  B       2017-01-01
0002  B       2017-05-01

表 Y(返回)

ID    Date Returned
0001  2017-05-01
0001  2017-08-01
0002  2017-04-01

我想最终得到:

ID    Type    Date Rented    Date Returned
0001  A       2017-02-01     2017-05-01
0001  A       2017-07-01     2017-08-01
0001  A       2017-09-01     NA
0002  B       2017-01-01     2017-04-01
0002  B       2017-05-01     NA

因此，对于每个 ID 和租赁日期，我正在查找返回表中大于该租赁日期的最小值。

我将使用 R 中的输出，因此如果有更简单的方法在 R/dplyr 而不是 SQL 中执行此操作，我会洗耳恭听......

Answer 1

df1 = read.table(text = "
ID    Type   DateRented
0001  A       2017-02-01
0001  A       2017-07-01
0001  A       2017-09-01
0002  B       2017-01-01
0002  B       2017-05-01
", header=T)

df2 = read.table(text = "
ID    DateRented
0001  2017-05-01
0001  2017-08-01
0002  2017-04-01
", header=T)

library(dplyr)
library(lubridate)

# update to a date format and order by ID and date
# (not needed if you have already a date format and ascending order)
df1 = df1 %>% mutate(DateRented = ydm(DateRented)) %>% arrange(ID, DateRented)
df2 = df2 %>% mutate(DateRented = ydm(DateRented)) %>% arrange(ID, DateRented)

# add row ids for each ID to your datasets
df1 = df1 %>% group_by(ID) %>% mutate(row_id = row_number()) %>% ungroup()
df2 = df2 %>% group_by(ID) %>% mutate(row_id = row_number()) %>% ungroup()

# join datasets and remove row id column
left_join(df1, df2, by=c("ID","row_id")) %>% select(-row_id)

# # A tibble: 5 x 4
#    ID Type   DateRented.x DateRented.y
#   <int> <fctr> <date>       <date>     
# 1     1 A      2017-02-01   2017-05-01  
# 2     1 A      2017-07-01   2017-08-01  
# 3     1 A      2017-09-01   NA          
# 4     2 B      2017-01-01   2017-04-01  
# 5     2 B      2017-05-01   NA