问题描述
我使用 pandas.groupby 将 pandas 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
我想将此表导出到 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 时,它们在 Excel 输出中被格式化为 1970-01-01 00:02:58.此时,我只需要删除日期部分并添加毫秒精度即可实现我的目标.有什么想法吗?
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 -
推荐答案
可以在 Pandas 中使用 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.