title: Pandas: conditional shift
body: <p>There is a way to shift a dataframe column dependently on the condition on two other columns? something like:</p>
  
  <pre><code>df["cumulated_closed_value"] = df.groupby("user").['close_cumsum'].shiftWhile(df['close_time']&gt;df['open_time])
  </code></pre>
  
  <p>I have figured out a way to do this but it's inefficient:</p>
  
  <p><strong>1)Load data and create the column to shift</strong> </p>
  
  <pre><code>df=pd.read_csv('data.csv')
  df.sort_values(['user','close_time'],inplace=True)
  df['close_cumsum']=df.groupby('user')['value'].cumsum()
  df.sort_values(['user','open_time'],inplace=True)
  print(df)
  </code></pre>
  
  <p>output:</p>
  
  <pre><code>   user  open_time close_time  value  close_cumsum
  0     1 2017-01-01 2017-03-01      5            18
  1     1 2017-01-02 2017-02-01      6             6
  2     1 2017-02-03 2017-02-05      7            13
  3     1 2017-02-07 2017-04-01      3            21
  4     1 2017-09-07 2017-09-11      1            22
  5     2 2018-01-01 2018-02-01     15            15
  6     2 2018-03-01 2018-04-01      3            18
  </code></pre>
  
  <p><strong>2) shift the column with a self-join and some filters</strong></p>
  
  <p>Self-join (this is memory inefficient)   <code>df2=pd.merge(df[['user','open_time']],df[['user','close_time','close_cumsum']], on='user')</code></p>
  
  <p>filter for 'close_time' &lt; 'open_time'. Then get the row with the max close_time</p>
  
  <pre><code>df2=df2[df2['close_time']&lt;df2['open_time']]
  idx = df2.groupby(['user','open_time'])['close_time'].transform(max) == df2['close_time']
  df2=df2[idx]
  </code></pre>
  
  <p><strong>3)merge with the original dataset:</strong></p>
  
  <pre><code>df3=pd.merge(df[['user','open_time','close_time','value']],df2[['user','open_time','close_cumsum']],how='left')
  print(df3)
  </code></pre>
  
  <p>output:</p>
  
  <pre><code>   user  open_time close_time  value  close_cumsum
  0     1 2017-01-01 2017-03-01      5           NaN
  1     1 2017-01-02 2017-02-01      6           NaN
  2     1 2017-02-03 2017-02-05      7           6.0
  3     1 2017-02-07 2017-04-01      3          13.0
  4     1 2017-09-07 2017-09-11      1          21.0
  5     2 2018-01-01 2018-02-01     15           NaN
  6     2 2018-03-01 2018-04-01      3          15.0
  </code></pre>
  
  <p><strong>There is a more pandas way to get the same result?</strong></p>
  
  <p><strong>Edit:</strong> I have added one data line to make the case more clear.
  My goal is to get the sum of all transactions closed before the opening time of the new transaction</p>