如何在Azure Data Explorer中用Plotly Graph Objects绘制仪表盘图

问题背景 #

在Azure Data Explorer（ADX）仪表盘中，可借助Kusto的Python插件结合Plotly Express绘制柱状图、散点图等常规图表，但Plotly Express不支持仪表盘图，因此需要使用Plotly Graph Objects库实现。

以下硬编码数值的ADX代码可正常运行：

['table']
| where abs(Prsr1IoTVal) >= 1 
| top 1 by IoTEventDateTime
| project  Pressure1Value=round(Prsr1IoTVal)
| evaluate python(typeof(plotly:string),
```if 1:
    import plotly.graph_objects as go
    fig = go.Figure(go.Indicator(
        mode = "gauge+number",
        value = 250,
        domain = {'x': [0, 1], 'y': [0, 1]},
        title = {'text': "Speed"}))
    plotly_obj = fig.to_json()
    result = pd.DataFrame(data = [plotly_obj], columns = ["plotly"])
```)

但当尝试从Kusto表传入动态值时，查询无行返回（已确认Kusto前置查询能返回有效值），对应代码如下：

['glualwndpwriotevents-target-tbl']
| where abs(Prsr1IoTVal) >= 1 
| top 1 by IoTEventDateTime
| project  Pressure1Value=round(Prsr1IoTVal)*100
| evaluate python(typeof(plotly:string),
```if 1:
    import plotly.graph_objects as go
    fig = go.Figure(
                    data=[go.Indicator(
                                mode = "gauge+number",
                                value = df['Pressure1Value'],
                                domain = {'x': [0, 1], 'y': [0, 1]},
                                title = {'text': "Speed"}
                                )
                        ]
                    )
    plotly_obj = fig.to_json()
    result = pd.DataFrame(data = [plotly_obj], columns = ["plotly"])
```)

解决方法 #

问题核心在于：df['Pressure1Value']返回的是Pandas Series对象，而Plotly Indicator的value参数需要单个数值类型。需提取Series中的单个值，修正后的代码如下：

['glualwndpwriotevents-target-tbl']
| where abs(Prsr1IoTVal) >= 1 
| top 1 by IoTEventDateTime
| project  Pressure1Value=round(Prsr1IoTVal)*100
| evaluate python(typeof(plotly:string),
```if 1:
    import plotly.graph_objects as go
    import pandas as pd

    # 从DataFrame中提取单个数值
    pressure_value = df['Pressure1Value'].iloc[0]

    fig = go.Figure(
                    data=[go.Indicator(
                                mode = "gauge+number",
                                value = pressure_value,
                                domain = {'x': [0, 1], 'y': [0, 1]},
                                title = {'text': "Speed"}
                                )
                        ]
                    )
    plotly_obj = fig.to_json()
    result = pd.DataFrame(data = [plotly_obj], columns = ["plotly"])
```)

原因说明 #

Kusto传入Python执行环境的df是包含一行数据的DataFrame，df['Pressure1Value']是Series类型数据，直接赋值给value会导致Plotly无法生成有效的图表JSON，ADX因此无法返回可渲染的结果。通过iloc[0]或values[0]提取单个数值后，Plotly能正常生成符合要求的仪表盘图表JSON，ADX即可正确返回并渲染图表。

内容的提问来源于stack exchange，提问作者GHariz