Convert pandas timedelta to be displayed in seconds

Question

I was working on re-formatting some data in a dataframe and I needed to calculate a value for a new timedelta column which I did by subtracting start date of event with the start date when series is shifted up one row:

data['DURATION_NEW'] = (data['START'] - data['START'].shift(-1))

This work fine and creates a timedelta column, but the data there are in a very strange format:

foo['DURATION_NEW']
Out[80]: 
0      -1 days +23:53:30
1      -1 days +15:35:00
2      -1 days +23:50:00
3      -1 days +23:49:00
4      -1 days +23:53:30
      
1459   -1 days +23:47:00
1461   -1 days +23:51:00
1462   -1 days +22:08:01
1463   -1 days +23:39:30
1464                 NaT
Name: DURATION_NEW, Length: 1406, dtype: timedelta64[ns]

I need to somehow convert this data to be displayed in seconds. First I tried to convert it to a datetime, but for some reason got an error that dtype timedelta64[ns] cannot be converted to datetime64[ns].

Next I tried to manually re-convert it while specifying that I want it to be in seconds:

foo['DURATION_NEW'] = pd.to_timedelta(foo['DURATION_NEW'], unit='sec')

That didn't work either. All stays exactly as it is now.

How can I do this properly?

Answer 1

Use the total_seconds() method on the dt accessor:

foo['DURATION_NEW'].dt.total_seconds()