Reading Excel file Date time Incorrectly

Question

I have an excel document with a column for time and another for dates. To read it, I'm employing the code below:

df = pd.read_excel(r'df.xlsx', parse_dates=[['date', 'time']])

While the date is the same, this works properly; but, if the dates vary, it will be misread. For instance, the excel file looks like this:

If I read it using this code the results will be like this:

2021-04-03 00:00:00 23:52:11,A
2021-04-03 00:00:00 23:56:05,A
2021-04-03 00:00:00 23:59:27,A
2021-04-04 00:00:00 1900-01-01 00:03:33,B
2021-04-04 00:00:00 1900-01-01 00:04:33,B
2021-04-04 00:00:00 1900-01-01 00:06:43,B
2021-04-04 00:00:00 1900-01-01 00:10:17,B

How can I fix this so the data frame has separate columns with the correct date and time like below?

2021-04-03  23:52:11,A
2021-04-03  23:56:05,A
2021-04-03  23:59:27,A
2021-04-04  00:03:33,B
2021-04-04  00:04:33,B
2021-04-04  00:06:43,B
2021-04-04  00:10:17,B

Excel file: https://www.apispreadsheets.com/table/lEooNma9w3X2XfaL/

Answer 1

Use dateutil.parser.

Example usage:

import dateutil.parser as parser

parser.parse("2021-04-04 00:00:00 1900-01-01 00:03:33,B")

The output will be a datetime object. I hope this helps you.