Pandas DataFrame新增列统计灯泡开启覆盖的完整夜间数

基础数据 #

给定如下存储灯泡开关状态的pandas DataFrame，时间字段均为datetime类型：

date          code     other              time
1  2022-02-27 15:30:21+00:00             5        ON               NaT
2  2022-02-29 17:05:21+00:00             5       OFF   2 days 01:35:00
3  2022-04-07 17:05:21+00:00             5       OFF               NaT
4  2022-04-06 16:10:21+00:00             4        ON               NaT
5  2022-04-07 15:30:21+00:00             4       OFF   0 days 23:20:00
6  2022-02-03 22:40:21+00:00             3        ON               NaT
7  2022-02-03 23:20:21+00:00             3       OFF   0 days 00:40:00
8  2022-02-04 00:20:21+00:00             3        ON               NaT
9  2022-02-04 14:30:21+00:00             3        ON               NaT
10 2022-01-31 15:30:21+00:00             3        ON               NaT
11 2022-02-04 15:35:21+00:00             3       OFF   4 days 00:05:00
12 2022-02-04 15:40:21+00:00             3       OFF               NaT
13 2022-02-04 19:40:21+00:00             3        ON               NaT
14 2022-02-06 15:35:21+00:00             3       OFF   1 days 19:55:00
15 2022-02-23 21:10:21+00:00             3        ON               NaT
16 2022-02-24 07:10:21+00:00             3       OFF   0 days 10:00:00

计算规则 #

需要新增nights列，计算逻辑如下：

• time列值为NaT的行，nights统一取值为0
• time列值不为NaT的行，统计对应灯泡本次开启周期内覆盖的完整夜间数量
• 夜间时段定义：每日22:00:00 至 次日05:00:00
• 完整覆盖判定：灯泡必须在整个夜间时段全程保持开启状态，若在夜间时段中途发生开关动作，该夜间不计入统计

基础匹配规则：每个带非NaTtime值的OFF记录，对应的开启周期起点是它前面紧邻的同code的ON记录

补充规则的判定示例：

date      code     other              time    nights
1  2022-02-27 21:00:00+00:00         1        ON               NaT      0
2  2022-02-28 01:00:00+00:00         1       OFF   0 days 04:00:00      0
3  2022-02-28 03:15:00+00:00         1        ON               NaT      0
4  2022-02-28 09:30:00+00:00         1       OFF   0 days 06:15:00      0

上述示例中两次开关周期都没有完整覆盖22:00到次日5:00的区间，因此nights均为0。

期望输出 #

最终输出的DataFrame格式如下：

date          code     other              time    nights
1  2022-02-27 15:30:21+00:00             5        ON               NaT      0
2  2022-02-29 17:05:21+00:00             5       OFF   2 days 01:35:00      2
3  2022-04-07 17:05:21+00:00             5       OFF               NaT      0
4  2022-04-06 16:10:21+00:00             4        ON               NaT      0
5  2022-04-07 15:30:21+00:00             4       OFF   0 days 23:20:00      1
6  2022-02-03 22:40:21+00:00             3        ON               NaT      0
7  2022-02-03 23:20:21+00:00             3       OFF   0 days 00:40:00      0
8  2022-02-04 00:20:21+00:00             3        ON               NaT      0
9  2022-02-04 14:30:21+00:00             3        ON               NaT      0
10 2022-01-31 15:30:21+00:00             3        ON               NaT      0
11 2022-02-04 15:35:21+00:00             3       OFF   4 days 00:05:00      4
12 2022-02-04 15:40:21+00:00             3       OFF               NaT      0
13 2022-02-04 19:40:21+00:00             3        ON               NaT      0
14 2022-02-06 15:35:21+00:00             3       OFF   1 days 19:55:00      2
15 2022-02-23 21:10:21+00:00             3        ON               NaT      0
16 2022-02-24 07:10:21+00:00             3       OFF   0 days 10:00:00      1

实现代码 #

import pandas as pd

# 初始化nights列为0
df['nights'] = 0

# 遍历所有带有效time值的OFF记录
for idx, row in df[df['time'].notna()].iterrows():
    # 定位当前OFF记录前、同编号灯泡的最后一次开启时间
    on_time = df.loc[:idx][(df.loc[:idx, 'code'] == row['code']) & (df.loc[:idx, 'other'] == 'ON')].iloc[-1]['date']
    off_time = row['date']
    count = 0
    # 逐天判断是否完整覆盖夜间时段
    check_date = on_time.normalize()
    while check_date <= off_time.normalize():
        night_start = check_date + pd.Timedelta(hours=22)
        night_end = check_date + pd.Timedelta(days=1, hours=5)
        if on_time <= night_start and off_time >= night_end:
            count += 1
        check_date += pd.Timedelta(days=1)
    df.loc[idx, 'nights'] = count

运行代码后输出结果与期望示例完全一致。

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