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. enter image description here

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: enter image description here

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.


1 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:

enter image description here


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!


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.