9

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

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?

UPDATE:

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 -

wrcobb
  • 543
  • 3
  • 7
  • 17
  • 1
    @pnuts added an example via an edit to the original post – wrcobb Sep 17 '14 at 19:42
  • @pnuts Very good point - your question made me realize that my problem is probably best solved by converting the float64 to a formatted time within pandas before exporting to Excel. Any thoughts on the best way to accomplish this? I'm using `to_datetime` but having some trouble with the `format=` string. These times are displaying as 1970-01-01 when i convert to datetimes. – wrcobb Sep 17 '14 at 20:27

1 Answers1

15

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()

Which gives the following output:

enter image description here

See also Working with Python Pandas and XlsxWriter.

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
  • 1
    I wonder how this would work with the `with pd.ExcelWriter('myexcel.xlsx') as writer:` if there is _datetime_ (=`df.index`), a _date_, a _time_, and _month_ column. Do I need to use the extended version as shown in the linked documentation? – DaCoEx Feb 17 '16 at 10:46
  • 1
    It should work. If not post a new question with some sample code. – jmcnamara Feb 17 '16 at 15:23