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