 user3032689 -4 years ago 89
R Question

# Correlationmatrix from data table

If I have the following data table:

``````set.seed(1)
TDT <- data.table(Group = c(rep("A",40),rep("B",60)),
Id = c(rep(1,20),rep(2,20),rep(3,20),rep(4,20),rep(5,20)),
Time = rep(seq(as.Date("2010-01-03"), length=20, by="1 month") - 1,5),
norm = round(runif(100)/10,2),
x1 = sample(100,100),
x2 = round(rnorm(100,0.75,0.3),2),
x3 = round(rnorm(100,0.75,0.3),2),
x4 = round(rnorm(100,0.75,0.3),2),
x5 = round(rnorm(100,0.75,0.3),2))
``````

How can I calculate the correlations between x1, x2, x3, x4 and x5 by Time?

This:

``````TDT[,x:= list(cor(TDT[,5:9])), by = Time]
``````

does not work.

How can it be done in
`datatable`
? Jealie
Answer Source

You were so close in your attempt! All you missed was an extra `list()`.

This works:

``````TDT[,x:= list(list(cor(TDT[,5:9]))), by = Time]
``````

And `TDT\$x` returns:

``````[]
x1          x2          x3         x4          x5
x1  1.00000000  0.72185099  0.07368766 -0.7031890 -0.36895449
x2  0.72185099  1.00000000  0.68058833 -0.7393130  0.05066973
x3  0.07368766  0.68058833  1.00000000 -0.5021462  0.10645894
x4 -0.70318896 -0.73931299 -0.50214616  1.0000000  0.11671020
x5 -0.36895449  0.05066973  0.10645894  0.1167102  1.00000000

[]
x1         x2          x3          x4         x5
x1  1.0000000 -0.1011948 -0.85191422 -0.15571603  0.4855237
x2 -0.1011948  1.0000000  0.56691559 -0.44002621 -0.6699172
x3 -0.8519142  0.5669156  1.00000000  0.02189754 -0.6168013
x4 -0.1557160 -0.4400262  0.02189754  1.00000000  0.2236542
x5  0.4855237 -0.6699172 -0.61680132  0.22365419  1.0000000

[...]
``````

The extra `list()` is needed because of how `data.table` parses the second element of the `DT[1,2]` syntax. This has been discussed in depth elsewhere in stackoverflow, with a most excellent answer that I invite you to read.

As a side note, it seems preferable to replace the outermost call to `list()` with `.()` to clarify the intent. I also like to single out explicitly the columns with a reference to `.SD` and `.SDcols`. With the same outcome, you could rewrite your code as:

``````TDT[, x := .(list(cor(.SD))), by = Time, .SDcols = 5:9]
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download
Latest added