Conditional Merge in Data.table #829

Activity

1. arunsrinivasan commented on Sep 26, 2014

   

   arunsrinivasan

   on Sep 26, 2014

   Member

   More actions

   Very recently, overlap joins (foverlaps function) has been implemented. It's not yet feature complete, but can do the task you've linked to quite efficiently.

   In the next release, we'll try to get it to work more seamlessly. At the moment, we can only join directly, not aggregate while joining (like data.table's x[y, j] syntax). But this is something we'll try to get for the next release.

   For the data shown here:

   require(data.table) ## 1.9.3
   setDT(df1)
   setDT(df2)[, end := datestamp]
   setkey(df2, datestamp, end)
   olaps = foverlaps(df1, df2, by.x=c("date1adj", "date2"), nomatch=0L)

   Now you can calculate sum(data) with by=list(date1adj, date2). But I agree this could be much nicer if we hook this up to [.data.table. Then we'd just have to do:

   df2[df1, j, ...]
2. arunsrinivasan

   self-assigned this

   on Sep 26, 2014
3. arunsrinivasan

   added this to the v1.9.6 milestone on Sep 26, 2014
4. arunsrinivasan

   added

   feature request

   on Sep 26, 2014
5. arunsrinivasan

   modified the milestones: v1.9.8, v1.9.6 on Oct 10, 2014
6. matthieugomez commented on Oct 29, 2014

   

   matthieugomez

   on Oct 29, 2014

   Contributor

   More actions

   Currently, I don't think functions in zoo, xts, etc allow to compute a rolling function based on all the observations in, say, the previous year, when the time index is irregular (ie when there is not a row for each period). For sure, they don't allow to do this within each group. Doing .foverlaps with .EACHI would be very useful for these cases!
7. ywhcuhk commented on Oct 29, 2014

   

   ywhcuhk

   on Oct 29, 2014

   Author

   More actions

   I very much agree on above. Looking forward to have this feature soon. This is the ONLY reason I am still sticking on SAS.
8. arunsrinivasan commented on Oct 29, 2014

   

   arunsrinivasan

   on Oct 29, 2014

   Member

   More actions

   Would be great to have a minimal reproducible example illustrating the feature that doesn't exist, that could be obtained through foverlaps(). Not familiar with either of those packages or than occasional use of some nice functions.
9. matthieugomez commented on Oct 31, 2014

   

   matthieugomez

   on Oct 31, 2014

   Contributor

   More actions

   @ywhuofu Actually after thinking more about this I think one does not need foverlap to do rolling functions. I've written an implementation using findIntervals. The nice thing is that since the function accept vectors, it can also be used within groups. Could you have a look there and tell if that's useful?
10. ywhcuhk commented on Jun 3, 2015

    

    ywhcuhk

    on Jun 3, 2015

    Author

    More actions

    @matthieugomez . I am sorry that wasn't following github constantly. I clicked your link and don't see the function you mentioned? Have you found a nicer solution to this problem? Because of this type of joins (sql inequality join), I now use Postgresql as back-ends to do this type of work, and then pips data back to R for analysis.

    If you have a solution, I am very eager to learn!

    Thank you!
11. ywhcuhk commented on Jun 3, 2015

    

    ywhcuhk

    on Jun 3, 2015

    Author

    More actions

    @arunsrinivasan, I just noticed your post, and I find overlaps() is a great new feature, but lacks some flexibility.

    1. In you example, you have to first add another column that is identical to datestamp as end because foverlaps() requires 2 columns from both data tables.
    2. You also have to modify the start column (minus 1 day) in order to have values that equal to start be included in the join.

    It would be much more flexible if one can specify inequality conditions inside the join statement.

    Above being said, thank you for the work on this new feature, and look forward to see improvements!
12. matthieugomez commented on Jun 4, 2015

    

    matthieugomez

    on Jun 4, 2015

    Contributor

    More actions

    @ywhuofu,
    I only wrote a function to roll a given function with respect to a time variable.
    For a real inequality join, foverlaps is your best best. It seems @arunsrinivasan plans to incorporate inequality join at some point directly into data.table syntax #830, which would make forverlaps easier to use for time-based joins.
13. arunsrinivasan

    modified the milestones: v2.0.0This milestone has been deleted, v1.9.8 on Nov 17, 2015
14. arunsrinivasan commented on Mar 9, 2016

    

    arunsrinivasan

    on Mar 9, 2016

    Member

    More actions

    This'll be automatically taken care of when #1452 is done.
15. arunsrinivasan

    closed this as completedon Mar 9, 2016
16. arunsrinivasan

    removed their assignment

    on Mar 9, 2016
17. arunsrinivasan

    removed this from the v1.9.8 milestone on Mar 9, 2016