3

I have microsecond resolution in my df which is very important but no matter what I try, I can't get excel to show microsecond resolution with either .xls or .xlsx. Any ideas on how to get them to display without converting to a string explicitly?

stgtscc
  • 970
  • 1
  • 7
  • 19
  • Can you give an example showing this? Pandas' Timestamp is in nanoseconds, so microseconds are no problem – Andy Hayden Feb 08 '14 at 19:56
  • @AndyHayden actually, pandas does support it however no matter what you do Excel does not support microsecond resolution. Pretty annoying if you ask me. What I'm doing now is converting all timestamps to text before outputting to excel - the only way I can think of. – stgtscc Feb 08 '14 at 22:50

1 Answers1

2

With the latest version of Pandas on GitHub (and in the soon to be released 0.13.1) you can specify the Excel date format in the ExcelWriter() like this:

import pandas as pd
from datetime import datetime

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

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

df.to_excel(writer, "Sheet1")

writer.close()

Which will display the microsecond times (or at least milliseconds since that is Excel's display limit):

enter image description here

See also Working with Python Pandas and XlsxWriter.

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
  • Thanks, I installed 0.13.1 and I believe it is at least serializing microseconds but it's just too bad excel can't display microsecond resolution... Perhaps libreoffice does. – stgtscc Feb 09 '14 at 00:01