如何在Pandas中按规则计算rice列对应的Outcome列？

需求说明 #

需要为包含Rank和rice列的DataFrame生成Outcome列，规则如下：

• 仅当当前rice值为正，且其上方的rice值为负时，计算当前rice值加上之前连续所有负值的总和
• 若当前rice值为正且上方rice值也为正，则该单元格不生成Outcome值

示例输出如下：

Rank    rice    Outcome
------------------------
 1.00   -41.05  
 2.00   763.1   722.05
 3.00   -702.1  
 4.00   -96.35  
 5.00   -670.1  
 6.00   -699.6  
 7.00   -642.1  
 8.00   -31.05  
 9.00   -69.15  
 10.00  -49.15  
 11.00  -539.3  
 12.00  -30.35  
 13.00  -530.1  
 14.00  569.1   -133
 15.00  -600.1  
 16.00  601.1   1
 17.00  620.1   
 18.00  -46.65  
 19.00  -615.2  
 20.00  -581.8  
 21.00  -562.8  
 22.00  538.2   491.55
 23.00  79.85   
 24.00  -582.3  
 25.00  -615.1  
 26.00  22.85   102.7
 27.00  -583.5  
 28.00  -578.1  

原代码问题分析 #

原代码仅计算了当前正值与前一个值的差值，没有考虑连续多个负值的情况，逻辑不符合需求：

import pandas as pd

data = [['1',-41.05],['2',763.1],['3',-702.1],['4',-96.35],['5',-670.1],['6',-699.6],['7',-642.1],['8',-31.05],['9',-69.15],['10',-49.15],['11',-539.3],['12',-30.35],['13',-530.1],['14',569.1],['15',-600.1],['16',601.1],['17',620.1],['18',-46.65],['19',-615.2],['20',-581.8],['21',-562.8],['22',538.2],['23',79.85],['24',-582.3],['25',-615.1],['26',22.85],['27',-583.5],['28',-578.1]]

df=pd.DataFrame(data, columns=['Rank', 'rice'])
df.loc[df['rice'] >0 , 'outcome'] = df['rice']-df['rice'].shift()

正确实现代码 #

import pandas as pd

data = [['1',-41.05],['2',763.1],['3',-702.1],['4',-96.35],['5',-670.1],['6',-699.6],['7',-642.1],['8',-31.05],['9',-69.15],['10',-49.15],['11',-539.3],['12',-30.35],['13',-530.1],['14',569.1],['15',-600.1],['16',601.1],['17',620.1],['18',-46.65],['19',-615.2],['20',-581.8],['21',-562.8],['22',538.2],['23',79.85],['24',-582.3],['25',-615.1],['26',22.85],['27',-583.5],['28',-578.1]]

df = pd.DataFrame(data, columns=['Rank', 'rice'])
df['rice'] = df['rice'].astype(float)  # 确保数值类型正确

# 1. 标记rice的正负：1为正，-1为负
df['sign'] = df['rice'].apply(lambda x: 1 if x > 0 else -1)

# 2. 创建分组键：当sign变化时，组号递增，把连续同符号的行归为一组
df['group'] = (df['sign'] != df['sign'].shift()).cumsum()

# 3. 计算每个组的rice总和
group_sum = df.groupby('group')['rice'].transform('sum')

# 4. 仅满足条件的行赋值：当前rice为正，且前一个rice为负（即组内包含负值和最后一个正值）
df['Outcome'] = None
mask = (df['rice'] > 0) & (df['sign'].shift() == -1)
df.loc[mask, 'Outcome'] = group_sum[mask].round(2)  # 保留两位小数匹配示例

# 清理临时列
df = df.drop(['sign', 'group'], axis=1)

# 查看结果
print(df.to_string(index=False))

代码逻辑说明 #

1. 标记正负：用sign列区分每行rice的正负，方便后续分组
2. 分组逻辑：通过cumsum()生成组号，将连续的负值序列 + 后续第一个正值归为一个组，这样每个目标组都包含需要计算的所有连续负值和触发计算的正值
3. 计算总和：对每个组计算rice总和，这个总和正好是当前正值加上所有连续负值的结果（比如示例中第2行：763.1 + (-41.05) = 722.05）
4. 筛选赋值：只在当前值为正且前一个值为负的行，将组总和赋值给Outcome，其他行留空

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