4

I have stock ticker data in the following format:

40289.65972
40289.66319
40289.66667

and Excel is able to magically convert them to:

4/22/14 3:50 PM
4/22/14 3:55 PM
4/22/14 4:00 PM

via "Format Cells"

How do I do the same conversion in pandas?

piRSquared
  • 265,629
  • 48
  • 427
  • 571
vgoklani
  • 9,351
  • 14
  • 56
  • 91
  • 3
    Use this http://stackoverflow.com/questions/1108428/how-do-i-read-a-date-in-excel-format-in-python to convert your ``floats`` into instances of ``datetime.datetime`` which you can use in Pandas. – diliop Aug 12 '12 at 01:07

3 Answers3

7

The solution mentioned in the link above works, so I will just repost the snippet here. Thanks!

import datetime

def minimalist_xldate_as_datetime(xldate, datemode):
    # datemode: 0 for 1900-based, 1 for 1904-based
    return (
        datetime.datetime(1899, 12, 30)
        + datetime.timedelta(days=xldate + 1462 * datemode)
    )
vgoklani
  • 9,351
  • 14
  • 56
  • 91
5

To stay within pandas (which is wicked fast), use to_timedelta()

import pandas as pd
# should get  7/7/1988 1:26:24 a.m. (https://support.microsoft.com/en-us/kb/214094)
pd.to_datetime('1899-12-30') + pd.to_timedelta(32331.06, 'D')

produces Timestamp('1988-07-07 01:26:24') If you have a dataframe full of excel-float-dates you can convert the whole thing:

df['BetterDT'] = pd.to_datetime('1899-12-30') + pd.to_timedelta(df.ExecDate, 'D')
sophros
  • 11,665
  • 7
  • 38
  • 61
jdmarino
  • 535
  • 5
  • 12
2

Excel considers 1900 a leap year, so be careful with exactly what you want to translate: http://spreadsheetpage.com/index.php/oddity/the_intentional_date_bug/

Alejandro
  • 21
  • 2