Python实现非规则时间序列中同类型并发事件的识别与统计

需求说明 #

需识别、计数并计算任意两台机器间同类型并发事件的时长。数据为非规则时间序列（datetime类型），同一机器的事件在时间上连续无间隙。

样例数据 #

MachineID,Event_StartTime,Event_EndTime,Event_Type,Duration
A,11/10/17 1:08,11/10/17 1:47,MAINTENANCE,0 days 00:39:00
A,11/10/17 1:47,11/10/17 2:56,RUN,0 days 01:09:00
A,11/10/17 2:56,11/10/17 3:41,STOP,0 days 00:45:00
A,11/10/17 3:41,11/10/17 7:33,RUN,0 days 03:52:00
B,11/10/17 7:29,11/10/17 14:54,STOP,0 days 07:25:00
A,11/10/17 7:33,11/23/17 14:44,STOP,13 days 07:11:00
C,11/10/17 10:17,11/10/17 17:07,STOP,0 days 06:50:00
B,11/10/17 14:54,11/10/17 15:53,MAINTENANCE,0 days 00:59:00
D,11/10/17 15:16,11/10/17 15:18,MAINTENANCE,0 days 00:02:00
D,11/10/17 15:18,11/20/17 13:40,RUN,9 days 22:22:00
B,11/10/17 15:53,11/12/17 12:18,RUN,1 days 20:25:00
E,11/10/17 16:57,11/10/17 17:08,STOP,0 days 00:11:00
C,11/10/17 17:07,11/10/17 17:52,MAINTENANCE,0 days 00:45:00
E,11/10/17 17:08,11/10/17 19:50,RUN,0 days 02:42:00
C,11/10/17 17:52,11/18/17 13:31,RUN,7 days 19:39:00
E,11/10/17 19:50,11/10/17 20:04,STOP,0 days 00:14:00

预期输出示例（以STOP事件为例） #

A&B: Count = 1, Total Duration = 0 days 07:21:00
A&C: Count = 1, Total Duration = 0 days 06:50:00
A&E: Count = 2, Total Duration = 0 days 00:25:00
B&C: Count = 1, Total Duration = 0 days 04:37:00
C&E: Count = 1, Total Duration = 0 days 00:10:00
Other combinations: no concurrent "STOP" events

Python实现方案 #

基础版本（适合新手理解） #

import pandas as pd
from itertools import combinations

# 1. 加载并预处理数据
df = pd.read_csv("your_data.csv")
df['Event_StartTime'] = pd.to_datetime(df['Event_StartTime'], format='%m/%d/%y %H:%M')
df['Event_EndTime'] = pd.to_datetime(df['Event_EndTime'], format='%m/%d/%y %H:%M')

# 2. 筛选目标事件类型
target_event = "STOP"
event_subset = df[df['Event_Type'] == target_event].reset_index(drop=True)

# 3. 生成所有机器对并计算并发统计
unique_machines = sorted(event_subset['MachineID'].unique())
machine_pairs = combinations(unique_machines, 2)
results = {}

for m1, m2 in machine_pairs:
    # 获取两台机器的事件时间范围
    m1_events = event_subset[event_subset['MachineID'] == m1][['Event_StartTime', 'Event_EndTime']].values
    m2_events = event_subset[event_subset['MachineID'] == m2][['Event_StartTime', 'Event_EndTime']].values
    
    total_dur = pd.Timedelta(0)
    count = 0
    
    # 计算每对事件的重叠
    for start1, end1 in m1_events:
        for start2, end2 in m2_events:
            overlap_start = max(start1, start2)
            overlap_end = min(end1, end2)
            if overlap_start < overlap_end:
                count += 1
                total_dur += overlap_end - overlap_start
    
    if count > 0:
        results[f"{m1}&{m2}"] = (count, total_dur)

# 4. 输出结果
for pair, (count, dur) in sorted(results.items()):
    print(f"{pair}: Count = {count}, Total Duration = {dur}")

# 输出无并发的提示
all_pairs_set = set(combinations(unique_machines, 2))
has_concurrent_set = set(tuple(p.split('&')) for p in results.keys())
if all_pairs_set - has_concurrent_set:
    print(f'Other combinations: no concurrent "{target_event}" events')

高效向量化版本（适合大数据量） #

避免嵌套循环，利用pandas广播提升效率：

import pandas as pd
import numpy as np
from itertools import combinations

def compute_concurrent_events(df, target_event):
    # 筛选目标事件
    event_df = df[df['Event_Type'] == target_event].copy()
    # 生成机器对的笛卡尔积，仅保留无序对（MachineID_x < MachineID_y）
    event_df['tmp_key'] = 1
    cross_df = pd.merge(event_df, event_df, on='tmp_key').query('MachineID_x < MachineID_y').drop('tmp_key', axis=1)
    
    # 计算重叠时间范围
    cross_df['overlap_start'] = np.maximum(cross_df['Event_StartTime_x'], cross_df['Event_StartTime_y'])
    cross_df['overlap_end'] = np.minimum(cross_df['Event_EndTime_x'], cross_df['Event_EndTime_y'])
    cross_df['overlap_duration'] = cross_df['overlap_end'] - cross_df['overlap_start']
    
    # 过滤有效重叠（时长>0）
    valid_overlaps = cross_df[cross_df['overlap_duration'] > pd.Timedelta(0)]
    
    # 按机器对分组统计
    stats = valid_overlaps.groupby(['MachineID_x', 'MachineID_y']).agg(
        Count=('overlap_duration', 'count'),
        Total_Duration=('overlap_duration', 'sum')
    ).reset_index()
    
    # 格式化结果字典
    result_dict = {}
    for _, row in stats.iterrows():
        pair = f"{row['MachineID_x']}&{row['MachineID_y']}"
        result_dict[pair] = (row['Count'], row['Total_Duration'])
    
    return result_dict, sorted(event_df['MachineID'].unique())

# 调用函数并输出结果
df = pd.read_csv("your_data.csv")
df['Event_StartTime'] = pd.to_datetime(df['Event_StartTime'], format='%m/%d/%y %H:%M')
df['Event_EndTime'] = pd.to_datetime(df['Event_EndTime'], format='%m/%d/%y %H:%M')

target_event = "STOP"
results, machines = compute_concurrent_events(df, target_event)

for pair, (count, dur) in sorted(results.items()):
    print(f"{pair}: Count = {count}, Total Duration = {dur}")

all_pairs = set(combinations(machines, 2))
has_concurrent = set(tuple(p.split('&')) for p in results.keys())
if all_pairs - has_concurrent:
    print(f'Other combinations: no concurrent "{target_event}" events')

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