基于Python Pandas计算早中晚各时段工作时长求助

步骤1：数据预处理，转换时间格式 #

首先需要把字符串格式的时间转换为Pandas可计算的datetime和timedelta类型，这是后续计算的基础。

import pandas as pd
from datetime import datetime, timedelta

# 原始数据
data = [ ('employee1', '2022-10-28', '12:06', '13:00:00', '00:00:00', '00:00:23'),
        ('employee2','2022-10-28', '10:00', '06:00:00', '00:00:00', '00:00:16'),
        ('employee3', '2022-05-06', '16:13', '08:00:00', '00:54:00', '00:00:09'),
        ('employee4', '2022-06-03', '2:33', '09:00:00', '00:19:00', '00:00:56'),
        ('employee5', '2022-08-12', '9:50', '20:00:00', '00:27:00', '00:00:22'),
        ('employee6', '2022-02-15', '6:52', '00:00:00', '00:35:00','00:00:35')]

df = pd.DataFrame(data, columns =['Name','date','start_time','hours_worked','minutes_worked','seconds_worked'])

# 1. 合并日期与开始时间，转换为datetime类型（自动补全不规范的时间格式）
df['start_datetime'] = pd.to_datetime(df['date'] + ' ' + df['start_time'], format='%Y-%m-%d %H:%M')

# 2. 计算总工作时长：把时/分/秒转换为总秒数，再转为timedelta类型
df['total_time_wkd'] = pd.to_timedelta(
    df['hours_worked'].str.split(':').str[0].astype(int) * 3600 +
    df['minutes_worked'].str.split(':').str[1].astype(int) * 60 +
    df['seconds_worked'].str.split(':').str[2].astype(int),
    unit='s'
)

# 3. 计算工作结束时间
df['end_datetime'] = df['start_datetime'] + df['total_time_wkd']

步骤2：定义时段划分与时长计算函数 #

编写一个函数，输入工作的起止时间，返回三个时段的重叠工作时长。重点处理晚班跨天的情况（当天17:00到次日03:00）。

def calculate_shift_hours(start_dt, end_dt):
    start_date = start_dt.date()
    end_date = end_dt.date()
    
    # 定义各时段的时间边界
    # 早班：当天03:00 - 12:00
    morning_start = datetime.combine(start_date, datetime.strptime('03:00', '%H:%M').time())
    morning_end = datetime.combine(start_date, datetime.strptime('12:00', '%H:%M').time())
    
    # 中班：当天12:00 - 17:00
    afternoon_start = datetime.combine(start_date, datetime.strptime('12:00', '%H:%M').time())
    afternoon_end = datetime.combine(start_date, datetime.strptime('17:00', '%H:%M').time())
    
    # 晚班分两部分：当天17:00-24:00，次日00:00-03:00（跨天情况）
    evening1_start = datetime.combine(start_date, datetime.strptime('17:00', '%H:%M').time())
    evening1_end = datetime.combine(start_date + timedelta(days=1), datetime.strptime('00:00', '%H:%M').time())
    evening2_start = evening1_end
    evening2_end = datetime.combine(start_date + timedelta(days=1), datetime.strptime('03:00', '%H:%M').time())
    
    # 计算两个时间区间的重叠时长，无重叠则返回0
    def get_overlap(a_start, a_end, b_start, b_end):
        overlap_start = max(a_start, b_start)
        overlap_end = min(a_end, b_end)
        return max(timedelta(0), overlap_end - overlap_start)
    
    # 计算各时段时长
    morning_hours = get_overlap(start_dt, end_dt, morning_start, morning_end)
    afternoon_hours = get_overlap(start_dt, end_dt, afternoon_start, afternoon_end)
    evening_hours = get_overlap(start_dt, end_dt, evening1_start, evening1_end)
    
    # 如果工作跨天，加上次日00:00-03:00的重叠时长
    if end_date > start_date:
        evening_hours += get_overlap(start_dt, end_dt, evening2_start, evening2_end)
    
    return morning_hours, afternoon_hours, evening_hours

步骤3：应用函数到DataFrame #

将计算函数应用到每一行，得到三个时段的时长，并转换为HH:MM:SS格式的字符串方便查看。

# 应用函数生成时段时长列
df[['time_worked_morning', 'time_worked_afternoon', 'time_worked_evening']] = df.apply(
    lambda row: calculate_shift_hours(row['start_datetime'], row['end_datetime']),
    axis=1, result_type='expand'
)

# 将timedelta转换为HH:MM:SS格式的字符串
def timedelta_to_str(td):
    total_seconds = int(td.total_seconds())
    hours = total_seconds // 3600
    minutes = (total_seconds % 3600) // 60
    seconds = total_seconds % 60
    return f"{hours:02d}:{minutes:02d}:{seconds:02d}"

df['time_worked_morning_str'] = df['time_worked_morning'].apply(timedelta_to_str)
df['time_worked_afternoon_str'] = df['time_worked_afternoon'].apply(timedelta_to_str)
df['time_worked_evening_str'] = df['time_worked_evening'].apply(timedelta_to_str)

查看结果 #

运行以下代码可以查看核心结果：

print(df[['Name', 'time_worked_morning_str', 'time_worked_afternoon_str', 'time_worked_evening_str']])

输出示例：

Name time_worked_morning_str time_worked_afternoon_str time_worked_evening_str
0  employee1                00:00:00                04:53:37                08:06:26
1  employee2                01:59:44                04:00:32                00:00:00
2  employee3                00:00:00                00:47:00                08:07:09
3  employee4                08:52:56                00:00:00                00:27:00
4  employee5                02:10:00                05:00:00                13:17:22
5  employee6                05:08:00                00:00:00                00:27:35

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