I can reproduce this behavior (pandas 1.2.3); it leaves you with a mix of datetime.datetime
and datetime.time
objects in the 'time' column.
One way around can be to import the time column as type string; you can explicitly specify that like
df = pd.read_excel(path_to_your_excelfile, dtype={'Time': str})
which will give you "excel day zero" prefixed to some entries. You can remove them by split on space and then taking the last element of the split result:
df['Time'].str.split(' ').str[-1]
Now you can proceed by converting string to datetime
, timedelta
etc. - whatever makes sense in your context.
Another way to handle this can be to specify that pandas should parse this column to datetime; like
df = pd.read_excel(path_to_your_excelfile, parse_dates=['Time'])
Then, you'll have pandas' datetime, with either today's date or "excel day zero":
df['Time']
0 2021-03-04 20:00:00
1 2021-03-04 22:00:00
2 2021-03-04 23:00:00
3 1899-12-30 00:00:00
4 2021-03-04 02:00:00
...
23 1899-12-30 00:00:00
Name: Time, dtype: datetime64[ns]
Now you have some options, depending on what you intend to do further with the data. You could just ignore the date, or strip it (df['Time'].dt.time
), or parse to string (df['Time'].dt.strftime('%H:%M:%S')
) etc.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…