Python中如何用Explode/Groupby实现排班时间序列转换？

需要将包含员工周一排班信息（Mon Start、Mon End、hours字段）的DataFrame，转换为按15分钟时间窗口统计在岗人数的DataFrame用于可视化。尝试过groupby、explode方法但没找到正确实现方式，附上的初始代码无法正确累加每个15分钟窗口的工作人数，求解决方案。

初始代码 #

import pandas as pd
import datetime

df = pd.DataFrame({'Mon Start': ['6:00', '7:00', '8:00', '9:00'], 'Mon End': ['14:30', '15:30', '16:30', '17:30'], 'hours': [8.50, 8.50, 8.50, 8.50]})

df_transposed_list = []

for i, row in df.iterrows():

    start_time = datetime.datetime.strptime(row['Mon Start'], '%H:%M')
    end_time = datetime.datetime.strptime(row['Mon End'], '%H:%M')

    time_intervals = [start_time + datetime.timedelta(minutes=15*i) for i in range(int((end_time - start_time).total_seconds() // 900 + 1))]

    num_people = row['hours'] / ((end_time - start_time).total_seconds() / 3600)

    hours_worked = []
    for j in range(len(time_intervals)):
        if time_intervals[j].minute in [0, 15, 30, 45]:
            if j == 0:
                hours_worked.append(min(num_people * 0.25, (15 - start_time.minute) / 60))
            elif j == len(time_intervals) - 1:
                hours_worked.append(min(num_people * 0.25, end_time.minute / 60))
            else:
                if time_intervals[j].hour < 7:
                    hours_worked.append(num_people * 0.25)
                elif num_people == 1:
                    hours_worked.append(num_people * 0.25)
                elif num_people == 2 and time_intervals[j].hour < 8:
                    hours_worked.append(num_people * 0.25)
                elif num_people == 2 and time_intervals[j].hour >= 8:
                    hours_worked.append(num_people * 0.5)
                elif num_people == 3 and time_intervals[j].hour < 8:
                    hours_worked.append(num_people * 0.25)
                elif num_people == 3 and time_intervals[j].hour >= 8:
                    hours_worked.append(num_people * 0.75)
        else:
            hours_worked.append(0)

    df_transposed = pd.DataFrame({'Time': [t.strftime('%H:%M') for t in time_intervals], 'Hours Worked': hours_worked})

    df_transposed_list.append(df_transposed)

df_transposed_all = pd.concat(df_transposed_list, axis=0, ignore_index=True)

print(df_transposed_all)

解决方案 #

核心思路 #

1. 将每个员工的排班起止时间转换为datetime类型，生成该员工覆盖的所有15分钟时间窗口。
2. 把每个员工对应的窗口列表拆分为单独行记录，标记该员工在对应窗口内的在岗状态。
3. 按时间窗口分组，直接累加每个窗口的在岗人数。

实现代码 #

import pandas as pd
import datetime

# 初始排班数据
df = pd.DataFrame({
    'Mon Start': ['6:00', '7:00', '8:00', '9:00'],
    'Mon End': ['14:30', '15:30', '16:30', '17:30'],
    'hours': [8.50, 8.50, 8.50, 8.50]
})

# 转换时间字段为datetime格式
df['Mon Start'] = pd.to_datetime(df['Mon Start'], format='%H:%M')
df['Mon End'] = pd.to_datetime(df['Mon End'], format='%H:%M')

# 生成单个员工对应的所有有效15分钟时间窗口
def generate_valid_windows(row):
    # 取排班时段的首尾15分钟对齐窗口
    window_start = row['Mon Start'].floor('15min')
    window_end = row['Mon End'].ceil('15min')
    # 生成所有15分钟间隔的时间点
    all_windows = pd.date_range(start=window_start, end=window_end, freq='15min')
    # 过滤掉与排班时间完全不重叠的窗口
    valid_windows = []
    for window in all_windows:
        current_window_end = window + datetime.timedelta(minutes=15)
        # 只要窗口和排班时间有交集就算有效
        if not (current_window_end <= row['Mon Start'] or window >= row['Mon End']):
            valid_windows.append(window.strftime('%H:%M'))
    return valid_windows

# 为每个员工生成对应的时间窗口列表
df['time_windows'] = df.apply(generate_valid_windows, axis=1)

# 展开窗口列表为单独行
df_exploded = df.explode('time_windows')

# 按时间窗口分组统计在岗人数
result = df_exploded.groupby('time_windows').size().reset_index(name='在岗人数')

# 按时间排序，优化可视化效果
result['time_windows'] = pd.to_datetime(result['time_windows'], format='%H:%M')
result = result.sort_values('time_windows').reset_index(drop=True)
result['time_windows'] = result['time_windows'].dt.strftime('%H:%M')

print(result)

代码说明 #

• 时间对齐处理：用floor和ceil确保生成的时间窗口完全覆盖员工排班时段，避免遗漏边缘时间点。
• 有效窗口过滤：通过判断窗口与排班时间的重叠关系，排除完全不在工作时段内的窗口，保证统计准确性。
• 分组统计：explode方法拆分员工与窗口的对应关系后，用groupby.size()直接累加每个窗口的在岗人数，解决了原代码无法正确求和的问题。
• 排序优化：最后按时间排序结果，让输出更符合可视化的时序需求。

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