I have a dataframe with below data.

I have a dataframe with below data.

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.

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,

• python
• pandas
• dataframe
• group-by

2 Answers 2

You can use a condition and the methods mask and where to create both columns

cond = df['Tag'].eq(2)
df['RBQ'] = df['Qty'].mask(cond, 0).cumsum()
df['RSQ'] = df['Qty'].where(cond, 0).cumsum()

Another solution is to use the same condition and pivot the dataframe based on that.

df2 = (df.join(df.assign(cols=df['Tag'].eq(2).map({True: 'RSQ', False: 'RBQ'}))
               .pivot(columns='cols', values='Qty')
               .fillna(0).cumsum())
      )

End result:

        DateTime  Tag  Qty   RBQ   RSQ
2025-01-01 13:00    1  270 270.0   0.0
2025-01-03 13:22    1   32 302.0   0.0
2025-01-10 12:33    2   44 302.0  44.0
2025-01-22 10:04    2  120 302.0 164.0
2025-01-29 09:30    3  182 484.0 164.0
2025-02-02 15:05    1  216 700.0 164.0

Edit: Here is a slightly modified version that takes in consideration the DateTime column. I modified the first value in the datetime column as an example.

cond = df['Tag'].eq(2)
tmp = df.sort_values(by='DateTime')

df['RBQ'] = tmp['Qty'].mask(cond, 0).cumsum()
df['RSQ'] = tmp['Qty'].where(cond, 0).cumsum()

For the second solution, you will have to use merge instead of join.

df2 = pd.merge(df, (df.assign(cols=df['Tag'].eq(2).map({True: 'RSQ', False: 'RBQ'}))
                   .pivot(index='DateTime', columns='cols', values='Qty')
                   .fillna(0).cumsum()), on='DateTime')

End result:

           DateTime  Tag  Qty   RBQ   RSQ
2025-02-04 13:00:00    1  270 700.0 164.0
2025-01-03 13:22:00    1   32  32.0   0.0
2025-01-10 12:33:00    2   44  32.0  44.0
2025-01-22 10:04:00    2  120  32.0 164.0
2025-01-29 09:30:00    3  182 214.0 164.0
2025-02-02 15:05:00    1  216 430.0 164.0

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