EN VI

Python - how to import time duration data from Excel when duration is more than 24 hours?

Python - how to import time duration data from Excel when duration is more than 24 hours

How to import data from Excel?

Total time
6:27:53
68:22:26
171:58:44

when I import this data into Pandas it returns this outcome.

0                 06:27:53
1      1900-01-02 20:22:26
2      1900-01-07 03:58:44

and don't know how to change format.

I tried to change format to string when importing the data.

pd.read_excel('data.xlsx', dtype={'Total time':'str')

and had the same outcome and can't change format to datetime.timedelta

Solution:

If you have datetime/time objects in Excel, the easiest might be to import as string, add the reference date when missing, convert to_datetime and subtract the reference:

df = pd.read_excel('data.xlsx', dtype={'Total time': 'str'})

ref = '1900-01-01 '

df['timedelta'] = pd.to_datetime(df['Total time']
                                 .where(df['Total time'].str.match('\d{4}-\d{2}-\d{2}'),
                                        ref+df['Total time'])
                                ).sub(pd.Timestamp(ref))

Another option, keeping the original types and converting using python + pandas:

from datetime import datetime

def to_timedelta(x):
    ref = datetime(1900, 1, 1)
    if isinstance(x, datetime):
        return x - ref
    return str(x)

df = pd.read_excel('data.xlsx')

df['timedelta'] = pd.to_timedelta(df['Total time'].apply(to_timedelta))

Output:

            Total time       timedelta
0             06:27:53 0 days 06:27:53
1  1900-01-02 20:22:26 1 days 20:22:26
2  1900-01-07 03:58:44 6 days 03:58:44