Correlation matrix using a moving window in data

Question

I'm trying to create correlation matrices using a 5-year moving window, so using data from 2000-2005, 2001-2006 etc.

Here's some example data:

d <- data.frame(v1=seq(2000,2015,1),           
                v2=rnorm(16),
                v3=rnorm(16),
                v4=rnorm(16))

  v1         v2         v3          v4
1  2000 -1.0907101 -1.3697559  0.52841978
2  2001 -1.3143654 -0.6443144 -0.44653227
3  2002 -0.1762554  2.0513870 -1.07372405
4  2003  0.1668012 -1.6985891 -0.32962331
5  2004  0.6006146 -0.1843326 -0.56936906
6  2005 -1.3113762 -0.3854868 -1.61247953
7  2006  3.1914908 -0.2635004  0.04689692
8  2007  0.7935639 -1.0844792 -0.25895397
9  2008  1.4217089  1.9572254  1.27221568
10 2009 -0.4192379 -0.5451291  0.18891557
11 2010 -0.1304170 -1.4676465  0.17137507
12 2011  1.2212943  0.9523027 -0.39269076
13 2012 -0.4464840 -0.7117153 -0.71619199
14 2013  0.1879822  1.0693801 -0.44835571
15 2014 -0.5602422 -0.7036433  0.53531753
16 2015  1.4322259  1.5398703  1.00294281

I've created new columns start and end for each group using dplyr:

d<-d%>%                                          
  mutate(start=floor(v1),
    end=ifelse(ceiling(v1)==start,start+5,ceiling(v1)))

I tried group_by(start,end) and then running the correlation, but that didn't work. Is there a quicker way than filtering the data to do this?

Your question is unclear. What do you want to generate exactly ? Since you have v2, v3, v4 as values what would you calculate on the first 3x3 cells of your matrix ? What would be the columns in your generated matrix? — Picarus
@Picarus the columns in the generated matrix to give a correlation value for each pair for each 5-year segment, would look like this: v2 v3 v4 v2 1.000000000 -0.005687711 0.8535461 v3 -0.005687711 1.000000000 -0.3555403 v4 0.853546141 -0.355540309 1.0000000 — User42

Chr Chr · Accepted Answer · 2020-11-18T12:13:07

This prints correlation matrices for 5 year windows:

require("tidyverse")

lapply(2000:2011, function(y) {
   filter(d, v1 >= y & v1 <= (y + 4)) %>%
   dplyr::select(-v1) %>%
   cor() %>%
   return()
})

Correlation matrix using a moving window in data

1 Answers