Search code examples
pythonpandasdataframedatetimeruntime-error

valueerror: time data does not match dataframe of pandas

df having data as below in pandas

"val1" 6972.75 01-AUG-18 08.11.51.319 AM
"val2" 6974.25 01-OCT-18 08.12.22.322 AM

I am using the code

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

when i am running the code its giving error below

ValueError: time data '01-AUG-18 08.11.51.319 AM' does not match format '%d-%b-%Y %H.%M.%S.%f' (match)
Solution

  • Your format is all messed up. You used the incorrect format for the month and year and you can deal with the AM/PM with %p. (Most of the formats can be found under the datetime docs

    %b  #Month as locale’s abbreviated name: [Jan, Feb, …, Dec (en_US)]
%y  #Year without century as a zero-padded decimal number: [00, 01, …, 99]
%p  #Locale’s equivalent of either AM or PM: [AM, PM (en_US)]

    import pandas as pd
df = pd.DataFrame({'TIME': ['01-AUG-18 08.11.51.319 AM', 
                            '01-OCT-18 08.12.22.322 AM']})

pd.to_datetime(df['TIME'], format="%d-%b-%y %H.%M.%S.%f %p", errors='coerce')
#0   2018-08-01 08:11:51.319
#1   2018-10-01 08:12:22.322
#Name: TIME, dtype: datetime64[ns]