Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
745 views
in Technique[技术] by (71.8m points)

python - formatting timedelta64 when using pandas.to_excel

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...