Let's say i have a financial historical dataset of 8 stocks that belong in 3 categories.I want to calculate the correlation of each stock within each group in R using the dplyr package.

library(tidyverse)
library(tidyquant)
Category = c("Social","Social","Internet","Technology",
             "Technology","Internet","Internet")
symbol = c("TWTR","FB","GOOG","TSLA","NOK","AMZN","AAPL")
A = tibble(Category,symbol)
B = tq_get(symbol, 
       from = "2021-01-01", 
       to = "2022-01-01")
BA = left_join(B,A,by="symbol")
BA%>%select(symbol,Category,close)

Few days ago i posted this similar question but the grouping variable was numeric and i my real world data set does not apply. The ideal output would be like this :

Any help of how i can do this in R using dplyr ?

optional data

var2 = c(rep("A",3),rep("B",3),rep("C",3),rep("D",3),rep("E",3),rep("F",3),
         rep("H",3),rep("I",3))

y2 = c(-1.23, -0.983, 1.28, -0.268, -0.46, -1.23,
       1.87, 0.416, -1.99, 0.289, 1.7, -0.455,
       -0.648, 0.376, -0.887,0.534,-0.679,-0.923,
       0.987,0.324,-0.783,-0.679,0.326,0.998);length(y2)
group2 = as.character(c(rep("xx",6),rep("xy",6),rep("xz",6),rep("xx",6)))
data2 = tibble(var2,group2,y2);data2

CodePudding user response：

A simple helper function,

fun <- function(ticker, value, ...) {
  com <- combn(unique(ticker), 2)
  L <- split(value, ticker)
  data.frame(
    Stock1 = com[1,], Stock2 = com[2,],
    Corr = mapply(function(a, b) cor(a, b, ...), L[com[1,]], L[com[2,]])
  )
}

And the work:

library(dplyr)
data2 %>%
  group_by(group2) %>%
  summarize(fun(var2, y2), .groups = "drop")
# # A tibble: 8 x 4
#   group2 Stock1 Stock2   Corr
#   <chr>  <chr>  <chr>   <dbl>
# 1 xx     A      B      -0.995
# 2 xx     A      H      -0.958
# 3 xx     A      I       0.853
# 4 xx     B      H       0.982
# 5 xx     B      I      -0.901
# 6 xx     H      I      -0.967
# 7 xy     C      D       0.469
# 8 xz     E      F      -0.186

Quick validation:

cor(filter(data2, var2 == "A")$y2, filter(data2, var2 == "B")$y2)
# [1] -0.9949738