| Status | Energy |
|---|---|
| 1 | 2 |
| 1 | 3 |
| 1 | 2 |
| 1 | 4 |
| 0 | 12 |
| 0 | 13 |
| 0 | 11 |
| 1 | 3 |
| 0 | 15 |
| 0 | 14 |
I have Dataframe with two columns: status and energy. I am trying to create a new column for energy loss based on the last occurrence of each status. I have already tried cumsum with resetting, but it’s not working. Looking for results like the below table. Just copying the Energy column value to result in column based on the last of each status occurrence.
|Status |Energy |Result_outcome|
|:---- |:---- |:---- |
|1 |2 |NaN |
|1 |3 |NaN |
|1 |2 |NaN |
|1 |4 |4 |
|0 |12 |NaN |
|0 |13 |NaN |
|0 |11 |11 |
|1 |3 |3 |
|0 |15 |NaN |
|0 |14 |14 |
>Solution :
Let us use shift to compare the current row and next row then update the values in result column based on the outcome of comparison
df.loc[df['Status'] != df['Status'].shift(-1), 'result'] = df['Energy']
Status Energy result
0 1 2 NaN
1 1 3 NaN
2 1 2 NaN
3 1 4 4.0
4 0 12 NaN
5 0 13 NaN
6 0 11 11.0
7 1 3 3.0
8 0 15 NaN
9 0 14 14.0