Websocket Json Data to DataFrame

I am learning how to work with APIs and web sockets in finance. My goal for this code is to access data and create a DataFrame with only columns (index, ask, bid & quote) I have tried appending values to the DataFrame but it creates a new DataFrame every time I receive a message similar to the df = new_df.loc[0] = data

Output of the current code

0    {'ask': 20150.93, 'bid': 20144.93, 'epoch': 16...
Name: tick, dtype: object

Dictionary after loading json

{'echo_req': {'ticks': 'R_100'}, 'msg_type': 'tick', 'subscription': {'id': '248a0656-44e9-91da-5e06-10712edf2cdf'}, 'tick': {'ask': 20150.19, 'bid': 20144.19, 'epoch': 1658228500, 'id': '248a0656-44e9-91da-5e06-10712edf2cdf', 'pip_size': 2, 'quote': 20147.19, 'symbol': 'R_100'}}

Desired output

index, ask, bid, quote as columns

Append values as rows every time we get a new message or tick.

import websocket
import json
import pandas as pd
    
app_id = '*****'  # Replace with your app_id.
    
def on_open(ws):
    json_data = json.dumps({"ticks": "R_100"})
    ws.send(json_data)

def on_message(ws, message):    
    global df
    data = json.loads(message)
    row = {
        'ask': [data['tick']['ask']],  # it has to be list
        'bid': [data['tick']['bid']],  # it has to be list
        'epoch': [data['tick']['epoch']],  # it has to be list
    }    
"""
if __name__ == '__main__':
    df = pd.DataFrame()
    on_message(None, json.dumps(data))
    on_message(None, json.dumps(data))
    print(df.to_string())
"""
if __name__ == "__main__":
    apiUrl = "wss:///websockets/v3?app_id=" + app_id
    ws = websocket.WebSocketApp(apiUrl, on_message=on_message, on_open=on_open)
    ws.run_forever()
    

Answer

You should create empty DataFrame at start as global variable and later use .append() to add new rows to this dataframe.

Minimal working example (but without API)

example_data = {
    'echo_req': {'ticks': 'R_100'},
    'msg_type': 'tick',
    'subscription': {'id': '248a0656-44e9-91da-5e06-10712edf2cdf'},
    'tick': {
        'ask': 20150.19,
        'bid': 20144.19,
        'epoch': 1658228500,
        'id': '248a0656-44e9-91da-5e06-10712edf2cdf',
        'pip_size': 2,
        'quote': 20147.19,
        'symbol': 'R_100'
    }
}

import json
import pandas as pd

def on_message(ws, message):
    global df

    data = json.loads(message)
    
    row = {
        'ask': data['tick']['ask'],
        'bid': data['tick']['bid'],
        'epoch': data['tick']['epoch'],
    }
    
    df = df.append(row, ignore_index=True)
    
if __name__ == '__main__':
    df = pd.DataFrame()

    on_message(None, json.dumps(example_data))
    on_message(None, json.dumps(example_data))
    
    print(df.to_string())

Result:

        ask       bid         epoch
0  20150.19  20144.19  1.658228e+09
1  20150.19  20144.19  1.658228e+09

---

EDIT:

Because .append() is deprecated and pandas suggests to use concat():

example_data = {
    'echo_req': {'ticks': 'R_100'},
    'msg_type': 'tick',
    'subscription': {'id': '248a0656-44e9-91da-5e06-10712edf2cdf'},
    'tick': {
        'ask': 20150.19,
        'bid': 20144.19,
        'epoch': 1658228500,
        'id': '248a0656-44e9-91da-5e06-10712edf2cdf',
        'pip_size': 2,
        'quote': 20147.19,
        'symbol': 'R_100'
    }
}

import json
import pandas as pd

def on_message(ws, message):
    global df

    data = json.loads(message)
    
    row = {
        'ask': [ data['tick']['ask'] ],      # it has to be list
        'bid': [ data['tick']['bid'] ],      # it has to be list 
        'epoch': [ data['tick']['epoch'] ],  # it has to be list
    }

    new_df = pd.DataFrame(row)
    df = pd.concat([df, new_df], ignore_index=True)
    
if __name__ == '__main__':
    df = pd.DataFrame()

    on_message(None, json.dumps(example_data))
    on_message(None, json.dumps(example_data))
    
    print(df.to_string())

---

Version with websocket – but I can’t test it

EDIT: Original question was using ws.binaryws.com but author changed it into websocket but this can be useless for other users – so I will keep original ws.binaryws.com in my code.

import websocket
import json
import pandas as pd
    
app_id = '*****'  # Replace with your app_id.
    
def on_open(ws):
    json_data = json.dumps({"ticks": "R_100"})
    ws.send(json_data)

def on_message(ws, message):    
    global df
    data = json.loads(message)
    row = {
        'ask':   [data['tick']['ask']],    # it has to be list
        'bid':   [data['tick']['bid']],    # it has to be list
        'epoch': [data['tick']['epoch']],  # it has to be list
    }
    new_df = pd.DataFrame(row)
    df = pd.concat([df, new_df], ignore_index=True)

if __name__ == "__main__":
    df = pd.DataFrame()
    apiUrl = "wss://ws.binaryws.com/websockets/v3?app_id=" + app_id
    ws = websocket.WebSocketApp(apiUrl, on_message=on_message, on_open=on_open)
    ws.run_forever()