CODEWITHSUNDEEP
pythonpandas
Jon Clements
Jon Clements

Reputation: 142216

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 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:

Upvotes: 14

Views: 943

Answers (4)

Alex Riley
Alex Riley

Reputation: 176978

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

Upvotes: 9

EdChum
EdChum

Reputation: 394269

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

Upvotes: 5

Carsten
Carsten

Reputation: 18446

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))

Upvotes: 8

Math
Math

Reputation: 2446

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

Upvotes: 1

Related Questions