如何在Python DataFrame中查找用户重叠时段及对应行索引

给定如下Pandas DataFrame，包含用户姓名、时段起始时间、结束时间及占比字段，其中用户Anne存在3个重叠时段：

• 2019-01-01 至 2019-01-31
• 2019-02-01 至 2019-02-28
• 2019-01-15 至 2019-02-15

需求：按用户维度，找出所有存在时段重叠的行，并返回这些行的索引。

原始DataFrame构建代码 #

import pandas as pd

df = pd.DataFrame({'Name':['Anne','Anne','Anne','Anne','Anne','Anne','Anne','Anne','Anne','Anne','Anne','Anne',
                           'Bob','Bob','Bob','Bob','Bob','Bob','Bob','Bob','Bob','Bob','Bob','Bob'],
               
               "start":["2019-01-01", "2019-02-01", "2019-03-01", "2019-04-01", "2019-05-01", "2019-06-01", "2019-07-01", "2019-08-01", "2019-09-01", "2019-10-01", "2019-11-01", "2019-12-01",  
                        "2019-01-01", "2019-02-01", "2019-03-01", "2019-04-01", "2019-05-01", "2019-06-01", "2019-07-01", "2019-08-01", "2019-09-01", "2019-10-01", "2019-11-01", "2019-12-01"],
               
                 "end":["2019-01-31", "2019-02-28", "2019-03-31", "2019-04-30", "2019-05-31", "2019-06-30", "2019-07-31", "2019-08-31", "2019-09-30", "2019-10-31", "2019-11-30", "2019-12-31",
                        "2019-01-31", "2019-02-28", "2019-03-31", "2019-04-30", "2019-05-31", "2019-06-30", "2019-07-31", "2019-08-31", "2019-09-30", "2019-10-31", "2019-11-30", "2019-12-31"],
                 
                "percentage":[1/12, 1/12, 1/12, 1/12, 1/12, 1/12, 1/12, 1/12, 1/12, 1/12, 1/12, 1/12,
                              1/12, 1/12, 1/12, 1/12, 1/12, 1/12, 1/12, 1/12, 1/12, 1/12, 1/12, 1/12]})

# 插入存在重叠的行
df.loc[len(df.index)] = ['Anne', "2019-01-15", "2019-02-15", 1/12] 

# 转换时间格式
df.start = df.start.apply(pd.to_datetime, format="%Y-%m-%d")
df.end   = df.end.apply(pd.to_datetime, format="%Y-%m-%d")

解决方案代码 #

def find_overlapping_indices(group):
    # 按起始时间排序，确保时段顺序正确
    sorted_group = group.sort_values('start')
    # 判断当前时段是否和上一个时段重叠
    overlaps = sorted_group['start'] < sorted_group['end'].shift()
    # 把与当前时段重叠的前一个时段也标记出来，避免遗漏
    overlaps |= overlaps.shift(-1).fillna(False)
    return sorted_group[overlaps].index

# 按用户分组处理，收集所有重叠行的索引
overlap_indices = df.groupby('Name').apply(find_overlapping_indices).explode().tolist()

print("存在时段重叠的行索引：", overlap_indices)

代码说明 #

• 针对每个用户的时段组，先按起始时间排序，保证时段按时间先后排列
• 通过比较当前时段的start和上一个时段的end，判断是否存在重叠
• 用逻辑或操作把前一个重叠的时段也标记上，这样不会漏掉参与重叠的任意一行
• 最后通过分组应用函数，展开结果得到所有重叠行的索引

运行结果：存在时段重叠的行索引： [0, 1, 24]，对应Anne的三个重叠时段行。

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