Search code examples
pythonpandasplotlyheatmap

Plotly heatmap from order book (pandas dataframe)

I am trying to feed order book data into a plotly heatmap that I retrieve from an exchange API. The data I retrieve is stored in a pandas dataframe with this format:

         id           timestamp                                               asks                                               bids
0         4 2021-07-13 18:50:02  [[31416.0, 12.6396], [31417.0, 0.3847], [31418...  [[31415.0, 0.6475], [31413.0, 0.1592], [31412....
1        10 2021-07-13 18:51:02  [[31416.0, 12.6396], [31417.0, 0.3847], [31418...  [[31415.0, 0.6475], [31413.0, 0.1592], [31412....
2        15 2021-07-13 18:52:02  [[31416.0, 12.6396], [31417.0, 0.3847], [31418...  [[31415.0, 0.6475], [31413.0, 0.1592], [31412....
3        20 2021-07-13 18:53:03  [[31416.0, 12.6396], [31417.0, 0.3847], [31418...  [[31415.0, 0.6475], [31413.0, 0.1592], [31412....
4        25 2021-07-13 18:54:02  [[31416.0, 12.6396], [31417.0, 0.3847], [31418...  [[31415.0, 0.6475], [31413.0, 0.1592], [31412....
...     ...                 ...                                                ...                                                ...
4863  25288 2021-07-18 15:10:01  [[31416.0, 12.6396], [31417.0, 0.3847], [31418...  [[31415.0, 0.6475], [31413.0, 0.1592], [31412....
4864  25294 2021-07-18 15:11:01  [[31416.0, 12.6396], [31417.0, 0.3847], [31418...  [[31415.0, 0.6475], [31413.0, 0.1592], [31412....
4865  25299 2021-07-18 15:12:02  [[31416.0, 12.6396], [31417.0, 0.3847], [31418...  [[31415.0, 0.6475], [31413.0, 0.1592], [31412....
4866  25304 2021-07-18 15:13:03  [[31416.0, 12.6396], [31417.0, 0.3847], [31418...  [[31415.0, 0.6475], [31413.0, 0.1592], [31412....
4867  25309 2021-07-18 15:14:02  [[31416.0, 12.6396], [31417.0, 0.3847], [31418...  [[31415.0, 0.6475], [31413.0, 0.1592], [31412....

[4868 rows x 4 columns]

As you can see, the content of the bids and asks columns have this format: [[price, size of order], [price, size of order], ...]

According to https://plotly.com/python/heatmaps/ the heatmap can be filled with data in according to this format:

import plotly.graph_objects as go

fig = go.Figure(data=go.Heatmap(
                   z=[[1, None, 30, 50, 1], [20, 1, 60, 80, 30], [30, 60, 1, -10, 20]],
                   x=['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday'],
                   y=['Morning', 'Afternoon', 'Evening'],
                   hoverongaps = False))
fig.show()

The heatmap shall be visualized like in this example: heatmap

--> x axis is the timestamp

--> y axis is the price

--> z axis/color is the order size at a given price

With the given example from plotly I struggle to get the y and z values right. I have the impression, I need to aggregate ALL orders (for all timestamps), then sort them by price, then put the prices into bins, then fill gaps (with None), then put them into the right array structure... this seems to be really complex and I couldn't figure out how to do it. Is there maybe a simpler way to achieve this? Or some magic python functions that do what's needed?

fig = go.Figure(data=go.Heatmap(
                   x=df['timestamp'],
                   y=???,
                   z=???
))

I'd appreciate any help! Thanks!

EDIT: Sample data from the dataframe: https://file.io/p2hktQxKljkI You can load it with df_order_book = pandas.read_csv("test.csv").

Solution
    • have looked at this structure data - just looked at ask
    • it makes sense when plotted as a scatter, smaller order, higher price to buy
    • heatmap, have not made sense of it

    get some data

    import requests
import pandas as pd
import time
df = pd.DataFrame()
for i in range(20):
    df = pd.concat([df, pd.json_normalize(requests.get("https://api.cryptowat.ch/markets/kraken/btcusd/orderbook").json()["result"]).assign(timestamp=pd.to_datetime("now"))])
    time.sleep(1)

    re-structure and plot as scatter

    import numpy as np
import plotly.graph_objects as go
import plotly.express as px

d = df.loc[:, ["timestamp", "asks"]].explode("asks").assign(
    price=lambda d: d["asks"].apply(lambda a: a[0]),
    size=lambda d: d["asks"].apply(lambda a: a[1]),
)


px.scatter(d, x="timestamp", y="size", color="price")

    heatmap

    go.Figure(go.Heatmap(x=d["timestamp"], y=d["size"], z=d["price"]))