pandas - Convert timing data (motor racing) to seconds-CodePudding

I am trying to convert timing data (lap times and sector times in motor racing) given in the format [ss.000 or m:ss.000] (se below) to seconds or datetime, in order to be able to analyse it.

df = pd.DataFrame([[48.004, 1:13.564], [38.965, 58.223], [45.630, 1:10.084]], columns=['S1', 'S2'])

What I would like to get is:

   A          B
0  48.004  73.564
1  38.965  58.223
2  45.630  70.084

The only way I was able to convert, is to split data first by '.' with str.split and then by ':'. Afterwards, I am converting minutes to seconds, add seconds and append milisesconds.

Is there any other more elegant way to convert to seconds?

CodePudding user response：

I would suggest converting the strings in proper time format(hh:mm:ss.fff), then use pd.to_timedelta to parse the strings to timedelta:

d = {'^(\d \.\d )$': r'00:00:\1', '^(\d :\d \.\d )$': r'00:\1'}
df.replace(d, regex=True).apply(pd.to_timedelta)

                      S1                     S2
0 0 days 00:00:48.004000 0 days 00:01:13.564000
1 0 days 00:00:38.965000 0 days 00:00:58.223000
2 0 days 00:00:45.630000 0 days 00:01:10.084000

CodePudding user response：

From the code below replace time_list values with your testdata:

from datetime import datetime,timedelta

time_list = ['45.222', '1:24.234', '1:23.679', '45.333']

def tot_sec_fun(input_list):
    prs_time = timedelta(minutes=int(input_list[0]),seconds=int(input_list[1]), milliseconds=int(input_list[2]))
    prs_time.total_seconds()
    return int(prs_time.total_seconds() * 1000)

comma_fun=lambda x : x.replace(':', ',').replace('.', ',').split(',')
op_list =[ comma_fun(elem) if (elem.find(':')>=0) else comma_fun('0:' elem) for elem in time_list ]
final_secs_count_list =[tot_sec_fun(elem) for elem in op_list]

print(final_secs_count_list)

Output:

[45222, 84234, 83679, 45333]

CodePudding user response：

Depending on what exactly you are trying to analyse, this could be sufficient:

Input:

time_list = ['45.222', '1:24.234', '1:23.679', '45.333']

Code:

pd.Series(pd.to_datetime(time_list)).dt.time