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

Question

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 1

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!