Allow two datetime format using raise ValueError python

Tags: , , , ,



I have two csv files with two different format which are '%m/%d/%Y %H:%M:%S' and '%d/%m/%Y %H:%M:%S'. Hence, I will get such ValueError :

ValueError: time data '14/12/2020 17:43:15' does not match format '%m/%d/%Y %H:%M:%S'

Sample data from file1.csv where month is in the middle:

timestamp             
15/12/2020 11:01:54
15/12/2020 11:02:54
15/12/2020 13:33:24

While file2.csv, month is at front :

timestamp            
12/15/2020 11:01:54
12/15/2020 11:02:54
12/15/2020 13:33:24

The column comes as String then I require to transform to only extract out the date. This is the code :

responses_df['date_only'] = [datetime.strptime(val, format='%m/%d/%Y %H:%M:%S').date() for val in responses_df['timestamp']]

My plan is to redirect format= to a function that will overcome ValueError and change the format from '%m/%d/%Y %H:%M:%S' to '%d/%m/%Y %H:%M:%S'.

Something like this.. :

responses_df['date_only'] = [datetime.strptime(val, format=get_format()).date() for val in responses_df['timestamp']]


def get_format():
     if ValueError is raised, use '%d/%m/%Y %H:%M:%S'
     else, use '%m/%d/%Y %H:%M:%S'

Answer

Try with

s1 = pd.to_datetime(df['timestamp'],format='%m/%d/%Y %H:%M:%S', errors='coerce')
s2 = pd.to_datetime(df['timestamp'],format='%d/%m/%Y %H:%M:%S', errors='coerce')

Then

df['new']=s1.fillna(s2)


Source: stackoverflow