使用 Pandas 从订单的时间序列创建订单簿的快照？

Creating a snapshot of an order book from time series of orders using pandas?

提问by bpeikes

I'm fairly new to python and pandas, and I'm wondering if anyone knows if there are any libraries for python build on top of pandas which would take a time series of orders which have the following columns: timestamp, id, price, size, exchange

我对 python 和 Pandas 还很陌生，我想知道是否有人知道是否有任何基于 Pandas 的 python 构建库，这些库需要一个时间序列的订单，这些订单具有以下列：时间戳、ID、价格、大小，交换

Each record adjusts the total per price and exchange by the size to give you a current view, i.e. records might look like:

每条记录根据大小调整每个价格和交换的总数，以提供当前视图，即记录可能如下所示：

9:00:25.123, 1, 1.02, 100, N
9:00:25.123, 2, 1.02, -50, N
9:00:25.129, 3, 1.03,  50, X
9:00:25.130, 4, 1.02, 150, X
9:00:25.131, 5, 1.02,  -5, X

I want to be able, for any time, get the current view of the market. So for example if I made the call for the market at 9:00:25.130, I would get:

我希望能够随时了解当前的市场情况。例如，如果我在 9:00:25.130 向市场发出呼叫，我会得到：

1.02, N,  50
1.02, X, 150
1.03, X,  50

A query for 9:00:25.131 would return

对 9:00:25.131 的查询将返回

1.02, N,  50
1.02, X, 145
1.03, X,  50

There may be a million or more of these records, iterating over all of the records for every request would take a long time, particularly if you were trying to look at times later in on the day. I suppose one could create "snapshots" on some time interval and use them like key frames in mpeg playback, and I could code it myself, but I think that book building/ playback, is such a common need for folks using pandas with financial data that their might already be libraries out there to do this.

可能有一百万或更多这样的记录，为每个请求迭代所有记录需要很长时间，特别是如果您试图查看当天晚些时候的时间。我想人们可以在某个时间间隔创建“快照”，并将它们用作 mpeg 播放中的关键帧，我可以自己编写代码，但我认为书籍构建/播放对于使用 Pandas 和财务数据的人们来说是一种常见的需求他们可能已经是图书馆来做到这一点。

Any ideas, or do I roll my own?

有什么想法，还是我自己动手？

回答by mtd

I know this is old but it's instructive to see the benefits and limits of pandas

我知道这已经过时了，但了解大Pandas的好处和局限性很有启发性

I built a trivial jupyter notebookto show how an order book like you describe could be built to be used as you requested.

我构建了一个简单的 jupyter 笔记本来展示如何构建像您描述的订单簿以按您的要求使用。

The core is a loop that updates the state of the order book and saves it for amalgamation into a pandas Dataframe:

核心是一个循环，它更新订单簿的状态并将其保存以合并到 Pandas Dataframe 中：

states = []
current_timestamp = None
current_state = {}

for timestamp, (id_, price, exch, size) in df.iterrows():
    if current_timestamp is None:
        current_timestamp = timestamp
    if current_timestamp != timestamp:
        for key in list(current_state):
            if current_state[key] == 0.:
                del current_state[key]
        states.append((current_timestamp, dict(**current_state)))
        current_timestamp = timestamp
    key = (exch, price)
    current_state.setdefault(key, 0.)
    current_state[key] += size
states.append((timestamp, dict(**current_state)))

order_book = pd.DataFrame.from_items(states).T

However: note how the book state has to be built up outside of pandas, and that a pandas.DataFrame of order book state isn't so well suited to model order book per-level priority or depth (Level 3 data), which can be a major limitation depending on how accurately you want to model the order book.

但是：请注意如何在 pandas 之外构建图书状态，并且订单簿状态的 pandas.DataFrame 不太适合按级别优先级或深度（级别 3 数据）对订单簿进行建模，这可以是一个主要限制，具体取决于您希望对订单簿建模的准确程度。

Order books and the orders and quotes that update them (both of which you group into the term "request") in the real world have fairly complex interactions. These interactions are governed by the rules of the exchange that manages them, and these rules change all the time. Since these rules take time to model correctly, are worth understanding to very few, and old sets of rules are usually not even of much academic interest, the only places one would tend to find these rules codified into a library are the places not very interested in sharing them with others.

