-2

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

Joseph Daudi
  • 1,557
  • 3
  • 17
  • 33
  • 2
    Why do you expect `'27th August 2017'` to fit the format `'%d-%m-%Y'`? It's not even close. – DeepSpace Sep 06 '17 at 12:11
  • @DeepSpace OP states " i would like to convert is to this format". So he doesn't know that this format is used for _parsing_. Not that bad a question after all, since pandas is able to parse the input date without any format (and I learned something) – Jean-François Fabre Sep 06 '17 at 12:18
  • I believe that OP has not yet read the documentation on how datetime's formats work and thus his expectations are incorrect. Here it is: https://docs.python.org/2/library/datetime.html#strftime-and-strptime-behavior – BoboDarph Sep 06 '17 at 12:21

1 Answers1

3

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

EdChum
  • 376,765
  • 198
  • 813
  • 562