[R] Transforming relational data

[Matthew Dowle]

Thanks for the attempt and required output. How about this?

firststep = DT[,cbind(expand.grid(B,B),v=1/length(B)),by=C][Var1!=Var2]
setkey(firststep,Var1,Var2,C)
firststep = firststep[,transform(.SD,cv=cumsum(v)),by=list(Var1,Var2)]
setkey(firststep,Var1,Var2,C)
DT[, {x=data.table(expand.grid(B,B),C[1]-1L)
      firststep[x,roll=TRUE,nomatch=0][,sum(cv)]   # prior familiarity
     },by=C]
        C  V1
[1,] 1999 0.0
[2,] 2001 0.5
[3,] 2004 2.5

I think you may have said you have large data. If so, this
method should be fast. Please let us know how you get on.

HTH
Matthew



On Thu, 17 Feb 2011 23:07:19 -0800, mathijsdevaan wrote:

> OK, for the last step I have tried this (among other things):
> library(data.table)
> DT = data.table(read.table(textConnection("    A  B  C 1 1  a  1999
> 2 1  b  1999
> 3 1  c  1999
> 4 1  d  1999
> 5 2  c  2001
> 6 2  d  2001
> 7 3  a  2004
> 8 3  b  2004
> 9 3  d  2004"),head=TRUE,stringsAsFactors=FALSE))
> 
> firststep = DT[,cbind(expand.grid(B,B),v=1/length(B)),by=C][Var1!=Var2]
> setkey(firststep,Var1,Var2)
> list1<-firststep[J(expand.grid(DT$B,DT$B),v=1/length(DT$B)),nomatch=0]
[,sum(v)]
> list1
> #27
> 
> What I would like to get:
> list
> 1  0
> 2  0.5
> 3  2.5
> 
> Thanks!