pythonpandasdataframegroup-by

Conditional running total based on date field in Pandas


I have a dataframe with below data.

DateTime Tag Qty
2025-01-01 13:00 1 270
2025-01-03 13:22 1 32
2025-01-10 12:33 2 44
2025-01-22 10:04 2 120
2025-01-29 09:30 3 182
2025-02-02 15:05 1 216

To be achieved: 2 new columns, first with cumulative sum of Qty until the DateTime on each row when Tag is not equal to 2, second with cumulative sum of Qty until the DateTime on each row when Tag is equal to 2. Below is the result I am looking for.

DateTime Tag Qty RBQ RSQ
2025-01-01 13:00 1 270 270 0
2025-01-03 13:22 1 32 302 0
2025-01-10 12:33 2 44 302 44
2025-01-22 10:04 2 120 302 164
2025-01-29 09:30 3 182 484 164
2025-02-02 15:05 1 216 600 164

I've been searching for a method, but doesn't seem to be getting it right. May I please get help on getting it right?

Thanks,


Solution

  • Just filter by Tag column and cumsum():

    df["RBQ"]=df[df["Tag"]!=2]["Qty"].cumsum()
    df["RSQ"]=df[df["Tag"]==2]["Qty"].cumsum()
    df1=df.ffill().fillna(0)
    df1.to_markdown()
    
    DateTime Tag Qty RBQ RSQ
    0 2025-01-01 13:00 1 270 270 0
    1 2025-01-03 13:22 1 32 302 0
    2 2025-01-10 12:33 2 44 302 44
    3 2025-01-22 10:04 2 120 302 164
    4 2025-01-29 09:30 3 182 484 164
    5 2025-02-02 15:05 1 216 700 164