根据日期和序列号连接两个数据帧？

Question

我有两个数据帧，如下：

df1 = pd.DataFrame({'serialNo':['aaaa','bbbb','cccc','ffff','aaaa','bbbb','aaaa'],
               'Name':['Sayonti','Ruchi','Tony','Gowtam','Toffee','Tom','Sayonti'],
               'testName':   [4402, 3747 ,5555,8754,1234,9876,3602],
               'moduleName':   ['singing', 'dance','booze', 'vocals','drama','paint','singing'],
               'endResult': ['WARNING', 'FAILED', 'WARNING', 'FAILED','WARNING','FAILED','WARNING'],
               'Date':['2018-10-5','2018-10-6','2018-10-7','2018-10-8','2018-10-9','2018-10-10','2018-10-8']})`

df2 = pd.DataFrame({'serialNo':['aaaa','bbbb','aaaa','ffff','xyzy','aaaa'],
               'Food':['Strawberry','Coke','Pepsi','Nuts','Apple','Candy'],
               'Work':   ['AP', 'TC','OD', 'PU','NO','PM'],
               'Date':['2018-10-1','2018-10-6','2018-10-2','2018-10-3','2018-10-5','2018-10-10']
               })

我想加入这两个我可以通过这种方式实现的：

result = pd.merge(df1,df2,on=['serialNo','Date'],how='inner')

但我想对此做一些更改，即两个数据框连接在一起，对日期列进行一定的检查，即我想检查df2' date‘是否在df1'Date’的3天之内。我不想添加一个单独的列来检查这个条件，而是想在连接时检查这个条件。我该如何实现这一点？

Answer 1

您只能在serialNo上连接，然后在连接后过滤结果：

df1['Date'] = pd.to_datetime(df1['Date'])
df2['Date'] = pd.to_datetime(df2['Date'])

result = pd.merge(df1,df2,on='serialNo' ,how='inner')
result = result[result.Date_x.sub(result.Date_y).abs().dt.days.le(3)]

更新

根据您下面的注释，删除.abs()链接方法并使用.between()而不是.le()

result = result[result.Date_x.sub(result.Date_y).dt.days.between(0,3)]