Convert partial H:M:S durations with missing parts, to Seconds; or Right-align non-NA data

user_1431750 0
Viewed 56

TL;DR: I want to right-align this df, overwriting NaN's/shifting them to the left:

In [6]: series.str.split(':', expand=True)
Out[6]:
        0       1       2
0       1  25.842    <NA>
1    <NA>    <NA>    <NA>
2       0  15.413    <NA>
3  54.154    <NA>    <NA>
4       3       2  06.284

to get it as continuous data with the right-most columns filled:

        0       1       2
0       0       1  25.842  # 0 or NA
1    <NA>    <NA>    <NA>  # this NA should remain
2       0       0  15.413
3       0       0  54.154
4       3       2  06.284

What I'm actually trying to do:

I've got a Pandas Series of Durations/timedeltas which are roughly in an H:M:S format - but sometimes the 'H' or the 'H:M' parts can be missing - so I can't just pass it onto Timedelta or datetime. What I want to do is convert them to seconds, which I've done but it seems a bit convoluted:

In [1]: import pandas as pd
   ...:
   ...: series = pd.Series(['1:25.842', pd.NA, '0:15.413', '54.154', '3:2:06.284'], dtype='string')
   ...: t = series.str.split(':')  # not using `expand` helps for the next step
   ...: t
Out[1]:
0       [1, 25.842]
1              <NA>
2       [0, 15.413]
3          [54.154]
4    [3, 2, 06.284]
dtype: object

In [2]: # reverse it so seconds are first; and NA's are just empty
   ...: rows = [i[::-1] if i is not pd.NA else [] for i in t]

In [3]: smh = pd.DataFrame.from_records(rows).astype('float')
   ...: # left-aligned is okay since it's continuous Secs->Mins->Hrs
   ...: smh
Out[3]:
        0    1    2
0  25.842  1.0  NaN
1     NaN  NaN  NaN
2  15.413  0.0  NaN
3  54.154  NaN  NaN
4   6.284  2.0  3.0

If I don't do this fillna(0) step then it generates NaN's for the seconds-conversion later.

In [4]: smh.iloc[:, 1:] = smh.iloc[:, 1:].fillna(0)  # NaN's in first col = NaN from data; so leave
   ...: # convert to seconds
   ...: smh.iloc[:, 0] + smh.iloc[:, 1] * 60 + smh.iloc[:, 2] * 3600
Out[4]:
0       85.842
1          NaN
2       15.413
3       54.154
4    10926.284
dtype: float64

^ Expected end result.

(Alternatively, I could write a small Python-only function to split on :'s and then convert based on how many values each list has.)

pandas timedelta series dataframe python
3 Answers

Let's try with numpy to right align the dataframe, the basic idea is to sort the dataframe along axis=1 so that the NaN values appear before the non-NaN values while also keeping the order of non-NaN values intact:

i = np.argsort(np.where(df.isna(), -1, 0), 1)
df[:] = np.take_along_axis(df.values, i, axis=1)


     0    1       2
0  NaN  1.0  25.842
1  NaN  NaN     NaN
2  NaN  0.0  15.413
3  NaN  NaN  54.154
4  3.0  2.0   6.284

In order to get the total seconds you can multiply the right aligned dataframe by [3600, 60, 1] and take sum along axis=1:

df.mul([3600, 60, 1]).sum(1)

0       85.842
1        0.000
2       15.413
3       54.154
4    10926.284
dtype: float64
user_128331660

You can attack the problem earlier by padding series with '0:' as follows:

# setup
series = pd.Series(['1:25.842', pd.NA, '0:15.413', '54.154', '3:2:06.284'], dtype='string')

# create a padding of 0 series
counts = 2 - series.str.count(':')
pad = pd.Series(['0:' * c if pd.notna(c) and c > 0 else '' for c in counts], dtype='string')

# apply padding
res = pad.str.cat(series)

t = res.str.split(':', expand=True)
print(t)

Output

      0     1       2
0     0     1  25.842
1  <NA>  <NA>    <NA>
2     0     0  15.413
3     0     0  54.154
4     3     2  06.284
user_40015920

1. Using the sorting NA's approach in Shubham's answer, I've come up with this - Utilise Pandas apply and Python sorted :

series = pd.Series(['1:25.842', pd.NA, '0:15.413', '54.154', '3:2:06.284'], dtype='string')
df = series.str.split(':', expand=True)

# key for sorted is `pd.notna`, so False(0) sorts before True(1)
df.apply(sorted, axis=1, key=pd.notna, result_type='broadcast')

(And then multiply as needed.) But it's quite slow, see below.

2. By pre-padding the '0:'s in Dani's answer, I can then create pd.Timedelta's directly and get their total_seconds:

res = ...  # from answer

pd.to_timedelta(res, errors='coerce').map(lambda x: x.total_seconds())

(But doing the expand-split and then multiply+sum is faster across ~10k rows.)

Performance caveats, with 10k rows of data:

Initial code/attempt in my question, row reversal - so maybe I'll stick with it:

%%timeit
t = series.str.split(':')
rows = [i[::-1] if i is not pd.NA else [] for i in t]
smh = pd.DataFrame.from_records(rows).astype('float')
smh.mul([1, 60, 3600]).sum(axis=1, min_count=1)

# 14.3 ms ± 310 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

Numpy argsort + take_along_axis:

%%timeit
df = series.str.split(':', expand=True)
i = np.argsort(np.where(df.isna(), -1, 0), 1)
df[:] = np.take_along_axis(df.values, i, axis=1)
df.apply(pd.to_numeric, errors='coerce').mul([3600, 60, 1]).sum(axis=1, min_count=1)

# 30.1 ms ± 1.03 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

Padding beforehand:

%%timeit
counts = 2 - series.str.count(':')
pad = pd.Series(['0:' * c if pd.notna(c) else '' for c in counts], dtype='string')
res = pad.str.cat(series)
t = res.str.split(':', expand=True)
t.apply(pd.to_numeric, errors='coerce').mul([3600, 60, 1]).sum(axis=1, min_count=1)

# 48.3 ms ± 607 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

Padding beforehand, timedeltas + total_seconds:

%%timeit
counts = 2 - series.str.count(':')
pad = pd.Series(['0:' * c if pd.notna(c) else '' for c in counts], dtype='string')
res = pad.str.cat(series)

pd.to_timedelta(res, errors='coerce').map(lambda x: x.total_seconds())

# 183 ms ± 9.83 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

Pandas apply + Python sorted (very slow):

%%timeit
df = series.str.split(':', expand=True)
df = df.apply(sorted, axis=1, key=pd.notna, result_type='broadcast')
df.apply(pd.to_numeric).mul([3600, 60, 1]).sum(axis=1, min_count=1)

# 1.4 s ± 36.7 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
user_14317500
Related