Skip to content
All library documents

Aggregating Stock Correlations into Sub-Industry Averages

Article Quant Q&A · Author: PuraVidaTrader
Read the original

Summary

The document explains how to summarize stock-level correlations by industry group. First, represent each pairwise stock correlation as a row in a long-form table, with fields for date, the industry of each stock, and the correlation value. Grouping by date and the two industry labels then produces an average correlation for every industry pair at each date. The approach can be repeated across sectors or sub-industry classifications and observation periods.

For pairs within the same industry, the example sets the resulting diagonal values to one. The grouped output remains a table; reshaping or pivoting it is needed to display a matrix for a particular date. This is a practical data-processing recipe rather than an explanation of correlation estimation or a test of the resulting averages. It does not discuss weighting choices, missing observations, or whether averaging correlations across securities is appropriate for a particular research purpose.

Key ideas

  • Convert pairwise stock correlations into a long-form table with date and industry labels.
  • Group by date and both industry labels to calculate average pairwise correlations.
  • Set within-industry diagonal entries to one when that is the desired matrix convention.
  • Reshape the grouped results to display them as a matrix for a selected date.
  • The method does not address weighting or missing-data decisions.

Tags

EquitiesStatisticsPortfolio construction
Full text
# Creating a matrix of average correlations for sub-industry from individual stock correlation matrix


# Creating a matrix of average correlations for sub-industry from individual stock correlation matrix












I am having trouble trying to figure out how to do this in Python. I have created it in Excel, but I would like to automate this for any sector or grouping of sub-industries.

I first start with creating a correlation matrix of the individual securities by sub-industry.

I then get the mean of the correlations from Building Products vs Casinos & Gaming, and then Building Products vs Construction Materials, and so on. The end result looks like this:

In this matrix, I set the diagonal as 1 since I am only worried about the average correlations between sub-industry.

I have done this for a few quarters but I am interested in automating this through Python. Data is from my Bloomberg Terminal.

## Answer by wjamdanf1234 (score 1)

https://quant.stackexchange.com/a/50546

I would turn the stock level matrix (which I assume would be a dataframe) into a tall table that would have rows that look something like this:

etc...

Now, having this dataframe, called corr_df, for example I would use this code:

`corr_df_avg = corr_df.groupby(['date','ind1','ind2'])['correlation'].mean()`

to get the average correlation between ind1 and ind2 on each given date.

To set the average correlation = 1 where ind1 = ind2 run this code

```
corr_df_avg = corr_df_avg.assign(correlation = corr_df_avg['correlation'].where(corr_df_avg['ind1']!=corr_df_avg['ind2'],1)
```

Then you are left with a tall table. If you want to get a matrix, for a given date, then you have to play around with either df.transpose() or df.unstack(). I am rusty on those always need to re-google it whenever I need it. Hopefully this helps!

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.