calculating slope on a rolling basis in pandas df python

Question

I have a dataframe :

            CAT         ^GSPC
Date        
2012-01-06  80.435059   1277.810059
2012-01-09  81.560600   1280.699951
2012-01-10  83.962914   1292.079956
....
2017-09-16  144.56653   2230.567646

and I want to find the slope of the stock / and S&P index for the last 63 days for each period. I have tried :

x = 0
temp_dct = {}
for date in df.index:
      x += 1
      max(x, (len(df.index)-64))    
      temp_dct[str(date)] = np.polyfit(df['^GSPC'][0+x:63+x].values, 
                                     df['CAT'][0+x:63+x].values, 
                                     1)[0]

However I feel this is very "unpythonic" , but I've had trouble integrating rolling/shift functions into this.

My expected output is to have a column called "Beta" that has the slope of the S&P (x values) and stock (y values) for all dates available

Belch

Given the simplicity of LR's solution, we could craft something with as_strided. However, I don't think the gain in performance could compensate the burden of writing up such a solution. – Halibut

Answer 1

# this will operate on series
def polyf(seri):
    return np.polyfit(seri.index.values, seri.values, 1)[0]

# you can store the original index in a column in case you need to reset back to it after fitting
df.index = df['^GSPC']
df['slope'] = df['CAT'].rolling(63, min_periods=2).apply(polyf, raw=False)

After running this, there will be a new column store the fitting result.

Phillipphillipe