我希望合并两个数据框,df1和df2,基于df2的行是否在df1的行后3-6个月的日期范围内。例如:
df1(每家公司都有季度数据):
company DATADATE
0 012345 2005-06-30
1 012345 2005-09-30
2 012345 2005-12-31
3 012345 2006-03-31
4 123456 2005-01-31
5 123456 2005-03-31
6 123456 2005-06-30
7 123456 2005-09-30
df2(对于每个公司,我都有可能在任何一天发生事件日期):
company EventDate
0 012345 2005-07-28 <-- won't get merged b/c not within date range
1 012345 2005-10-12
2 123456 2005-05-15
3 123456 2005-05-17
4 123456 2005-05-25
5 123456 2005-05-30
6 123456 2005-08-08
7 123456 2005-11-29
8 abcxyz 2005-12-31 <-- won't be merged because company not in df1
理想的合并数据框——将df2中的EventDates与df1中DATADATE的行相对应,且时间间隔为3-6个月(即1个季度)的行将被合并:
company DATADATE EventDate
0 012345 2005-06-30 2005-10-12
1 012345 2005-09-30 NaN <-- nan because no EventDates fell in this range
2 012345 2005-12-31 NaN
3 012345 2006-03-31 NaN
4 123456 2005-01-31 2005-05-15
5 123456 2005-01-31 2005-05-17
5 123456 2005-01-31 2005-05-25
5 123456 2005-01-31 2005-05-30
6 123456 2005-03-31 2005-08-08
7 123456 2005-06-30 2005-11-19
8 123456 2005-09-30 NaN
我正试图应用这个相关主题[根据不规则时间间隔合并pandas数据帧],通过给df1添加start_time和end_time列来表示DATADATE之后3个月(start_time)到6个月(end_time),然后使用np.searchsorted(),但这种情况有点棘手,因为我想按每个公司进行合并。