CSV时序数据峰值持续时长检测算法优化需求

我有一份CSV格式的时序数据流，第一列为日期时间，第二列为数值，数据已绘制成图表。需要编写算法输出包含峰值起始时间及持续时长的数组，但当前基于Pandas和NumPy的峰值检测代码输出结果不符合预期，期望得到如[(2023 03 14 14 31 00.00) 28 mins]格式的结果。

数据图表 #

数据图表展示了时序数据的变化趋势：前期数值持续下降，达到低点后快速上升至峰值，随后逐渐回落。

CSV数据样本 #

Column1,Column2
2023-03-14 14:00:59.0,195.80
2023-03-14 14:02:06.0,174.20
2023-03-14 14:03:14.0,156.76
2023-03-14 14:04:21.0,142.36
2023-03-14 14:05:29.0,131.00
2023-03-14 14:06:37.0,122.00
2023-03-14 14:07:44.0,114.91
2023-03-14 14:08:52.0,109.18
2023-03-14 14:10:00.0,104.56
2023-03-14 14:11:07.0,100.74
2023-03-14 14:12:15.0,97.93
2023-03-14 14:13:22.0,95.45
2023-03-14 14:14:30.0,93.43
2023-03-14 14:15:37.0,91.85
2023-03-14 14:16:45.0,90.73
2023-03-14 14:17:53.0,89.49
2023-03-14 14:19:00.0,88.59
2023-03-14 14:20:08.0,87.91
2023-03-14 14:21:15.0,87.13
2023-03-14 14:22:23.0,86.68
2023-03-14 14:23:30.0,86.23
2023-03-14 14:24:38.0,86.23
2023-03-14 14:25:45.0,108.61
2023-03-14 14:26:53.0,142.70
2023-03-14 14:28:01.0,175.89
2023-03-14 14:29:08.0,203.79
2023-03-14 14:30:16.0,225.84
2023-03-14 14:31:23.0,241.25
2023-03-14 14:32:31.0,253.29
2023-03-14 14:33:39.0,262.18
2023-03-14 14:34:46.0,262.29
2023-03-14 14:35:54.0,262.29
2023-03-14 14:37:01.0,262.29
2023-03-14 14:38:09.0,260.83
2023-03-14 14:39:16.0,235.51
2023-03-14 14:40:24.0,208.85
2023-03-14 14:41:31.0,185.45
2023-03-14 14:42:39.0,166.33

当前峰值检测代码 #

import pandas as pd
import numpy as np
from datetime import datetime


# Read the data from the CSV file
df = pd.read_csv('test.csv')

# Convert the first column to datetime format
df['Column1'] = pd.to_datetime(df['Column1'])

# Convert the second column to numeric type
df['Column2'] = pd.to_numeric(df['Column2'])

# Find the peaks using numpy
diff1 = np.diff(df['Column2'])
diff2 = np.diff(np.sign(diff1))
peaks, = np.where(diff2 < 0)


peak_durations = np.zeros(len(peaks), dtype=float)
start_times = np.zeros(len(peaks), dtype='datetime64[m]')
for i, peak_index in enumerate(peaks):
    start_index = np.argmax(df['Column2'][:peak_index]) # Index of start of peak
    end_index = np.argmin(df['Column2'][peak_index:]) + peak_index # Index of end of peak
    duration_minutes = (df['Column1'][end_index] - df['Column1'][start_index]).total_seconds() / 60
    peak_durations[i] = duration_minutes
    start_times[i] = df['Column1'][start_index]

# Convert start times to desired string format
start_times_str = [np.datetime_as_string(dt, unit='ms') for dt in start_times]

# Combine start times and durations into a 2-dimensional array
peaks_info = np.vstack((start_times_str, peak_durations)).T

print(peaks_info)

当前错误输出 #

