How can I reference the line above a particular line in a pandas dataframe?

January 3, 2023

I have the following pandas dataframe:

issue_stat     timestamp   state
    0          11:00:00     hi
    1          12:40:00     lo
    9          13:00:00     av
    3          15:00:00     hi 
    8          18:00:00     hi
    4          20:00:00     lo

I want to map the state of the line above timestamp=18:00:00 to jazz. I MUST use the timestamp=18:00:00 in my code. How would I do this?

I know how to map the state of timestamp=18:00:00:

dataframe.loc[dataframe['timestamp'] == '18:00:00', 'state'] = whatever

But I am having difficult pointing to the line above it. Again I emphasise, I MUST reference the timestamp = 18:00:00 in my code.

So the output looks like this:

issue_stat     timestamp   state
     0          11:00:00     hi
     1          12:40:00     lo
     9          13:00:00     av
     3          15:00:00    jazz 
     8          18:00:00     hi
     4          20:00:00     lo

>Solution :

The shift() method moves the series in either direction. So to set the state of the cell where the following timestamp is 18:00:

df.loc[df["timestamp"].shift(-1) == '18:00:00', 'state'] = 'jazz'

Produces:

   issue_stat           timestamp    state
0           0            11:00:00       hi
1           1            12:40:00       lo
2           9            13:00:00       av
3           3            15:00:00     jazz
4           8            18:00:00       hi
5           4            20:00:00       lo