CODEWITHSUNDEEP
pythonpandasdatetime
Reut
Reut

Reputation: 1592

time data doesn't match format specified when it seemed to be match

I have dataframe in pandas that has two dates columns:

>>>ID  name    start        end  
0  12  Tik     1/6/2020    None
1  32  Tak     12/31/2019  None
2  45  Tek     9/1/2019   1/30/2020
3  78  Tok     9/1/2019   1/29/2020

I'm trying to convert those dates into datetime to be in format of Y-m-d e.g that 12/31/2019 will be 2019-12-31 :

df[['start','end']] =df[['start','end']].apply(pd.to_datetime, format=''%Y-%m-%d'')

but whenever I run this I get error:

ValueError: time data 1/6/2020 doesn't match format specified

I have tried than to specify the format to be like the given date (e.g (d-m-Y):

df[['start','end']] =df[['start','end']].apply(pd.to_datetime, format=''%d-%m-%Y'')

>>>ValueError: time data '1/6/2020' does not match format '%d-%m-%Y' (match)

I have tried to break it as suggest in the first answer here: How to change the datetime format in pandas and to first conver to datetime and then use strftime but in the first line I got the error that Ineed to specify the format.

I can't find any reason why is this happen, maybe is because the day and month do not have two digits?

my end goal is to convert those date columns into %Y-%m-%d format

Upvotes: 1

Views: 3874

Answers (1)

Trenton McKinney
Trenton McKinney

Reputation: 62373

  • The issue seems to be the use of two sets of quotes around the format
    • ''...''
  • The format parameter of pandas.to_datetime specifies the current format of the column, not the desired format.
    • The format of these dates is '%m/%d/%Y'
import pandas as pd

# setup the dataframe
df = pd.DataFrame({'ID': [12, 32, 45, 78], 'name': ['Tik', 'Tak', 'Tek', 'Tok'], 'start': ['1/6/2020', '12/31/2019', '9/1/2019', '9/1/2019'], 'end': [None, None, '1/30/2020', '1/29/2020']})

# convert to datetime
df[['start','end']] = df[['start','end']].apply(pd.to_datetime, format='%m/%d/%Y')

# display(df)
   ID name      start        end
0  12  Tik 2020-01-06        NaT
1  32  Tak 2019-12-31        NaT
2  45  Tek 2019-09-01 2020-01-30
3  78  Tok 2019-09-01 2020-01-29

Upvotes: 1

Related Questions