基于groupby过滤DataFrame：保留各symbol最后一次cml_units为0后的行

原始交易数据 #

import pandas as pd

# 原始DataFrame数据
data = {
    'symbol': ['BP.L', 'BP.L', 'AAPL', 'AAPL', 'AAPL', 'AAPL', 'AAPL', 'AAPL', 'AAPL', 'AAPL', 'AAPL', 'AAPL', 'AAPL', 'AAPL', 'BP.L', 'AAPL', 'AAPL', 'BP.L', 'AAPL'],
    'cml_units': [2, 0, 0, 6, 47, 0, 10, 3, 0, 0, 5, 0, 1, 0, 2, 2, 0, 1, 3],
    'number_of_shares': [2, -2, -3, 3, 47, -47, 7, 3, 3, 7, 5, -5, 1, -1, 2, 2, -2, -1, 3],
    'price': [504.8275, 504.2625, 142.45, 146.4, 171.52, 149.84, 140.09, 142.45, 138.34, 138.34, 138.34, 150.32, 150.27, 149.7, 562.4942, 149.7, 148.28, 562.185, 148.28],
    'time': ['2022-10-04 14:14:11', '2022-10-04 14:43:18', '2022-10-04 15:28:33', '2022-10-06 10:13:53', '2022-08-18 13:45:02', '2022-09-25 19:18:42', '2022-10-09 13:53:05', '2022-10-04 09:06:15', '2022-10-13 09:38:23', '2022-10-13 09:38:26', '2022-10-13 09:46:32', '2022-11-01 18:42:08', '2022-11-01 18:42:47', '2022-11-14 12:41:36', '2022-10-14 12:42:48', '2022-11-14 14:39:57', '2022-11-15 09:07:41', '2022-11-15 09:12:41', '2022-11-15 13:14:36'],
    'gain_loss': [0.00, -1.13, 0.00, 0.00, 0.00, -1018.96, 0.00, 0.00, -24.18, -12.25, 0.00, 59.90, 0.00, -0.57, 0.00, 0.00, -2.84, -0.31, 0.00],
    'cml_cost': [1009.65, -0.01, 284.93, 1151.51, 8061.44, 0.00, 1692.94, 712.31, 0.00, 0.00, 691.70, 0.00, 150.27, 0.00, 1124.98, 299.40, 0.00, 562.49, 444.84],
    'cash_flow': [-1009.65, 1008.52, 427.35, -439.20, -8061.44, 7042.48, -980.63, -427.35, 415.02, 968.38, -691.70, 751.60, -150.27, 149.70, -1124.99, -299.40, 296.56, 562.18, -444.84],
    'avg_price': [504.83, 0.00, 0.00, 191.92, 171.52, 0.00, 169.29, 142.46, 0.00, 0.00, 138.34, 0.00, 150.27, 0.00, 562.49, 149.70, 0.00, 562.49, 148.28]
}

df = pd.DataFrame(data)
# 转换时间列格式，确保排序逻辑正确
df['time'] = pd.to_datetime(df['time'])

需求说明 #

过滤每个股票（symbol）对应的最后一次cml_units为0（清仓状态）之前的所有行，仅保留最后一次清仓后的交易记录：

• BP.L最后一次清仓是2022-10-04 14:43:18，之后的交易全部保留
• AAPL最后一次清仓是2022-11-15 09:07:41，之后的交易全部保留

实现代码 #

def filter_post_last_zero(group):
    # 按交易时间排序，保证时间顺序正确
    group_sorted = group.sort_values('time').reset_index(drop=True)
    # 找出所有清仓（cml_units=0）的行索引
    zero_indices = group_sorted[group_sorted['cml_units'] == 0].index
    if len(zero_indices) == 0:
        # 没有清仓记录则返回全部行
        return group_sorted
    else:
        # 获取最后一次清仓的索引
        last_zero_idx = zero_indices[-1]
        # 返回最后一次清仓之后的所有行
        return group_sorted.loc[last_zero_idx+1:]

# 按股票分组应用过滤逻辑
filtered_df = df.groupby('symbol').apply(filter_post_last_zero).reset_index(drop=True)
# 手动调整索引与示例一致（可选，按需保留）
filtered_df = filtered_df.set_index(pd.RangeIndex(start=66, stop=66+len(filtered_df)))

print(filtered_df)

最终结果 #

symbol  cml_units  number_of_shares     price                time  gain_loss  cml_cost  cash_flow  avg_price
66       BP.L          2                 2  562.4942 2022-10-14 12:42:48       0.00   1124.98   -1124.99     562.49
67       BP.L          1                -1  562.1850 2022-11-15 09:12:41      -0.31    562.49     562.18     562.49
68       AAPL          3                 3  148.2800 2022-11-15 13:14:36       0.00    444.84    -444.84     148.28

内容的提问来源于stack exchange，提问作者originn