Search code examples
pythonpandasdataframedatetimetimedelta

Convert multiple time format object as datetime format


I have a dataframe with a list of time value as object and needed to convert them to datetime, the issue is, they are not on the same format so when I try:

df['Total call time'] = pd.to_datetime(df['Total call time'], format='%H:%M:%S')

it gives me an error

ValueError: time data '3:22' does not match format '%H:%M:%S' (match)

or if use this code

df['Total call time'] = pd.to_datetime(df['Total call time'], format='%H:%M')

I get this error

ValueError: unconverted data remains: :58

These are the values on my data

Total call time
2:04:07
3:22:41
2:30:41
2:19:06
1:45:55
1:30:08
1:32:15
1:43:28
**45:48**
1:41:40
5:08:37
**3:22**
4:29:05
2:47:25
2:39:29
2:29:32
2:09:52
3:31:57
2:27:58
2:34:28
3:14:10
2:12:10
2:46:58

Solution

  • times = """\
    2:04:07
    3:22:41
    2:30:41
    2:19:06
    1:45:55
    1:30:08
    1:32:15
    1:43:28
    45:48
    1:41:40
    5:08:37
    3:22
    4:29:05
    2:47:25
    2:39:29
    2:29:32
    2:09:52
    3:31:57
    2:27:58
    2:34:28
    3:14:10
    2:12:10
    2:46:58""".split()
    
    import pandas as pd
    df = pd.DataFrame(times, columns=['elapsed'])
    def pad(s):
        if len(s) == 4:
            return '00:0'+s
        elif len(s) == 5:
            return '00:'+s
        return s
    print(pd.to_timedelta(df['elapsed'].apply(pad)))
    

    Output:

    0    0 days 02:04:07
    1    0 days 03:22:41
    2    0 days 02:30:41
    3    0 days 02:19:06
    4    0 days 01:45:55
    5    0 days 01:30:08
    6    0 days 01:32:15
    7    0 days 01:43:28
    8    0 days 00:45:48
    9    0 days 01:41:40
    10   0 days 05:08:37
    11   0 days 00:03:22
    12   0 days 04:29:05
    13   0 days 02:47:25
    14   0 days 02:39:29
    15   0 days 02:29:32
    16   0 days 02:09:52
    17   0 days 03:31:57
    18   0 days 02:27:58
    19   0 days 02:34:28
    20   0 days 03:14:10
    21   0 days 02:12:10
    22   0 days 02:46:58
    Name: elapsed, dtype: timedelta64[ns]