convert to datetime based on condition-CodePudding

I want to convert my datetime object into seconds

0       49:36.5
1       50:13.7
2       50:35.8
3       50:37.4
4       50:39.3
        ...    
92    1:00:47.8
93    1:01:07.7
94    1:02:15.3
95    1:05:03.0
96    1:05:29.6
Name: Finish, Length: 97, dtype: object

the problem is that the format changes at index 92 which results in an error: ValueError: expected hh:mm:ss format before .

This error is caused when I try to convert the column to seconds

filt_data["F"] = pd.to_timedelta('00:' filt_data["Finish"]).dt.total_seconds()

when I do the conversion in two steps it works but results in two different column which I don't know how to merge nor does it seem really efficient:

filt_data["F1"] = pd.to_timedelta('00:' filt_data["Finish"].loc[0:89]).dt.total_seconds()
filt_data["F2"] = pd.to_timedelta('0' filt_data["Finish"].loc[90:97]).dt.total_seconds()

the above code does not cause any error and gets the job done but results in two different columns. Any idea how to do this?

Ideally I would like to loop through the column and based on the format i.E. "50:39.3" or "1:00:47.8" add "00:" or "0" to the object.

CodePudding user response：

I would use

You can write a function that you apply on each separate entry in the time column:

def format_time(time):
    time = time.split('.')[0]
    time = time.split(':')
    if(len(time) < 3):
        time.insert(0, "0")
    return ":".join(time)


df["formatted_time"] = df.time.apply(format_time)    
df

Then you could undertake two steps:

Convert column to datetime
Convert column to UNIX timestamp (number of seconds since 1970-01-01)

df["time_datetime"] = pd.to_datetime(df.formatted_time, infer_datetime_format=True)
df["time_seconds"] = (df.time_datetime - pd.Timestamp("1970-01-01")) // pd.Timedelta('1s')
df