How do I change a column of non-standard strings to datetime?

user_16709730 0
Viewed 33

I have a pandas dataframe with one column containing times as a string.

d = {'Name': ['Joe', 'Bob'], 'Time': ['58:34', '1:02:17']}
df = pd.DataFrame(data=d)

I want to change the column to datetime so that I can calculate the seconds of each person. However, the issue I keep running into is that the hours have been removed from the first time so I get:

"time data '58:34' does not match format '%H:%M:%S' (match)

and a similar issue if I change the format to just minutes and seconds.

pandas python
2 Answers

Time is not actually a datetime, it's more of a duration. I would suggest a normalisation function which handles converting the duration to seconds:

import pandas as pd

d = {'Name': ['Joe', 'Bob'], 'Time': ['58:34', '1:02:17']}
df = pd.DataFrame(data=d)

def duration_to_sec(x):
    bits = x.split(":")
    multiplier = 1
    seconds = 0
    for b in reversed(bits):
        seconds += (int(b) * multiplier)
        multiplier *= 60
    return seconds
df["dur_sec"] = df["Time"].apply(duration_to_sec)
user_88451080

You can add a 0 to your time if it's missing the hr value then you convert to DateTime

d = {'Name': ['Joe', 'Bob'], 'Time': ['58:34', '1:02:17']}
df = pd.DataFrame(data=d)
df.Time = df.Time.apply(lambda x: x if len(x.split(":")) == 3 else "0:{}".format(x))
df['Time'] = pd.to_datetime(df.Time, format='%H:%M:%S')
user_24574830
Related