Time formatting in Python Pandas

Question

I've an Excel file having time in mm:ss.0 format. I formatted this into [h]:mm:ss;@ in Excel. How can this be done in Pandas?

Time : 58:44.1 in mm:ss.0 format yields a result 7:58:44 in [h]:mm:ss;@ format in Excel after formatting.

For example:

Input      Desired Output
58:44.1    7:58:44
07:53.3    8:07:53
46:59.6    9:47:00
20:14.0    10:20:14
50:58.7    11:50:59
19:50.0    12:19:50
41:53.5    13:41:53

So you just want add hour component, increment it and drop the .0 correct? — EdChum
– EdChum, Commented Oct 29, 2015 at 10:27

EdChum · Accepted Answer · 2015-10-29 10:44:38Z

2

You can slice the string using vectorised str method, then construct datetime using to_datetime from this passing the format string and add a TimedeltaIndex to it and then access the time component using dt.time:

In [199]:
df['Desired Output'] = (pd.to_datetime(df['Input'].str[:-2], format='%M:%S') + pd.TimedeltaIndex(np.arange(7, 7 + len(df)), 'h')).dt.time
df

Out[199]:
     Input Desired Output
0  58:44.1       07:58:44
1  07:53.3       08:07:53
2  46:59.6       09:46:59
3  20:14.0       10:20:14
4  50:58.7       11:50:58
5  19:50.0       12:19:50
6  41:53.5       13:41:53

You can see that the dtype for Desired Output is not datetime.time:

In [201]:
df['Desired Output'].iloc[0]

Out[201]:
datetime.time(7, 58, 44)

answered Oct 29, 2015 at 10:44

EdChum

397k204 gold badges836 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Naive Babes Over a year ago

How can I make the Desired Output as datetime.time since I need to find duration of two such input times?

EdChum Over a year ago

Sorry it is datetime.time as I showed in the last line of code

Naive Babes Over a year ago

In my result it is still an object type.

EdChum Over a year ago

That's correct, you can see though that when you look at a single row values that the dtype is datetime.time

Naive Babes Over a year ago

I got two columns namely desiredOutput1 and desiredOutput2 in the format that you've given. I, now, want to calculate the time difference between the two which I'm unable to with an error TypeError: unsupported operand type(s) for -: 'datetime.time' and 'datetime.time'

|

Collectives™ on Stack Overflow

Time formatting in Python Pandas

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related