Pandas time operations

I have a data frame with information from a broker, the data show the distance traveled, time spent and the date of the race.

DISTANCE TIME DATE 9.8 51:50.92 09/08/18 11.1 55:59.70 24/08/18 11.1 49:25.20 29/08/18

I would like to perform an arithmetic operation between the records in the "TIME" column as differences between the times, calculate the average speed of each race.

When transforming the text string to pandas datetime it remains with the following format:

1900-01-01 00:51:50 1900-01-01 00:55:59 1900-01-01 00:49:25

What I need is the time so I do the following

df['TIME'] = pd.to_datetime(df['TIME'], format = '%M:%S')

Which leads me to the next problem, the column is now of type "object" and I can't perform operations of any kind on it.

How can I transform a column to time without losing the datetime format? and perform operations with it.

I would like, for example, to calculate speed, differences between times and frequencies between races.

What is the code you use for the operation :D
– Phung Duy Phong
Aug 31 at 3:35

@PhungDuyPhong df.TIME.diff() and df.DISTANCE / df.TIME I've seen tutorials where they use functions like df.TIME[0] - df.TIME[1] I have seen tutorials in which they use functions as is and it returns the difference in time.
– alejandrohtadinom
Aug 31 at 17:39

df.TIME.diff()

df.DISTANCE / df.TIME

df.TIME[0] - df.TIME[1]

2 Answers
2

Instead of to_datetime, you could use to_timedelta. See below:

to_datetime

to_timedelta

df['TIME'] = '00:' + df['TIME'] df['TIME'] = pd.to_timedelta(df['TIME'])

The result of this is:

DISTANCE TIME DATE 0 9.8 00:51:50.920000 09/08/18 1 11.1 00:55:59.700000 24/08/18 2 11.1 00:49:25.200000 29/08/18

You can now use mathematical operations on TIME. However, as mentioned in Phung Duy Phong's comment above, it's hard to say whether this will solve your issues without seeing the code you'd like to run.

TIME

TIME is actually of type timedelta64[ns]. I imagine that the ability to perform calculations on the times is more important than literally being datetime, but if that's wrong, OP will need to try a different approach.
– cpander
Aug 31 at 3:50

TIME

timedelta64[ns]

datetime

If I wanted to calculate the speed of each runner in Km/m, is there any way to apply the arithmetic function / in two different types of data float and datetime?. that would be the main function of the table.
– alejandrohtadinom
Aug 31 at 17:33

Thank you very much, your answer helped me a lot @cpander
– alejandrohtadinom
Aug 31 at 18:01

Supposing that Distance is km, use this to get your speed in km/h:

import pandas as pd df=pd.DataFrame('DISTANCE':[9.5,11.1,11.1],'TIME':['51:50.92','55:59.70','49:25.20'],'DATE':['09/08/18','24/08/18','29/08/18']) df['TIME'] = '00:' + df['TIME'] df['TIME'] = pd.to_timedelta(df['TIME']) df['SPEED (km/h)']=[df['DISTANCE'][i]/(df['TIME'][i].total_seconds()/3600) for i in df.index]#total_seconds() won't work with a pandas column so you need to apply it to each TIME cell

Yes, this was what I was looking for, thank you very much
– alejandrohtadinom
Aug 31 at 18:02

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

搜尋此網誌

Dfyjkt