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?
Advertisement
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 |