Calculate new value based on decreasing value
pandas, python
Solution
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])`.
Problem
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`.