Python实现基于交易类型的客户级条件累积求和（含加减逻辑）

需在示例的df DataFrame中，按客户维度，基于交易类型执行累积计算操作：

• 交易类型为buy时，对count列进行累加；
• 交易类型为sell时，用前一次的累积值乘以percentage列的值，再从累积值中扣除该结果（即当前累积值 = 前一次累积值 × (1 - percentage)）。
  最终得到每个客户的最终持仓final_df。

生成代码 #

import pandas as pd

date = ['2022-01-04', '2022-01-04', '2022-01-04', '2022-01-04',
        '2022-01-08', '2022-01-09', '2022-01-09', '2022-01-10']
customer = [1, 1, 1, 1, 1, 1, 1, 1]
trading = ['buy', 'buy', 'buy', 'sell', 'sell', 'sell', 'buy', 'sell']
count = [1200, 1200, 2100, 0, 0, 0, 70, 0]
percentage = [0.0, 0.0, 0.0, 0.1, 0.2, 0.1, 0.0, 0.5]
process = ['1200',
           '1200 + 1200 = 2400',
           '2400 + 2100 = 4500',
           '4500 - 4500*0.1 = 4050',
           '4050 - 4050*0.2 = 3240',
           '3240 - 3240*0.1 = 2916',
           '2916 + 70 = 2986',
           '2986 - 2986*0.5 = 1493']
value = [1200, 2400, 4500, 4050, 3240, 2916, 2986, 1493]

data = {'date':date,'customer':customer,'trading':trading,
        'count':count,'percentage':percentage,
        'process':process,'value':value}
df = pd.DataFrame(data)

示例结果 #

目标最终结果 #

final_df = pd.DataFrame(df.iloc[-1:][['date','customer','value']])

# 创建前后交易类型列
df['t_prev'] = df.trading.shift(1)
df['t_next'] = df.trading.shift(-1)

# 组合当前-前-后交易类型的所有可能（共8种）
choi = [
    #1
    ((df.trading=='buy')&(df.t_prev=='buy')&(df.t_next=='buy')),
    #2
    ((df.trading=='buy')&(df.t_prev=='buy')&(df.t_next=='sell')),
    #3
    ((df.trading=='buy')&(df.t_prev=='sell')&(df.t_next=='buy')),
    #4
    ((df.trading=='buy')&(df.t_prev=='sell')&(df.t_next=='sell')),
    #5
    ((df.trading=='sell')&(df.t_prev=='buy')&(df.t_next=='buy')),
    #6
    ((df.trading=='sell')&(df.t_prev=='buy')&(df.t_next=='sell')),
    #7
    ((df.trading=='sell')&(df.t_prev=='sell')&(df.t_next=='buy')),
    #8
    ((df.trading=='sell')&(df.t_prev=='sell')&(df.t_next=='sell'))
]

cond = [
    #1
    df.groupby(['customer'])['count'].cumsum(),
    #2
    df.groupby(['customer'])['count'].cumsum(),
    #3
    df.groupby(['customer'])['count'].cumsum(),
    #4
    df.groupby(['customer'])['count'].cumsum(),
    #5
    (
         df.groupby(['customer'])['count'].cumsum().shift(1)\
         - (df.groupby(['customer'])['count'].cumsum().shift(1)*df['percentage'])
    ),
    #6
    (
         df.groupby(['customer'])['count'].cumsum().shift(1)\
         - (df.groupby(['customer'])['count'].cumsum().shift(1)*df['percentage'])
    ),
    #7
    (
         (
             df.groupby(['customer'])['count'].cumsum().shift(1)\
             - (df.groupby(['customer'])['count'].cumsum().shift(1)*df['percentage'])
         )\    

        -(
             df.groupby(['customer'])['count'].cumsum().shift(1)\
             - (df.groupby(['customer'])['count'].cumsum().shift(1)*df['percentage'])
         )*df['percentage']
    ),
    #8
    (
        (
            df.groupby(['customer'])['count'].cumsum().shift(1)\
            - (df.groupby(['customer'])['count'].cumsum().shift(1)*df['percentage'])
        )\
        
        -(
            df.groupby(['customer'])['count'].cumsum().shift(1)\
            - (df.groupby(['customer'])['count'].cumsum().shift(1)*df['percentage'])
        )*df['percentage']
    )
]

df['value'] = np.select(choi,cond,df.groupby(['customer'])['count'].cumsum())
final_df = pd.DataFrame(df.iloc[-1:][['date','customer','value']])

由于该累积计算依赖前一行的结果，属于迭代式计算，无法直接用cumsum等向量化方法实现，需对每个客户分组后逐行迭代计算：

代码实现 #

import pandas as pd

# 生成示例数据（可替换为真实数据）
date = ['2022-01-04', '2022-01-04', '2022-01-04', '2022-01-04',
        '2022-01-08', '2022-01-09', '2022-01-09', '2022-01-10']
customer = [1, 1, 1, 1, 1, 1, 1, 1]
trading = ['buy', 'buy', 'buy', 'sell', 'sell', 'sell', 'buy', 'sell']
count = [1200, 1200, 2100, 0, 0, 0, 70, 0]
percentage = [0.0, 0.0, 0.0, 0.1, 0.2, 0.1, 0.0, 0.5]

data = {'date':date,'customer':customer,'trading':trading,
        'count':count,'percentage':percentage}
df = pd.DataFrame(data)

# 定义持仓计算函数
def calculate_position(group):
    current_pos = 0
    values = []
    for _, row in group.iterrows():
        if row['trading'] == 'buy':
            current_pos += row['count']
        elif row['trading'] == 'sell':
            current_pos *= (1 - row['percentage'])
        values.append(current_pos)
    group['value'] = values
    return group

# 按客户分组计算持仓
df = df.groupby('customer', group_keys=False).apply(calculate_position)

# 提取最终结果
final_df = df.iloc[-1:][['date', 'customer', 'value']]
print(final_df)

运行结果 #

date  customer   value
7 2022-01-10         1  1493.0

方案说明 #

1. 分组计算：通过groupby('customer')确保每个客户的持仓计算独立进行，不会相互干扰；
2. 迭代更新：自定义函数calculate_position逐行遍历每个客户的交易记录，维护当前持仓current_pos，根据交易类型执行累加或减持操作；
3. 结果赋值：将每一步的持仓值存入列表，最终赋值回value列，提取最后一行得到客户的最终持仓。

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