convert string data type duration to date time format in Pandas

Question

How do I extract just the time portion of datetime64[ns] column formatted 1900-01-01 00:32:59 and convert it into total seconds?

I want the end result to be 1979 in seconds in this example, where we are exactly (32*60 + 59) seconds from 1900-01-01 00:00:00.

Answer 1

You can use to_timedelta + strftime + total_seconds + astype:

df = pd.DataFrame({"date": ["1900-01-01 00:32:59"]})
df['date'] = pd.to_datetime(df['date'])
print (df)
                 date
0 1900-01-01 00:32:59

df['total'] = pd.to_timedelta(df['date'].dt.strftime("%H:%M:%S"))
                .dt.total_seconds().astype(int)
print (df)
                 date  total
0 1900-01-01 00:32:59   1979

Or floor + total_seconds + astype:

df['total'] = (df['date'] - df['date'].dt.floor('D')).dt.total_seconds().astype(int)
print (df)
                 date  total
0 1900-01-01 00:32:59   1979

Or normalize + total_seconds + astype

df['total'] = (df['date'] - df['date'].dt.normalize()).dt.total_seconds().astype(int)
print (df)
                 date  total
0 1900-01-01 00:32:59   1979