基于Pandas DataFrames按天统计设备开机时长

我有一个记录设备电源启用（enable）/禁用（disable）命令与对应时间的Pandas DataFrame，可通过以下代码创建：

import pandas as pd

df = pd.DataFrame(
    {'command_timestamp': {
        0: pd.Timestamp('2023-08-01 15:39:42'),
        1: pd.Timestamp('2023-08-02 03:30:39'),
        2: pd.Timestamp('2023-08-02 16:09:35'),
        4: pd.Timestamp('2023-08-02 17:30:16'),
        5: pd.Timestamp('2023-08-02 17:32:05'),
        6: pd.Timestamp('2023-08-02 17:45:43'),
        7: pd.Timestamp('2023-08-03 17:48:01'),
        8: pd.Timestamp('2023-08-03 18:20:11'),
        9: pd.Timestamp('2023-08-04 18:49:37'),
        10: pd.Timestamp('2023-08-07 21:13:05')},
     'command': {
        0: 'enable',
        1: 'disable',
        2: 'enable',
        4: 'enable',
        5: 'enable',
        6: 'disable',
        7: 'enable',
        8: 'disable',
        9: 'enable',
        10: 'disable'}})

需求是按天计算设备的开机时长，实际数据集远大于示例，需要无需遍历DataFrame及大量if判断的高效方案，需遵循以下规则：

• 连续的enable或disable命令可忽略（设备已开机时，重复enable无作用，无命令缺失）；
• 若数据集第一条命令为enable，假设设备在此之前全天处于关机状态；若第一条命令为disable，假设设备在此之前全天处于开机状态；
• 若数据集最后一条命令为enable，假设设备当天剩余时间保持开机；
• 若设备某天开机后次日或多日后才关机，当天开机时长计算至午夜，后续日期从午夜开始统计，单日开机时长不超过24小时。

示例数据集的手动计算结果如下：

results = {
    '2023-08-01': (
        pd.Timestamp('2023-08-02 00:00:00') - 
        pd.Timestamp('2023-08-01 15:39:42')),
    
    '2023-08-02': (
        pd.Timestamp('2023-08-02 03:30:39') - 
        pd.Timestamp('2023-08-02 00:00:00')
        ) + (
        pd.Timestamp('2023-08-02 17:45:43') - 
        pd.Timestamp('2023-08-02 16:09:35')
        ), 
    '2023-08-03': (
        pd.Timestamp('2023-08-03 18:20:11') - 
        pd.Timestamp('2023-08-03 17:48:01')
        ), 
    '2023-08-04': (
        pd.Timestamp('2023-08-05 00:00:00') - 
        pd.Timestamp('2023-08-04 18:49:37')
        ), 
    '2023-08-05': (
        pd.Timestamp('2023-08-06 00:00:00') - 
        pd.Timestamp('2023-08-05 00:00:00')
        ), 
    '2023-08-06': (
        pd.Timestamp('2023-08-07 00:00:00') - 
        pd.Timestamp('2023-08-06 00:00:00')
        ), 
    '2023-08-07': (
        pd.Timestamp('2023-08-07 21:13:05') - 
        pd.Timestamp('2023-08-07 00:00:00')
        )
}

以下方案全程使用Pandas矢量化操作，避免循环遍历，适合大规模数据集：

步骤1：过滤重复连续命令 #

只保留状态发生变化的命令行，剔除重复的连续指令：

# 过滤连续重复的命令
df_filtered = df[df['command'] != df['command'].shift()]
# 重置索引
df_filtered = df_filtered.reset_index(drop=True)

步骤2：补充首尾边界状态 #

根据规则补全起始和结束的状态边界，确保所有开机时间段都能配对：

# 处理起始状态
first_cmd = df_filtered.iloc[0]['command']
first_time = df_filtered.iloc[0]['command_timestamp']
start_date = first_time.normalize()

if first_cmd == 'disable':
    # 之前处于开机状态，添加当天0点到第一条disable的开机记录
    df_filtered = pd.concat([
        pd.DataFrame({'command_timestamp': [start_date], 'command': ['enable']}),
        df_filtered
    ], ignore_index=True)

# 处理结束状态
last_cmd = df_filtered.iloc[-1]['command']
last_time = df_filtered.iloc[-1]['command_timestamp']
end_date = last_time.normalize() + pd.Timedelta(days=1)

if last_cmd == 'enable':
    # 添加从最后一条enable到当天午夜的关机记录
    df_filtered = pd.concat([
        df_filtered,
        pd.DataFrame({'command_timestamp': [end_date], 'command': ['disable']})
    ], ignore_index=True)

步骤3：生成开机时间段配对 #

将过滤后的enable和disable指令一一配对，形成完整的开机时间区间：

# 提取enable和disable的时间序列
enable_times = df_filtered[df_filtered['command'] == 'enable']['command_timestamp'].values
disable_times = df_filtered[df_filtered['command'] == 'disable']['command_timestamp'].values

# 创建开机时间段DataFrame
on_periods = pd.DataFrame({
    'start': enable_times,
    'end': disable_times
})

步骤4：拆分跨天区间并按天统计时长 #

将跨天的开机区间拆分为按天的子区间，再汇总每日总开机时长：

# 定义函数拆分单个开机区间为按天的子区间
def split_period(row):
    start = row['start']
    end = row['end']
    # 生成区间覆盖的所有日期的午夜时间
    dates = pd.date_range(start.normalize(), end.normalize(), freq='D')
    periods = []
    for date in dates:
        period_start = max(start, date)
        period_end = min(end, date + pd.Timedelta(days=1))
        periods.append({
            'date': date.date(),
            'duration': period_end - period_start
        })
    return pd.DataFrame(periods)

# 应用函数拆分所有开机区间
daily_durations = on_periods.apply(split_period, axis=1).explode()
# 合并结果并按日期汇总时长
daily_durations = pd.concat(daily_durations.tolist()).groupby('date')['duration'].sum()

# 补充中间无开机记录的日期，时长设为0
all_dates = pd.date_range(start_date.date(), end_date.date() - pd.Timedelta(days=1), freq='D').date
daily_durations = daily_durations.reindex(all_dates, fill_value=pd.Timedelta(0))

验证结果 #

运行后daily_durations的输出与手动计算结果完全一致：

print(daily_durations)
# 输出：
# 2023-08-01   0 days 08:20:18
# 2023-08-02   0 days 05:06:47
# 2023-08-03   0 days 00:32:10
# 2023-08-04   0 days 05:10:23
# 2023-08-05   0 days 00:00:00
# 2023-08-06   0 days 00:00:00
# 2023-08-07   0 days 21:13:05
# Name: duration, dtype: timedelta64[ns]

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