使用NaN值的两个数据帧的VLOOKUP

Question

我有以下数据帧df1

    name           mobile_no      
0   Hector ABC       123       
1   Hector ABC       287        
2   Jose JKD         567      
3   Luis AH          NaN      
4   Billy DH         NaN 
5   Harry AC         569

 

和另一个数据帧df2

    download_date  mobile_no      
0   2021-05-30       123        
1   2020-09-28       287      
2   2021-02-11       789        
3   2021-10-06       321        
4   2020-01-15       569      

如果df1手机号码匹配，我想返回df2的下载日期。执行pd.merge会使df1的行数增加一倍。有没有办法逐行检查并返回下载日期？ 我不能在df1中删除重复项（如果有的话），df1中有更多的列。我有点希望它像一个excelvlookup，通过简单地匹配查找值，返回该行所选列的结果。我试过这样的方法：

df1['download_date'] = np.where(df1.mobile_no == df2.mobile_no, df2.download_date, np.nan)

预期结果：

    name         mobile_no    download_date
0   Hector ABC      123        2021-05-30
1   John DYC        237        2020-09-28
2   Jose JKD        567           NaN
3   Luis AH         NaN           NaN
4   Billy DH        NaN           NaN
5   Harry AC        569        2020-01-15

Answer 1

你要找的是^{}：

df["download_date"] = df["mobile_no"].map(df2.set_index("mobile_no")["download_date"])
print (df)

         name  mobile_no download_date
0 Hector  ABC      123.0    2021-05-30
1 Hector  ABC      287.0    2020-09-28
2 Jose    JKD      567.0           NaN
3 Luis     AH        NaN           NaN
4 Billy    DH        NaN           NaN
5 Harry    AC      569.0    2020-01-15

Answer 2

merge与pd.concat

m = df1.mobile_no.isna()
merged_df = pd.concat([df1.loc[m], df1.loc[~m].merge(df2, on='mobile_no', how ='left')]).sort_index()

输出

         name  mobile_no download_date
0  Hector ABC        123    2021-05-30
1  Hector ABC        287    2020-09-28
2    Jose JKD        567           NaN
3     Luis AH       <NA>           NaN
3    Harry AC        569    2020-01-15
4    Billy DH       <NA>           NaN