Support for on-the-fly columns in on

The ability to create columns in on on-the-fly, like we can in j or by, would be appreciated.

As a usage example, this would have simplified my answer to the following self-join / moving-average-type answer:

http://stackoverflow.com/a/36534824/3576984

Reproduced here:

require(data.table)
date <- as.Date(c('2012-01-01','2012-01-01','2012-01-01',
                  '2012-01-02','2012-01-02','2012-01-03',
                  '2012-01-04','2012-01-05','2012-01-05',
                  '2012-01-06','2012-01-06','2012-01-06'))
email <- c('[email protected]', '[email protected]','[email protected]',
           '[email protected]', '[email protected]','[email protected]',
           '[email protected]','[email protected]','[email protected]',
           '[email protected]','[email protected]','[email protected]')
dt <- data.table(date, email)

dt[dt[ , .(date3=date, date2 = date - 2, email)], 
   on = .(date >= date2, date<=date3), 
   allow.cartesian = TRUE
   ][ , .(count = uniqueN(email)), 
      by = .(date = date + 2)]

Would have been prettier / easier to parse as:

dt[dt, on = .(date >= date-2, date<=date), allow.cartesian = TRUE
   ][ , .(count = uniqueN(email)), by = date]

looks like a duplicate of #625

Big vote for this enhancement since it makes the syntax of data.table shorter, more intuitive and more SQL alike.

When done, update:

http://stackoverflow.com/q/37978658/559784
and
https://stackoverflow.com/q/48282827/559784

Because Arun started a list with SO posts which may be updated, I thought it I could add some more I stumbled over:

How to merge dataframes of unequal length based on time with buffer intervals in R?

Data.Table non-equi join with arithmetic operations

subset data.frame base on a time interval + or - list of dates

To update: https://stackoverflow.com/q/52901099

To update: https://stackoverflow.com/q/53559119/2204410
(or close as a duplicate with one of the links above)

To update: inner_join() with range of values for one of the keys (year)

What would the API be? The difficult part is that users may want to use the newly created on condition. The evaluation would be easier if we could get users to use a helper function if they want to access the new column in j.

dt[dt, 
   on = .(date >= list(past_date = date-2), 
          date <= date), 
   allow.cartesian = TRUE]

The alternative would be like SQL and have the eval happen twice, once in on and then have users repeat in j

dt[dt,
   on = .(date >= date-2,
          date<=date), 
   .(x.date, past_date = i.date - 2),
   allow.cartesian = TRUE]

To update https://stackoverflow.com/a/60586692/3817004

AFAIR bmerge already do shallow copies of data, so adding new columns there would be probably easiest way to achieve that. We just need a proper handling for is.call of LHS and RHS of each element of on argument using internal .parse_on, and then using that in bmerge. The most tricky part can be handling column names well. Eventually we could allow computed columns to be named, the same as we allow in by argument: .(date2 = date >= date-2, date<=date).

Navigation Menu

Repository navigation

Support for on-the-fly columns in on #1639

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Support for on-the-fly columns in on #1639

Description

Activity

jangorecki commented on Apr 10, 2016

aryoda commented on May 4, 2016

arunsrinivasan commented on Jun 25, 2016

Henrik-P commented on Mar 2, 2018

franknarf1 commented on Oct 20, 2018

jaapwalhout commented on Nov 30, 2018

UweBlock commented on Jun 18, 2019

ColeMiller1 commented on Feb 22, 2020

UweBlock commented on Mar 8, 2020

jangorecki commented on Jun 27, 2020

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions