Export DataFrame timedelta column to timestamp Excel column

0 votes

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:

Excel file

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.

Feb 6, 2023 in Others by Kithuzzz
• 38,010 points
1,036 views

1 answer to this question.

0 votes

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:

enter image description here

answered Feb 6, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

datatable remove column on export to pdf and excel

When we are using jquery datatable for ...READ MORE

answered Feb 17, 2022 in Others by gaurav
• 23,260 points
3,548 views
0 votes
1 answer

Merge and export Excel/Word/PDF to PDF

Use GroupDocs.Merger for .NET API to merge Word, Excel, ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
869 views
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,420 points
1,530 views
0 votes
1 answer

Append same text to every cell in a column in Excel

Solution All your data is in column A ...READ MORE

answered Oct 17, 2022 in Others by narikkadan
• 63,420 points
3,223 views
0 votes
1 answer

Convert column in excel date format (DDDDD.tttt) to datetime using pandas

Given # s = df['date'] s 0 ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,420 points
2,727 views
0 votes
1 answer

How to freeze the top row and the first column using XlsxWriter?

You can use worksheet.freeze_panes() to achieve this . There ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 63,420 points
1,704 views
0 votes
1 answer

Compare 2 columns in same excel sheet in pandas

Try this: import pandas as pd import numpy as ...READ MORE

answered Dec 16, 2022 in Others by narikkadan
• 63,420 points
2,055 views
0 votes
1 answer
0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,420 points
1,762 views
0 votes
1 answer

Output python dataframe to excel and create a new data_validation column in the exported excel sheet

Use pandas.ExcelWriter with worksheet.data_validation from xlswriter : df["code"] = None items = list(range(1,10)) max_row, max_col = ...READ MORE

answered Jan 24, 2023 in Others by narikkadan
• 63,420 points
864 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP