如何将Excel中超过24小时的时长数据导入Pandas并转为timedelta

问题原因 #

Excel中超过24小时的时长会被底层存储为日期时间类型（Excel的时间本质是天数的小数），pd.read_excel会优先解析单元格的格式，直接指定dtype={'Total time':'str'}无法获取到Excel单元格显示的原始时长字符串，最终导致格式混乱且无法直接转为timedelta。

方法1：直接读取单元格显示的字符串（推荐） #

通过openpyxl读取Excel单元格的显示文本，跳过Pandas的自动解析，直接拿到原始时长格式：

import pandas as pd
from openpyxl import load_workbook

# 加载Excel文件
wb = load_workbook('data.xlsx')
ws = wb.active

# 提取所有单元格的显示文本
data = []
for row in ws.iter_rows(values_only=False):
    row_data = [cell.text for cell in row]
    data.append(row_data)

# 转为DataFrame并转换为timedelta类型
df = pd.DataFrame(data[1:], columns=data[0])
df['Total time'] = pd.to_timedelta(df['Total time'])

方法2：对已导入的混乱数据进行转换 #

如果已经完成数据导入，可以通过手动计算将日期时间格式转换为对应时长：

import pandas as pd
from datetime import datetime

# 读取原始数据
df = pd.read_excel('data.xlsx')

def convert_to_timedelta(value):
    if isinstance(value, datetime):
        # Excel日期起点为1899-12-30（兼容Excel的1900闰年bug），计算与该日期的时间差
        return value - datetime(1899, 12, 30)
    else:
        # 纯时间格式直接转换
        return pd.to_timedelta(str(value))

df['Total time'] = df['Total time'].apply(convert_to_timedelta)

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