我是一名有用的助手,可以为您翻译以下内容。此处为一个示例代码:
我有两个数据框,可以用以下 MWE 表示:
import pandas as pd
from datetime import datetime
import numpy as np
df_1 = pd.DataFrame(np.random.randn(9), columns = ['A'], index= [
datetime(2015,1,1,19,30,1,20),
datetime(2015,1,1,20,30,2,12),
datetime(2015,1,1,21,30,3,50),
datetime(2015,1,1,22,30,5,43),
datetime(2015,1,1,22,30,52,11),
datetime(2015,1,1,23,30,54,8),
datetime(2015,1,1,23,40,14,2),
datetime(2015,1,1,23,41,13,33),
datetime(2015,1,1,23,50,21,32),
])
df_2 = pd.DataFrame(np.random.randn(9), columns = ['B'], index= [
datetime(2015,1,1,18,30,1,20),
datetime(2015,1,1,21,0,2,12),
datetime(2015,1,1,21,31,3,50),
datetime(2015,1,1,22,34,5,43),
datetime(2015,1,1,22,35,52,11),
datetime(2015,1,1,23,0,54,8),
datetime(2015,1,1,23,41,14,2),
datetime(2015,1,1,23,42,13,33),
datetime(2015,1,1,23,56,21,32),
])
我希望将这两个数据框合并成一个,我知道可以使用以下代码实现:
In [21]: df_1.join(df_2, how='outer')
Out[21]:
A B
2015-01-01 18:30:01.000020 NaN -1.411907
2015-01-01 19:30:01.000020 0.109913 NaN
2015-01-01 20:30:02.000012 -0.440529 NaN
2015-01-01 21:00:02.000012 NaN -1.277403
2015-01-01 21:30:03.000050 -0.194020 NaN
2015-01-01 21:31:03.000050 NaN -0.042259
2015-01-01 22:30:05.000043 1.445220 NaN
2015-01-01 22:30:52.000011 -0.341176 NaN
2015-01-01 22:34:05.000043 NaN 0.905912
2015-01-01 22:35:52.000011 NaN -0.167559
2015-01-01 23:00:54.000008 NaN 1.289961
2015-01-01 23:30:54.000008 -0.929973 NaN
2015-01-01 23:40:14.000002 0.077622 NaN
2015-01-01 23:41:13.000033 -1.688719 NaN
2015-01-01 23:41:14.000002 NaN 0.178439
2015-01-01 23:42:13.000033 NaN -0.911314
2015-01-01 23:50:21.000032 -0.750953 NaN
2015-01-01 23:56:21.000032 NaN 0.092930
这不是我想要实现的。
我希望仅根据df_1的时间序列索引将df_2与df_1合并 - 其中“B”列中的值将是最接近df_1索引的时间的值。
我以前使用iterrows
和relativedelta
来实现过这个功能,如下所示:
for i, row in df_1.iterrows():
df_2_temp = df_2.copy()
df_2_temp['Timestamp'] = df_2_temp.index
df_2_temp['Time Delta'] = abs(df_2_temp['Timestamp'] - row.name).apply(lambda x: x.seconds)
closest_value = df_2_temp.sort_values('Time Delta').iloc[0]['B']
df_1.loc[row.name, 'B'] = closest_value
这个方法能够起作用,但速度很慢,而且我有非常大的数据框需要执行此操作。
是否有更快的解决方案?也许是Pandas内置的功能?