python - how to fix messy Excel import with datetime.datetime and datetime.time objects in the same column

Question

I have to read multiple Excel-files into a pandas data frame using pd.read_excel(). The Excel files have a column with a date and a column with a time (HH:MM:SS). In the pandas df, the time-column gets interpreted as datetime.time, but whenever the time is midnight 00:00:00, it gets interpreted as 1899-12-30T00:00:00.000Z which is a datetime.datetime.

|  Excel   |         pandas df        | type in pandas df |
|----------|--------------------------|-------------------|
| 23:30:00 | 23:30:00                 | datetime.time     |
| 23:45:00 | 23:45:00                 | datetime.time     |
| 00:00:00 | 1899-12-30T00:00:00.000Z | datetime.datetime |
| 00:15:00 | 00:15:00                 | datetime.time     |

How can I force pandas read the entire time-column as time or datetime (I don't care which, I just want them all to be the same)?

What I need in the end is a datetime-column (can't be the index, so no "parse dates") where I simply combine the Date- and Time-columns. Unfortunately, I can't figure out how, because of the messy combination of time and datetime in the Time-column.

question from:https://stackoverflow.com/questions/65935466/how-to-fix-messy-excel-import-with-datetime-datetime-and-datetime-time-objects-i

Answer 1

You can convert the column to string, parse to pandas datetime with pd.to_datetime, and optionally format to HH:MM:SS string again with strftime. Example, assuming 'time' being the column in question,

df['time'] = pd.to_datetime(df['time'].astype(str)).dt.strftime('%H:%M:%S')

Would e.g. give you

df
       time
0  23:30:00
1  23:45:00
2  00:00:00
3  00:15:00

You could also specify to import the column directly as string with a keyword in the call to pd.read_excel, e.g. dtype={'time': str}.