CODEWITHSUNDEEP
rdatemergedata.tableaggregate
user45415631
user45415631

Reputation: 175

Conditional merging tables

I have 2tables:

    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 merge those 2 table in one table grouping by "Time" but with one condition: the month from first table must match with the following month form the second - like September from first table should match with October from second table.

Thank you!

Upvotes: 3

Views: 110

Answers (1)

David Arenburg
David Arenburg

Reputation: 92282

This is a perfect job for data.table rolling join

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 the unmatched row, 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

Upvotes: 4

Related Questions