非平衡面板分组后按条件处理流动性值：基于cumsum实现

问题 #

我有一组非平衡面板数据，业务逻辑是用户先增加流动性，之后全额提取该流动性。数据按NF_TOKEN_ID分组，需要执行以下操作：

• 当ACTION字段值为DECREASE_LIQUIDITY时，将对应的AMOUNT0_ADJUSTED和AMOUNT1_ADJUSTED字段的0值替换为本次提取前所有INCREASE_LIQUIDITY操作的累计金额
• 注意：累计求和仅针对本次提取前的新增资金，不追溯更早的记录（即每次提取后，后续的新增资金重新累计）

原始数据 #

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

期望结果 #

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

解决方案 #

通过分组创建累计周期、计算周期内累计金额，再替换目标字段的方式实现，具体代码如下：

import pandas as pd

# 假设原始数据已加载到df中
# 1. 按NF_TOKEN_ID分组，为每个分组内的记录创建"周期标记"：每次DECREASE_LIQUIDITY后，后续INCREASE进入新周期
df['cycle'] = df.groupby('NF_TOKEN_ID')['ACTION'].transform(
    lambda x: x.eq('DECREASE_LIQUIDITY').cumsum()
)

# 2. 按NF_TOKEN_ID和cycle分组，计算每个周期内AMOUNT0和AMOUNT1的累计值（仅统计INCREASE记录）
amount_cols = ['AMOUNT0_ADJUSTED', 'AMOUNT1_ADJUSTED']
for col in amount_cols:
    df[f'cum_{col}'] = df.groupby(['NF_TOKEN_ID', 'cycle'])[col].transform(
        lambda x: x.where(x.index == x.index.max(), x.cumsum())
    )

# 3. 替换DECREASE_LIQUIDITY记录中的0值为对应周期的累计金额
for col in amount_cols:
    df[col] = df.apply(
        lambda row: row[f'cum_{col}'] if row['ACTION'] == 'DECREASE_LIQUIDITY' and row[col] == 0 else row[col],
        axis=1
    )

# 4. 清理临时列
df.drop(columns=['cycle', 'cum_AMOUNT0_ADJUSTED', 'cum_AMOUNT1_ADJUSTED'], inplace=True)

print(df)

代码说明 #

1. 创建周期标记：通过对DECREASE_LIQUIDITY操作计数，把每个提取操作后的新增资金划分为