Setting Cumulative Values To Constant After It Reaches Threshold

I have a pandas time series which contains cumulative monthly values. If in a month on a certain date, the value becomes a certain number, I need to set rest of the days to 1000. E

Solution 1:

Use groupby and cumprod:

df['cummulative_value'] = (df.groupby(df['Date'].dt.strftime('%Y%m'))['cummulative_value']
                            .transform(lambda x: np.where(x.ge(-71).cumprod(),x,1000)))
print(df)

Output:

Datecummulative_value02017-01-08                 -312017-01-09                 -622017-01-10               100032017-01-11               100042017-01-26               100052017-02-07                 -562017-02-14                 -672017-02-21                 -6

Solution 2:

Here's one approach that involves creating a mask:

df.set_index(pd.to_datetime(df['Date'], format="%m/%d/%Y"), inplace=True)

mask = df['cummulative_value'].lt(-71).groupby(df.index.month).cumsum()

# Date# 2017-01-08    False# 2017-01-09    False# 2017-01-10     True# 2017-01-11     True# 2017-01-26     True# 2017-02-07    False# 2017-02-14    False# 2017-02-21    False

df.loc[mask, 'cummulative_value'] = 1000

df.reset_index(drop=True)

#         Date  cummulative_value# 0   1/8/2017                 -3# 1   1/9/2017                 -6# 2  1/10/2017               1000# 3  1/11/2017               1000# 4  1/26/2017               1000# 5   2/7/2017                 -5# 6  2/14/2017                 -6# 7  2/21/2017                 -6