Calculate new value based on decreasing value

Question

Problem:

What'd I like to do is step-by-step reduce a value in a Series by a continuously decreasing base figure.

I'm not sure of the terminology for this - I did think I could do something with cumsum and diff but I think I'm leading myself on a wild goose chase there...

Starting code:

import pandas as pd

ALLOWANCE = 100
values = pd.Series([85, 10, 25, 30])

Desired output:

desired = pd.Series([0, 0, 20, 30])

Rationale:

Starting with a base of ALLOWANCE - each value in the Series is reduced by the amount remaining, as is the allowance itself, so the following steps occur:

• Start with 100, we can completely remove 85 so it becomes 0, we now have 15 left as ALLOWANCE
• The next value is 10 and we still have 15 available, so this becomes 0 again and we have 5 left.
• The next value is 25 - we only have 5 left, so this becomes 20 and now we have no further allowance.
• The next value is 30, and since there's no allowance, the value remains as 30.

Answer 1

Following your initial idea of cumsum and diff, you could write:

>>> (values.cumsum() - ALLOWANCE).clip_lower(0).diff().fillna(0)
0     0
1     0
2    20
3    30
dtype: float64

This is the cumulative sum of values minus the allowance. Negative values are clipped to zeros (since we don't care about numbers until we have overdrawn our allowance). From there, you can calculate the difference.

However, if the first value might be greater than the allowance, the following two-line variation is preferred:

s = (values.cumsum() - ALLOWANCE).clip_lower(0)
desired = s.diff().fillna(s)

This fills the first NaN value with the "first value - allowance" value. So in the case where ALLOWANCE is lowered to 75, it returns desired as Series([10, 10, 25, 30]).