python - Specifying date format when converting with pandas.to_datetime

Question

I have data in a csv file with dates stored as strings in a standard UK format - %d/%m/%Y - meaning they look like:

12/01/2012
30/01/2012

The examples above represent 12 January 2012 and 30 January 2012.

When I import this data with pandas version 0.11.0 I applied the following transformation:

import pandas as pd
...
cpts.Date = cpts.Date.apply(pd.to_datetime)

but it converted dates inconsistently. To use my existing example, 12/01/2012 would convert as a datetime object representing 1 December 2012 but 30/01/2012 converts as 30 January 2012, which is what I want.

After looking at this question I tried:

cpts.Date = cpts.Date.apply(pd.to_datetime, format='%d/%m/%Y')

but the results are exactly the same. The source code suggests I'm doing things right so I'm at a loss. Does anyone know what I'm doing wrong?

Did you use read_csv? Because then you can do it directly while reading in. — joris
@joris Yes I did use read_csv. Could you tell what function does the date conversion, and does it handle my formatting issues? — cms_mgr
But coming back to your original question (because this should also work), what version did you use, because for me it works. — joris
@joris seems to be the case in 11.0 and dev, posted as issue on github — Andy Hayden
Yes, accessing a single column will return a Series. So you could try cpts[['Date']].apply(pd.to_datetime, ...) as a workaround (due to the double [ it will return a dataframe with one column). But note that it should also work on a Series (it is a bug it doesn't), and that the easier way is just to call pd.to_datetime(..) on the column directly as @AndyHayden pointed out in his answer or to do the conversion in read_csv. — joris

joris joris · Accepted Answer · 2013-05-21T14:25:47

You can use the parse_dates option from read_csv to do the conversion directly while reading you data.
The trick here is to use dayfirst=True to indicate your dates start with the day and not with the month. See here for more information: http://pandas.pydata.org/pandas-docs/dev/generated/pandas.io.parsers.read_csv.html

When your dates have to be the index:

>>> import pandas as pd
>>> from StringIO import StringIO
>>> s = StringIO("""date,value
... 12/01/2012,1
... 12/01/2012,2
... 30/01/2012,3""")
>>> 
>>> pd.read_csv(s, index_col=0, parse_dates=True, dayfirst=True)
            value
date             
2012-01-12      1
2012-01-12      2
2012-01-30      3

Or when your dates are just in a certain column:

>>> s = StringIO("""date
... 12/01/2012
... 12/01/2012
... 30/01/2012""")
>>> 
>>> pd.read_csv(s, parse_dates=[0], dayfirst=True)
                 date
0 2012-01-12 00:00:00
1 2012-01-12 00:00:00
2 2012-01-30 00:00:00

python - Specifying date format when converting with pandas.to_datetime

2 Answers