Switch from seconds to Timedelta by column with specific name

user_18396935 0
Viewed 36

Let it be the following python pandas dataframe:

other value time_UK total_time_UK color counter_time_UK
392 idk 0 0 red 8393283
2393 odp 930233 23423423 blue 322332
392 kfl 0 0 red 0
323 bcn 783732 32423 green 42332
9695 uie 0 0 red 3423283
111 xas 0 3423323 blue 322

I would like for the columns containing the string time_UK, to convert their values in seconds to TimeDelta and if its value is 0 to NaT.

other value time_UK total_time_UK color counter_time_UK
392 idk NaT NaT red pd.to_timedelta(8393283)
2393 odp pd.to_timedelta(930233) pd.to_timedelta(23423423) blue pd.to_timedelta(322332)
392 kfl NaT NaT red NaT
323 bcn pd.to_timedelta(783732) pd.to_timedelta(32423) green pd.to_timedelta(42332)
9695 uie NaT NaT red pd.to_timedelta(3423283)
111 xas NaT pd.to_timedelta(3423323) blue pd.to_timedelta(322)

Right now Im using next code:

    # Sets the 0 second values to NaT    
    df.loc[df['time_UK'] == 0.0, 'time_UK'] = pd.NaT
    
    # Converts seconds to TimeDelta type
    df['time_UK'] = pd.to_timedelta(df['time_UK'], unit='s')

I am grateful for the help offered.

pandas timedelta dataframe python
1 Answers

Use DataFrame.filter for DataFrame df1 by columns name time_UK, convert to floats, replace 0 by DataFrame.mask with convert all columns to timedeltas by to_timedelta:

df1 = df.filter(like='time_UK').astype(float)
df[df1.columns] = df1.mask(df1.eq(0)).apply(pd.to_timedelta, unit='s')
print (df)
   other value          time_UK     total_time_UK  color  counter_time_UK
0    392   idk              NaT               NaT    red 97 days 03:28:03
1   2393   odp 10 days 18:23:53 271 days 02:30:23   blue  3 days 17:32:12
2    392   kfl              NaT               NaT    red              NaT
3    323   bcn  9 days 01:42:12   0 days 09:00:23  green  0 days 11:45:32
4   9695   uie              NaT               NaT    red 39 days 14:54:43
5    111   xas              NaT  39 days 14:55:23   blue  0 days 00:05:22
user_29010020
Related