如何根据另一列查找时差

Question

我有这样一个数据集

user-id      date-time                   msg
  1          2016-12-09 10:25:00          1
  2          2016-12-09 10:26:00          0
  3          2016-12-09 10:26:00          1
  2          2016-12-09 10:27:00          1
  1          2016-12-09 10:28:00          2
  2          2016-12-09 10:28:00          1
  3          2016-12-09 10:29:00          2
  2          2016-12-09 10:29:00          1
  1          2016-12-09 10:30:00          3

我想有一个新的列来计算每个记录和消息第一次与该记录相似时的时间差。像这样：

 user-id      date-time                  msg        time-diffrence
  1          2016-12-09 10:25:00          1            00:00
  2          2016-12-09 10:26:00          0            00:00
  3          2016-12-09 10:26:00          1            01:00
  2          2016-12-09 10:27:00          1            02:00
  1          2016-12-09 10:28:00          2            00:00
  2          2016-12-09 10:28:00          1            03:00
  3          2016-12-09 10:29:00          2            01:00
  2          2016-12-09 10:29:00          1            04:00
  1          2016-12-09 10:30:00          3            00:00

我找到了只考虑日期-时间，或使用loc或iloc的解决方案，但它们并不适用

Answer 1

选项1

使用groupby和iloc：

df['time-difference'] = df.groupby('msg')['date-time'].apply(lambda x: x - x.iloc[0])

输出：

   user-id           date-time  msg time-difference
0        1 2016-12-09 10:25:00    1        00:00:00
1        2 2016-12-09 10:26:00    0        00:00:00
2        3 2016-12-09 10:26:00    1        00:01:00
3        2 2016-12-09 10:27:00    1        00:02:00
4        1 2016-12-09 10:28:00    2        00:00:00
5        2 2016-12-09 10:28:00    1        00:03:00
6        3 2016-12-09 10:29:00    2        00:01:00
7        2 2016-12-09 10:29:00    1        00:04:00
8        1 2016-12-09 10:30:00    3        00:00:00

选项2

将groupby与transform和first或min一起使用：

df['time-difference'] = df['date-time'] - df.groupby('msg')['date-time'].transform('first')

输出：

   user-id           date-time  msg time-difference
0        1 2016-12-09 10:25:00    1        00:00:00
1        2 2016-12-09 10:26:00    0        00:00:00
2        3 2016-12-09 10:26:00    1        00:01:00
3        2 2016-12-09 10:27:00    1        00:02:00
4        1 2016-12-09 10:28:00    2        00:00:00
5        2 2016-12-09 10:28:00    1        00:03:00
6        3 2016-12-09 10:29:00    2        00:01:00
7        2 2016-12-09 10:29:00    1        00:04:00
8        1 2016-12-09 10:30:00    3        00:00:00