python - Dataframe groupby - 返回日志条目的增量时间

Question

我有一些日志数据，我想先按 user_id 分组，然后选择第二个条目。下面就是这样做的。缺少的步骤是分组后每个条目相对于第一个条目的年龄。

dd = pd.DataFrame({'item_id': {0: 0, 1: 4, 2: 6, 3: 8, 4: 9, 5: 1}, 'date': {0: '2013-12-29T17:56:01Z', 1: '2013-12-29T19:44:09Z', 2: '2013-12-29T19:58:05Z', 3: '2013-12-29T20:00:09Z', 4: '2013-12-29T20:13:35Z', 5: '2013-12-29T20:19:56Z'}, 'user_id': {0: 6, 1: 8, 2: 3, 3: 3, 4: 6, 5: 6}})
print "Step 1: Original DataFrame, sorted by date:\n",  dd

g = dd.groupby(by='user_id', sort=False)
print "\nStep 2: Grouped by User ID:\n", g.head()

# Print the 2nd entey (if it exists)
print "\nStep 3: The 2nd user for each entry:\n", g.nth(1).dropna(how='all')

# age?

返回:

Step 1: Original DataFrame, sorted by date:
                   date  item_id  user_id
0  2013-12-29T17:56:01Z        0        6
1  2013-12-29T19:44:09Z        4        8
2  2013-12-29T19:58:05Z        6        3
3  2013-12-29T20:00:09Z        8        3
4  2013-12-29T20:13:35Z        9        6
5  2013-12-29T20:19:56Z        1        6

Step 2: Grouped by User ID:
                           date  item_id  user_id
user_id                                          
6       0  2013-12-29T17:56:01Z        0        6
        4  2013-12-29T20:13:35Z        9        6
        5  2013-12-29T20:19:56Z        1        6
8       1  2013-12-29T19:44:09Z        4        8
3       2  2013-12-29T19:58:05Z        6        3
        3  2013-12-29T20:00:09Z        8        3

Step 3: The 2nd user for each entry:
                         date  item_id
user_id                               
6        2013-12-29T20:13:35Z        9
3        2013-12-29T20:00:09Z        8

但我想在第 2 步打印相对于该用户消费的第一个 item_id 的年龄(例如，十进制天数)，这样我就可以判断第 3 步中日志条目的年龄。是否有一种无需迭代即可执行此操作的 pythonic 方法？

期望的输出是:

   user_id                 date  item_id      age
0        3  2013-12-29 20:00:09        8  0:02:04
1        6  2013-12-29 20:13:35        9  2:17:34

Answer 1

首先将日期从字符串列转换为 datetime64[ns] dtype

In [21]: dd['date'] = pd.to_datetime(dd['date'])

In [22]: dd
Out[22]: 
                 date  item_id  user_id
0 2013-12-29 17:56:01        0        6
1 2013-12-29 19:44:09        4        8
2 2013-12-29 19:58:05        6        3
3 2013-12-29 20:00:09        8        3
4 2013-12-29 20:13:35        9        6
5 2013-12-29 20:19:56        1        6

[6 rows x 3 columns]

按日期排序

In [23]: dd.sort_index(by='date')
Out[23]: 
                 date  item_id  user_id
0 2013-12-29 17:56:01        0        6
1 2013-12-29 19:44:09        4        8
2 2013-12-29 19:58:05        6        3
3 2013-12-29 20:00:09        8        3
4 2013-12-29 20:13:35        9        6
5 2013-12-29 20:19:56        1        6

[6 rows x 3 columns]

定义一个函数来区分该列(并只返回组的其余部分)

In [4]: def f(x):
   ...:     x['diff'] = x['date']-x['date'].iloc[0]
   ...:     return x
   ...: 

In [5]: dd.sort_index(by='date').groupby('user_id').apply(f)
Out[5]: 
                 date  item_id  user_id     diff
0 2013-12-29 17:56:01        0        6 00:00:00
1 2013-12-29 19:44:09        4        8 00:00:00
2 2013-12-29 19:58:05        6        3 00:00:00
3 2013-12-29 20:00:09        8        3 00:02:04
4 2013-12-29 20:13:35        9        6 02:17:34
5 2013-12-29 20:19:56        1        6 02:23:55

[6 rows x 4 columns]

差异现在是 timedelta64[ns]，参见 here了解如何转换/舍入到特定频率(例如天)。

这是 pandas 0.13(隔天或第二天发布)。其中大部分也适用于 0.12。