CODEWITHSUNDEEP
pythonpandasdataframedatetimeruntime-error
krocks
krocks

Reputation: 75

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)

Upvotes: 1

Views: 120

Answers (2)

ALollz
ALollz

Reputation: 59519

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]

Upvotes: 2

Quang Hoang
Quang Hoang

Reputation: 150735

You have extra AM at the end. Try stripping it out:

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

Or safer is to include the format directive for them:

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

Or just let pandas guess, which is simpler but takes longer to run:

pd.to_datetime(df['TIME'], dayfirst=True)

Upvotes: 2

Related Questions