1

Let's say, I have the following times.

Time_1      Time_2          
10:08:00    10:08:00          
11:00:00    12:00:00         
12:30:00    14:30:00

I would like to calculate the average time between them and would like to get the following result.

Time_1      Time_2         Average_time
10:08:00    10:08:00         10:08:00
11:00:00    12:00:00         11.30.00
12:30:00    14:30:00         13.15.00

Can anyone help with this?

Hiwot
  • 568
  • 5
  • 18
  • You should try converting them into simpler datatypes like maybe a number representing the number of seconds passed since midnight. That way you can average the two dates with simple number addition divided by 2, then convert that resulting average into a date format. – Robo Mop Jun 27 '21 at 08:17
  • Related: [how-to-calculate-the-time-interval-between-two-time-strings](https://stackoverflow.com/questions/3096953/how-to-calculate-the-time-interval-between-two-time-strings) – Anurag Dabas Jun 27 '21 at 08:28
  • By the way, the average of 12:30:00 & 14:30:00 should be 13:30:00, not 13:15:00. – Aldrin Saurov Sarker Jun 27 '21 at 08:29

6 Answers6

3

you can use timedelta, this code calculate average of times.

import datetime
import pandas as pd
time1="11:00:00"
time2="12:00:00"
t1 = pd.Timedelta(time1)
t2 = pd.Timedelta(time2)
avg=(t1+t2)/2

d = {'time1':[t1] , 'time2':[t2], 'average':[avg]}

df = pd.DataFrame(d)

print(df.to_string())

output:

    time1    time2  average
0 11:00:00 12:00:00 11:30:00
Niloofar Adelkhani
  • 217
  • 3
  • 12
2

Python has a built-in function to calculate time, timedelta.

from datetime import datetime, timedelta

t1 = '10:00:00'
t2 = '11:00:00' # For example
format = '%H:%M:%S'

startTime = datetime.strptime(t1, format) # convert t1 to time object
tdelta = datetime.strptime(t2, format) - datetime.strptime(t1, format)

Now, tdelta contains the time difference between t1 & t2. timedelta.seconds will return the time in seconds. Just add half of the seconds with the starting time.

tdelta = startTime + timedelta(0, tdelta.seconds//2)

print(tdelta.time()) # Your expected output
Aldrin Saurov Sarker
  • 203
  • 1
  • 13
  • How can you apply your solution to the data frame? I am getting an error ```ValueError: Value must be Timedelta, string, integer, float, timedelta or convertible, not Series``` – Hiwot Jun 27 '21 at 08:37
  • Read the official documentation. It describes how can you convert dataframe to string objects. https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_string.html – Aldrin Saurov Sarker Jun 27 '21 at 08:57
1

try this:

df['Average_time'] = (df['Time_1']-df['Time_2'])/2+df['Time_1']
Anurag Dabas
  • 23,866
  • 9
  • 21
  • 41
Amir Gh
  • 84
  • 1
  • 4
1

an alternative:



class TimeObject:

    def __init__(self, s=None):
        if s:
            hours, minutes, seconds = [int(v) for v in s.split(":")]
            self.seconds = hours*3600 + minutes*60 + seconds
        else:
            self.seconds = 0

    def __str__(self):
        hours = self.seconds//3600
        minutes = (self.seconds - hours*3600)//60
        seconds = (self.seconds-hours*3600-minutes*60)
        return f"{hours:02d}:{minutes:02d}:{seconds:02d}"

    @classmethod
    def fromSeconds(cls, seconds):
        ans = TimeObject()
        ans.seconds = seconds
        return ans


def average(a, b):
    avg = (a.seconds+b.seconds)//2
    return TimeObject.fromSeconds(avg)


if __name__ == "__main__":

    o1 = TimeObject("12:30:00")
    o2 = TimeObject("14:30:00")

    ans = average(o1, o2)
    print(ans)

But I think you should prefer the timedelta solution

gosom
  • 1,299
  • 3
  • 16
  • 35
1

Try:

df['Average_time']=(df[['Time_1','Time_2']]
                    .astype(str)
                    .agg(pd.to_timedelta)
                    .eval("Time_1+Time_2").div(2))

output:

    Time_1      Time_2      Average_time
0   10:08:00    10:08:00    0 days 10:08:00
1   11:00:00    12:00:00    0 days 11:30:00
2   12:30:00    14:30:00    0 days 13:30:00

If you really don't care about days in timedelta and you want only hours then you can use:

df['Average_time']=pd.to_datetime(df['Average_time'].dt.total_seconds(),unit='s').dt.time

output of df:

    Time_1      Time_2      Average_time
0   10:08:00    10:08:00    10:08:00
1   11:00:00    12:00:00    11:30:00
2   12:30:00    14:30:00    13:30:00

If you want to include days as well in time delta and convert them to hours then you can follow up this question

Anurag Dabas
  • 23,866
  • 9
  • 21
  • 41
0

With datetime objects, you could convert its to timestamp which is a float representation of a date, compute the mean, and convert back to datetime object.

>>> d1 = dateutil.parser.parse("1970-01-01T11:00:00")
>>> d2 = dateutil.parser.parse("1970-01-01T12:00:00")
>>> datetime.datetime.fromtimestamp((d1.timestamp() + d2.timestamp()) / 2)
datetime.datetime(1970, 1, 1, 11, 30)

But in your case you only have time part of datetime, which can be confuse. What's the mean of "23:59:00" and "00:01:00" ?

Maybe you should complete time with arbitrary day, assuming that if t1 > t2 then t2 is day+1.

Balaïtous
  • 826
  • 6
  • 9