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 :
JavaScript
x
2
1
ValueError: time data '14/12/2020 17:43:15' does not match format '%m/%d/%Y %H:%M:%S'
2
Sample data from file1.csv
where month is in the middle:
JavaScript
1
5
1
timestamp
2
15/12/2020 11:01:54
3
15/12/2020 11:02:54
4
15/12/2020 13:33:24
5
While file2.csv
, month is at front :
JavaScript
1
5
1
timestamp
2
12/15/2020 11:01:54
3
12/15/2020 11:02:54
4
12/15/2020 13:33:24
5
The column comes as String then I require to transform to only extract out the date
.
This is the code :
JavaScript
1
2
1
responses_df['date_only'] = [datetime.strptime(val, format='%m/%d/%Y %H:%M:%S').date() for val in responses_df['timestamp']]
2
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.. :
JavaScript
1
8
1
responses_df['date_only'] = [datetime.strptime(val, format=get_format()).date() for val in responses_df['timestamp']]
2
3
4
def get_format():
5
if ValueError is raised, use '%d/%m/%Y %H:%M:%S'
6
else, use '%m/%d/%Y %H:%M:%S'
7
8
Advertisement
Answer
Try with
JavaScript
1
3
1
s1 = pd.to_datetime(df['timestamp'],format='%m/%d/%Y %H:%M:%S', errors='coerce')
2
s2 = pd.to_datetime(df['timestamp'],format='%d/%m/%Y %H:%M:%S', errors='coerce')
3
Then
JavaScript
1
2
1
df['new']=s1.fillna(s2)
2