New column value based on two columns in df and possible NaN Value

Question

I am trying to create a new column based on a condition of two column timestamps such as below,

def time_delta(df):
if df['a_time'] > df['b_time']:
    res = 'Early'
else:
    res = 'Late'
return res

or

df['new_col'] = np.where(df['a_time'] > df['b_time'], 'Early', 'Late')

But the a_time column sometimes has a NaN value, I want the result or value in the new column if the a_time is NaN to be NaN as well how can I do this or tweak what I have for this?

Desired Output

     time_a           |         time_b          | new_column
2019-08-19 22:25:26.133   2019-08-19 23:00:00.000    Before_b
       NaN                2019-08-19 22:00:00.000     NaN
2019-08-19 23:00:00.000   2019-08-19 20:00:00.000    After_b

Thanks!

df['new_col'] = np.where(df['a_time'] > df['b_time'], 'Early', 'Late').mask(df['time_a'].isna()) ? — Scott Boston
– Scott Boston, Commented Sep 4, 2019 at 21:59

Hryhorii Pavlenko · Accepted Answer · 2019-09-04 21:59:42Z

2

You could use numpy.isnat to fix it.

df['new_col'] = np.where(df['a_time'] > df['b_time'], 'Early', 'Late')

df.loc[(np.isnat(df['a_time']) | np.isnat(df['b_time'])), 'new_col'] = np.NaN

answered Sep 4, 2019 at 21:59

Hryhorii Pavlenko

3,9104 gold badges21 silver badges38 bronze badges

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

1 Comment

Chris90 Over a year ago

Thanks for the response, so run the code I have then run the code you showed in the next line below?

Andy L. · Accepted Answer · 2019-09-04 23:42:09Z

1

You may do it with np.select

df['new_column'] = np.select([df.isna().any(1), df.time_a > df.time_b], [pd.NaT, 'Early'], 'Late')

Out[923]:
                   time_a              time_b new_column
0 2019-08-19 22:25:26.133 2019-08-19 23:00:00  Late
1 NaT                     2019-08-19 22:00:00  NaT
2 2019-08-19 23:00:00.000 2019-08-19 20:00:00  Early

answered Sep 4, 2019 at 23:42

Andy L.

25.3k4 gold badges20 silver badges30 bronze badges

Collectives™ on Stack Overflow

New column value based on two columns in df and possible NaN Value

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related