CODEWITHSUNDEEP
rdata.table
erasmortg
erasmortg

Reputation: 3278

Create a sequence of dates in one data frame based on a second data frame by group

I have two data frames that share a grouping ID. I would like to create a sequence of dates in one, based on the conditions set by the other one. MRE as follows:

jdates <- structure(list(Group.1 = c(8, 9), x = structure(c(16674, 16661), class = "Date")), .Names = c("Group.1", "x"), row.names = c(NA, -2L), class = c("data.table", "data.frame"))

jtrying <- structure(list(id = c(8, 8, 8, 9, 9, 9), values1 = 1:6, values2 = 7:12), .Names = c("id", "values1", "values2"), row.names = c(NA, -6L), class = c("data.table", "data.frame"))

In this example, I would like to create a column of dates in jtrying starting from the next date in jdates (per group - Group.1 in jdates and id in jtrying).

With data.table, none of these (terrible) approaches worked:

jtrying[ , date := seq(jdates$x + 1, length.out=3, by = 1), by = jdates$Group.1]

jtrying[ , date := seq(jdates$x + 1, length.out=3, by = 1), by = id]

jtrying[ , date := lapply(.SD,(seq(jdates$x + 1, length.out=3, by = 1))), by = id]

jtrying[ , date := lapply(.SD,function(x) seq(jdates$x + 1, length.out=3, by = 1)), by = id]

I have been trying data.table approaches, as they are thought to be faster (and the real data is quite big), but in reality, anything (within reason) could do.

My expected result is a data.frame that looks like this:

 jtrying
   id values1 values2       date
1:  8       1       7 2015-08-28
2:  8       2       8 2015-08-29
3:  8       3       9 2015-08-30
4:  9       4      10 2015-08-15
5:  9       5      11 2015-08-16
6:  9       6      12 2015-08-17

Upvotes: 2

Views: 99

Answers (1)

Frank
Frank

Reputation: 66819

Here's what I would do

jtrying[jdates, 
  date := seq(from=x+1, by=1, length.out=.N)
, on=c(id="Group.1"), by=.EACHI]

This syntax X[Y, newcol := ..., on=c(Xcol=Ycol), by=.EACHI] works as follows:

  • X is merged with Y on the columns identified in on.
  • X builds its newcol separately for each value of the merge columns (i.e., by=.EACHI).

Upvotes: 2

Related Questions