R Dplyr solution for summarize_at correlation

Question

I am attempting to calculate correlation by (group_by) MktDate, for all columns in a dataframe to another column (Security Return).

I have attempted a number of dplyr solutions and can't quite get the correlation example to work properly but have no issues getting an example using mean to work properly.

This works, to calculate mean by specified columns

MyMeanTest <- MyDataTest %>%
filter(MktDate >='2009-12-31') %>%
group_by(MktDate) %>%
summarize_at(c('RtnVol_EM','OCFROI_EM'),mean,na.rm=TRUE)

This does not work. essentially I want the correlation for the columns specified, grouped by MktDate with the column FwdRet_12M. I get the following error message - Error in summarise_impl(.data, dots) : Evaluation error: not all arguments have the same length.

MyCorTest <- MyDataTest %>%
  group_by(MktDate) %>% 
summarize_at(c('RtnVol_EM','OCFROI_EM'),funs(cor(.,MyDataTest$FwdRet_12M,use="pairwise.complete.obs", "spearman")))

With the code example above I should end with something like this MktDate,RtnVol_EM,OCFROI_EM...

Here is some sample code that should help to understand the structure of the data and end objective.

MyDataTest <- structure(list(MktDate = structure(c(17896, 17896, 17896, 17896, 
17927, 17927, 17927, 17927), class = "Date"), FwdRet = c(2, 3, 
4, 5, 5, 2, 1, 4), Fact1 = c(10, 30, 20, 15, 12, 25, 26, 28), 
    Fact2 = c(100, 500, 300, 400, 150, 400, 430, 420)), .Names = c("MktDate", 
"FwdRet", "Fact1", "Fact2"), row.names = c(NA, -8L), class = "data.frame")

When running the pairwise correlation grouped by date on that data set the following should be the result.

MktDate,Fact1,Fact2
12/31/18,.2,.4
1/31/19,.4,-.8

How does it not work? Do you get an error message? An unexpected result? — divibisan
I updated the original question with the error message received. I'll try to find time later today to post a reproducible example. — user11058887
I also added an example towards the bottom of the original post with a comma delimited end result and sample input data. — user11058887

benc benc · Accepted Answer · 2019-04-22T19:33:25

One possible approach would be to reshape your data so that you have the variable you always want in the correlation (FwdRet) in one column and the variable that changes in a separate column. Like so:

MyDataTest_reshape <- MyDataTest %>% 
  gather(factor, value, -MktDate, -FwdRet) 

MyDataTest_reshape

         MktDate FwdRet factor value
  1  2018-12-31      2  Fact1    10
  2  2018-12-31      3  Fact1    30
  3  2018-12-31      4  Fact1    20
  4  2018-12-31      5  Fact1    15
  5  2019-01-31      5  Fact1    12
  6  2019-01-31      2  Fact1    25
  7  2019-01-31      1  Fact1    26
  8  2019-01-31      4  Fact1    28
  9  2018-12-31      2  Fact2   100
  10 2018-12-31      3  Fact2   500
  11 2018-12-31      4  Fact2   300
  12 2018-12-31      5  Fact2   400
  13 2019-01-31      5  Fact2   150
  14 2019-01-31      2  Fact2   400
  15 2019-01-31      1  Fact2   430
  16 2019-01-31      4  Fact2   420

Then you can take that reshaped data and feed it into your correlation:

MyDataTest_reshape %>%
  group_by(MktDate, factor) %>% 
  summarize(correlation = cor(FwdRet, value)) %>% 
  spread(factor, correlation)

# A tibble: 2 x 3
# Groups:   MktDate [2]
  MktDate      Fact1  Fact2
  <date>       <dbl>  <dbl>
1 2018-12-31  0.0756  0.529
2 2019-01-31 -0.627  -0.736

You can also do this all in one step, of course:

MyDataTest %>% 
gather(factor, value, -MktDate, -FwdRet) %>% 
group_by(MktDate, factor) %>% 
summarize(correlation = cor(FwdRet, value)) %>% 
spread(factor, correlation)

R Dplyr solution for summarize_at correlation

2 Answers