ValueError: time data '27th August 2017 23:32:58' does not match format '%d-%m-%Y %H:%M:%S' (match)

Question

i am using pandas and odo to import csv files into a database, there is a date field in file with this format 27th August 2017 23:06:25 i would like to convert is to this format %d-%m-%Y %H:%M:%S.

Here is my the piece of code i am using:

df['Date'] = pd.to_datetime(df['Date'], format='%d-%m-%Y %H:%M:%S')

I end up with the error

ValueError: time data '27th August 2017 23:32:58' does not match format '%d-%m-%Y %H:%M:%S' (match)

Anyone having an idea solving this? please

Answer 1

pandas can parse this fine without a format specifier:

In[25]:
pd.to_datetime('27th August 2017 23:32:58')

Out[25]: Timestamp('2017-08-27 23:32:58')

So you don't need to state the format for this example

The other point here is that even if you tried something like:

In[28]:
pd.to_datetime('27th August 2017 23:32:58', format='%dth %B %Y %H:%M:%S')

Out[28]: Timestamp('2017-08-27 23:32:58')

Which does work it will fail for date strings like:

'3rd June 2011 12:11:23'

because of the 'rd', you can't pass a format to handle the day format using to_datetime, see the python strptime reference. You would need to strip those out in order for it to work but pandas is man/woman enough to sniff the format so there is no need