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

def on_open(ws):
    print('opened connection')
    subscribe_message ={
    "type": "subscribe",
    "channels": [
            "name": "level2",
            "product_ids": [

timeZero = datetime.now(timezone.utc)
timeClose = timeZero+timedelta(seconds=61)
def on_message(ws,message):
    if js['type']=='snapshot':
        print('Start: ',timeZero)
    elif js['type']=='l2update':
        if mydate >= timeClose:
            print('Closing at ', mydate)
        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)

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?

  • $\begingroup$ Many things are inefficient about this. 1. You seem to write to the file on every update (are you not even just overwriting the existing data?). Buffer your updates and write them in a chunk after reaching your buffer's capacity. 2. Use asyncio to not block on writing to disk or write in a different thread. 3. You go through overhead of constructing a pandas data-frame just for writing it to csv straight-away. 4. Why even go through the hassle of json-parsing and re-formatting the messages vs. just storing the raw messages if this is only for analysis later? $\endgroup$ Aug 24, 2022 at 15:43

1 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:

  1. 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.

  2. 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.

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

  • $\begingroup$ Thank you very much for this answer. Could you provide a pseudocode of the implementation of this class LOBWriter with Websocket? $\endgroup$
    – apt45
    Aug 27, 2022 at 11:06
  • $\begingroup$ Whenever you have time, can you provide a pseudocode? Thank you very much $\endgroup$
    – apt45
    Sep 14, 2022 at 1:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.