how to speed-up a very slow pandas apply function?
<p>I have a very large pandas dataset, and at some point I need to use the following function</p>

<pre><code>def proc_trader(data):
    data['_seq'] = np.nan
    # make every ending of a roundtrip with its index
    data.ix[data.cumq == 0,'tag'] = np.arange(1, (data.cumq == 0).sum() + 1)
    # backfill the roundtrip index until previous roundtrip;
    # then fill the rest with 0s (roundtrip incomplete for most recent trades)
    data['_seq'] =data['tag'].fillna(method = 'bfill').fillna(0)
    return data['_seq']
    # btw, why on earth this function returns a dataframe instead of the series `data['_seq']`??
</code></pre>

<p>and I use apply</p>

<pre><code>reshaped['_spell']=reshaped.groupby(['trader','stock'])[['cumq']].apply(proc_trader)
</code></pre>

<p>Obviously, I cannot share the data here, but do you see a bottleneck in my code? Could it be the <code>arange</code> thing? There are many <code>name-productid</code> combinations in the data.</p>

<p>Minimal Working Example:</p>

<pre><code>import pandas as pd
import numpy as np

reshaped= pd.DataFrame({'trader' : ['a','a','a','a','a','a','a'],'stock' : ['a','a','a','a','a','a','b'], 'day' :[0,1,2,4,5,10,1],'delta':[10,-10,15,-10,-5,5,0] ,'out': [1,1,2,2,2,0,1]})


reshaped.sort_values(by=['trader', 'stock','day'], inplace=True)
reshaped['cumq']=reshaped.groupby(['trader', 'stock']).delta.transform('cumsum')
reshaped['_spell']=reshaped.groupby(['trader','stock'])[['cumq']].apply(proc_trader).reset_index()['_seq']
</code></pre>