Subtract datetime column and get result in seconds pandas

user_7931463 0
Viewed 669

I have a df as:

Name    Date       Time
 A   02/20/2021   12:30:06
 A   02/20/2021   12:30:20
 A   02/21/2021   12:30:20
 A   02/22/2021   02:30:30

i am trying to combine them into one datetime and subtract current row with previous to get the difference columns of datetime in seconds like:

Name   Date        Time      diff
 A    02/20/2021   12:30:06   
 A    02/20/2021   12:30:20   14 seconds
 A    02/21/2021   12:30:20   86400 seconds
 A    02/22/2021   02:30:30   50410 seconds

I am trying this:

df['Datetime'] = df['Date'].astype(str)+' '+df['Time'].astype(str)


df[['diff']] = df.groupby('Name')[['Datetime', 'Result']].diff()

But it is giving me output as 0 days 00:00:10.I am not able to find proper solution anywhere.thanks in advance

pandas dataframe datetime python
1 Answers

Create datetimes first by to_datetime and then convert timedeltas by Series.dt.total_seconds:

df['Datetime'] = pd.to_datetime(df['Date'].astype(str) +' '+df['Time'].astype(str))

df['diff'] = df.groupby('Name')['Datetime'].diff().dt.total_seconds()
print (df)
  Name        Date      Time            Datetime     diff
0    A  02/20/2021  12:30:06 2021-02-20 12:30:06      NaN
1    A  02/20/2021  12:30:20 2021-02-20 12:30:20     14.0
2    A  02/21/2021  12:30:20 2021-02-21 12:30:20  86400.0
3    A  02/22/2021  02:30:30 2021-02-22 02:30:30  50410.0

For integers use integer na for integers with missing values:

df['diff'] = df.groupby('Name')['Datetime'].diff().dt.total_seconds().astype('Int64')
print (df)
  Name        Date      Time            Datetime   diff
0    A  02/20/2021  12:30:06 2021-02-20 12:30:06   <NA>
1    A  02/20/2021  12:30:20 2021-02-20 12:30:20     14
2    A  02/21/2021  12:30:20 2021-02-21 12:30:20  86400
3    A  02/22/2021  02:30:30 2021-02-22 02:30:30  50410

If need seconds instead floats add custom function with Series.map:

df['Datetime'] = pd.to_datetime(df['Date'].astype(str) +' '+df['Time'].astype(str))

f = lambda x: '' if pd.isna(x) else f'{int(x)} seconds'
df['diff'] = df.groupby('Name')['Datetime'].diff().dt.total_seconds().map(f)
print (df)
  Name        Date      Time            Datetime           diff
0    A  02/20/2021  12:30:06 2021-02-20 12:30:06               
1    A  02/20/2021  12:30:20 2021-02-20 12:30:20     14 seconds
2    A  02/21/2021  12:30:20 2021-02-21 12:30:20  86400 seconds
3    A  02/22/2021  02:30:30 2021-02-22 02:30:30  50410 seconds
user_29010020
Related