Get value From Previous or Next Rows Based on Condition from two or more columns in Python

I am using Pandas Python to calculate timedelta between rows which when it uses previous or next rows is based on conditions.

My table is like this sampel table

I want to create timedelta column with this condition: it gets value from previous rows when STATUS > status_before and gets value from next rows when STATUS < status_after.

I tried several ways to do this, and mostly ended up with this error:

The truth value of a Series is ambiguous. Use a.empty, a.bool(),a.item(), a.any() or a.all().

this one is one example of what I did:

if db.STATUS == 0 and db.status_before == 1:
   db.delta = db['REPORTDATE'].shift()

Any solutions?

Answer

• to get better quality answers always provide your sample data as text not images
• np.where() allows you to use conditional logic in an assignment
• your logic does not specify what value to use when condition is not met, so I’ve used REPORTDATE

import numpy as np

df = pd.DataFrame({"REPORTDATE":pd.date_range("2021-06-18", freq="8h", periods=5), "NAMA":["WPP 571"]*5, 
              "PELABUHAN":(["PP Belawan","di laut"]*3)[1:6], "STATUS":([1,0]*3)[0:5],
             "ASAL":(["PP Belawan","di laut"]*3)[0:5], "IGNORED":[0]*5,
             "INSERTTIME":[pd.to_datetime("2021-06-24 09:26:35")]*5})



(
    df.assign(
        status_after=df["STATUS"].shift(-1), status_before=df["STATUS"].shift()
    ).assign(
        timedelta=lambda d: np.where(
            (d.STATUS == 0) & (d["status_before"] == 1),
            d["REPORTDATE"].shift(),
            d["REPORTDATE"],
        )
    )
)

output

	REPORTDATE	NAMA	PELABUHAN	STATUS	ASAL	IGNORED	INSERTTIME	status_after	status_before	timedelta
0	2021-06-18 00:00:00	WPP 571	di laut	1	PP Belawan	0	2021-06-24 09:26:35	0	nan	2021-06-18 00:00:00
1	2021-06-18 08:00:00	WPP 571	PP Belawan	0	di laut	0	2021-06-24 09:26:35	1	1	2021-06-18 00:00:00
2	2021-06-18 16:00:00	WPP 571	di laut	1	PP Belawan	0	2021-06-24 09:26:35	0	0	2021-06-18 16:00:00
3	2021-06-19 00:00:00	WPP 571	PP Belawan	0	di laut	0	2021-06-24 09:26:35	1	1	2021-06-18 16:00:00
4	2021-06-19 08:00:00	WPP 571	di laut	1	PP Belawan	0	2021-06-24 09:26:35	nan	0	2021-06-19 08:00:00