Cross-correlation with multiple groups in one data table.

Question

I would like to calculate cross-correlations between time series groups inside data.table. I have time series data in this format:

data = data.table( group = c(rep("a", 5),rep("b",5),rep("c",5)) , Y = rnorm(15) )

   group           Y
 1:    a  0.90855520
 2:    a -0.12463737
 3:    a -0.45754652
 4:    a  0.65789709
 5:    a  1.27632196
 6:    b  0.98483700
 7:    b -0.44282527
 8:    b -0.93169070
 9:    b -0.21878359
10:    b -0.46713392
11:    c -0.02199363
12:    c -0.67125826
13:    c  0.29263953
14:    c -0.65064603
15:    c -1.41143837

Each group has the same number of observations. I am looking for a way to get cross-correlation between groups:

group.1   group.2    correlation
      a         b          0.xxx
      a         c          0.xxx
      b         c          0.xxx

I am working on a script to multiply each group and add cross-correlations, but the data size is quite large. Is there an efficient / zen way to do this?

Answer 1

Does it help?

data[,id:=rep(1:5,3)]
dtw  = dcast.data.table(data, id ~ group, value.var="Y" )[, id := NULL]
cor(dtw)

See Correlation between groups in R data.table

---

Another way:

# data
set.seed(45L)
data = data.table( group = c(rep("a", 5),rep("b",5),rep("c",5)) , Y = rnorm(15) )

# method 2
setkey(data, "group")
data2 = data[J(c("b", "c", "a"))][, list(group2=group, Y2=Y)]
data[, c(names(data2)) := data2]

data[, cor(Y, Y2), by=list(group, group2)]

#     group group2         V1
# 1:      a      b -0.2997090
# 2:      b      c  0.6427463
# 3:      c      a -0.6922734

---

And to generalize this "other" way to more than three groups ...

data = data.table( group = c(rep("a", 5),rep("b",5),rep("c",5),rep("d",5)) ,
                   Y = rnorm(20) )
setkey(data, "group")

groups = unique(data$group)
ngroups = length(groups)
library(gtools)
pairs = combinations(ngroups,2,groups)

d1 = data[pairs[,1],,allow.cartesian=TRUE]
d2 = data[pairs[,2],,allow.cartesian=TRUE]
d1[,c("group2","Y2"):=d2]
d1[,cor(Y,Y2), by=list(group,group2)]
#    group group2          V1
# 1:     a      b  0.10742799
# 2:     a      c  0.52823511
# 3:     a      d  0.04424170
# 4:     b      c  0.65407400
# 5:     b      d  0.32777779
# 6:     c      d -0.02425053