将 pandas 中一个时间序列的值替换为另一个时间序列的值

我有两个数据框：

s1: 
         time     X1
0  1234567000  96.32
1  1234567005  96.01
2  1234567009  96.05

s2: 
         time     X2
0  1234566999  23.88
1  1234567006  23.96

我想用第二个 DataFrame 替换第一个时间序列/DataFrame 的值，同时保留时间戳，以获得：

frame: 
         time     X2
0  1234567000  23.88
1  1234567005  23.88
2  1234567009  23.96

输出 （frame）应该有时间戳s1但值s2. time是整数（不是 UNIX 时间戳）。X1 and X2是浮动的。

有没有什么巧妙的方法可以用熊猫做到这一点？

我目前使用外连接/合并+ fillna +内连接/合并+ del 列链，但这似乎效率不高。

from __future__ import print_function
import pandas as pd

def merge_dataframes(s1, s2, common_column, back_fill=False, verbose=False):
    if verbose: print('s1: \n{0}'.format(s1))
    if verbose: print('s2: \n{0}'.format(s2))
    frame = pd.merge(s1,s2,how='outer').sort_values(by=common_column)
    if verbose: print('frame: \n{0}'.format(frame))
    frame.fillna(method='ffill', inplace=True)
    if verbose: print('frame: \n{0}'.format(frame))
    frame = pd.merge(frame,s1,how='inner').sort_values(by=common_column)
    if verbose: print('frame: \n{0}'.format(frame))        
    for column_name in s1.columns:
        if (column_name not in common_column) and (column_name not in s2.columns):
            del frame[column_name]
    if back_fill:
        frame.fillna(method='bfill', inplace=True)
        if verbose: print('frame: \n{0}'.format(frame))            
    return frame

def main():
    '''
    Demonstrate the use of merge_dataframes(s1, s2, common_column)
    '''
    s1 = pd.DataFrame({
        'time':[1234567000,1234567005,1234567009],
        'X1':[96.32,96.01,96.05]
    },columns=['time','X1'])  

    s2 = pd.DataFrame({
        'time':[1234566999,1234567006],
        'X2':[23.88,23.96]
    },columns=['time','X2'])  

    common_column = 'time'
    frame = merge_dataframes(s1, s2, common_column, verbose=True)
    print('frame: \n{0}'.format(frame))

if __name__ == "__main__":
    main()
    #cProfile.run('main()') # if you want to do some profiling

这是我的解决方案，我分解了步骤。

过去仅第一次搜索：

M1=pd.DataFrame({},index=df1.time,columns=df2.time)
M1=M1.apply(lambda x:x.index-x.name)
del M1.index.name
M2=M1.stack().reset_index()
M2=M2.loc[M2[0]>=0,]
M2[0]=abs(M2[0])
M2=M2.sort_values(['level_0',0]).drop_duplicates(['level_0'],keep='first')
df1.merge(M2,left_on='time',right_on='level_0',how='left').merge(df2,left_on='time_y',right_on='time').loc[:,['time_x','X1','X2']]
       time_x     X1     X2
0  1234567000  96.32  23.88
1  1234567005  96.01  23.88
2  1234567009  96.05  23.96

第二次搜索全部：唯一不同的是M2=M2.loc[M2[0]>=0,]

M1=pd.DataFrame({},index=df1.time,columns=df2.time)
M1=M1.apply(lambda x:x.index-x.name)
del M1.index.name
M2=M1.stack().reset_index()
#M2=M2.loc[M2[0]>=0,]
M2[0]=abs(M2[0])
M2=M2.sort_values(['level_0',0]).drop_duplicates(['level_0'],keep='first')
df1.merge(M2,left_on='time',right_on='level_0',how='left').merge(df2,left_on='time_y',right_on='time').loc[:,['time_x','X1','X2']]



Out[173]: 
       time_x     X1     X2
0  1234567000  96.32  23.88
1  1234567005  96.01  23.96
2  1234567009  96.05  23.96

通过使用更新itertools product

from itertools import product
import pandas as pd
DF=pd.DataFrame(list(product(df1.time, df2.time)), columns=['l1', 'l2'])
DF['DIFF']=DF.l1-DF.l2
DF=DF.loc[DF.DIFF>=0,]
DF=DF.sort_values(['l1','DIFF']).drop_duplicates(['l1'],keep='first')
df1.merge(DF,left_on='time',right_on='l1',how='left').merge(df2,left_on='l2',right_on='time').loc[:,['time_x','X1','X2']]


Out[357]: 
       time_x     X1     X2
0  1234567000  96.32  23.88
1  1234567005  96.01  23.88
2  1234567009  96.05  23.96