减去datetime列并以秒为单位获得结果

Question

我有一个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

我试图将它们组合成一个datetime，并将当前行与上一行相减，以秒为单位获得datetime的差列，如：

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

我正在尝试：

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


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

但是它给我的输出是0天00:00:10。我在任何地方都找不到合适的解决方案。提前谢谢

Answer 1

首先通过^{}创建日期时间，然后通过^{}转换时间增量：

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

对于整数，使用integer na表示缺少值的整数：

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

如果需要秒而不是floats，请使用^{}添加自定义函数：

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