Python Pandas自定义时间格式在Excel输出 [英] Python Pandas custom time format in Excel output

问题描述

我已经使用 pandas.groupby 在两列上分组一个大熊猫DataFrame，并计算平均和中位数。我的结果数据集看起来与此类似：

I have used pandas.groupby to group a pandas DataFrame on two columns and calculate average and median times. My resulting dataset looks similar to this:

Size        Category        Average Time        Median Time
 1             A            0.002056385         0.000310995
               B                                0.000310995
               C            0.000310995
 10            A                                0.001852681
               B            0.000310995
               C            0.000310995

我想将此表导出为ex​​cel，并将Excel格式化为Excel中的自定义格式（hh：mm：ss.000）。换句话说，我想把时间看成毫秒级。例如，以这种方式格式化的0.000310995显示为00：00：26.870（26.870秒）。

I would like to export this table to excel and format the Time Columns as a custom format in Excel like so (hh:mm:ss.000). In other words, I want to view the times as millisecond-level times. For example, 0.000310995 formatted in this fashion displays as 00:00:26.870 (26.870 seconds).

有没有人对如何完成这个专长有任何见解？

Does anyone have any insight on how to accomplish this feat?

更新：

使用 to_datetime（df ['Average Time ']，unit ='d'）。我的时代现在被格式化为DataFrame中的 1970-01-01 00：02：57.638400 。但是，当使用 to_excel 导出到Excel时，它们格式化为 1970-01-01 00:02:58 Excel输出。在这一点上，我只需要删除日期部分，并添加毫秒的精度来实现我的目标。任何想法？

I have gotten a bit closer by using to_datetime(df['Average Time'], unit='d'). My times are now formatted like 1970-01-01 00:02:57.638400 in the DataFrame. However, when using to_excel to export to Excel they are formatted as 1970-01-01 00:02:58 in the Excel output. At this point, I only need to drop the date portion and add millisecond precision to achieve my goal. Any thoughts?

非常感谢您提供的任何帮助 -

Thanks very much in advance for any help you can offer -

推荐答案

您可以在熊猫中使用 ExcelWriter 的 datetime_format 参数：

You can use the datetime_format parameter of ExcelWriter in Pandas:

import pandas as pd
from datetime import datetime


df = pd.DataFrame([datetime(2014, 9, 18, 12, 30, 5, 60000)])

writer = pd.ExcelWriter("time.xlsx",  datetime_format='hh:mm:ss.000')

df.to_excel(writer, "Sheet1")

writer.close()

其中给出以下输出：

另请参见使用Python Pandas和XlsxWriter 。

这篇关于Python Pandas自定义时间格式在Excel输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！