I have a table that contains 4 numeric vectors, I would like to calculate and populate a new table that shows the rolling correlation of each possible column pair.
I want to transform this simple table:
| Column A | Column B | Column c | Column d |
|---|---|---|---|
| 1 | 8 | 3 | 4 |
| 7 | 5 | 5 | 7 |
Into a table that looks like this with vectors of rolling correlation.
| AB | AC | AD | BC | BD | CD |
|---|
I tried the rollapply function and more or less every related question in the search bar and have not been able to resolve the problem.
This solution is based on this comment by @GGrothendieck, plus combn() to generate the new column names.
library(zoo)
# example data
set.seed(13)
dat <- data.frame(
A = rnorm(20),
B = rnorm(20),
C = rnorm(20),
D = rnorm(20)
)
pairnames <- names(dat) |>
combn(2, \(x) paste(x, collapse = ""))
dat |>
rollapply(10, \(x) cor(x)[lower.tri(cor(x))], by.column = FALSE) |>
as.data.frame() |>
setNames(pairnames)
AB AC AD BC BD CD
1 0.0003534898 -0.13612373 -0.22849979 0.45029555 -0.11457305 -0.26487479
2 -0.0580758074 -0.12874149 0.06382879 0.46018347 -0.07621848 -0.29229352
3 0.1551727794 -0.01388046 0.10599110 0.32994185 -0.19459186 -0.37626526
4 -0.0264603584 -0.02640355 0.21575705 0.29424980 -0.19790990 -0.35080532
5 -0.0067446215 0.04424283 0.48912209 0.47819290 -0.21369265 -0.11972016
6 -0.1970381395 0.06705549 0.37975883 0.57596940 -0.44240169 -0.06895075
7 -0.2725443707 0.11127621 0.42059540 0.67974874 -0.38430076 -0.09602750
8 -0.0434380298 0.12698397 0.67938827 0.58240537 -0.32976570 -0.26464101
9 0.1026328910 0.34521756 0.66884148 0.16218774 -0.09503962 -0.02151401
10 0.0717026253 0.31339628 0.75363354 -0.03758187 0.04365913 0.09453714
11 0.0861075966 0.19405089 0.83701868 0.03339110 -0.03997420 0.14340951
# make some example data
d <- data.frame(A = sample(1:10), B = sample(1:10), C = sample(1:10), D = sample(1:10))
# define what kind of rolling correlation we want to do
my_rollcor <- function(x) {
zoo::rollapply(
x, width = 3, by.column = FALSE, fill = NA,
FUN = \(y) cor(y[,1], y[,2])
)
}
# apply the rolling correlation to the combination of columns
out <- combn(d, 2, FUN = my_rollcor)
out <- as.data.frame(out)
# finally, make the correct names for those columns
names(out) <- combn(names(d), 2, \(x) paste0(x[[1]], x[[2]]))
# print the output
out
AB AC AD BC BD CD 1 NA NA NA NA NA NA 2 -0.85148509 0.93676591 0.2726855 -0.61413236 0.2723189 0.59213691 3 0.19966750 0.89340515 -0.3662897 0.61858957 0.8386279 0.09078413 4 0.93325653 0.84855529 0.3394221 0.98198051 0.6546537 0.78571429 5 0.18898224 0.94491118 0.1428571 0.50000000 -0.9449112 -0.18898224 6 -0.59030128 0.98974332 -0.6185896 -0.69955860 -0.2690610 -0.50000000 7 -0.09078413 0.92857143 -0.7857143 -0.45392065 -0.5447048 -0.50000000 8 -0.97986371 0.14803423 0.2448990 0.05241424 -0.4335550 -0.92261291 9 -0.38572714 -0.09078413 0.8660254 0.95382097 0.1272570 0.41931393 10 NA NA NA NA NA NA
