formatting timedelta64 when using pandas.to_excel

Question

I am writing to an excel file using an ExcelWriter:

writer = pd.ExcelWriter(fn,datetime_format=' d  hh:mm:ss')
df.to_excel(writer,sheet_name='FOO')

The writing operation is successful and opening the corresponding excel file I see datetimes nicely formatted as required. However, another column of the dataframe with dtype timedelta64[ns] is automatically converted to a numerical value, so in Python I see

0 days 00:23:33.499998

while in excel:

 0.016359954

which is likely the same duration converted in number of days. Is there any way to control the timedelta formatting using pd.ExcelWriter?

Answer 1

Excel has no data type for a timedelta or equivalent, so you have a couple imperfect choices.

To keep their "datetime-ness" in Excel, you could convert to a datetime, then display them in Excel with a format showing only the time part.

df = pd.DataFrame({'td': [pd.Timedelta(1, 'h'), pd.Timedelta(1.5, 'h')]})
df['td_datetime']
df['td_datetime'] = df['td'] + pd.Timestamp(0)

writer = pd.ExcelWriter('tmp.xlsx', datetime_format='hh:mm:ss')
df.to_excel(writer)
# tmp.xlsx
# td         td_datetime
# 0.041667   01:00:00
# 0.0625     01:30:00

Alternatively, you could format as string before serializing:

df['td_str'] = df['td'].astype(str)

df
Out[24]: 
        td                     td_str
0 01:00:00  0 days 01:00:00.000000000
1 01:30:00  0 days 01:30:00.000000000

Answer 2

Some addition to the above.

Excel zero date is 1-1-1900, while pandas.TimeStamp(0) gives me 1-1-1970.

So, I changed code to

df['td_datetime'] = df['td'] + pd.Timestamp('1900-01-01')

and now it works correctly (and you can correctly add cells to add timedeltas)

Also you might like to display hours only (not 1 day 1 hour, but 25 hours) and for this you can use the following format:

writer = pd.ExcelWriter('tmp.xlsx', datetime_format='[h]:mm:ss')