Conditional merge tables
I have 2 tables:
Time X1
8/1/2013 56
9/1/2013 14
10/1/2013 8
11/1/2013 4
12/1/2013 78
Time X2
8/1/2013 42
9/1/2013 44
10/1/2013 2
11/1/2013 75
12/1/2013 36
How can I combine these two tables in one table using "Time", but with one condition: the month from the first table must match the following month form, the second one like September from the first table must match October from the second table.
Thank!
+3
source to share
1 answer
This is the perfect job for a data.table
sliding joint
library(data.table)
setkey(setDT(dat1)[, Time := as.Date(Time, format = "%m/%d/%Y")], Time)
setkey(setDT(dat2)[, Time := as.Date(Time, format = "%m/%d/%Y") - 1], Time)
dat2[dat1, roll = -Inf]
# Time X2 X1
# 1: 2013-08-01 44 56
# 2: 2013-09-01 2 14
# 3: 2013-10-01 75 8
# 4: 2013-11-01 36 4
# 5: 2013-12-01 NA 78
Edit: If you don't want an unbeatable string then use nomatch = 0
dat2[dat1, roll = -Inf, nomatch = 0]
# Time X2 X1
# 1: 2013-08-01 44 56
# 2: 2013-09-01 2 14
# 3: 2013-10-01 75 8
# 4: 2013-11-01 36 4
+4
source to share