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()
Advertisement
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()