Pandas's to_datetime function and datetime format

Question

I have a .csv file with two columns (Date and Time). The time zone is "Europe/Paris" with a +02:00 hours shift. The file is structured in 2 parts with two datetime formats.

Date	Time
08-11-25	11:00
08-11-25	11:09
08-11-25	11:20
08-11-25	11:30
08-11-25	11:40
08-11-25	11:50
...	...
...	...
9-22-25	11:00:00 PM
09-22-25	11:10:00 PM
09-22-25	11:20:00 PM
09-22-25	11:30:00 PM
09-22-25	11:40:00 PM
09-22-25	11:49:59 PM
09-23-25	0:00:00 AM
09-23-25	0:10:00 AM
09-23-25	0:20:00 AM
09-23-25	0:30:00 AM
09-23-25	0:40:00 AM
09-23-25	0:50:00 AM
09-23-25	1:00:00 AM
09-23-25	1:10:00 AM

I read the .csv file and set the date/hour with the following python script:

import pandas as pd
df = pd.read_csv('./Data.csv')
df['Date2'] = df['Date'] + ' ' + df['Time']

s = pd.to_datetime(df['Date2'],errors='coerce', format='%m-%d-%y %H:%M')
s = s.fillna(pd.to_datetime(df['Date2'], dayfirst=False, format='%m-%d-%y %I:%M:%S %p',errors='coerce' ))
df['Date2'] = s

There is no problem with the first format, but the second one is more tricky. As you can see below, I have a (timezone?) problem from midnight to 1 AM.

2025-09-23 23:00:00
2025-09-23 23:10:00
2025-09-23 23:20:00
2025-09-23 23:30:00
2025-09-23 23:40:00
2025-09-23 23:49:59
NaT
NaT
NaT
NaT
NaT
NaT
2025-09-24 01:00:00
2025-09-24 01:10:00
2025-09-24 01:20:00
2025-09-24 01:30:00
2025-09-24 01:39:59
2025-09-24 01:50:00

How can I solve this problem?

Mark Tolonen · Accepted Answer · 2025-10-24 18:47:20Z

0 AM is incorrect. In the AM/PM system, it should be 12 AM. It has nothing to do with time zone. It is just an invalid time string according to the format specified. format='mixed' appears to handle the problem. See example below:

Given this input.csv:

Date,Time
08-11-25,11:00
08-11-25,11:09
08-11-25,11:20
08-11-25,11:30
08-11-25,11:40
08-11-25,11:50
9-22-25,11:00:00 PM
09-22-25,11:10:00 PM
09-22-25,11:20:00 PM
09-22-25,11:30:00 PM
09-22-25,11:40:00 PM
09-22-25,11:49:59 PM
09-23-25,0:00:00 AM
09-23-25,0:10:00 AM
09-23-25,0:20:00 AM
09-23-25,0:30:00 AM
09-23-25,0:40:00 AM
09-23-25,0:50:00 AM
09-23-25,1:00:00 AM
09-23-25,1:10:00 AM

and this code:

import pandas as pd
df = pd.read_csv('input.csv')
df['Date2'] = pd.to_datetime(df['Date'] + ' ' + df['Time'],
                             errors='coerce', dayfirst=False, format='mixed')
print(df['Date2'])

Output:

0    2025-08-11 11:00:00
1    2025-08-11 11:09:00
2    2025-08-11 11:20:00
3    2025-08-11 11:30:00
4    2025-08-11 11:40:00
5    2025-08-11 11:50:00
6    2025-09-22 23:00:00
7    2025-09-22 23:10:00
8    2025-09-22 23:20:00
9    2025-09-22 23:30:00
10   2025-09-22 23:40:00
11   2025-09-22 23:49:59
12   2025-09-23 00:00:00
13   2025-09-23 00:10:00
14   2025-09-23 00:20:00
15   2025-09-23 00:30:00
16   2025-09-23 00:40:00
17   2025-09-23 00:50:00
18   2025-09-23 01:00:00
19   2025-09-23 01:10:00
Name: Date2, dtype: datetime64[ns]

Collectives™ on Stack Overflow

Pandas's to_datetime function and datetime format

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related