CODEWITHSUNDEEP
pythondatetimepandas
LMLPP
LMLPP

Reputation: 105

How to sum time in a dataframe

I have a dataframe of time data in the format

hh:mm:ss
hh:mm:ss

(type string)

I need to be able to sum the values (to acquire total time) in a few of the columns. I'm wondering if anyone has any recommendations on the best way to do this and get the sum in the same format.

Upvotes: 2

Views: 18980

Answers (3)

jezrael
jezrael

Reputation: 862511

You can use to_timedelta with sum:

import pandas as pd

df = pd.DataFrame({'A': ['18:22:28', '12:15:10']})

df['A'] = pd.to_timedelta(df.A)

print (df)
         A
0 18:22:28
1 12:15:10

print (df.dtypes)
A    timedelta64[ns]
dtype: object

print (df.A.sum())
1 days 06:37:38

Upvotes: 6

Alyssa Haroldsen
Alyssa Haroldsen

Reputation: 3731

Maybe try using datetime.timedelta?

import re
from datetime import timedelta

_TIME_RE = re.compile(r'(\d+):(\d+):(\d+)')

def parse_timedelta(line):
    # Invalid lines (such as blank) will be considered 0 seconds
    m = _TIME_RE.match(line)
    if m is None:
        return timedelta()
    hours, minutes, seconds = [int(i) for i in m.groups()]
    return timedelta(hours=hours, minutes=minutes, seconds=seconds)

def format_timedelta(delta):
    hours, rem = divmod(delta.seconds + delta.days * 86400, 3600)
    minutes, seconds = divmod(rem, 60)
    return '{:02}:{:02}:{:02}'.format(hours, minutes, seconds)

If data is a list containing the lines:

print(format_timedelta(sum(parse_timedelta(line) for line in data)))

Upvotes: 1

Jeff
Jeff

Reputation: 2228

You can do this using timedelta:

import pandas as pd
import datetime

data = {'t1':['01:15:31', 
              '00:47:15'], 
        't2':['01:13:02', 
              '00:51:33']
        }

def make_delta(entry):
    h, m, s = entry.split(':')
    return datetime.timedelta(hours=int(h), minutes=int(m), seconds=int(s))

df = pd.DataFrame(data)
df = df.applymap(lambda entry: make_delta(entry))

df['elapsed'] = df['t1'] + df['t2']

In [23]: df
Out[23]:
        t1       t2  elapsed
0 01:15:31 01:13:02 02:28:33
1 00:47:15 00:51:33 01:38:48

Edit: I see you need to do this by column, not row. In that case do the same thing, but:

In [24]: df['t1'].sum()
Out[24]: Timedelta('0 days 02:02:46')

Upvotes: 8

Related Questions