Export DataFrame timedelta column to timestamp Excel column

Question

A datetime64 and a timedelta64 are contained in a DataFrame that I have. Unfortunately, I'm unable to export the latter to an Excel file with a properly formed hh:mm:ss column:

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'])

with pd.ExcelWriter(
    "df.xlsx",
    datetime_format="YYYY-MM-DD",
    engine="xlsxwriter",
) as writer:
    workbook = writer.book
    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", 50, cell_format=time_format)

The resulting Excel file will display like this:

So, the date_time format in the ExcelWriter object is applied correctly for column A, as well as the width setting for column B, but the number formatting isn't working.

Answer 1

The reason that the column format isn't being applied is that Pandas is applying a cell number format of "0" to the timedelta values. The cell format overrides the column format so that isn't applied. You can verify this by adding the following at the end of the with statement and you will see that it is formatted as expected:

    worksheet.write(7, 1, .5)

I'm not sure what is the best way to work around but you could iterate over the timedelta values and rewrite them out to override the pandas formatted values. Something like this:

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'])

with pd.ExcelWriter(
    "df.xlsx",
    datetime_format="YYYY-MM-DD",
    engine="xlsxwriter",
) as writer:
    workbook = writer.book
    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", 50, cell_format=time_format)

    col = df.columns.get_loc('duration')
    for row, timedelta in enumerate(df['duration'], 1):
        worksheet.write(row, col, timedelta)

Output: