Python中的Vlookup，在大型数据帧中具有近似匹配

Question

我有以下问题。

我有基本数据帧(df(：

询问<2th>数据<1th>两秒>>td>09:00-54>td>09:00-555701.0

类型		出价	Hora
买方	5711.0	5711.5	09:00:49	2022-01-07	09:00:51
卖方	5710.0	5710.5	09:00:52	2022-01-07
买方	5707.5	5708.0	09:00:53	2022-01-07
买方	5700.5	17:59:59	2022-01-14	18:00:01

这里使用的是模糊合并。尝试查看pd.merge_af.

您可以尝试以下操作：

# setup
import pandas as pd
import numpy as np
data = {'Type': {0: 'Buyer', 1: 'Seller', 2: 'Buyer', 3: 'Buyer'}, 
'Bid': {0: 5711.0, 1: 5710.0, 2: 5707.5, 3: 5700.5}, 
'Ask': {0: 5711.5, 1: 5710.5, 2: 5708.0, 3: 5701.0}, 
'Hora': {0: '09:00:49', 1: '09:00:52', 2: '09:00:53', 3: '17:59:59'}, 
'Data': {0: '2022-01-07', 1: '2022-01-07', 2: '2022-01-07', 
3: '2022-01-14'}, 
'Two Seconds': {0: '09:00:51', 1: '09:00:54', 2: '09:00:55', 
3: '18:00:01'}
}
df = pd.DataFrame(data)
data2 = {'Bid': {0: 5713.0, 1: 5708.0, 2: 5703.5}, 
'Ask': {0: 5713.5, 1: 5708.5, 2: 5704.0}, 
'Hora': {0: '09:00:51', 1: '09:00:55', 2: '18:00:00'}, 
'Data': {0: '2022-01-07', 1: '2022-01-07', 2: '2022-01-14'}
}
df2 = pd.DataFrame(data2)
# code
df['Two Seconds'] = pd.to_datetime(df['Two Seconds'], format='%H:%M:%S')
df2['Hora'] = pd.to_datetime(df2['Hora'], format='%H:%M:%S')
df = pd.merge_asof(df, df2, left_on='Two Seconds', 
right_on='Hora', suffixes=('','_y'))
df['Two Seconds'] = np.where(df['Type'] == 'Buyer', df['Ask_y'], df['Bid_y'])
df.drop([col for col in df.columns if col.endswith('_y')], axis=1, inplace=True)
print(df)
Type     Bid     Ask      Hora        Data  Two Seconds
0   Buyer  5711.0  5711.5  09:00:49  2022-01-07       5713.5
1  Seller  5710.0  5710.5  09:00:52  2022-01-07       5713.0
2   Buyer  5707.5  5708.0  09:00:53  2022-01-07       5708.5
3   Buyer  5700.5  5701.0  17:59:59  2022-01-14       5704.0