How to vectorize to speed up Dataframe apply pandas

Question

I have a tXn (5000 X 100) dataframe wts_df,

wts_df.tail().iloc[:, 0:6]
Out[71]: 
                  B         C         H         L         R         T  
2020-09-25  0.038746  0.033689 -0.047835 -0.002641  0.009501 -0.030689   
2020-09-28  0.038483  0.033189 -0.061742  0.001199  0.009490 -0.028370   
2020-09-29  0.038620  0.034957 -0.031341  0.006179  0.007815 -0.027317   
2020-09-30  0.038610  0.034902 -0.014271  0.004512  0.007836 -0.024672   
2020-10-01  0.038790  0.029937 -0.044198 -0.008415  0.008347 -0.030980

and two similar txn dataframes, vol_df and rx_df (same index and columns). For now we can use,

rx_df = wts_df.applymap(lambda x: np.random.rand())
vol_df = wts_df.applymap(lambda x: np.random.rand())

I need to do this (simplified):

for date in wts_df.index:
  wts = wts_df.loc[date]   # is a vector now 1Xn

  # mutliply all entries of rx_df and vol_df until this date by these wts, and sum across columns

  rx = rx_df.truncate(after=date)   # still a dataframe but truncated at a given date, kXn
  vol = vol_df_df.truncate(after=date)   

  wtd_rx = (wts * rx).sum(1)   # so a vector kX1
  wtd_vol = (wts * vol).sum(1)   

  # take ratio
  rx_vol = rx / vol

  rate[date] = rx_vol.tail(20).std()

So rate looks like this

pd.Series(rate).tail()
Out[71]: 
              rate         
2020-09-25  0.0546   
2020-09-28  0.0383  
2020-09-29  0.0920    
2020-09-30  0.0510  
2020-10-01  0.0890

The above loop is slow, so i tried this:

def rate_calc(wts, date, rx_df=rx_df, vol_df=vol_df):
    wtd_rx = (rx_df * wts).sum(1) 
    wtd_vol = (vol_df * wts).sum(1)
    rx_vol = wtd_rx / wtd_vol
    rate = rx_vol.truncate(after=date).tail(20).std() 
    return rate

rates = wts_df.apply(lambda x: rate_calc(x, x.name), axis=1)

This is still very slow. Moreover I need to do this for multiple wts_df contained in a dict so the total operations takes a lot time.

rates = {key: val.apply(lambda x: rate_calc(x, x.name), axis=1) for key, val in wts_df_dict.iteritems()}

Any ideas how to speed such operations?

user_992600 · Answer

Your question falls under the category of 'optimization' so allow me to share with you few pointers to solve your problem.

First, when it comes to speed, always use %timeit to ensure you get better results with a new stratgegy.

Second, there are few ways to iterate a data:

with iterrows() -- use it only when the data sample is small (or better yet, try not to use it as it's too slow).
With apply --better alternative to iterrows and much more efficient but when the data set is large (like in your example) it may present a delay problem.
Vectorizing --simply put, you execute the operation on the entire column/array and its significantly fast. Winner!

So, in order to solve your speed problem your strategy should be in the form of vectorizing. So here's how it should work; (mind the .values):

df['new_column'] = my_function(df['column_1'].values, df['column_2'].values...) and you will note a super fast result.

How to vectorize to speed up Dataframe apply pandas

1 Answers