New dataframe column that looks for value in a row and returns value from another column

user_11899030 0
Viewed 26

I have a Python dataframe and need to create a new column that returns a value from a row beneath the current row and in a different column. Below is the dataframe:

 event        event time  
 pass            10.10  
 failed block    10.20
 failed check    10.21  
 reception       10.25
 pass            17.60
 reception       17.65

I need to create a column called reception time. Reception time will look for the first instance of reception after each pass and return the event time of the reception as shown below:

 event         event time   reception time  
 pass            10.10          10.25  
 failed block    10.20            NA
 reception       10.25            NA  
 pass            17.60          17.65  
 reception       17.65            NA

There could be 50 or more lines in between pass and reception.

I'm new to Python and can't figure out a solution.

dataframe python
1 Answers

You can use two masks, bfill and where:

df['reception time'] = (df['event time']
                          .where(df['event'].eq('reception'))
                          .bfill()
                          .where(df['event'].eq('pass'))
                       )

output:

          event  event time  reception time
0          pass       10.10           10.25
1  failed block       10.20             NaN
2  failed check       10.21             NaN
3     reception       10.25             NaN
4          pass       17.60           17.65
5     reception       17.65             NaN
user_163434640
Related