I want to find the Covariance between 1 column with all the other columns in a dataframe in pandas

Question

I am trying to analyse the National Stock Exchange Data. I want to calculate the covariance of each stock wrt the index (nifty), using pandas, and there after calculate the beta of each stock. How shall I go about it?

I found the method to calculate the covariance of 1 column with another, but my dataframe has about 36 stock closing price columns, and 1 index closing column. How can i calculate the covariance of all columns wrt the index column with a single command?

Answer 1

You first need to calculate returns of the prices (you are using adjusted closing prices, right?).

returns = df.pct_change()

Next, you calculate the covariance as a series (I'm using a dictionary comprehension to create the series):

index = 'SPY'  # Change to your ticker for the index.
s = pd.Series({symbol: returns[index].cov(returns[symbol]) 
               for symbol in df 
               if symbol != index})

This will give you the covariance of each stock with the index.