Replace empty datetime value with other datetime value from same dataframe based on some condition

Question

I have a dataframe:

                  login   Status               start
0   2021-05-28 09:29:35 Resolved                 NaT
1   2021-05-28 11:46:11   Closed                 NaT
2   2021-05-29 15:59:16      WIP                 NaT
3   2021-05-30 10:43:57   Closed 2021-05-31 12:53:57
4   2021-06-27 17:53:29 Resolved                 NaT

I want to fill start value with login value if start is NULL and Status is either Resolved or Closed.
Expected DataFrame:

                  login   Status               start
0   2021-05-28 09:29:35 Resolved 2021-05-28 09:29:35
1   2021-05-28 11:46:11   Closed 2021-05-28 11:46:11
2   2021-05-29 15:59:16      WIP                 NaT
3   2021-05-30 10:43:57   Closed 2021-05-31 12:53:57
4   2021-06-27 17:53:29 Resolved 2021-06-27 17:53:29

iam unable to put condition for Null start values

I tried to make a function :

def fun(row):       
    if row.start.isna() and (row['Status'] == 'Resolved') or (row['Status' == 'Closed']):
        return row['start']
    else:
        return row['login']

and then using apply to run the function:

df['start'] = df.apply(fun, axis=1)

But iam getting error :

AttributeError: 'Timestamp' object has no attribute 'isna'

How can we get the above result

TIA

Same error > AttributeError: 'Timestamp' object has no attribute 'isnull' — Aezak
– Aezak, Commented Jun 28, 2021 at 9:45

Shubham Sharma · Accepted Answer · 2021-06-28 09:47:46Z

1

We can fill the NaT values in start after masking the values in login where the corresponding Status is not one of Closed, Resolved

m = df['Status'].isin(['Resolved', 'Closed'])
df['start'] = df['start'].fillna(df['login'].mask(~m))

  login                Status   start
0 2021-05-28 09:29:35  Resolved 2021-05-28 09:29:35
1 2021-05-28 11:46:11    Closed 2021-05-28 11:46:11
2 2021-05-29 15:59:16       WIP                 NaT
3 2021-05-30 10:43:57    Closed 2021-05-31 12:53:57
4 2021-06-27 17:53:29  Resolved 2021-06-27 17:53:29

answered Jun 28, 2021 at 9:47

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rob Raymond · Accepted Answer · 2021-06-28 09:50:11Z

Simple fillna() with .loc[]

df = pd.read_csv(io.StringIO("""                  login   Status               start
0   2021-05-28 09:29:35  Resolved                 NaT
1   2021-05-28 11:46:11   Closed                 NaT
2   2021-05-29 15:59:16      WIP                 NaT
3   2021-05-30 10:43:57   Closed  2021-05-31 12:53:57
4   2021-06-27 17:53:29  Resolved                 NaT"""), sep="\s\s+", engine="python")
df["login"] = pd.to_datetime(df["login"])
df["start"] = pd.to_datetime(df["start"])

df.loc[~df["Status"].eq("WIP"),"start"] = df.loc[~df["Status"].eq("WIP"),"start"].fillna(df["login"])

df

	login	Status	start
0	2021-05-28 09:29:35	Resolved	2021-05-28 09:29:35
1	2021-05-28 11:46:11	Closed	2021-05-28 11:46:11
2	2021-05-29 15:59:16	WIP	NaT
3	2021-05-30 10:43:57	Closed	2021-05-31 12:53:57
4	2021-06-27 17:53:29	Resolved	2021-06-27 17:53:29

Collectives™ on Stack Overflow

Replace empty datetime value with other datetime value from same dataframe based on some condition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related