0

I have a column in my Excel sheet called "Start_Time" and the data in the column is in "HH:MM:SS" format, for example "10:13:20".

But when I use pandas.read_excel() function to load the data. The "Start_Time" column showed decimal values (for example: 0.425925925925926) with data type as "object".

How could make the df["Start_Time"] to display as "10:13:20"?

I tried pd.Timedelta(), but it works for only one value at a time. I want to convert all values in that column.

Start Time End Time
16:24:50 16:32:27
10:35:53
15:06:46 15:21:43
6:39:50 6:39:50
21:55:02
3:29:04 3:29:13
0:53:06 0:53:06
10:21:13 10:25:18
16:15:25 16:19:31
Mel Huang
  • 55
  • 1
  • 6
  • can you share sample excel? I tried and unable to reproduce the issue – Naveed Oct 27 '22 at 17:53
  • Thanks, I just added a sample data in the post. – Mel Huang Oct 27 '22 at 18:37
  • Thanks! can you link your sample excel? – Naveed Oct 27 '22 at 18:44
  • :). I can't share it since it's private data. Thanks though. I used pd.to_datetime(df['Start_Time'],unit = "d", errors='coerce').dt.strftime('%H:%M:%S'). Then, I dropped all the nan. It worked. :) :) – Mel Huang Oct 27 '22 at 19:44
  • related: [Pandas problem with a column with mixed time and date time](https://stackoverflow.com/q/70858332/10197418), [Python Datetime conversion for excel dataframe](https://stackoverflow.com/q/74205015/10197418) – FObersteiner Oct 28 '22 at 07:40

1 Answers1

0

Excel stores the date as a serial number counting from a starting date.

When you choose a format it converts that serial number to the format demanded.

If you want Pandas to display the correct date then you have to convert the serial number.

Solar Mike
  • 7,156
  • 4
  • 17
  • 32