Calculate average correlation of a pairwise correlation matrix (below the diagonal)

[Christian Spek]

Dear all,

I would like to calculate the average correlation of a pairwise correlation matrix, below the diagonal. I have a database with ESG ratings from different providers. For example:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(s_Refinitiv_gov_rank s_Sustainalytics_gov_rank s_IVA_gov_rank s_KLD_gov_rank)
 -1.366491 -1.5359126 .   -1.38519
  -1.19336 -1.7091907 .   -1.38519
-1.4703695  -.8428002 .   -1.38519
-1.2626122  -1.501257 . -1.6017385
-1.1587336  -1.605224 .   -1.38519
-1.1587336  -.9814227 .   -1.38519
-1.4357432  -1.674535 .   -1.38519
 -.8124716  -.8428002 .   -1.38519
-1.6088742   -.946767 .   -1.38519
 -.9856026  -.8428002 .   -1.38519
 -1.054855 -1.5359126 .   -1.38519
 -.9509764  -.8428002 .   -1.38519
 -1.054855   -1.39729 .   -1.38519
-1.6088742 -1.7091907 .   -1.38519
 -1.366491  -1.501257 .   -1.38519
end

You can see that in this data example, there are some missing values in one of the rating providers, however this is not the case in the whole sample, and I believe should not matter when computing a pairwise correlation matrix. I currently use the following code for computing the pairwise correlation matrix, and the average correlation:

pwcorr s_Refinitiv_gov_rank s_Sustainalytics_gov_rank s_IVA_gov_rank s_KLD_gov_rank
mata
C = st_matrix("r(C)")
mean(abs(select(vech(C), vech(C) :< 1)))
end

This gives the following output:

pwcorr s_Refinitiv_gov_rank s_Sustainalytics_gov_rank s_IVA_gov_rank s_KLD_gov_rank

             | s_Refi.. s_Sust.. s_IVA_g~ s_KLD_g~
-------------+------------------------------------
s_Re~ov_rank |   1.0000 
s_Su~ov_rank |   0.3781   1.0000 
s_IVA_gov_~k |   0.1882   0.3294   1.0000 
s_KLD_gov_~k |   0.3894   0.3833   0.3257   1.0000 

. mata
------------------------------------------------- mata (type end to exit) ------------------------------------------------------------------------------------------------------------------------
: C = st_matrix("r(C)")

: mean(abs(select(vech(C), vech(C) :< 1)))
  .3323343444

: end

Here, the average correlation is correct. However, when I want to calculate the average correlation by some group (e.g. industry), I get an incorrect value:

pwcorr s_Refinitiv_gov_rank s_Sustainalytics_gov_rank s_IVA_gov_rank s_KLD_gov_rank if ff_12 == 11

             | s_Refi.. s_Sust.. s_IVA_g~ s_KLD_g~
-------------+------------------------------------
s_Re~ov_rank |   1.0000 
s_Su~ov_rank |   0.3012   1.0000 
s_IVA_gov_~k |   0.1431   0.0808   1.0000 
s_KLD_gov_~k |   0.3682   0.2743   0.3223   1.0000 

. mata
------------------------------------------------- mata (type end to exit) ------------------------------------------------------------------------------------------------------------------------
: C = st_matrix("r(C)")

: mean(abs(select(vech(C), vech(C) :< 1)))
  .4362420583

: end

When I calculate the average of the elements manually, I get an average of around 0.248. My question is: what I am doing wrong? Which code can I use to get the correct value for the average?

With kind regards,

Christian Spek

[Andrew Musau]

Your example already reveals the problem. You are not selecting the right matrix with the imposition "<1".

* Example generated by -dataex-. For more info, type help dataex
clear
input float(s_Refinitiv_gov_rank s_Sustainalytics_gov_rank s_IVA_gov_rank s_KLD_gov_rank)
 -1.366491 -1.5359126 .   -1.38519
  -1.19336 -1.7091907 .   -1.38519
-1.4703695  -.8428002 .   -1.38519
-1.2626122  -1.501257 . -1.6017385
-1.1587336  -1.605224 .   -1.38519
-1.1587336  -.9814227 .   -1.38519
-1.4357432  -1.674535 .   -1.38519
 -.8124716  -.8428002 .   -1.38519
-1.6088742   -.946767 .   -1.38519
 -.9856026  -.8428002 .   -1.38519
 -1.054855 -1.5359126 .   -1.38519
 -.9509764  -.8428002 .   -1.38519
 -1.054855   -1.39729 .   -1.38519
-1.6088742 -1.7091907 .   -1.38519
 -1.366491  -1.501257 .   -1.38519
end

pwcorr s_Refinitiv_gov_rank s_Sustainalytics_gov_rank s_IVA_gov_rank s_KLD_gov_rank
mata
C = st_matrix("r(C)")
D= abs(select(vech(C), vech(C) :< 1))
D
E= abs(select(vech(C), vech(C) :< .999999))
E
end

Res.:

 mata
------------------------------------------------- mata (type end to exit) -------------------------------------------------------------------------------------------------------------------------------------------------
: 
: C = st_matrix("r(C)")

: 
: D= abs(select(vech(C), vech(C) :< 1))

: 
: D
                 1
    +---------------+
  1 |            1  |
  2 |  .3385280835  |
  3 |  .0345100646  |
  4 |  .1554653514  |
  5 |            1  |
    +---------------+

: 
: E= abs(select(vech(C), vech(C) :< .999999))

: 
: E
                 1
    +---------------+
  1 |  .3385280835  |
  2 |  .0345100646  |
  3 |  .1554653514  |
    +---------------+

: 
: end
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[Christian Spek]

It works well, thank you!

[Leonardo Guizzetti]

Here's a way to do it with matrix algebra and Mata.

clear *
cls

mata:
  C = ( 1, 0.8, 0.6 \ ///
        0.8, 1, 0.5 \ ///
        0.6, 0.5, 1)
  C
 
  K = lowertriangle(J(rows(C), rows(C), 1)) - I(rows(C))
  K = K :/ sum(K)
  av_corr = sum(diagonal(C * K))
  av_corr
end

[Mike Lacy]

The -alpha- command, usually used for calculating the reliability of indices, returns among other things the inter-item mean covariance or correlation.

 alpha s_Refinitiv_gov_rank s_Sustainalytics_gov_rank s_IVA_gov_rank s_KLD_gov_rank, std

s_IVA_gov_rank constant in analysis sample, dropped from analysis

Test scale = mean(standardized items)

Average interitem correlation:      0.1762
Number of items in the scale:            3
Scale reliability coefficient:      0.3908

[Christian Spek]

Thank you all for your suggestions. I have resolved my issue using the reply from Andrew Musau.