基于Python Pandas实现无重复计数的颜色展示时长统计

核心逻辑 #

要实现重叠时段不重复统计，核心思路如下：

• 先将每条记录的起始时间转换为带日期的完整时间戳，同时基于时长计算每条记录的结束时间
• 自定义区间合并工具函数：对同一统计维度（单日单颜色/单日全颜色）的所有时间段按开始时间排序，合并重叠/包含的区间后计算总时长，最终结果与24小时（86400秒）取最小值，符合时长上限要求
• 分别按「日期+颜色」「日期」两个维度分组统计，即可得到单颜色去重时长和全颜色合计去重时长

完整代码实现 #

import pandas as pd

# 示例数据构造
data = [
    ["2021-07-06", "RED", "11:00:00.00", 5],
    ["2021-07-06", "RED", "11:00:00.00", 9],
    ["2021-07-06", "BLUE", "11:00:00.00", 3],
    ["2021-07-06", "RED", "11:00:00.00", 3],
    ["2021-07-06", "BLUE", "12:00:00.00", 10],
    ["2021-07-06", "BLUE", "12:00:00.00", 7],
    ["2021-07-06", "RED", "12:00:00.00", 9],
    ["2021-07-06", "BLUE", "12:00:00.00", 5],
    ["2021-07-06", "RED", "12:00:00.00", 1],
    ["2021-07-06", "RED", "12:00:00.00", 2]
]
df = pd.DataFrame(data, columns=["date", "color", "start time", "duration(seconds)"])

# 1. 时间预处理：计算每条记录的完整开始、结束时间戳
df["start"] = pd.to_datetime(df["date"] + " " + df["start time"])
df["end"] = df["start"] + pd.to_timedelta(df["duration(seconds)"], unit="s")

# 2. 自定义合并重叠区间、计算去重时长的函数
def calc_non_overlap_duration(intervals):
    if len(intervals) == 0:
        return 0
    # 按区间开始时间排序
    sorted_intervals = sorted(intervals, key=lambda x: x[0])
    merged = [sorted_intervals[0]]
    for curr_start, curr_end in sorted_intervals[1:]:
        last_start, last_end = merged[-1]
        # 当前区间和上一个合并区间重叠，合并处理
        if curr_start <= last_end:
            merged[-1] = (last_start, max(last_end, curr_end))
        else:
            merged.append((curr_start, curr_end))
    # 统计总时长，上限为24小时
    total_seconds = sum((end - start).total_seconds() for start, end in merged)
    return min(total_seconds, 86400)

# 3. 构造每条记录的时间区间
df["interval"] = list(zip(df["start"], df["end"]))

# 4. 统计每日各颜色去重时长
color_daily_duration = df.groupby(["date", "color"])["interval"].apply(
    calc_non_overlap_duration
).reset_index(name="duration_seconds")
print("每日各颜色去重时长：")
print(color_daily_duration)
"""
输出结果：
         date color  duration_seconds
0  2021-07-06  BLUE              13.0
1  2021-07-06   RED              18.0
"""

# 5. 统计每日所有颜色合计去重时长
total_daily_duration = df.groupby("date")["interval"].apply(
    calc_non_overlap_duration
).reset_index(name="total_duration_seconds")
print("\n每日全颜色合计去重时长：")
print(total_daily_duration)
"""
输出结果：
         date  total_duration_seconds
0  2021-07-06                    19.0
"""

结果说明 #

运行代码后得到的结果和示例预期完全一致：2021-07-06红色去重时长18秒、蓝色13秒，合计去重总时长19秒。

内容的提问来源于stack exchange，提问作者left-them-on-red