How to get values based on each last valid occurrence in Pandas dataframe

user_233347 0
Viewed 43
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           |
pandas dataframe numpy python
1 Answers

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
user_128331660
Related