将DataFrame的timedelta64列导出为Excel的HH:MM:SS格式列失败排查

问题根源 #

1. 数据类型不兼容：Pandas导出timedelta64类型时，会将其转换为以天为单位的浮点数（例如1小时=1/24≈0.0416667），而Excel的HH:MM:SS格式是针对原生日期/时间类型的单元格，直接给浮点数单元格套用该格式无法正确解析为时长。
2. 格式优先级冲突：df.to_excel()会自动为timedelta64列添加单元格级的默认格式，后续通过worksheet.set_column()设置的列级格式会被覆盖，无法生效。

解决方法 #

将timedelta64转换为Excel兼容的时长数值（以天为单位的浮点数），再设置对应的时长格式，同时避免Pandas自动添加的格式覆盖自定义设置：

import pandas as pd

data = {
    "date": [
        "2023-02-05",
        "2023-02-05",
        "2022-12-02",
        "2022-11-29",
        "2022-11-18",
    ],
    "duration": [
        "01:07:48",
        "05:23:06",
        "02:41:58",
        "00:35:11",
        "02:00:20",
    ],
}

df = pd.DataFrame(data)
df['date'] = pd.to_datetime(df['date'], format='%Y-%m-%d')
df['duration'] = pd.to_timedelta(df['duration'])

# 将timedelta转换为Excel可识别的时长数值（总秒数/一天的秒数=天数）
df['duration'] = df['duration'].dt.total_seconds() / 86400

with pd.ExcelWriter(
    "df.xlsx",
    datetime_format="YYYY-MM-DD",
    engine="xlsxwriter",
) as writer:
    workbook = writer.book
    # 用[HH]:MM:SS支持超过24小时的时长，若确定时长不超24小时可改用HH:MM:SS
    time_format = workbook.add_format({"num_format": "[HH]:MM:SS"})
    df.to_excel(writer, sheet_name="sheet", index=False)
    worksheet = writer.sheets["sheet"]
    worksheet.set_column("A:A", 20)
    worksheet.set_column("B:B", 15, cell_format=time_format)

补充说明 #

• 使用[HH]:MM:SS格式可以正确显示超过24小时的时长，若你的数据时长均不超过24小时，直接用HH:MM:SS即可。
• 转换后的数值仍可在Excel中进行时长相关的计算（比如求和、平均值），保留了数据的可操作性。

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