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