Python中实现每小时滚动数据Max与对应Min的差值计算

需求：处理时间序列数据，每小时完成两组差值计算：

• 5-10分钟区间的最大值 - 0-5分钟区间的最小值
• 35-40分钟区间的最大值 - 30-35分钟区间的最小值

以下是完整实现代码：

import pandas as pd

ts = ['25/02/2023 0:00', '25/02/2023 0:01', '25/02/2023 0:02', '25/02/2023 0:03', '25/02/2023 0:04',  '25/02/2023 0:05', '25/02/2023 0:06', '25/02/2023 0:07', 
        '25/02/2023 0:08', '25/02/2023 0:09', '25/02/2023 0:10', '25/02/2023 0:11', '25/02/2023 0:12',  '25/02/2023 0:13', '25/02/2023 0:14', '25/02/2023 0:15', 
        '25/02/2023 0:16', '25/02/2023 0:17', '25/02/2023 0:18', '25/02/2023 0:19', '25/02/2023 0:20', '25/02/2023 0:21', '25/02/2023 0:22', '25/02/2023 0:23', 
        '25/02/2023 0:24', '25/02/2023 0:25', '25/02/2023 0:26', '25/02/2023 0:27', '25/02/2023 0:28', '25/02/2023 0:29', '25/02/2023 0:30', '25/02/2023 0:31',
        '25/02/2023 0:32', '25/02/2023 0:33', '25/02/2023 0:34', '25/02/2023 0:35', '25/02/2023 0:36', '25/02/2023 0:37', '25/02/2023 0:38', '25/02/2023 0:39',
        '25/02/2023 0:40', '25/02/2023 0:41', '25/02/2023 0:42', '25/02/2023 0:43', '25/02/2023 0:44', '25/02/2023 0:45', '25/02/2023 0:46', '25/02/2023 0:47', 
        '25/02/2023 0:48', '25/02/2023 0:49', '25/02/2023 0:50', '25/02/2023 0:51', '25/02/2023 0:52', '25/02/2023 0:53', '25/02/2023 0:54', '25/02/2023 0:55', 
        '25/02/2023 0:56', '25/02/2023 0:57', '25/02/2023 0:58', '25/02/2023 0:59', '25/02/2023 1:00', '25/02/2023 1:01', '25/02/2023 1:02', '25/02/2023 1:03', 
        '25/02/2023 1:04', '25/02/2023 1:05', '25/02/2023 1:06', '25/02/2023 1:07', '25/02/2023 1:08', '25/02/2023 1:09', '25/02/2023 1:10', '25/02/2023 1:11']

temp = ['0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16',  '17', '18', '19', '20', '21', '22', '23', '24', '25',
            '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', 
            '50', '51', '52', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64',  '65', '66', '67', '68', '69', '70', '71']

df = pd.DataFrame(list(zip(ts, temp)), columns=['ts', 'temp'])
df['ts'] = pd.to_datetime(df['ts'])
# 转换温度为数值类型，否则无法计算差值
df['temp'] = df['temp'].astype(float)
df1 = df.set_index('ts')

# 生成每5分钟窗口的最小值，筛选0-5、30-35分钟区间的结果（对应索引分钟为5、35）
df2 = df1.rolling(5, step=5).agg(['min'])  
df3 = df2[df2.index.minute.isin([5,35])]      
df32 = df3.reset_index()

# 生成每5分钟窗口的最大值，筛选5-10、35-40分钟区间的结果（对应索引分钟为10、40）
df4 = df1.rolling(5, step=5).agg(['max'])
df5 = df4[df4.index.minute.isin([10,40])]  
df52 = df5.reset_index()

# 合并数据并计算差值
df6 = pd.concat([df32, df52], axis=1, join='inner')
# 重命名列名以便区分
df6.columns = ['min_ts', 'min_temp', 'max_ts', 'max_temp']
# 计算两组差值
df6['差值（目标区间Max-对应前置区间Min）'] = df6['max_temp'] - df6['min_temp']
# 添加差值类型标注，区分每小时的两组计算
df6['差值类型'] = ['5-10min Max - 0-5min Min'] * len(df6)
df6.loc[1::2, '差值类型'] = '35-40min Max - 30-35min Min'

print(df6)

输出结果 #

min_ts  min_temp           max_ts  max_temp  差值（目标区间Max-对应前置区间Min）                  差值类型
0 2023-02-25 00:05:00       1.0 2023-02-25 00:10:00      10.0                        9.0  5-10min Max - 0-5min Min
1 2023-02-25 00:35:00      31.0 2023-02-25 00:40:00      40.0                        9.0  35-40min Max - 30-35min Min
2 2023-02-25 01:05:00      61.0 2023-02-25 01:10:00      70.0                        9.0  5-10min Max - 0-5min Min

关键修正点 #

1. 将temp列转为数值类型，避免字符串无法进行减法运算
2. 合并后重命名列名，解决原代码中列名重复、多层索引混乱的问题
3. 通过直接列运算得到目标差值，添加类型标注明确每组差值对应的区间
4. 保留原代码中rolling(5, step=5)的窗口逻辑，确保区间筛选逻辑和需求一致

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