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?
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