基于累计抵扣总额从DataFrame中逐行扣除款项的实现需求

现有两个Pandas DataFrame：
df1结构如下：

import pandas as pd
df1 = pd.DataFrame({
    'name': ['virat', 'virat', 'virat', 'virat', 'virat', 'rohit', 'rohit'],
    'amount': [1000, 500, 200, 500, 150, 120, 100]})

df2结构如下（name列具有唯一性）：

df2 = pd.DataFrame({
    'name': ['virat', 'rohit'],
    'amount_paid': [1400, 200]})

需求：使用df2中每个name对应的amount_paid金额，按顺序从df1对应name的amount行中扣除，直至amount_paid金额耗尽。最终输出包含amount_left（该行剩余金额）和amount_used_to_knock_off（该行被抵扣的金额）的结果，预期输出如下：

name  amount_left  amount_used_to_knock_off
0  virat            0                       1000
1  virat          100                        400
2  virat          200                          0
3  virat          500                          0
4  virat          150                          0
5  rohit            0                        120
6  rohit           20                         80

通过分组计算累计金额，结合条件判断实现逐行抵扣逻辑，代码如下：

# 1. 合并df1与df2，将每个name的总抵扣金额关联到对应行
df_merged = df1.merge(df2, on='name', how='left')

# 2. 按name分组，计算每行的累计amount，以及上一行的累计amount
df_merged['cum_amount'] = df_merged.groupby('name')['amount'].cumsum()
df_merged['prev_cum_amount'] = df_merged['cum_amount'].shift(1).fillna(0)
# 修正分组内第一行的上一行累计值为0
df_merged['prev_cum_amount'] = df_merged.groupby('name')['prev_cum_amount'].transform(lambda x: x.fillna(0))

# 3. 定义函数计算每行的剩余金额和抵扣金额
def calculate_knock_off(row):
    total_paid = row['amount_paid']
    current_amount = row['amount']
    current_cum = row['cum_amount']
    prev_cum = row['prev_cum_amount']
    
    if current_cum <= total_paid:
        # 累计金额未超过总抵扣额，全额抵扣当前行金额
        used = current_amount
        left = 0
    elif prev_cum >= total_paid:
        # 上一行累计已耗尽总抵扣额，当前行无抵扣
        used = 0
        left = current_amount
    else:
        # 部分抵扣：总抵扣额减去上一行累计值为当前行抵扣金额
        used = total_paid - prev_cum
        left = current_amount - used
    return pd.Series([left, used], index=['amount_left', 'amount_used_to_knock_off'])

# 4. 应用函数并整理结果列
df_result = df_merged.join(df_merged.apply(calculate_knock_off, axis=1))
df_result = df_result[['name', 'amount_left', 'amount_used_to_knock_off']]

print(df_result)

• 数据合并：通过merge将df2的总抵扣额匹配到df1的对应行，确保每行都能获取所属name的抵扣总额。
• 累计金额计算：分组计算amount的累计和，用来判断当前行是否处于抵扣范围内；prev_cum_amount记录上一行的累计值，用于确定当前行的抵扣比例。
• 分情况抵扣：针对全额抵扣、无抵扣、部分抵扣三种场景，分别计算剩余金额与抵扣金额，覆盖所有可能的抵扣情况。
• 结果整理：提取需要的字段，得到符合预期的输出结构。

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