title: Efficient Python Pandas Stock Beta Calculation on Many Dataframes
body: <p>I have many (4000+) CSVs of stock data (Date, Open, High, Low, Close) which I import into individual Pandas dataframes to perform analysis.  I am new to python and want to calculate a rolling 12month beta for each stock, I found a post to calculate rolling beta (<a href="https://stackoverflow.com/questions/34802972/python-pandas-calculate-rolling-stock-beta-using-rolling-apply-to-groupby-object">Python pandas calculate rolling stock beta using rolling apply to groupby object in vectorized fashion</a>) however when used in my code below takes over 2.5 hours!  Considering I can run the exact same calculations in SQL tables in under 3 minutes this is too slow.</p>
  
  <p>How can I improve the performance of my below code to match that of SQL?  I understand Pandas/python has that capability. My current method loops over each row which I know slows performance but I am unaware of any aggregate way to perform a rolling window beta calculation on a dataframe.</p>
  
  <p>Note: the first 2 steps of loading the CSVs into individual dataframes and calculating daily returns only takes ~20seconds.  All my CSV dataframes are stored in the dictionary called 'FilesLoaded' with names such as 'XAO'.</p>
  
  <p>Your help would be much appreciated!
  Thank you :)</p>
  
  <pre><code>import pandas as pd, numpy as np
  import datetime
  import ntpath
  pd.set_option('precision',10)  #Set the Decimal Point precision to DISPLAY
  start_time=datetime.datetime.now()
  
  MarketIndex = 'XAO'
  period = 250
  MinBetaPeriod = period
  # ***********************************************************************************************
  # CALC RETURNS 
  # ***********************************************************************************************
  for File in FilesLoaded:
      FilesLoaded[File]['Return'] = FilesLoaded[File]['Close'].pct_change()
  # ***********************************************************************************************
  # CALC BETA
  # ***********************************************************************************************
  def calc_beta(df):
      np_array = df.values
      m = np_array[:,0] # market returns are column zero from numpy array
      s = np_array[:,1] # stock returns are column one from numpy array
      covariance = np.cov(s,m) # Calculate covariance between stock and market
      beta = covariance[0,1]/covariance[1,1]
      return beta
  
  #Build Custom "Rolling_Apply" function
  def rolling_apply(df, period, func, min_periods=None):
      if min_periods is None:
          min_periods = period
      result = pd.Series(np.nan, index=df.index)
      for i in range(1, len(df)+1):
          sub_df = df.iloc[max(i-period, 0):i,:]
          if len(sub_df) &gt;= min_periods:  
              idx = sub_df.index[-1]
              result[idx] = func(sub_df)
      return result
  
  #Create empty BETA dataframe with same index as RETURNS dataframe
  df_join = pd.DataFrame(index=FilesLoaded[MarketIndex].index)    
  df_join['market'] = FilesLoaded[MarketIndex]['Return']
  df_join['stock'] = np.nan
  
  for File in FilesLoaded:
      df_join['stock'].update(FilesLoaded[File]['Return'])
      df_join  = df_join.replace(np.inf, np.nan) #get rid of infinite values "inf" (SQL won't take "Inf")
      df_join  = df_join.replace(-np.inf, np.nan)#get rid of infinite values "inf" (SQL won't take "Inf")
      df_join  = df_join.fillna(0) #get rid of the NaNs in the return data
      FilesLoaded[File]['Beta'] = rolling_apply(df_join[['market','stock']], period, calc_beta, min_periods = MinBetaPeriod)
  
  # ***********************************************************************************************
  # CLEAN-UP
  # ***********************************************************************************************
  print('Run-time: {0}'.format(datetime.datetime.now() - start_time))
  </code></pre>