Efficient way to store orderbook in Python

Question

I am using the Coinbase WebSocket API to extract real-time data about the orderbook for BTC-USD.

I am using the following code to store the snapshots of bids and asks and the changes to the orderbook everytime there is an update from the exchange.

import websocket,json
import pandas as pd
import numpy as np
from datetime import datetime, timedelta,timezone
from dateutil.parser import parse

pd.DataFrame(columns=['time','side','price','changes']).to_csv("changes.csv")
def on_open(ws):
    print('opened connection')
    
    subscribe_message ={
    "type": "subscribe",
    "channels": [
        {
            "name": "level2",
            "product_ids": [
                "BTC-USD"
            ]
        }
    ]
}
    print(subscribe_message)
    ws.send(json.dumps(subscribe_message))

timeZero = datetime.now(timezone.utc)
timeClose = timeZero+timedelta(seconds=61)
def on_message(ws,message):
    js=json.loads(message)
    #print([js['time'],js['trade_id'],js['last_size'],js['best_ask'],js['best_bid']])
    if js['type']=='snapshot':
        print('Start: ',timeZero)
        pd.DataFrame(js['asks'],columns=['price','size']).to_csv("snapshot_asks.csv")
        pd.DataFrame(js['bids'],columns=['price','size']).to_csv("snapshot_bids.csv")
    elif js['type']=='l2update':
        mydate=parse(js['time'])
        if mydate >= timeClose:
            print('Closing at ', mydate)
            ws.close()
        side = js['changes'][0][0]
        price = js['changes'][0][1]
        change = js['changes'][0][2]
        pd.DataFrame([[js['time'],side, price, change]],columns=['time','side','price','changes']).to_csv("changes.csv",mode='a', header=False)
    
    
    
socket = "wss://ws-feed.exchange.coinbase.com"
ws = websocket.WebSocketApp(socket,on_open=on_open, on_message=on_message)
ws.run_forever()

In this way, all the changes are saved in a csv file. This code runs for approximately 1 minute, but I would like to make it run for one day and then reconstruct the orderbook.

Once this is done, I want to analyze the orderbook every second to study what is the price impact of buying (or selling) some specific amount bitcoins.

Of course, this code creates a very huge file 'changes.csv', and if I try to make it run on AWS, the CPU usage reaches 90% after some time and the process gets killed. What is the most efficient way to store the orderbook at every second?

Answer 1

The best solution is to change how you're doing it completely:

1. Store the data before it even gets to your program and process it later. e.g. Use your program merely as a daemon to subscribe to the data. tcpdump everything that's incoming to the interface that you're receiving data on. Post-process it later. Chances are that tcpdump will be faster and have less CPU or memory overhead than anything you can write inside this Python application.

But if you have to use this Python program for whatever reason, e.g. out of convenience, then the 3 most obvious optimizations you can do are:

2. Reduce casting and function calls. Don't cast every event to a DataFrame and don't parse datetime on every message. pandas objects come with a lot of overhead that you don't need here if the purpose is simply to write to disk. Function calls are expensive in Python because each call creates a new stack frame. If you have to, cast a large number of records at once. Or better, just write straight to disk without the overhead of marshalling a pandas object. Process the datetime in batch afterwards if you have to.

3. Buffered writing. Keep in scope a file handler object, e.g. f = open(fname, 'w') and f.write() the data inside your message handler. By default, Python will buffer the writes with your OS default buffer size. e.g. Create a class, LOBWriter and encapsulate on_message() as a class method of it; create the file handler as an instance attribute inside __init__ when you instantiate LOBWriter. Remember to manage this object and close it before your program ends.

4. Use a faster JSON decoder. e.g. UltraJSON.