I'm trying to add new columns to data.table, where values in rows depend on the relative relationship of the values in the row. To be more precise, if there is a value X in a row, I would like to know how many other values are in the same column (and group), that are within X-30.
That is, given this:
DT<-data.table(
X = c(1, 2, 2, 1, 1, 2, 1, 2, 2, 1, 1, 1),
Y = c(100, 101, 133, 134, 150, 156, 190, 200, 201, 230, 233, 234),
Z = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))
I would like to get a new column, with values:
N <- c(0, 0, 0, 0, 1, 1, 0, 0, 1, 0, 1, 2)
I've tried the following, but I don't get the results I could use:
DT[,list(Y,num=cumsum(Y[-.I]>DT[.I,Y]-30),Z),by=.(X)]
Any ideas how to do this?