user3609179 user3609179 - 2 months ago 12
Python Question

python pandas parse date without delimiters 'time data '060116' does not match format '%dd%mm%YY' (match)'

I am trying to parse a date column that looks like the one below,

date
061116
061216
061316
061416


However I cannot get pandas to accept the date format as there is no delimiter (eg '/'). I have tried this below but receive the error:


ValueError: time data '060116' does not match format '%dd%mm%YY' (match)


pd.to_datetime(df['Date'], format='%dd%mm%YY')

Answer

You need add parameter errors='coerce' to_datetime, because 13 and 14 months does not exist, so this dates are converted to NaT:

print (pd.to_datetime(df['Date'], format='%d%m%y', errors='coerce'))
0   2016-11-06
1   2016-12-06
2          NaT
3          NaT
Name: Date, dtype: datetime64[ns]

Or maybe you need swap months with days:

print (pd.to_datetime(df['Date'], format='%m%d%y'))

0   2016-06-11
1   2016-06-12
2   2016-06-13
3   2016-06-14
Name: Date, dtype: datetime64[ns]

EDIT:

print (df)
         Date
0  0611160130
1  0612160130
2  0613160130
3  0614160130

print (pd.to_datetime(df['Date'], format='%m%d%y%H%M', errors='coerce'))
0   2016-06-11 01:30:00
1   2016-06-12 01:30:00
2   2016-06-13 01:30:00
3   2016-06-14 01:30:00
Name: Date, dtype: datetime64[ns]

Python's strftime directives.