I have unbalanced panel. Basically, someone will increase liquidity before decreasing that liquidity fully. I have groups (NF_TOKEN_ID). I want to first groupby and whenever ACTION is DECREASE_LIQUIDITY i want to replace the zero value with all the funds that have been withdrawn. This amount corresponds to however much has been added previously. So I will have to cumsum previous values if value from ACTION is INCREASE_LIQUIDITY. cumsum should only be applied to funds that have been added right before, not retrospectively.
My current df.
BLOCK_TIMESTAMP NF_TOKEN_ID ACTION LIQUIDITY AMOUNT0_ADJUSTED AMOUNT1_ADJUSTED
0 2023-01-19 11:43:23+00:00 417467.0 INCREASE_LIQUIDITY 2.0002500037479372e+16 0.0 999999.999999
1 2023-01-21 10:08:35+00:00 417467.0 DECREASE_LIQUIDITY 2.0002500037479372e+16 0.0 0.0
2 2023-01-23 17:43:23+00:00 417467.0 INCREASE_LIQUIDITY 1.9999500037496876e+16 1000000.0 0.0
3 2023-01-28 21:42:47+00:00 417467.0 DECREASE_LIQUIDITY 1.9999500037496876e+16 0.0 0.0
4 2023-01-31 09:20:11+00:00 417467.0 INCREASE_LIQUIDITY 2.001358136187257e+16 0.0 1000553.996968
5 2023-02-05 14:19:11+00:00 417467.0 DECREASE_LIQUIDITY 2.001358136187257e+16 0.0 0.0
6 2023-02-06 16:00:59+00:00 417467.0 INCREASE_LIQUIDITY 3.9510177985927736e+16 900000.0 1075372.476351
7 2023-02-11 16:21:47+00:00 417467.0 DECREASE_LIQUIDITY 3.9510177985927736e+16 0.0 0.0
8 2023-02-11 18:17:47+00:00 417467.0 INCREASE_LIQUIDITY 3.999900007499375e+16 2000000.0 0.0
9 2023-02-13 08:42:47+00:00 417467.0 DECREASE_LIQUIDITY 3.999900007499375e+16 0.0 0.0
10 2023-02-16 23:39:11+00:00 417467.0 INCREASE_LIQUIDITY 6.000384593243181e+16 3000267.297679 0.0
11 2023-02-18 13:02:47+00:00 417467.0 INCREASE_LIQUIDITY 2.000210525110979e+16 1000130.263937 0.0
64 2023-01-19 11:52:47+00:00 417520.0 INCREASE_LIQUIDITY 1.5233876511464717e+21 2360900.644245 17981.537918728
65 2023-01-19 11:52:47+00:00 417520.0 DECREASE_LIQUIDITY 1.5233876511464717e+21 0.0 0.0
66 2023-01-19 11:52:59+00:00 417521.0 INCREASE_LIQUIDITY 1e+19 0.05981737761 0.05981737761
81 2023-01-19 11:54:35+00:00 417537.0 INCREASE_LIQUIDITY 17130998133876.0 49.99712 0.02400335355
82 2023-01-23 07:29:23+00:00 417537.0 INCREASE_LIQUIDITY 28028281686564.0 121.373999 0.01412890286
83 2023-01-23 17:34:35+00:00 417537.0 INCREASE_LIQUIDITY 9508091561328.0 39.513265 0.00581565507
84 2023-01-25 00:55:47+00:00 417537.0 DECREASE_LIQUIDITY 54667371381768.0 0.0 0.0
Desired df
BLOCK_TIMESTAMP NF_TOKEN_ID ACTION LIQUIDITY AMOUNT0_ADJUSTED AMOUNT1_ADJUSTED
0 2023-01-19 11:43:23+00:00 417467.0 INCREASE_LIQUIDITY 2.0002500037479372e+16 0.0 999999.999999
1 2023-01-21 10:08:35+00:00 417467.0 DECREASE_LIQUIDITY 2.0002500037479372e+16 0.0 999999.999999
2 2023-01-23 17:43:23+00:00 417467.0 INCREASE_LIQUIDITY 1.9999500037496876e+16 1000000.0 0.0
3 2023-01-28 21:42:47+00:00 417467.0 DECREASE_LIQUIDITY 1.9999500037496876e+16 1000000.0 0.0
4 2023-01-31 09:20:11+00:00 417467.0 INCREASE_LIQUIDITY 2.001358136187257e+16 0.0 1000553.996968
5 2023-02-05 14:19:11+00:00 417467.0 DECREASE_LIQUIDITY 2.001358136187257e+16 0.0 1000553.996968
6 2023-02-06 16:00:59+00:00 417467.0 INCREASE_LIQUIDITY 3.9510177985927736e+16 900000.0 1075372.476351
7 2023-02-11 16:21:47+00:00 417467.0 DECREASE_LIQUIDITY 3.9510177985927736e+16 900000.0 1075372.476351
8 2023-02-11 18:17:47+00:00 417467.0 INCREASE_LIQUIDITY 3.999900007499375e+16 2000000.0 0.0
9 2023-02-13 08:42:47+00:00 417467.0 DECREASE_LIQUIDITY 3.999900007499375e+16 2000000.0 0.0
10 2023-02-16 23:39:11+00:00 417467.0 INCREASE_LIQUIDITY 6.000384593243181e+16 3000267.297679 0.0
11 2023-02-18 13:02:47+00:00 417467.0 INCREASE_LIQUIDITY 2.000210525110979e+16 1000130.263937 0.0
64 2023-01-19 11:52:47+00:00 417520.0 INCREASE_LIQUIDITY 1.5233876511464717e+21 2360900.644245 17981.537918728
65 2023-01-19 11:52:47+00:00 417520.0 DECREASE_LIQUIDITY 1.5233876511464717e+21 2360900.644245 17981.537918728
66 2023-01-19 11:52:59+00:00 417521.0 INCREASE_LIQUIDITY 1e+19 0.05981737761 0.05981737761
81 2023-01-19 11:54:35+00:00 417537.0 INCREASE_LIQUIDITY 17130998133876.0 49.99712 0.02400335355
82 2023-01-23 07:29:23+00:00 417537.0 INCREASE_LIQUIDITY 28028281686564.0 121.373999 0.01412890286
83 2023-01-23 17:34:35+00:00 417537.0 INCREASE_LIQUIDITY 9508091561328.0 39.513265 0.00581565507
84 2023-01-25 00:55:47+00:00 417537.0 DECREASE_LIQUIDITY 54667371381768.0 210.884384 0.04394791148
>Solution :
IIUC, you can use:
cols = ['AMOUNT0_ADJUSTED', 'AMOUNT1_ADJUSTED']
decl = df['ACTION'] == 'DECREASE_LIQUIDITY'
df.loc[decl, cols] = -(df.groupby(['NF_TOKEN_ID', decl.shift(fill_value=0).cumsum()])
[cols].cumsum()[decl])
Output:
>>> df[['NF_TOKEN_ID', 'ACTION'] + cols]
NF_TOKEN_ID ACTION AMOUNT0_ADJUSTED AMOUNT1_ADJUSTED
0 417467.0 INCREASE_LIQUIDITY 0.000000e+00 1.000000e+06
1 417467.0 DECREASE_LIQUIDITY -0.000000e+00 -1.000000e+06
2 417467.0 INCREASE_LIQUIDITY 1.000000e+06 0.000000e+00
3 417467.0 DECREASE_LIQUIDITY -1.000000e+06 -0.000000e+00
4 417467.0 INCREASE_LIQUIDITY 0.000000e+00 1.000554e+06
5 417467.0 DECREASE_LIQUIDITY -0.000000e+00 -1.000554e+06
6 417467.0 INCREASE_LIQUIDITY 9.000000e+05 1.075372e+06
7 417467.0 DECREASE_LIQUIDITY -9.000000e+05 -1.075372e+06
8 417467.0 INCREASE_LIQUIDITY 2.000000e+06 0.000000e+00
9 417467.0 DECREASE_LIQUIDITY -2.000000e+06 -0.000000e+00
10 417467.0 INCREASE_LIQUIDITY 3.000267e+06 0.000000e+00
11 417467.0 INCREASE_LIQUIDITY 1.000130e+06 0.000000e+00
64 417520.0 INCREASE_LIQUIDITY 2.360901e+06 1.798154e+04
65 417520.0 DECREASE_LIQUIDITY -2.360901e+06 -1.798154e+04
66 417521.0 INCREASE_LIQUIDITY 5.981738e-02 5.981738e-02
81 417537.0 INCREASE_LIQUIDITY 4.999712e+01 2.400335e-02
82 417537.0 INCREASE_LIQUIDITY 1.213740e+02 1.412890e-02
83 417537.0 INCREASE_LIQUIDITY 3.951326e+01 5.815655e-03
84 417537.0 DECREASE_LIQUIDITY -2.108844e+02 -4.394791e-02
EDIT
How do I set the values to negative when ACTION is DECREASE_LIQUIDITY?