在现实世界中，订单簿以及更新它们的订单和报价（您将它们都归入术语“请求”中）具有相当复杂的交互作用。这些交互受管理它们的交易所规则的约束，并且这些规则一直在变化。由于这些规则需要时间来正确建模，很少有人值得理解，而且旧的规则集通常甚至没有太大的学术兴趣，人们倾向于发现这些规则被编入图书馆的唯一地方是不太感兴趣的地方与他人分享。

To understand the theory behind a simple ("stylised") model of an order book, its orders and quotes thereupon, see the paper "A stochastic model for order book dynamics" by Rama Cont, Sasha Stoikov, Rishi Talreja, Section 2:

要了解订单簿的简单（“风格化”）模型背后的理论、订单和报价，请参阅Rama Cont、Sasha Stoikov、Rishi Talreja的论文“订单簿动态的随机模型”，第 2 节：

2.1 Limit order books

Consider a financial asset traded in an order-driven market. Market participants can post two types of buy/sell orders. A limitorder is an order to trade a certain amount of a security at a given price. Limit orders are posted to a electronic trading system and the state of outstanding limit orders can be summarized by stating the quantities posted at each price level: this is known as the limit order book. The lowest price for which there is an outstanding limit sell order is called the ask priceand the highest buy price is called the bid price. [...more useful description]

2.2. Dynamics of the order book

Let us now describe how the limit order book is updated by the inflow of new orders. [...] Assuming that all orders are of unit size [...],

? a limit buy order at price level p<p_A(t) increases the quantity at level p: x → x_{p?1}

? a limit sell order at price level p>p_B(t) increases the quantity at level p: x → x_{p+1}

? a market buy order decreases the quantity at the ask price: x → x_{p_A(t)?1}

? a market sell order decreases the quantity at the bid price: x → x_{p_B(t)+1}

? a cancellation of an oustanding limit buy order at price level p<p_A(t) decreases the quantity at level p: x → x_{p+1}

? a cancellation of an oustanding limit sell order at price level p>p_B(t) decreases the quantity at level p: x → x_{p?1}

The evolution of the order book is thus driven by the incoming flow of market orders, limit orders and cancellations at each price level [...]

2.1 限价订单簿

考虑在订单驱动的市场中交易的金融资产。市场参与者可以发布两种类型的买/卖订单。一个限制定单是一种在给定的价格进行交易一定量的安全性。限价订单被发布到电子交易系统，未完成的限价订单的状态可以通过说明在每个价格水平发布的数量来概括：这被称为限价订单簿。有未完成限价卖单的最低价称为卖价，最高买价称为买价。[...更有用的描述]

2.2. 订单动态

现在让我们描述如何通过新订单的流入来更新限价订单簿。[...] 假设所有订单都是单位大小 [...]，

? 价格水平 p<p_A(t) 的限价买单增加水平 p 的数量：x → x_{p?1}

? 价格水平 p>p_B(t) 的限价卖单会增加水平 p 的数量：x → x_{p+1}

? 市价买单以卖价减少数量：x → x_{p_A(t)?1}

? 市价卖单以买入价减少数量：x → x_{p_B(t)+1}

? 在价格水平 p<p_A(t) 取消未完成的限价买单会减少水平 p 的数量：x → x_{p+1}

? 在价格水平 p>p_B(t) 取消未完成的限价卖单会减少水平 p 的数量：x → x_{p?1}

因此，订单簿的演变是由每个价格水平的市价订单、限价订单和取消订单流 [...]

Some libraries where you can see people's attempts at modeling or visualising a simple limit order book are:

您可以在其中看到人们尝试对简单的限价订单簿进行建模或可视化的一些库：

• PyRebuildLOBis a working example, but pandas plays a relatively small part in its implementation besides as a fancy array.
• Background and visualisation Analysing an Electronic Limit Order Bookby David Kane, Andrew Liu, and Khanh Ngyuen
• Usual visualisation from Oculus Information:
• Unusual visualisation from working4arbitrage:

• PyRebuildLOB是一个工作示例，但除了作为花哨的数组之外，pandas 在其实现中的作用相对较小。
• 背景和可视化分析电子限价订单簿David Kane、Andrew Liu 和 Khanh Ngyuen
• 来自Oculus 信息的常用可视化：
• 来自working4arbitrage 的不寻常的可视化：

And there is a good quant.stackoverflow.com question and answers here.

并有一个很好的quant.stackoverflow.com问题和答案在这里。