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