Check if NaT changes to datetime and update value

user_11315276 0
Viewed 46

I have the following data frames.

df_1:

order_id   date
123        2020-01-01
456        NaT
789        2020-10-10
135        2020-05-31
234        NaT
111        NaT

df_2:

order_id   date
123        2020-01-02
456        2021-01-01
789        2020-10-11
135        2020-6-01

The output should capture anytime the date changes to a date in the future from the previous entry and/or when NaT changes to a new date.

new_df should equal:

order_id   date
123        2020-01-02
456        2021-01-01
789        2020-10-11
135        2020-6-01

What I have tried:

df_1['date'] = pd.to_datetime(
    df_1['date'])
df_2['date'] = pd.to_datetime(
    df_2['date'])
s = df_2.set_index('order_id')['date']

mapped = df_1['order_id'].map(s)
mask = mapped > df_1['date']
df_1.loc[mask, 'date'] = mapped

This is only giving changes when the date is changed to a future date but it is not capturing anytime NaT becomes a new date.

pandas series dataframe python-3.x
2 Answers

Use np.where after coercing the dates to datetime.

import numpy as np
df_1['date']=pd.to_datetime(df_1['date'])
df_2['date']=pd.to_datetime(df_2['date'])
df=pd.merge(df_2,df_1, how='left', on='order_id',suffixes=('_left', ''))
df=df.assign(date=np.where(df['date'].isna()|df['date_left'].sub(df['date']).dt.days.gt(0),df['date_left'],df['date'])).drop('date_left',1)



  order_id       date
0       123 2020-01-02
1       456 2021-01-01
2       789 2020-10-11
3       135 2020-06-01
user_89869750

You can merge, which subsets to only those orders that might have been updated then check if the date is later, or if it was null then filled, and use that to subset the merged result.

df_1['date'] = pd.to_datetime(df_1['date'])
df_2['date'] = pd.to_datetime(df_2['date'])

res = df_1.merge(df_2, on='order_id', suffixes=['_orig', ''])
#   order_id  date_orig       date
#0       123 2020-01-01 2020-01-02
#1       456        NaT 2021-01-01
#2       789 2020-10-10 2020-10-11
#3       135 2020-05-31 2020-06-01

m = res['date'].gt(res['date_orig']) | (res['date_orig'].isnull() & res['date'].notnull())
res.loc[m, ['order_id', 'date']]

   order_id       date
0       123 2020-01-02
1       456 2021-01-01
2       789 2020-10-11
3       135 2020-06-01
user_43333590
Related