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.
CodePudding user response:
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)
CodePudding user response:
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')