I have a pandas dataframe like below for the columns value_to_sum and indicator. I'd like to sum all values within value_to_sum up to and including the most recent value within that column where indicator == True. If indicator == False, I do not want to sum.
| row | value_to_sum | indicator | desired_outcome |
|---|---|---|---|
| 1 | 1 | True | NaN |
| 2 | 3 | True | 1 |
| 3 | 1 | False | NaN |
| 4 | 2 | False | NaN |
| 5 | 4 | False | NaN |
| 6 | 6 | True | 10 |
| 7 | 2 | True | 6 |
| 8 | 3 | False | NaN |
How can I achieve the values under desired_outcome?
You can set a group based on the .cumsum() of True values of column indicator and then use .groupby() together with .transform() to get the sum of value_to_sum of each group.
Then, for indicator == True, since the desired outcome is up to the previous row, we get the value of desired_outcome from last row by using .shift(). At the same time, for indicator == False, we set the value of desired_outcome to NaN. These last 2 steps are done altogether by a call to np.where().
df['desired_outcome'] = df.assign(group=df['indicator'].cumsum()).groupby('group')['value_to_sum'].transform('sum')
df['desired_outcome'] = np.where(df['indicator'], df['desired_outcome'].shift(), np.nan)
Result:
print(df)
row value_to_sum indicator desired_outcome
0 1 1 True NaN
1 2 3 True 1.0
2 3 1 False NaN
3 4 2 False NaN
4 5 4 False NaN
5 6 6 True 10.0
6 7 2 True 6.0
7 8 3 False NaN
value_to_sumon that same row within the sum underdesired_outcome. But the sum should be inclusive of the last row whereindicator == True. So "most recent" means the most recent row previous to the row we're at.