将Excel中[h]:mm格式时长转为Pandas timedelta，解决超24小时丢失天数问题

我正在从Excel工作表导入数据，其中Duration字段以[h]:mm格式展示总时长，其底层存储为浮点型天数。想要将该列转为Pandas DataFrame的timedelta类型，但操作后超过24小时的天数部分总是丢失。

现象展示 #

Excel源数据 #

超过24小时的记录已高亮：

Pandas导入后的原始数据 #

BATCH_NO             Duration
354      7154             04:36:00
465      7270             06:35:00
466      7271             08:05:00
467      7272             05:54:00
468      7273             09:10:00
472      7277             06:15:00
476      7280             10:23:00
477      7284             06:09:00
499      7313             06:46:00
503      7322             05:27:00
510      7333             14:15:00
515      7335  1900-01-01 07:51:00
516      7338             07:51:00
517      7339             09:00:00
518      7339             05:29:00
519      7339             09:00:00
520      7339             05:29:00
522      7342             12:10:00
525      7343             08:00:00
530      7346             08:25:00

使用pd.to_datetime转换后的结果 #

天数部分直接被丢弃：

BATCH_NO  Duration
354      7154  04:36:00
465      7270  06:35:00
466      7271  08:05:00
467      7272  05:54:00
468      7273  09:10:00
472      7277  06:15:00
476      7280  10:23:00
477      7284  06:09:00
499      7313  06:46:00
503      7322  05:27:00
510      7333  14:15:00
515      7335  07:51:00
516      7338  07:51:00
517      7339  09:00:00
518      7339  05:29:00
519      7339  09:00:00
520      7339  05:29:00
522      7342  12:10:00
525      7343  08:00:00
530      7346  08:25:00

尝试过的方法及问题 #

• 尝试指定dtype={'Duration': float}导入，报错：float() argument must be a string or a number, not 'datetime.time'
• 指定dtype={'Duration': str}或object可以导入，但列数据类型仍被识别为datetime.time，无法直接转换为包含天数的timedelta

需求：不想修改Excel源数据，也不想通过导出CSV作为中间步骤。

解决方案 #

方法1：直接读取Excel底层的浮点值（推荐） #

利用openpyxl读取单元格的原始浮点天数，再转换为timedelta：

import pandas as pd
from openpyxl import load_workbook

wb = load_workbook('your_file.xlsx', data_only=True)
ws = wb['Sheet1']  # 替换为你的工作表名

# 读取数据并转换时长
data = []
for row in ws.iter_rows(min_row=2, values_only=True):
    batch_no, duration_float = row[0], row[1]
    duration = pd.to_timedelta(duration_float, unit='D')
    data.append({'BATCH_NO': batch_no, 'Duration': duration})

df = pd.DataFrame(data)

方法2：处理DataFrame中的混合类型列 #

如果已经导入DataFrame，区分datetime和time类型分别转换：

import pandas as pd

def convert_to_timedelta(val):
    if isinstance(val, pd.Timestamp):
        # 计算1900-01-01到该时间的间隔
        return val - pd.Timestamp('1900-01-01')
    else:
        # 将time类型转为timedelta
        return pd.to_timedelta(f'{val.hour}:{val.minute}:00')

df['Duration'] = df['Duration'].apply(convert_to_timedelta)

方法3：导入时用converters参数直接转换 #

在read_excel阶段完成转换：

import pandas as pd

def duration_converter(val):
    if isinstance(val, pd.Timestamp):
        return val - pd.Timestamp('1900-01-01')
    else:
        return pd.to_timedelta(f'{val.hour}:{val.minute}:00')

df = pd.read_excel('your_file.xlsx', converters={'Duration': duration_converter})

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