1

I am trying to calculate rolling correlation for two variables in a data frame (the variables position in the data set are 29 and 35 respectively). I run these lines of code -

df <- df %>%
  mutate (rrcorr_prccq = zoo::rollapply(width = 20, function (x) cor (x[,29], x[,35], method = 'spearman'),by.column = FALSE, fill = NA)
  )

But I got the following errors -

Error during wrapup: ℹ In argument: `rrcorr_prccq = zoo::rollapply(...)`.
Caused by error in `zoo()`:
! “x” : attempt to define invalid zoo object
Error: no more error handlers available (recursive errors?); invoking 'abort' restart

Here is some information about the dataset -

df <- structure(list(gvkey = c("001003", "001003", "001003", "001003", 
"001004", "001004"), datadate = structure(c(7335, 7424, 7516, 
7608, 7363, 7455), class = "Date"), datayear = c(1990, 1990, 
1990, 1990, 1990, 1990), fyearq = c(1989, 1990, 1990, 1990, 1989, 
1989), fqtr = c(4, 1, 2, 3, 3, 4), fyr = c(1, 1, 1, 1, 5, 5), 
    indfmt = c("INDL", "INDL", "INDL", "INDL", "INDL", "INDL"
    ), consol = c("C", "C", "C", "C", "C", "C"), popsrc = c("D", 
    "D", "D", "D", "D", "D"), datafmt = c("STD", "STD", "STD", 
    "STD", "STD", "STD"), tic = c("ANTQ", "ANTQ", "ANTQ", "ANTQ", 
    "AIR", "AIR"), cusip = c("000354100", "000354100", "000354100", 
    "000354100", "000361105", "000361105"), conm = c("A.A. IMPORTING CO INC", 
    "A.A. IMPORTING CO INC", "A.A. IMPORTING CO INC", "A.A. IMPORTING CO INC", 
    "AAR CORP", "AAR CORP"), curcdq = c("USD", "USD", "USD", 
    "USD", "USD", "USD"), datacqtr = c("1989Q4", "1990Q1", "1990Q2", 
    "1990Q3", "1990Q1", "1990Q2"), datafqtr = c("1989Q4", "1990Q1", 
    "1990Q2", "1990Q3", "1989Q3", "1989Q4"), cshoq = c(2.683, 
    2.683, 2.683, 2.033, 16.07, 16.082), saleq = c(4.913, 3.506, 
    3.153, 2.678, 112.278, 119.396), cik = c("0000730052", "0000730052", 
    "0000730052", "0000730052", "0000001750", "0000001750"), 
    costat = c("I", "I", "I", "I", "A", "A"), mkvaltq = c(NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), prccq = c(0.125, 
    NA, 0.094, 0.094, 31.125, 21.25), prchq = c(0.75, NA, 0.094, 
    0.094, 37.125, 31.75), prclq = c(0.125, NA, 0.094, 0.094, 
    30, 20.25), del_saleq = c(NA, -1.407, -0.353, -0.475, NA, 
    7.11799999999999), del_prccq = c(NA, NA, NA, 0, NA, -9.875
    ), del_prchq = c(NA, NA, NA, 0, NA, -5.375), del_prclq = c(NA, 
    NA, NA, 0, NA, -9.75), RET_prccq = c(NA, NA, NA, 0, NA, -0.317269076305221
    ), RET_prchq = c(NA, NA, NA, 0, NA, -0.144781144781145), 
    RET_prclq = c(NA, NA, NA, 0, NA, -0.325), mkvaltq_prccq = c(0.335375, 
    NA, 0.252202, 0.191102, 500.17875, 341.7425), mkvaltq_prchq = c(2.01225, 
    NA, 0.252202, 0.191102, 596.59875, 510.6035), mkvaltq_prclq = c(0.335375, 
    NA, 0.252202, 0.191102, 482.1, 325.6605), REV_prccq = c(NA, 
    NA, -1.39967169173916, -2.48558361503281, NA, 0.0208285478101202
    ), REV_prchq = c(NA, NA, -1.39967169173916, -2.48558361503281, 
    NA, 0.0139403666445686), REV_prclq = c(NA, NA, -1.39967169173916, 
    -2.48558361503281, NA, 0.0218571180723483)), row.names = c(NA, 
-6L), class = c("tbl_df", "tbl", "data.frame"))
Mark
  • 7,785
  • 2
  • 14
  • 34
Sharif
  • 163
  • 1
  • 9
  • Hi, rrcorr is the name of the data frame. I changed it to df now. Hope this helps to minimize the confusion. – Sharif Sep 01 '23 at 04:01
  • This is a big dataset, millions of rows. So running dput (df) might not be very convenient. – Sharif Sep 01 '23 at 04:06
  • 1
    I updated the questions including the output from `dput(head(df))` – Sharif Sep 01 '23 at 04:08
  • Thanks for the post, but I got the following error when I run your code - ` Error during wrapup: ℹ In argument: `rrcorr_prccq = zoo::rollapply(...)`. Caused by error in `cor()`: ! 'x' must be numeric Error: no more error handlers available (recursive errors?); invoking 'abort' restart ` – Sharif Sep 01 '23 at 04:25

1 Answers1

1

The issue is that the first argument of rollapply is the data, and you didn't provide the data as the first argument. Do that, and it all works fine:

library(dplyr)

df |> mutate(rrcorr_prccq = zoo::rollapply(df, width = 20, function (x) cor(x[,29], x[,35], method = 'spearman'), by.column = FALSE, fill = NA))

Output:

# A tibble: 6 × 38
  gvkey  datadate   datayear fyearq  fqtr   fyr indfmt consol popsrc datafmt
  <chr>  <date>        <dbl>  <dbl> <dbl> <dbl> <chr>  <chr>  <chr>  <chr>  
1 001003 1990-01-31     1990   1989     4     1 INDL   C      D      STD    
2 001003 1990-04-30     1990   1990     1     1 INDL   C      D      STD    
3 001003 1990-07-31     1990   1990     2     1 INDL   C      D      STD    
4 001003 1990-10-31     1990   1990     3     1 INDL   C      D      STD    
5 001004 1990-02-28     1990   1989     3     5 INDL   C      D      STD    
6 001004 1990-05-31     1990   1989     4     5 INDL   C      D      STD    
# ℹ 28 more variables: tic <chr>, cusip <chr>, conm <chr>, curcdq <chr>,
#   datacqtr <chr>, datafqtr <chr>, cshoq <dbl>, saleq <dbl>, cik <chr>,
#   costat <chr>, mkvaltq <dbl>, prccq <dbl>, prchq <dbl>, prclq <dbl>,
#   del_saleq <dbl>, del_prccq <dbl>, del_prchq <dbl>, del_prclq <dbl>,
#   RET_prccq <dbl>, RET_prchq <dbl>, RET_prclq <dbl>, mkvaltq_prccq <dbl>,
#   mkvaltq_prchq <dbl>, mkvaltq_prclq <dbl>, REV_prccq <dbl>, REV_prchq <dbl>,
#   REV_prclq <dbl>, rrcorr_prccq <lgl>

Note: in the example, you only gave 6 rows, so the example gives every value as NA (hence "rrcorr_prccq "). In the actual data, this wouldn't be the case.

For more information on rollapply(), consider reading the documentation.

Mark
  • 7,785
  • 2
  • 14
  • 34