title: Rolling sum based on dates, adding in conditions that actively update values in Pandas Dataframe if met?
body: <p>I am calculating rolling last 180 day sales totals by ID in Python using Pandas and need to be able to update the last 180 day cumulative sales column if a user hits a certain threshold.  For example, if someone reaches $100 spent cumulatively in the last 180 days, their cumulative spend for that day should reflect them reaching that level and effectively "redeeming" that $100, leaving them only with the excess from the last visit as progress towards their next $100 hit. (See the example below) </p>
  
  <p>I also need to create a separate data frame during this process containing only the dates &amp; user_ids for when the $100 is met to keep track of how many times the threshold has been met across all users.</p>
  
  <p>I was thinking somehow I could use apply with conditional statements, but was not sure exactly how it would work as the data frame needs to be updated on the fly to have the rolling sums for later dates be calculated taking into account this updated total.  In other words, the cumulative sums for dates after they hit the threshold need to be adjusted for the fact that they "redeemed" the $100.</p>
  
  <p>This is what I have so far that gets the rolling cumulative sum by user.  I don't know if its possible to chain conditional methods with apply to this or what the best way forward is.</p>
  
  <pre class="lang-py prettyprint-override"><code>order_data['rolling_sales_180'] = order_data.groupby('user_id').rolling(window='180D', on='day')['sales'].sum().reset_index(drop=True)
  </code></pre>
  
  <p>See the below example of expected results. In row 6, the user reaches $120, crossing the $100 threshold, but the $100 is subtracted from his cumulative sum as of that date and he is left with $20 as of that date because that was the amount in excess of the $100 threshold that he spent on that day.  He then continues to earn cumulatively on this $20 for his subsequent visit within 180 days.  A user can go through this process many times, earning many rewards over different 180 day periods.</p>
  
  <pre><code>print(order_data)
  
           day  user_id   sales  \
  0 2017-08-10      1      10   
  1 2017-08-22      1      10   
  2 2017-08-31      1      10   
  3 2017-09-06      1      10   
  4 2017-09-19      1      10   
  5 2017-10-16      1      30   
  6 2017-11-28      1      40   
  7 2018-01-22      1      10   
  8 2018-03-19      1      10   
  9 2018-07-25      1      10   
  
     rolling_sales_180  
  0              10  
  1              20  
  2              30  
  3              40  
  4              50  
  5              80  
  6              20  
  7              30  
  8              40  
  9              20
  </code></pre>
  
  <p>Additionally, as mentioned above, I need a separate data frame to be created throughout this process with the day, user_id, sales, and rolling_sales_180 that only includes all the days during which the $100 threshold was met in order to count the number of times this goal is reached.  See below:</p>
  
  <pre><code>print(threshold_reached)
  
           day  user_id   sales  rolling_sales_180
  0 2017-11-28      1      40        120
  .
  .
  .
  
  </code></pre>