3

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
EdChum
  • 376,765
  • 198
  • 813
  • 562
Naive Babes
  • 355
  • 1
  • 3
  • 10

1 Answers1

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)
EdChum
  • 376,765
  • 198
  • 813
  • 562
  • How can I make the Desired Output as `datetime.time` since I need to find duration of two such input times? – Naive Babes Oct 29 '15 at 12:26
  • Sorry it is `datetime.time` as I showed in the last line of code – EdChum Oct 29 '15 at 12:31
  • In my result it is still an object type. – Naive Babes Oct 29 '15 at 12:56
  • That's correct, you can see though that when you look at a single row values that the dtype is `datetime.time` – EdChum Oct 29 '15 at 13:06
  • 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'` – Naive Babes Oct 29 '15 at 13:19
  • Please see this: http://stackoverflow.com/questions/5259882/subtract-two-times-in-python basically you can't perform subtractions with datetime.time, you can with datetime or you can use combine – EdChum Oct 29 '15 at 13:21
  • I tried subtraction for datetime and got these results for the following inputs: `Time 1 Time 2 Difference 8:59:45 9:27:30 -1 days +23:27:45 9:52:29 10:08:54 -1 days +23:16:26` But the desired output is `00:27:45 and 00:16:26` respectively – Naive Babes Oct 29 '15 at 13:42
  • Unfortunately this is documented behaviour, also if you have another question please post another question rather than request further assistance via comments you will find that this is a common problem – EdChum Oct 29 '15 at 13:45