如何基于InfluxDB数据用Plotly生成准确的加速度频谱图？

问题 #

原本想用InfluxDB+Grafana生成加速度频谱图，但因InfluxDB标签特性导致Grafana无法正确渲染，转而使用Plotly。

数据集是Pandas DataFrame，包含列：

• time(2m)：测量时间戳
• max_value：信号幅值
• frequency：InfluxDB的字符串类型标签，用于过滤数据
• max_frequency_value：浮点型频率数值，用于频率排序

数据集示例：

max_value  max_frequency_value fill           frequency time(2m)
2025-04-01 13:38:24.510491001+00:00  3.524501e-06              0.00000                      0.0         
2025-04-01 13:38:24.510491001+00:00  1.616359e-09          10058.59375       10058.593749999998         
2025-04-01 13:38:24.510491001+00:00  1.890132e-09          10156.25000       10156.249999999998         
2025-04-01 13:38:24.510491001+00:00  1.291029e-09          10253.90625       10253.906249999998         
2025-04-01 13:38:24.510491001+00:00  1.067521e-09          10351.56250       10351.562499999998         
...                                           ...                  ...  ...                 ...      ...
2025-04-01 13:38:24.510491001+00:00  2.739630e-05             97.65625        97.65624999999999         
2025-04-01 13:38:24.510491001+00:00  2.044526e-09            976.56250        976.5624999999999         
2025-04-01 13:38:24.510491001+00:00  2.568848e-09           9765.62500        9765.624999999998         
2025-04-01 13:38:24.510491001+00:00  1.435887e-09           9863.28125        9863.281249999998         
2025-04-01 13:38:24.510491001+00:00  1.607229e-09           9960.93750        9960.937499999998         

[129 rows x 5 columns]

尝试的Plotly代码：

def plot_spectrogram_from_df(df, output_file='spectrogram.png'):
    """
    Generates and saves an acceleration spectrogram from a DataFrame containing:
      - 'time(2m)': Timestamp.
      - 'max_value': Signal amplitude.
      - 'frequency': Frequency tag (string) from InfluxDB.
      - 'max_frequency_value': Numeric value (float) of the frequency.
    
    The function uses the frequency tag for filtering and the frequency_value to order the streams correctly.
    """
    # Create a copy of the DataFrame and rename columns for convenience
    df_plot = df.copy().rename(columns={
        'time(2m)': 'time',
        'max_value': 'amplitude',
        'frequency': 'freq_tag',
        'max_frequency_value': 'freq_val'
    })
    
    # Convert time column to datetime
    df_plot['time'] = pd.to_datetime(df_plot['time'])
    
    # Create a mapping from the frequency tag to its numeric value
    freq_mapping = df_plot.drop_duplicates('freq_tag').set_index('freq_tag')['freq_val']
    
    # Pivot the DataFrame: rows = time, columns = frequency tag, values = amplitude
    pivot_df = df_plot.pivot_table(index='time', columns='freq_tag', values='amplitude', aggfunc='mean')
    
    # Sort the frequency tags based on the numeric frequency value
    sorted_tags = sorted(pivot_df.columns, key=lambda tag: freq_mapping[tag])
    pivot_df = pivot_df[sorted_tags]
    
    
    fig = go.Figure(data=go.Heatmap(
        x=pivot_df.index,
        y=[freq_mapping[tag] for tag in sorted_tags],
        z=pivot_df.T.values,  # Transpose so each row corresponds to a frequency
        colorscale='Viridis'
    ))
    
    fig.update_layout(
        title='Acceleration Spectrogram',
        xaxis_title='Time',
        yaxis_title='Frequency'
    )
    
 
    fig.write_image(output_file)

但生成的频谱图无法准确反映频率和幅值随时间的分布，y轴频率排列、幅值颜色分布均与Grafana中预期不符，推测数据聚合或可视化环节存在问题，需要优化数据处理或可视化步骤。

优化方案 #

1. 修正数据聚合逻辑 #

原代码用pivot_table的aggfunc='mean'，但你的幅值是max_value，用均值会弱化峰值特性。建议改用max聚合，或直接用pivot（当每个(time, freq_tag)组合唯一时）：

