Pandas: copy value from row above of another column when condition match

user_18582223 0
Viewed 281

When the column "event" has a value different than 0, and rows above from column "input1" has value 5, I need to copy the value from row above of column "label".

What I have now:

input1 input2  input3 event   label
     0      0       0     0       0
     5      5       0     0       2
     5      5       0     0       2
     0      0       0    24       0
     0      0       0     0       0
     5      0       5     0       3
     5      0       5     0       3
     5      0       5     0       3
     0      0       0    25       0
     0      0       0     0       0

What I need to happen:

input1 input2  input3 event   label  marker
     0      0       0     0       0       0
     5      5       0     0       2       0
     5      5       0     0       2       0
     0      0       0    24       0       2
     0      0       0     0       0       0
     5      0       5     0       3       0
     5      0       5     0       3       0
     5      0       5     0       3       0
     0      0       0    25       0       3
     0      0       0     0       0       0
pandas conditional-statements python
1 Answers

You can use boolean masks eq/ne, and shift to get the previous values, then select with where:

# is the previous input1 equal to 5?
m1 = df['input1'].shift().eq(5)
# is event not 0?
m2 = df['event'].ne(0)

# get the previous label if both conditions are true, else 0
df['marker'] = df['label'].shift(fill_value=0).where(m1&m2, 0)
# OR
# df['marker'] = df['label'].shift().where(m1&m2, 0).convert_dtypes()

output:

   input1  input2  input3  event  label  marker
0       0       0       0      0      0       0
1       5       5       0      0      2       0
2       5       5       0      0      2       0
3       0       0       0     24      0       2
4       0       0       0      0      0       0
5       5       0       5      0      3       0
6       5       0       5      0      3       0
7       5       0       5      0      3       0
8       0       0       0     25      0       3
9       0       0       0      0      0       0
user_163434640
Related