Lets say I have a data table like this.
customer_id time_stamp value
1: 1 223 4
2: 1 252 1
3: 1 456 3
4: 2 455 5
5: 2 632 2
So customer_id and time_stamp together form a unique key. I want to add some new columns indicating the previous and last values of "value". That is, I want the result to be like this.
customer_id time_stamp value value_PREV value_NEXT
1: 1 223 4 NA 1
2: 1 252 1 4 3
3: 1 456 3 1 NA
4: 2 455 5 NA 2
5: 2 632 2 5 NA
I want it to be fast and work with a rare, irregular time. I thought a data connection. However, the sliding connection seems to find the last time OR at the same time. Therefore, if you perform a sliding join on two copies of the same table (after adding _PREV to the column names of the copy), this does not quite work. You can lure it out by adding a tiny number to the copy time variable, but that's awkward.
rollin join data.table? , 40 R. , , , . , , .
.
data=data.table(customer_id=c(1,2,1,1,2),time_stamp=c(252,632,456,223,455),value=c(1,2,3,4,5))
data_sorted=data[order(customer_id,time_stamp)]
, . , , NA
customer_id , , .
. - ?
add_prev_next_cbind<-function(data,ident="customer_id",timecol="time_stamp",prev_tag="PREV",
next_tag="NEXT",sep="_"){
o=order(data[[ident]],data[[timecol]])
uo=order(o)
data=data[o,]
Nrow=nrow(data)
Ncol=ncol(data)
data_prev=data[c(1,1:(Nrow-1)),]
data_next=data[c(2:(Nrow),Nrow),]
prev_diff=data[[ident]] != data_prev[[ident]]
prev_diff[1]=T
next_diff=data[[ident]] != data_next[[ident]]
next_diff[Nrow]=T
names=names(data)
names_prev=paste(names,prev_tag,sep=sep)
names_next=paste(names,next_tag,sep=sep)
setnames(data_prev,names,names_prev)
setnames(data_next,names,names_next)
data_all=cbind(data,data_prev,data_next)
data_all=data_all[uo,]
return(data_all)
}