根据时间戳合并pandas数据框

Question

有两个这样的数据帧

df1

Data  Value
0   2020-01-31 10:30:00      3
1   2020-01-31 11:30:00      4
2   2020-01-31 12:30:00     19
3   2020-01-31 13:30:00     22
4   2020-01-31 14:30:00      4
5   2020-01-31 15:30:00      5
6   2020-01-31 15:30:00      6
7   2020-01-31 16:30:00      7
8   2020-01-31 17:30:00      9
9   2020-01-31 18:30:00      7
10  2020-01-31 19:30:00      8

df2

DSTART                 DEnd Condition
0  2020-01-31 11:30:00  2020-01-31 12:30:00       BAD
1  2020-01-31 15:30:00  2020-01-31 16:30:00     ERROR

然后，我想连接df1和df2，但我不知道怎么做，因为在第二个df中我只有开始和结束时间。如何根据第二个df的数据将条件放在第一个df上?

这是我想要的

Data  Value Condition
0   2020-01-31 10:30:00      3        OK
1   2020-01-31 11:30:00      4       BAD
2   2020-01-31 12:30:00     19       BAD
3   2020-01-31 13:30:00     22       BAD
4   2020-01-31 14:30:00      4        OK
5   2020-01-31 15:30:00      5     ERROR
6   2020-01-31 15:30:00      6     ERROR
7   2020-01-31 16:30:00      7        OK
8   2020-01-31 17:30:00      9        OK
9   2020-01-31 18:30:00      7        OK
10  2020-01-31 19:30:00      8        OK

Answer 1

尝试pd.merge_asof:

# make sure that `Data`, `DSTART`, `DEnd` are datetime type
# use `pd.to_datetime` if needed
tmp = pd.merge_asof(df1,df2, left_on='Data', right_on='DSTART')
df1['Condition'] = tmp['Condition'].where(tmp['Data'] <= tmp['DEnd'], 'OK')

输出:

Data  Value Condition
0  2020-01-31 10:30:00      3        OK
1  2020-01-31 11:30:00      4       BAD
2  2020-01-31 12:30:00     19       BAD
3  2020-01-31 13:30:00     22        OK
4  2020-01-31 14:30:00      4        OK
5  2020-01-31 15:30:00      5     ERROR
6  2020-01-31 15:30:00      6     ERROR
7  2020-01-31 16:30:00      7     ERROR
8  2020-01-31 17:30:00      9        OK
9  2020-01-31 18:30:00      7        OK
10 2020-01-31 19:30:00      8        OK