Python Pandas查找连续Signal=2超3行分组并计算时间差及平均值

实现思路 #

• 数据预处理：将Time列转换为datetime时间类型，保证时间计算准确性
• 连续信号分组：对Signal列生成分组标记，每当Signal值不等于2时分组ID累加，相同ID即为连续的同Signal值行
• 过滤有效分组：仅保留Signal=2且分组内行数>3的分组
• 分组计算耗时：取每个有效分组首尾行的时间差，转换为秒单位
• 统计最终结果：计算有效分组总数、总耗时、单组平均耗时

完整代码 #

import pandas as pd

# 构造测试数据集
data = {
    'Time': {1: '2021-08-26 07:30:23', 2: '2021-08-26 07:30:32', 4: '2021-08-26 07:30:37', 9: '2021-08-26 07:30:44', 10: '2021-08-26 07:30:55', 11: '2021-08-26 07:31:05', 12: '2021-08-26 07:31:15', 13: '2021-08-26 07:31:26', 14: '2021-08-26 07:31:36', 15: '2021-08-26 07:31:47', 16: '2021-08-26 07:31:57', 17: '2021-08-26 07:32:07', 18: '2021-08-26 07:32:18', 19: '2021-08-26 07:32:28', 20: '2021-08-26 07:32:38', 21: '2021-08-26 07:32:49', 22: '2021-08-26 07:32:59', 23: '2021-08-26 07:33:09', 24: '2021-08-26 07:33:20', 26: '2021-08-26 07:33:27', 28: '2021-08-26 07:33:31', 31: '2021-08-26 07:33:41', 33: '2021-08-26 07:33:47', 38: '2021-08-26 07:34:20', 40: '2021-08-26 07:34:22', 42: '2021-08-26 07:34:23', 45: '2021-08-26 07:34:25', 51: '2021-08-26 07:35:23', 54: '2021-08-26 07:35:33', 56: '2021-08-26 07:35:34', 60: '2021-08-26 07:35:57', 62: '2021-08-26 07:35:59', 64: '2021-08-26 07:36:00', 66: '2021-08-26 07:36:01', 68: '2021-08-26 07:36:02', 74: '2021-08-26 07:37:02', 76: '2021-08-26 07:37:06', 78: '2021-08-26 07:37:07', 80: '2021-08-26 07:37:09', 84: '2021-08-26 07:37:39', 86: '2021-08-26 07:37:43', 89: '2021-08-26 07:37:55', 91: '2021-08-26 07:37:57', 92: '2021-08-26 07:37:58', 94: '2021-08-26 07:37:59', 95: '2021-08-26 07:38:09', 96: '2021-08-26 07:38:20', 97: '2021-08-26 07:38:30', 98: '2021-08-26 07:38:40', 101: '2021-08-26 07:38:53', 103: '2021-08-26 07:38:55', 105: '2021-08-26 07:38:56', 107: '2021-08-26 07:39:01', 109: '2021-08-26 07:39:08', 111: '2021-08-26 07:39:09', 116: '2021-08-26 07:39:50', 118: '2021-08-26 07:39:51', 120: '2021-08-26 07:39:54', 122: '2021-08-26 07:39:56', 123: '2021-08-26 07:39:56', 127: '2021-08-26 07:40:01', 146: '2021-08-26 07:43:04', 147: '2021-08-26 07:43:14', 149: '2021-08-26 07:43:22', 155: '2021-08-26 07:44:05', 157: '2021-08-26 07:44:06', 159: '2021-08-26 07:44:08', 161: '2021-08-26 07:44:11', 164: '2021-08-26 07:44:24', 166: '2021-08-26 07:44:26', 168: '2021-08-26 07:44:39', 170: '2021-08-26 07:44:40', 172: '2021-08-26 07:44:43', 174: '2021-08-26 07:44:44', 176: '2021-08-26 07:44:45', 178: '2021-08-26 07:44:47', 181: '2021-08-26 07:45:07', 184: '2021-08-26 07:45:27', 186: '2021-08-26 07:45:28', 188: '2021-08-26 07:45:34', 190: '2021-08-26 07:45:37', 192: '2021-08-26 07:45:42', 194: '2021-08-26 07:45:46', 196: '2021-08-26 07:45:51', 197: '2021-08-26 07:46:02', 199: '2021-08-26 07:46:06', 201: '2021-08-26 07:46:08', 203: '2021-08-26 07:46:09', 205: '2021-08-26 07:46:12', 207: '2021-08-26 07:46:17', 209: '2021-08-26 07:46:20', 213: '2021-08-26 07:46:52', 214: '2021-08-26 07:47:02', 215: '2021-08-26 07:47:13', 216: '2021-08-26 07:47:23', 217: '2021-08-26 07:47:33', 218: '2021-08-26 07:47:44', 219: '2021-08-26 07:47:54', 220: '2021-08-26 07:48:04', 221: '2021-08-26 07:48:15'},
    'Signal': {1: 3, 2: 3, 4: 3, 9: 3, 10: 3, 11: 3, 12: 3, 13: 3, 14: 3, 15: 3, 16: 3, 17: 3, 18: 3, 19: 3, 20: 3, 21: 3, 22: 3, 23: 3, 24: 3, 26: 3, 28: 3, 31: 2, 33: 2, 38: 2, 40: 2, 42: 2, 45: 3, 51: 3, 54: 2, 56: 2, 60: 2, 62: 2, 64: 2, 66: 2, 68: 3, 74: 3, 76: 3, 78: 2, 80: 2, 84: 2, 86: 2, 89: 2, 91: 2, 92: 3, 94: 3, 95: 3, 96: 3, 97: 3, 98: 3, 101: 3, 103: 2, 105: 3, 107: 2, 109: 2, 111: 2, 116: 2, 118: 2, 120: 2, 122: 2, 123: 3, 127: 3, 146: 3, 147: 3, 149: 3, 155: 3, 157: 3, 159: 3, 161: 3, 164: 3, 166: 3, 168: 3, 170: 3, 172: 3, 174: 3, 176: 2, 178: 2, 181: 2, 184: 2, 186: 2, 188: 2, 190: 2, 192: 2, 194: 3, 196: 3, 197: 3, 199: 3, 201: 3, 203: 2, 205: 2, 207: 3, 209: 3, 213: 3, 214: 3, 215: 3, 216: 3, 217: 3, 218: 3, 219: 3, 220: 3, 221: 3}
}
df = pd.DataFrame(data)
# 转换时间列为datetime类型
df['Time'] = pd.to_datetime(df['Time'])

# 生成连续信号分组ID
df['group_id'] = (df['Signal'] != 2).cumsum()

# 过滤有效分组：Signal=2 且组内行数>3
valid_groups = df[df['Signal'] == 2].groupby('group_id').filter(lambda x: len(x) > 3)

# 计算每个有效分组的耗时
group_result = valid_groups.groupby('group_id').agg(
    开始时间=('Time', 'first'),
    结束时间=('Time', 'last'),
    组内行数=('Time', 'count')
)
group_result['耗时_秒'] = (group_result['结束时间'] - group_result['开始时间']).dt.total_seconds()

# 统计最终结果
total_count = len(group_result)
total_cost = group_result['耗时_秒'].sum()
avg_cost = group_result['耗时_秒'].mean()

# 输出结果
print("各有效分组明细：")
print(group_result)
print(f"\n有效分组总数量：{total_count}")
print(f"总耗时：{total_cost}秒")
print(f"单组平均耗时：{avg_cost}秒")

运行结果 #

各有效分组明细 #