python - KDB+ 像 asof 一样加入 pandas 中的时间序列数据？

Question

kdb+ 有一个 aj通常用于沿时间列连接表的函数。

这是一个例子，我有交易和报价表，我得到了每笔交易的现行报价。

q)5# t
time         sym  price size 
-----------------------------
09:30:00.439 NVDA 13.42 60511
09:30:00.439 NVDA 13.42 60511
09:30:02.332 NVDA 13.42 100  
09:30:02.332 NVDA 13.42 100  
09:30:02.333 NVDA 13.41 100  

q)5# q
time         sym  bid   ask   bsize asize
-----------------------------------------
09:30:00.026 NVDA 13.34 13.44 3     16   
09:30:00.043 NVDA 13.34 13.44 3     17   
09:30:00.121 NVDA 13.36 13.65 1     10   
09:30:00.386 NVDA 13.36 13.52 21    1    
09:30:00.440 NVDA 13.4  13.44 15    17

q)5# aj[`time; t; q]
time         sym  price size  bid   ask   bsize asize
-----------------------------------------------------
09:30:00.439 NVDA 13.42 60511 13.36 13.52 21    1    
09:30:00.439 NVDA 13.42 60511 13.36 13.52 21    1    
09:30:02.332 NVDA 13.42 100   13.34 13.61 1     1    
09:30:02.332 NVDA 13.42 100   13.34 13.61 1     1    
09:30:02.333 NVDA 13.41 100   13.34 13.51 1     1  

如何使用 pandas 执行相同的操作？我正在使用索引为 datetime64 的交易和报价数据框。

In [55]: quotes.head()
Out[55]: 
                              bid    ask  bsize  asize
2012-09-06 09:30:00.026000  13.34  13.44      3     16
2012-09-06 09:30:00.043000  13.34  13.44      3     17
2012-09-06 09:30:00.121000  13.36  13.65      1     10
2012-09-06 09:30:00.386000  13.36  13.52     21      1
2012-09-06 09:30:00.440000  13.40  13.44     15     17

In [56]: trades.head()
Out[56]: 
                            price   size
2012-09-06 09:30:00.439000  13.42  60511
2012-09-06 09:30:00.439000  13.42  60511
2012-09-06 09:30:02.332000  13.42    100
2012-09-06 09:30:02.332000  13.42    100
2012-09-06 09:30:02.333000  13.41    100

我看到 pandas 有一个 asof 函数，但它没有在 DataFrame 上定义，只在 Series 对象上定义。我想可以循环遍历每个 Series 并将它们一一对齐，但我想知道是否有更好的方法？

Answer 1

前段时间我写了一个广告不足的 ordered_merge 函数:

In [27]: quotes
Out[27]: 
                        time    bid    ask  bsize  asize
0 2012-09-06 09:30:00.026000  13.34  13.44      3     16
1 2012-09-06 09:30:00.043000  13.34  13.44      3     17
2 2012-09-06 09:30:00.121000  13.36  13.65      1     10
3 2012-09-06 09:30:00.386000  13.36  13.52     21      1
4 2012-09-06 09:30:00.440000  13.40  13.44     15     17

In [28]: trades
Out[28]: 
                        time  price   size
0 2012-09-06 09:30:00.439000  13.42  60511
1 2012-09-06 09:30:00.439000  13.42  60511
2 2012-09-06 09:30:02.332000  13.42    100
3 2012-09-06 09:30:02.332000  13.42    100
4 2012-09-06 09:30:02.333000  13.41    100

In [29]: ordered_merge(quotes, trades)
Out[29]: 
                        time    bid    ask  bsize  asize  price   size
0 2012-09-06 09:30:00.026000  13.34  13.44      3     16    NaN    NaN
1 2012-09-06 09:30:00.043000  13.34  13.44      3     17    NaN    NaN
2 2012-09-06 09:30:00.121000  13.36  13.65      1     10    NaN    NaN
3 2012-09-06 09:30:00.386000  13.36  13.52     21      1    NaN    NaN
4 2012-09-06 09:30:00.439000    NaN    NaN    NaN    NaN  13.42  60511
5 2012-09-06 09:30:00.439000    NaN    NaN    NaN    NaN  13.42  60511
6 2012-09-06 09:30:00.440000  13.40  13.44     15     17    NaN    NaN
7 2012-09-06 09:30:02.332000    NaN    NaN    NaN    NaN  13.42    100
8 2012-09-06 09:30:02.332000    NaN    NaN    NaN    NaN  13.42    100
9 2012-09-06 09:30:02.333000    NaN    NaN    NaN    NaN  13.41    100

In [32]: ordered_merge(quotes, trades, fill_method='ffill')
Out[32]: 
                        time    bid    ask  bsize  asize  price   size
0 2012-09-06 09:30:00.026000  13.34  13.44      3     16    NaN    NaN
1 2012-09-06 09:30:00.043000  13.34  13.44      3     17    NaN    NaN
2 2012-09-06 09:30:00.121000  13.36  13.65      1     10    NaN    NaN
3 2012-09-06 09:30:00.386000  13.36  13.52     21      1    NaN    NaN
4 2012-09-06 09:30:00.439000  13.36  13.52     21      1  13.42  60511
5 2012-09-06 09:30:00.439000  13.36  13.52     21      1  13.42  60511
6 2012-09-06 09:30:00.440000  13.40  13.44     15     17  13.42  60511
7 2012-09-06 09:30:02.332000  13.40  13.44     15     17  13.42    100
8 2012-09-06 09:30:02.332000  13.40  13.44     15     17  13.42    100
9 2012-09-06 09:30:02.333000  13.40  13.44     15     17  13.41    100

它可以很容易地(好吧，对于熟悉代码的人来说)扩展为模仿 KDB 的“左连接”。我意识到在这种情况下，前向填写贸易数据是不合适的；只是说明功能。