Calculate New Value Based On Decreasing Value

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

Solution 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]).

Solution 2:

Your idea with cumsum and diff works. It doesn't look too complicated; not sure if there's an even shorter solution. First, we compute the cumulative sum, operate on that, and then go back (diff is kinda sorta the inverse function of cumsum).

import math

c = values.cumsum() - ALLOWANCE
# now we've got [-15, -5, 20, 50]
c[c < 0] = 0 # negative values don't make sense here

# (c - c.shift(1)) # <-- what I had first: diff by accident

# it is important that we don't fill with 0, in case that the first
# value is greater than ALLOWANCE
c.diff().fillna(math.max(0, values[0] - ALLOWANCE))

Solution 3:

This is probably not so performant but at the moment this is a Pandas way of doing this using rolling_apply:

In [53]:

ALLOWANCE = 100
def reduce(x):
    global ALLOWANCE
    # short circuit if we've already reached 0
    if ALLOWANCE == 0:
        return x
    val = max(0, x - ALLOWANCE)
    ALLOWANCE = max(0, ALLOWANCE - x)
    return val

pd.rolling_apply(values, window=1, func=reduce)
Out[53]:
0     0
1     0
2    20
3    30
dtype: float64

Or more simply:

In [58]:

values.apply(reduce)
Out[58]:
0     0
1     0
2    20
3    30
dtype: int64

Solution 4:

It should work with a while loop :

ii = 0
while (ALLOWANCE > 0 and ii < len(values)):
    if (ALLOWANCE > values[ii]):
        ALLOWANCE -= values[ii]
        values[ii] = 0
    else:
        values[ii] -= ALLOWANCE
        ALLOWANCE = 0
    ii += 1