CODEWITHSUNDEEP
rdata.table
forecaster
forecaster

Reputation: 1159

R data.table apply date by variable last

I have a data.table in R. I have to decrement date from last variable within by group. So in the example below, the date "2012-01-21" should be the 10th row when id = "A" and then decrement until the 1st row. and then for id="B" the date should be "2012-01-21" for 5th row and then decrement by 1 until it reaches first row. Basically the the decrement should start from last value by "id". How could I accomplish in R data.table?

The code below does the opposite. The date starts from 1st row and decrements, how would you start the date by last row and then decrement.

end<- as.Date("2012-01-21")

dt <- data.table(id = c(rep("A",10),rep("B",5)),sales=10+rnorm(15))

dtx <- dt[,date := seq(end,by = -1,length.out = .N),by=list(id)]


 > dtx
    id     sales       date
 1:  A 12.008514 2012-01-21
 2:  A 10.904740 2012-01-20
 3:  A  9.627039 2012-01-19
 4:  A 11.363810 2012-01-18
 5:  A  8.533913 2012-01-17
 6:  A 10.041074 2012-01-16
 7:  A 11.006845 2012-01-15
 8:  A 10.775066 2012-01-14
 9:  A  9.978509 2012-01-13
10:  A  8.743829 2012-01-12
11:  B  8.434640 2012-01-21
12:  B  9.489433 2012-01-20
13:  B 10.011354 2012-01-19
14:  B  8.681002 2012-01-18
15:  B  9.264915 2012-01-17

Upvotes: 1

Views: 35

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388807

We could reverse the sequence generated above. 

library(data.table)

dt[,date := rev(seq(end,by = -1,length.out = .N)),id]

dt
#    id     sales       date
# 1:  A 10.886312 2012-01-12
# 2:  A  9.803543 2012-01-13
# 3:  A  9.063694 2012-01-14
# 4:  A  9.762628 2012-01-15
# 5:  A  8.764109 2012-01-16
# 6:  A 11.095826 2012-01-17
# 7:  A  8.735148 2012-01-18
# 8:  A  9.227285 2012-01-19
# 9:  A 12.024336 2012-01-20
#10:  A  9.976514 2012-01-21
#11:  B  8.488753 2012-01-17
#12:  B  9.141837 2012-01-18
#13:  B 11.435365 2012-01-19
#14:  B 10.817839 2012-01-20
#15:  B  8.427098 2012-01-21

Similarly, 

dt[,date := seq(end - .N + 1,by = 1,length.out = .N),id]

Upvotes: 1

Related Questions