python - 当前一个值为负时，熊猫重置 cumsum

Question

我需要对分组的数据帧执行累积求和，但是当前一个值为负且当前值为正时，我需要将其重置。

在 RI 中可以使用 ave() 函数对 groupby 应用条件，但我不能在 python 中这样做，所以我在思考解决方案时遇到了一些麻烦。谁能帮我吗？

这是一个示例：

import pandas as pd

df = pd.DataFrame({'PRODUCT': ['A'] * 40, 'GROUP': ['1'] * 40, 'FORECAST': [100, -40, -40, -40]*10, })

df['CS'] = df.groupby(['GROUP', 'PRODUCT']).FORECAST.cumsum()

# Reset cumsum if
# condition: (df.FORECAST > 0) & (df.groupby(['GROUP', 'PRODUCT']).FORECAST.shift(-1).fillna(0) <= 0)

标签： pythonpandasdataframe

Answer 1

此解决方案将用于重置任何要求和的值从负数变为正数的示例的总和（无论数据集是否像您的示例中那样良好和周期性）

import numpy as np
import pandas as pd

df = pd.DataFrame({'PRODUCT': ['A'] * 40, 'GROUP': ['1'] * 40, 'FORECAST': [100, -40, -40, -40]*10, })

cumsum = np.cumsum(df['FORECAST'])

# Array of indices where sum should be reset
reset_ind = np.where(df['FORECAST'].diff() > 0)[0]

# Sums that need to be subtracted at resets
subs = cumsum[reset_ind-1].values

# Repeat subtraction values for every entry BETWEEN resets and values after final reset
rep_subs = np.repeat(subs, np.hstack([np.diff(reset_ind), df['FORECAST'].size - reset_ind[-1]]))

# Stack together values before first reset and resetted sums
df['CS'] = np.hstack([cumsum[:reset_ind[0]], cumsum[reset_ind[0]:] - rep_subs])

或者，基于this solution to a similar question（以及我对有用性的认识groupby）

import pandas as pd
import numpy as np

df = pd.DataFrame({'PRODUCT': ['A'] * 40, 'GROUP': ['1'] * 40, 'FORECAST': [100, -40, -40, -40]*10, })

# Create indices to group sums together
df['cumsum'] = (df['FORECAST'].diff() > 0).cumsum()

# Perform group-wise cumsum
df['CS'] = df.groupby(['cumsum'])['FORECAST'].cumsum()

# Remove intermediary cumsum column
df = df.drop(['cumsum'], axis=1)