Cumsum in Python/ Pandas with condition of resetting lower bound to zero

Question

I'm struggling to find the bug in my code, and would kindly seek your advise on how I can fix the issue and progress. Essentially, I'm trying to calculate cumulative sum of a Pandas DataFrame column. The condition is the cumulative sum output is reset to 0 when it falls to negative. The DF consistes of product type/ activity/ quantity (BUY: +ve/ SELL: -ve value). I'm providing the code to buid a simulated datagrame and the code I used to calculate the cumulative sum. However, I'm not really quite getting the output what I was expecting. The table also inlude 2 additional columns (desired_output & py_output) - formaer being the result I'd expect, and later being the output I see in Python from running my code. I'm using below code snippet to get the cumulative sum of the ['quantity'] column:

neg = df['quantity'] < 0
df['py_output'] = df['quantity'].groupby([neg[::-1].cumsum(),df['product']]).cumsum().clip(0)

Any advise/ suggestinos on what I'm getting wrong and what I can do to get the correct output would be greatly appreciated :-)

import pandas as pd


data = [['Product-1', 'Time-1', '1. BUY', 1395, 1395]
        , ['Product-1', 'Time-2', '2. SELL', -9684, 0]
        , ['Product-1', 'Time-3', '1. BUY', 1352, 1352]
        , ['Product-1', 'Time-4', '2. SELL', -1348, 4]
        , ['Product-1', 'Time-5', '1. BUY', 1951, 1955]
        , ['Product-1', 'Time-6', '2. SELL', -1947, 8]
        , ['Product-1', 'Time-7', '1. BUY', 2554, 2562]
        , ['Product-1', 'Time-8', '1. BUY', 714, 3276]
        , ['Product-1', 'Time-9', '1. BUY', 445, 3721]
        , ['Product-1', 'Time-10', '1. BUY', 2948, 6669]
        , ['Product-1', 'Time-11', '1. BUY', 1995, 8664]
        , ['Product-1', 'Time-12', '2. SELL', -4161, 4503]
        , ['Product-1', 'Time-13', '2. SELL', -4161, 342]
        , ['Product-1', 'Time-14', '2. SELL', -2895, 0]
        , ['Product-1', 'Time-15', '1. BUY', 186, 186]
        , ['Product-1', 'Time-16', '1. BUY', 2646, 2832]
        , ['Product-1', 'Time-17', '1. BUY', 2594, 5426]
        , ['Product-1', 'Time-18', '2. SELL', -3202, 2224]
        , ['Product-1', 'Time-19', '1. BUY', 4170, 6394]
        , ['Product-1', 'Time-20', '1. BUY', 1766, 8160]
        , ['Product-1', 'Time-21', '2. SELL', -4403, 3757]
        , ['Product-1', 'Time-22', '2. SELL', -3523, 234]
        , ['Product-1', 'Time-23', '1. BUY', 1403, 1637]
        , ['Product-1', 'Time-24', '1. BUY', 1566, 3203]
        , ['Product-1', 'Time-25', '2. SELL', -1357, 1846]
        , ['Product-1', 'Time-26', '2. SELL', -1566, 280]
        , ['Product-1', 'Time-27', '1. BUY', 791, 1071]
        , ['Product-1', 'Time-28', '1. BUY', 2384, 3455]
        , ['Product-1', 'Time-29', '1. BUY', 1292, 4747]
        , ['Product-1', 'Time-30', '1. BUY', 1343, 6090]
        , ['Product-1', 'Time-31', '1. BUY', 322, 6412]
        , ['Product-2', 'Time-1', '1. BUY', 1248, 1248]
        , ['Product-2', 'Time-2', '1. BUY', 3276, 4524]
        , ['Product-2', 'Time-3', '1. BUY', 707, 5231]
        , ['Product-2', 'Time-4', '2. SELL', -3534, 1697]
        , ['Product-2', 'Time-5', '1. BUY', 1358, 3055]
        , ['Product-2', 'Time-6', '1. BUY', 253, 3308]
        , ['Product-2', 'Time-7', '2. SELL', -1082, 2226]
        , ['Product-2', 'Time-8', '1. BUY', 238, 2464]
        , ['Product-2', 'Time-9', '1. BUY', 371, 2835]]

cols = ['product', 'time', 'activity', 'quantity', 'desired_output']
 
df = pd.DataFrame(data, columns=cols)
 
neg = df['quantity'] < 0
df['py_output'] = df['quantity'].groupby([neg[::-1].cumsum(),df['product']]).cumsum().clip(0)

print(df)

I researched through a number of references, including below Stackoverflow threads. However, unfortunately, I haven't been able to find a solution that would give me the right answer.

Python Pandas groupby limited cumulative sum

Cumsum on Pandas DF with reset to zero for negative cumulative values

maximdu · Accepted Answer · 2023-04-22T14:10:59.880

0

If performance/speed/efficiency in not very important for you, try using simple for loop:

cumsum = 0
result = []
for i in df["quantity"]:
    if cumsum + i < 0:
        cumsum = 0
    else:
        cumsum += i
    result.append(cumsum)
df["result"] = result

To calculate the sum for each product separately, you can use groupby with transform

def zero_bounded_cumsum(values):
    cumsum = 0
    result = []
    for i in values:
        if cumsum + i < 0:
            cumsum = 0
        else:
            cumsum += i
        result.append(cumsum)
    return result

df["result"] = df.groupby("product")["quantity"].transform(zero_bounded_cumsum)

edited Apr 22 '23 at 14:10

answered Apr 22 '23 at 13:04

maximdu

211
2

Hi, Thanks so much for the kind response. I'm looking to do the cumsum by ['product'] group. i.e. the counter will reset at 0 every time a new product starts. So, the final value for 'Product-1' will be 6421 (row 30), and in row 31 value will start with 1248 (=quantity). So, the number looks right. Many thanks! :-) – Nadeer Khan Apr 22 '23 at 13:56
Oh, that's right, I forgot about different products. I added this to my answer. – maximdu Apr 22 '23 at 14:12
This is what I was looking for! Really appreciate your time and help! Have a wonderful day. :-) – Nadeer Khan Apr 22 '23 at 14:16

Cumsum in Python/ Pandas with condition of resetting lower bound to zero

1 Answers1