如何在Pandas中实现Excel的Col1_loss与Col3_loss计算逻辑？

问题背景 #

现有如下Pandas DataFrame：

import pandas as pd

data = {
    "Col1": [57600, 42375, 417225, 30600, 60375],
    "Col2": [9300, 9400, 9500, 9600, 9700],
    "Col3": [802350, 950700, 2800875, 1170300, 1616175]
}
df = pd.DataFrame(data)

需要计算col1_loss和col3_loss两个新列，对应Excel中的计算逻辑：

• col1_loss 公式：

  =IFERROR($B2*SUM(A2:$A$2)-SUMPRODUCT($B2:B$2,$A2:A$2),0)
  

  （Excel中A列对应Col1，B列对应Col2）

• col3_loss 公式：

  =IFERROR(SUMPRODUCT($B2:B$14,$C2:C$14)-$B2*SUM($C2:C$14),0)
  

  （Excel中B列对应Col2，C列对应Col3，$B$14为数据最后一行）

注意：DataFrame行数为30-70的可变值，需适配任意行数。

解决方案 #

1. 计算col1_loss #

Excel公式核心逻辑：对当前行及以上所有行，计算当前行Col2 × 上方所有Col1的和减去Col2与Col1对应乘积的和，计算出错时返回0。

在Pandas中用expanding窗口实现，高效且适配任意行数：

df['col1_loss'] = (
    df['Col2'] * df['Col1'].expanding().sum()
    - (df['Col2'] * df['Col1']).expanding().sum()
).fillna(0)

2. 计算col3_loss #

Excel公式核心逻辑：对当前行及以下所有行，计算Col2与Col3对应乘积的和减去当前行Col2 × 下方所有Col3的和，计算出错时返回0。

通过反转DataFrame实现反向累积计算，再还原顺序：

# 反转DataFrame，计算反向expanding值
reversed_df = df[['Col2', 'Col3']][::-1].reset_index(drop=True)
sum_col3_rev = reversed_df['Col3'].expanding().sum()
sum_product_rev = (reversed_df['Col2'] * reversed_df['Col3']).expanding().sum()

# 计算反向的col3_loss后，还原为原顺序
col3_loss_rev = sum_product_rev - reversed_df['Col2'] * sum_col3_rev
df['col3_loss'] = col3_loss_rev[::-1].reset_index(drop=True).fillna(0)

完整验证代码 #

import pandas as pd

data = {
    "Col1": [57600, 42375, 417225, 30600, 60375],
    "Col2": [9300, 9400, 9500, 9600, 9700],
    "Col3": [802350, 950700, 2800875, 1170300, 1616175]
}
df = pd.DataFrame(data)

# 计算col1_loss
df['col1_loss'] = (
    df['Col2'] * df['Col1'].expanding().sum()
    - (df['Col2'] * df['Col1']).expanding().sum()
).fillna(0)

# 计算col3_loss
reversed_df = df[['Col2', 'Col3']][::-1].reset_index(drop=True)
sum_col3_rev = reversed_df['Col3'].expanding().sum()
sum_product_rev = (reversed_df['Col2'] * reversed_df['Col3']).expanding().sum()
col3_loss_rev = sum_product_rev - reversed_df['Col2'] * sum_col3_rev
df['col3_loss'] = col3_loss_rev[::-1].reset_index(drop=True).fillna(0)

print(df)

运行后会得到包含目标列的完整DataFrame，且代码支持任意行数的输入数据。

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