[['2023-03-14T14:32:00.000' '189.15']
 ['2023-03-14T14:34:00.000' '186.9']
 ['2023-03-14T14:34:00.000' '186.9']
 ['2023-03-14T14:34:00.000' '186.9']
 ['2023-03-14T14:34:00.000' '186.9']
 ['2023-03-14T14:34:00.000' '186.9']
 ['2023-03-14T14:34:00.000' '186.9']
 ['2023-03-14T14:34:00.000' '186.9']
 ['2023-03-14T14:34:00.000' '186.9']
 ['2023-03-14T14:34:00.000' '186.9']
 ['2023-03-14T14:34:00.000' '552.3166666666667']
 ['2023-03-14T14:34:00.000' '552.3166666666667']
 ['2023-03-14T14:34:00.000' '552.3166666666667']
 ['2023-03-14T14:34:00.000' '552.3166666666667']
 ['2023-03-14T14:34:00.000' '552.3166666666667']
 ['2023-03-14T14:34:00.000' '552.3166666666667']
 ['2023-03-14T14:34:00.000' '552.3166666666667']
 ['2023-03-14T14:34:00.000' '552.3166666666667']
 ['2023-03-14T14:34:00.000' '552.3166666666667']
 ['2023-03-14T14:34:00.000' '552.3166666666667']
 ['2023-03-14T14:34:00.000' '552.3166666666667']
 ['2023-03-14T14:34:00.000' '552.3166666666667']
 ['2023-03-14T14:34:00.000' '552.3166666666667']
 ['2023-03-14T14:34:00.000' '552.3166666666667']
 ['2023-03-14T14:34:00.000' '552.3166666666667']
 ['2023-03-14T14:34:00.000' '552.3166666666667']
 ['2023-03-14T14:34:00.000' '552.3166666666667']
 ['2023-03-14T14:34:00.000' '552.3166666666667']
 ['2023-03-14T14:34:00.000' '561.3333333333334']]

期望输出格式 #

[(2023 03 14 14 31 00.00) 28 mins]

注：无法提供完整CSV文件。

问题分析与解决方案 #

当前代码存在两个核心问题：

1. 峰值检测逻辑错误：通过np.diff和np.sign的方式会把数值变化中的局部小波动误判为峰值，导致输出大量无效结果；同时argmax(df['Column2'][:peak_index])会取整个前期的最大值作为峰值起点，不符合“峰值上升阶段的起始点”的需求。
2. 时间格式与时长计算错误：时间格式未转换为要求的空格分隔样式，且时长计算范围错误。

修正后的代码如下：

import pandas as pd
import numpy as np

# 读取数据
df = pd.read_csv('test.csv')
df['Column1'] = pd.to_datetime(df['Column1'])
df['Column2'] = pd.to_numeric(df['Column2'])

# 定义峰值阈值：这里以超过前期低点的2倍（可根据实际调整）作为峰值判定标准
low_point = df['Column2'].min()
peak_threshold = low_point * 2

# 标记进入峰值区间的点：数值从低于阈值变为高于阈值
df['is_peak_start'] = (df['Column2'] >= peak_threshold) & (df['Column2'].shift(1) < peak_threshold)
# 标记离开峰值区间的点：数值从高于阈值变为低于阈值
df['is_peak_end'] = (df['Column2'] < peak_threshold) & (df['Column2'].shift(1) >= peak_threshold)

# 获取峰值起始和结束时间
peak_starts = df[df['is_peak_start']]['Column1']
peak_ends = df[df['is_peak_end']]['Column1']

# 处理可能的边界情况（比如数据结尾仍在峰值区间）
if len(peak_starts) > len(peak_ends):
    peak_ends = peak_ends.append(pd.Series(df['Column1'].iloc[-1]))

# 生成期望格式的结果
result = []
for start, end in zip(peak_starts, peak_ends):
    # 格式化时间为指定样式
    formatted_start = start.strftime('%Y %m %d %H %M %S.%f')[:-4]  # 保留两位小数
    # 计算持续分钟数，取整
    duration = round((end - start).total_seconds() / 60)
    result.append(f'({formatted_start}) {duration} mins')

print(result)

代码说明：

• 峰值区间判定：通过阈值区分峰值区间，避免局部波动误判；使用shift方法准确捕捉区间的起始和结束点。
• 时间格式化：利用strftime将时间转换为空格分隔的格式，保留两位小数的毫秒部分。
• 时长计算：准确计算峰值区间的时间差并转换为分钟数，取整后符合输出要求。

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