python - How to generate an acceleration spectrogram using Plotly from InfluxDB data - Stack Overflow

admin管理员组

文章数量:1345700

I'm trying to generate an acceleration spectrogram from data stored in InfluxDB. The challenge is that the spectrograms in Grafana are not rendered correctly due to the way InfluxDB handles tags. My boss mentioned that this issue cannot be fixed in Grafana and suggested using another visualization library like Plotly.

The image of Grafana Spectogram broken:

My dataset is provided as a DataFrame with the following columns:

time(2m): Timestamp of the measurement.

max_value: Amplitude or signal value.

frequency: A tag from InfluxDB (string) used to filter and ensure all frequency streams are present.

max_frequency_value: A float representing the numeric value of the frequency, which is essential for correctly ordering the data.

And here we have an example of the dataframe:

                                        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]

I need to use both the frequency tag (string) for filtering and the max_frequency_value (float) to order the frequency streams properly.

Here's an example of what I've tried using 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)

Despite this approach, the resulting spectrogram does not accurately reflect the distribution of frequencies and amplitudes over time.

How can I improve the data processing or visualization to generate an accurate acceleration spectrogram?

Any advice or suggestions would be greatly appreciated. Thanks in advance!

I attempted to pivot the DataFrame so that time becomes the x-axis and the frequency (ordered by its numeric value) becomes the y-axis, using the amplitude values to generate a heatmap with Plotly. The approach was to use the InfluxDB frequency tag for filtering and max_frequency_value (float) for proper ordering.

I was expecting to see a clear acceleration spectrogram where each frequency stream is correctly placed on the y-axis and the time evolution of the amplitude is shown in color intensity. Instead, the resulting plot didn't accurately reflect the data distribution as seen in Grafana, which indicates that something might be off in how the data is aggregated or visualized.

本文标签： pythonHow to generate an acceleration spectrogram using Plotly from InfluxDB dataStack Overflow