Pandas - Lookup date in another DF, if no date available then-CodePudding

**DF1 **

Unique IDs	Task	Date 1	Date 2
123	Track	28/05/2018	28/05/2018
455	Expire		07/02/2018
986	Sell	05/01/2020	28/05/2018

**DF2 **

Unique IDs
123
455
986

What I'd like to do is, look up the unique ID from DF2 and get the Date 1 that corresponds to the "Track" Task. If the Track task is empty then take the Date 2.

Desired Output

Unique IDs	Date
123	28/05/2018
455	07/02/2018
986	05/01/2020

Ideally I would like an if statement

CodePudding user response：

You can do it with a mask, to know if Date1 is nan or not and then set date column of df2 either with date1 values or date2 values.

df1 = pd.DataFrame({"id": [123, 455, 986],
                    "task": ["Track", "Expire", "Sell"],
                    "date1": [pd.Timestamp("28/05/2018"), np.nan, pd.Timestamp("05/01/2020")],
                    "date2": [pd.Timestamp("28/05/2018"), pd.Timestamp("07/02/2018"), pd.Timestamp("28/05/2018")]})

df2 = pd.DataFrame({"id": [123, 455, 986]})

mask = df1.loc[:, "date1"].notna()
df2.loc[mask, "date"] = df1.loc[mask, "date1"]
df2.loc[~mask, "date"] = df1.loc[~mask, "date2"]
print(df2)

   id       date
0  123 2018-05-28
1  455 2018-07-02
2  986 2020-05-01