如何基于时间间隔扩展Pandas DataFrame行并处理可选休息时段

我有一个记录考勤的Pandas DataFrame，包含Start Time、End Time，以及可选的Rest Break、Meal Break起止时间。需要将单行数据扩展为带正确时间间隔的多行数据，需满足：

• 考勤表可能无任何休息时段
• 考勤表可能仅含休息时段、仅含用餐时段或两者皆有
• 用餐与休息时段顺序不固定

示例输入DataFrame #

所需输出DataFrame #

当前代码（缺少Category列赋值） #

import pandas as pd

# Your original DataFrame
data = {'Id': [1, 2, 3, 4, 5],
    'Start Time': ['2024-01-26 07:59', '2024-01-26 14:29', '2024-01-26 08:02', '2024-01-26 09:15', '2024-01-26 09:10'],
    'End Time': ['2024-01-26 12:33', '2024-01-26 17:35', '2024-01-26 12:45', '2024-01-26 16:15', '2024-01-26 16:37'],
    'Rest Break Start Time': ['2024-01-26 10:43', '2024-01-26 16:33', None, None, '2024-01-26 15:43'],
    'Rest Break End Time': ['2024-01-26 10:53', '2024-01-26 16:44', None, None, '2024-01-26 15:55'],
    'Meal Break Start Time': ['2024-01-26 12:03', None, None, '2024-01-26 12:15', '2024-01-26 13:06'],
    'Meal Break End Time': ['2024-01-26 12:33', None, None, '2024-01-26 12:45', '2024-01-26 13:37']}

df = pd.DataFrame(data)

# Create an empty DataFrame to store the expanded rows
expanded_df = pd.DataFrame(columns=['Id', 'Start Time', 'End Time'])

# Iterate through each row of the original DataFrame
for index, row in df.iterrows():
    id_value = row['Id']
    start_time = pd.to_datetime(row['Start Time'])
    end_time = pd.to_datetime(row['End Time'])

    # Collect all times
    times = {start_time, end_time}
    for column in ['Rest Break Start Time', 'Rest Break End Time', 'Meal Break Start Time', 'Meal Break End Time']:
        if not pd.isna(row[column]):
            times.add(pd.to_datetime(row[column]))

    # Sort the times
    sorted_times = sorted(times)

    # Create intervals
    for i in range(len(sorted_times) - 1):
        if sorted_times[i] != sorted_times[i + 1]:
            expanded_df = expanded_df.append({'Id': id_value, 'Start Time': sorted_times[i], 'End Time': sorted_times[i + 1]}, ignore_index=True)

# Sort the expanded DataFrame by 'Id' and 'Start Time'
expanded_df = expanded_df.sort_values(by=['Id', 'Start Time']).reset_index(drop=True)

# Show the result
print(expanded_df)

解决方案 #

要解决分类列赋值问题，核心思路是：

1. 先收集每行所有的休息/用餐时段的起止时间与对应分类，形成一个时段-分类映射字典
2. 排序所有时间戳后，遍历每个时间间隔：
   • 如果间隔的起始时间是某个休息/用餐时段的开始，则分类为对应的Rest Break或Meal Break
   • 否则分类为Session

修改后的完整代码：

import pandas as pd

# 原始数据
data = {'Id': [1, 2, 3, 4, 5],
    'Start Time': ['2024-01-26 07:59', '2024-01-26 14:29', '2024-01-26 08:02', '2024-01-26 09:15', '2024-01-26 09:10'],
    'End Time': ['2024-01-26 12:33', '2024-01-26 17:35', '2024-01-26 12:45', '2024-01-26 16:15', '2024-01-26 16:37'],
    'Rest Break Start Time': ['2024-01-26 10:43', '2024-01-26 16:33', None, None, '2024-01-26 15:43'],
    'Rest Break End Time': ['2024-01-26 10:53', '2024-01-26 16:44', None, None, '2024-01-26 15:55'],
    'Meal Break Start Time': ['2024-01-26 12:03', None, None, '2024-01-26 12:15', '2024-01-26 13:06'],
    'Meal Break End Time': ['2024-01-26 12:33', None, None, '2024-01-26 12:45', '2024-01-26 13:37']}

df = pd.DataFrame(data)

# 转换所有时间列为datetime类型
time_cols = ['Start Time', 'End Time', 'Rest Break Start Time', 'Rest Break End Time', 
             'Meal Break Start Time', 'Meal Break End Time']
df[time_cols] = df[time_cols].apply(pd.to_datetime)

expanded_rows = []

for _, row in df.iterrows():
    id_val = row['Id']
    # 收集所有休息/用餐时段的起止与分类
    break_intervals = {}
    # 处理Rest Break
    if not pd.isna(row['Rest Break Start Time']) and not pd.isna(row['Rest Break End Time']):
        break_intervals[row['Rest Break Start Time']] = ('Rest Break', row['Rest Break End Time'])
    # 处理Meal Break
    if not pd.isna(row['Meal Break Start Time']) and not pd.isna(row['Meal Break End Time']):
        break_intervals[row['Meal Break Start Time']] = ('Meal Break', row['Meal Break End Time'])
    
    # 收集所有时间点
    all_times = {row['Start Time'], row['End Time']}
    for start, (_, end) in break_intervals.items():
        all_times.add(start)
        all_times.add(end)
    sorted_times = sorted(all_times)
    
    # 生成每个时间间隔的分类
    current_time = sorted_times[0]
    for next_time in sorted_times[1:]:
        if current_time == next_time:
            current_time = next_time
            continue
        # 判断当前间隔是否为休息/用餐时段
        if current_time in break_intervals:
            category, _ = break_intervals[current_time]
        else:
            category = 'Session'
        # 添加到结果列表
        expanded_rows.append({
            'Id': id_val,
            'Category': category,
            'Start Time': current_time,
            'End Time': next_time
        })
        current_time = next_time

# 转换为DataFrame并排序
expanded_df = pd.DataFrame(expanded_rows).sort_values(by=['Id', 'Start Time']).reset_index(drop=True)

print(expanded_df)

代码说明 #

• 先将所有时间列统一转换为datetime类型，避免类型问题
• 为每行构建break_intervals字典，键是休息/用餐的开始时间，值是（分类名称，结束时间）
• 遍历排序后的时间戳，对每个间隔判断起始时间是否在break_intervals中，以此分配分类
• 使用列表收集行数据再转换为DataFrame，比append方法效率更高

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