title: Dataframe cell to be locked and used for a running balance calculation (follow up question)
body: <p>(This is a follow up question to <a href="https://stackoverflow.com/questions/60445075/dataframe-cell-to-be-locked-and-used-for-a-running-balance-calculation-condition">my previous question</a> which was answered correctly).</p>
  
  <p>Say I have the following dataframe</p>
  
  <pre><code>import pandas as pd
  df = pd.DataFrame()
  
  df['E'] = ('SIT','SCLOSE', 'SHODL', 'SHODL', 'SHODL', 'SHODL', 'SHODL', 'SHODL','SHODL','SCLOSE_BUY','BCLOSE_SELL', 'BHODL', 'BHODL', 'BHODL', 'BHODL', 'BHODL', 'BHODL','BUY','SIT','SIT')
  
  df['F'] = (0.00,1.00,10.00, 5.00,6.00,-6.00, 6.00, 2.00,10.00,10.00,-8.00,33.00,-15.00,6.00,-1.00,5.00,10.00,0.00,0.00,0.00)
  
  df.loc[19, 'G'] = 100.0000
  </code></pre>
  
  <p>With column G starting at 100 the same rules apply per my previous question whereby if a BUY or SELL occurs on col E the corresponding balance on column G is locked and continually used as the base amount to calculate a running balance with col F being the % increase/decrease for each row in the running balance until a BCLOSE or SCLOSE on col E is shown.  </p>
  
  <p>I have explained the rules in the previous question however new to this question is if a SCLOSE_BUY is shown the SELL is closed and a BUY is opened and vise versa for a BCLOSE_SELL. A BCLOSE, SCLOSE, SCLOSE_BUY or BCLOSE_SELL row all become the final row for the running balance calculation 
  and will be used as the base when a BUY or SELL is shown next</p>
  
  <p>FYI the a successful response to my previous question was made by Andy L. as follows however this response cannot handle the new scenario when a BCLOSE_SELL and a SCLOSE_BUY occur after one another</p>
  
  <pre><code>df1 = df[::-1]
  s = df1.B.isin(['BCLOSE','SCLOSE']).shift(fill_value=False).cumsum()
  grps = df1.groupby(s)
  init_val= 100
  l = []
  for _, grp in grps:
      s = grp.C * 0.01 * init_val
      s.iloc[0] = init_val
      s = s.cumsum()
      init_val = s.iloc[-1]
      l.append(s)
  </code></pre>
  
  <p>The above answer does not address the problem I encounter in real life whereby instead of occuring a BCLOSE I instead receive a BCLOSE_SELL which basically turns the BUY into a SELL (ie. I close the BUY and open a SELL) which becomes the base amount for the ongoing rows.</p>
  
  <p>If the rows continued as SHODL's I am able to adjust the code so that the running balance is correctly calculated however if I subsequently receive a SCLOSE_BUY (as seen in row 9 of my dataframe) I need to make this row close the SELL and reopen a BUY and this row will also be the new base amount for
  my running balance.</p>
  
  <p>I understand this all sounds confusing as such the below column added to my above dataframe is what the result should look like.</p>
  
  <pre><code>df['G'] = (191.62,191.62,190.19,175.89,168.74,160.16,168.74,160.16,157.3,143,130,138,105,120,114,115,110,100,100,100)
  </code></pre>