# 替换原透视表代码，用max保留峰值
pivot_df = df_plot.pivot_table(index='time', columns='freq_tag', values='amplitude', aggfunc='max')

如果存在重复时间+频率的数据，先去重保留最新值：

df_plot = df_plot.sort_values('time').drop_duplicates(subset=['time', 'freq_tag'], keep='last')

2. 修复频率排序逻辑 #

原代码依赖字符串标签映射数值，易因浮点精度（如10058.59375和10058.593749999998）出错，直接基于数值排序更可靠：

# 获取排序后的频率数值
sorted_freq_vals = sorted(df_plot['freq_val'].unique())
# 建立数值到标签的唯一映射
val_to_tag = df_plot.drop_duplicates('freq_val').set_index('freq_val')['freq_tag']
# 按数值顺序重新排列透视表列
pivot_df = pivot_df[[val_to_tag[val] for val in sorted_freq_vals]]

3. 处理缺失值与数据结构 #

透视表会产生缺失值，填充为0（无测量数据的频率幅值为0）避免颜色断层：

pivot_df = pivot_df.fillna(0)

4. 优化可视化细节 #

• 加速度频谱常用对数y轴，提升低频区域可读性：

fig.update_yaxes(type='log')

• 限制颜色范围，避免极值掩盖大部分数据的颜色差异：

# 基于数据分位数设置颜色上下限
min_amp = df_plot['amplitude'].quantile(0.01)
max_amp = df_plot['amplitude'].quantile(0.99)
fig.update_traces(zmin=min_amp, zmax=max_amp)

• 添加颜色条标签，明确颜色含义：

fig.update_layout(coloraxis_colorbar=dict(title='Amplitude'))

完整优化代码 #

import pandas as pd
import plotly.graph_objects as go

def plot_spectrogram_from_df(df, output_file='spectrogram.png'):
    """
    Generates and saves an acceleration spectrogram from a DataFrame containing:
      - 'time(2m)': Timestamp.
      - 'max_value': Signal amplitude.
      - 'frequency': Frequency tag (string) from InfluxDB.
      - 'max_frequency_value': Numeric value (float) of the frequency.
    """
    # 复制并重命名列
    df_plot = df.copy().rename(columns={
        'time(2m)': 'time',
        'max_value': 'amplitude',
        'frequency': 'freq_tag',
        'max_frequency_value': 'freq_val'
    })
    
    # 转换时间格式并去重
    df_plot['time'] = pd.to_datetime(df_plot['time'])
    df_plot = df_plot.sort_values('time').drop_duplicates(subset=['time', 'freq_tag'], keep='last')
    
    # 生成透视表，保留峰值
    pivot_df = df_plot.pivot_table(index='time', columns='freq_tag', values='amplitude', aggfunc='max')
    pivot_df = pivot_df.fillna(0)
    
    # 按频率数值排序
    sorted_freq_vals = sorted(df_plot['freq_val'].unique())
    val_to_tag = df_plot.drop_duplicates('freq_val').set_index('freq_val')['freq_tag']
    sorted_tags = [val_to_tag[val] for val in sorted_freq_vals]
    pivot_df = pivot_df[sorted_tags]
    
    # 创建热力图
    fig = go.Figure(data=go.Heatmap(
        x=pivot_df.index,
        y=sorted_freq_vals,
        z=pivot_df.T.values,
        colorscale='Viridis',
        zmin=df_plot['amplitude'].quantile(0.01),
        zmax=df_plot['amplitude'].quantile(0.99)
    ))
    
    # 更新布局
    fig.update_layout(
        title='Acceleration Spectrogram',
        xaxis_title='Time',
        yaxis_title='Frequency (Hz)',
        coloraxis_colorbar=dict(title='Amplitude'),
        height=600,
        width=1000
    )
    
    # 启用对数y轴（可选，根据数据调整）
    fig.update_yaxes(type='log')
    
    fig.write_image(output_file)

额外检查点 #

• 确认数据集包含多个时间点：如果只有单个时间戳，生成的频谱图会是一条竖线，需确保数据有连续时间的测量值。
• 验证max_frequency_value与frequency标签的一一对应关系：避免一个数值对应多个标签的情况。

内容的提问来源于stack exchange，提问作者Juan Luis Martos