如何用Pandas（或无需窗口函数）消除促销活动时间戳重叠？

问题背景 #

需要处理存储促销活动的DataFrame，去除同一店铺（store）、同一活动ID（id）下的重复时间段，同时合并连续或重叠的促销时段，并且希望了解是否存在无需窗口函数的实现方案。

示例数据 #

import pandas as pd
import numpy as np
from pandas import Timestamp

t1 = np.array([['Store A', 32,
        Timestamp('2024-02-15 00:00:00'),
        Timestamp('2024-03-14 00:00:00')],
       ['Store A', 32,
        Timestamp('2024-02-15 00:00:00'),
        Timestamp('2024-03-14 00:00:00')],
       ['Store A', 32,
        Timestamp('2024-02-15 00:00:00'),
        Timestamp('2024-03-14 00:00:00')],
       ['Store A', 32,
        Timestamp('2024-03-14 00:00:00'),
        Timestamp('2024-04-11 00:00:00')],
       ['Store A', 32,
        Timestamp('2024-03-14 00:00:00'),
        Timestamp('2024-04-11 00:00:00')],
       ['Store A', 32,
        Timestamp('2024-03-14 00:00:00'),
        Timestamp('2024-04-11 00:00:00')],
       ['Store A', 32,
        Timestamp('2024-03-14 00:00:00'),
        Timestamp('2024-04-11 00:00:00')],
       ['Store A', 35,
        Timestamp('2024-02-15 00:00:00'),
        Timestamp('2024-03-14 00:00:00')],
       ['Store A', 35,
        Timestamp('2024-02-15 00:00:00'),
        Timestamp('2024-03-14 00:00:00')],
       ['Store A', 35,
        Timestamp('2024-02-15 00:00:00'),
        Timestamp('2024-03-14 00:00:00')],
       ['Store A', 35,
        Timestamp('2024-02-15 00:00:00'),
        Timestamp('2024-03-14 00:00:00')],
       ['Store A', 35,
        Timestamp('2024-03-14 00:00:00'),
        Timestamp('2024-04-11 00:00:00')],
       ['Store A', 35,
        Timestamp('2024-03-14 00:00:00'),
        Timestamp('2024-04-11 00:00:00')],
       ['Store A', 35,
        Timestamp('2024-03-14 00:00:00'),
        Timestamp('2024-04-11 00:00:00')],
       ['Store B', 66,
        Timestamp('2024-02-15 00:00:00'),
        Timestamp('2024-03-14 00:00:00')],
       ['Store B', 66,
        Timestamp('2024-02-15 00:00:00'),
        Timestamp('2024-03-14 00:00:00')],
       ['Store B', 66,
        Timestamp('2024-02-15 00:00:00'),
        Timestamp('2024-03-14 00:00:00')],
       ['Store B', 66,
        Timestamp('2024-02-15 00:00:00'),
        Timestamp('2024-03-14 00:00:00')],
       ['Store B', 66,
        Timestamp('2024-03-14 00:00:00'),
        Timestamp('2024-04-11 00:00:00')],
       ['Store B', 66,
        Timestamp('2024-03-14 00:00:00'),
        Timestamp('2024-04-11 00:00:00')]], dtype=object)

df = pd.DataFrame(t1)
df.columns = ['store', 'id', 'start', 'end']

解决方案 #

步骤1：去除完全重复的行 #

先清理掉完全相同的重复记录，只保留唯一的时间段条目：

df_unique = df.drop_duplicates(subset=['store', 'id', 'start', 'end']).reset_index(drop=True)

方法1：使用窗口函数（高效处理大数据） #

通过分组排序+标记合并组的方式，快速完成连续/重叠时间段的合并：

# 按店铺、ID、开始时间排序
df_sorted = df_unique.sort_values(['store', 'id', 'start'])

# 标记每个需要合并的组：当前时间段的开始时间 > 之前所有时间段的最晚结束时间时，开启新组
df_sorted['group'] = (df_sorted.groupby(['store', 'id'])['start']
                      .apply(lambda x: x > x.shift().cummax())
                      .cumsum())

# 分组聚合，取每个组的最早开始时间和最晚结束时间
result = df_sorted.groupby(['store', 'id', 'group'], as_index=False).agg(
    start=('start', 'min'),
    end=('end', 'max')
).drop(columns='group')

方法2：无需窗口函数（逻辑直观，适合小数据） #

通过分组后逐个遍历合并时间段，完全不依赖窗口函数：

def merge_intervals(intervals):
    # 按开始时间排序时间段
    sorted_intervals = sorted(intervals, key=lambda x: x[0])
    merged = []
    for curr_start, curr_end in sorted_intervals:
        if not merged:
            merged.append([curr_start, curr_end])
        else:
            last_start, last_end = merged[-1]
            # 若当前时间段与上一个连续或重叠，则合并
            if curr_start <= last_end:
                merged[-1][1] = max(last_end, curr_end)
            else:
                merged.append([curr_start, curr_end])
    return merged

# 按店铺和ID分组处理每个组的时间段
merged_data = []
for (store, id_val), group_df in df_unique.groupby(['store', 'id']):
    intervals = list(zip(group_df['start'], group_df['end']))
    merged = merge_intervals(intervals)
    for start, end in merged:
        merged_data.append({'store': store, 'id': id_val, 'start': start, 'end': end})

result_no_window = pd.DataFrame(merged_data)

两种方法最终得到的结果一致，都会将同一店铺、同一ID下的连续/重叠时间段合并为单一的时段，同时去除了重复记录。

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