Python Pandas - how to convert string HH:MM:SS.mmm to float sss.ms?

Question

I would like to convert string value HH:MM:SS.mmm to float value sec.milliseconds for arithmetic calculations. Is there a direct way to do this as currently I am doing it via split string function and it is a very tedious process. Dataframe looks like:

Col1
00:00:05.063
00:01:00.728
00:03:10.117

Output should look like

  5.063
 60.728
190.117

Appreciate any help.

score 1 · Answer 1 · answered Mar 15 '18 at 05:58

1

Convert your column to timedelta, and then to int (with returns the result in ns, divide by 1e9 to get seconds):

pd.to_timedelta(df.Col1).astype(int) / 1e9

0      5.063
1     60.728
2    190.117
Name: Col1, dtype: float64

answered Mar 15 '18 at 05:58

cs95

379,657
97
704
746

score 1 · Answer 2 · answered Mar 15 '18 at 06:10

1

Use to_timedelta + total_seconds:

df['Col1'] = pd.to_timedelta(df['Col1']).dt.total_seconds()
print (df)
      Col1
0    5.063
1   60.728
2  190.117

answered Mar 15 '18 at 06:10

jezrael

822,522
95
1,334
1,252

I tried this and worked very well. Thanks for the prompt response. However, I get the below warning when I run the code C:\Users\R15445\AppData\Local\Continuum\anaconda3\lib\site-packages\ipykernel_launcher.py:3: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead – Kartik Mar 15 '18 at 07:48
Unfortunately this warning is not show correctly. I guess problem is with filtering - need `copy` like [here](https://stackoverflow.com/a/46728170/2901002). So what is your code above `df['Col1'] = pd.to_timedelta(df['Col1']).dt.total_seconds()` ? – jezrael Mar 15 '18 at 07:51
Another possible problem is for set values - check [here](https://stackoverflow.com/a/20627316/2901002) – jezrael Mar 15 '18 at 07:56

score 0 · Answer 3 · edited Apr 03 '20 at 10:36

0

df['HourandMinute'] = df['Dayandtime_call'].dt.hour + \
    df['Dayandtime_call'].dt.minute / 100

edited Apr 03 '20 at 10:36

chrysn

810
6
19

answered Apr 03 '20 at 08:31

alessio aurilia

1

please explain your answer – Jan Černý Apr 03 '20 at 08:53
I suppose "Dayandtime_call" should be "Col1" here, given that's how it's displayed in cs95's output. – chrysn Apr 03 '20 at 08:54

Python Pandas - how to convert string HH:MM:SS.mmm to float sss.ms?

3 Answers3