Pandas重复序列的计数

Question

我对用Python识别模式还很陌生，可能需要一些指导。我有一个很大的数据集，我在下面粘贴了它的示例：

我的目标是找到'foo' 'bar' 'baz'的任何序列模式，并计算'foo' 'bar' 'baz'的重复模式（如果该模式自身重复多次），同时按id分组。你知道吗

id  class_name  created_at
0   1   foo 2019-02-08 19:11:04
1   1   bar 2019-02-08 19:11:34
2   1   foo 2019-02-08 19:12:04
3   1   baz 2019-02-08 19:12:35
4   1   bar 2019-02-08 19:13:05
5   1   foo 2019-02-08 19:13:35
6   1   bar 2019-02-08 19:14:04
7   1   baz 2019-02-08 19:14:35
8   1   foo 2019-02-08 19:15:05
9   1   bar 2019-02-08 19:15:35
10  1   baz 2019-02-08 19:16:03
11  2   foo 2019-02-08 19:16:34
12  2   bar 2019-02-08 19:17:07
13  2   foo 2019-02-08 19:17:42
14  2   bar 2019-02-08 19:18:04
15  2   baz 2019-02-08 19:18:34
16  2   baz 2019-02-08 19:19:04
17  2   bar 2019-02-08 19:19:34
18  2   bar 2019-02-08 19:20:04
19  2   foo 2019-02-08 19:20:34

例如，上述数据集的输出类似于：

id  count           start_time             end_time
 1      2  2019-02-08 19:13:35  2019-02-08 19:16:03
 2      1  2019-02-08 19:17:42  2019-02-08 19:18:34

列类型如下：

id                     int64
class_name            object
created_at    datetime64[ns]
dtype: object

哪些模块最适合此任务？你知道吗

数据如下：

{'id': {0: 1, 1: 1, 2: 1, 3: 1, 4: 1, 5: 1, 6: 1, 7: 1, 8: 1, 9: 1, 10: 1, 11: 2, 12: 2, 13: 2, 14: 2, 15: 2, 16: 2, 17: 2, 18: 2, 19: 2}, 'class_name': {0: 'foo', 1: 'bar', 2: 'foo', 3: 'baz', 4: 'bar', 5: 'foo', 6: 'bar', 7: 'baz', 8: 'foo', 9: 'bar', 10: 'baz', 11: 'foo', 12: 'bar', 13: 'foo', 14: 'bar', 15: 'baz', 16: 'baz', 17: 'bar', 18: 'bar', 19: 'foo'}, 'created_at': {0: Timestamp('2019-02-08 19:11:04'), 1: Timestamp('2019-02-08 19:11:34'), 2: Timestamp('2019-02-08 19:12:04'), 3: Timestamp('2019-02-08 19:12:35'), 4: Timestamp('2019-02-08 19:13:05'), 5: Timestamp('2019-02-08 19:13:35'), 6: Timestamp('2019-02-08 19:14:04'), 7: Timestamp('2019-02-08 19:14:35'), 8: Timestamp('2019-02-08 19:15:05'), 9: Timestamp('2019-02-08 19:15:35'), 10: Timestamp('2019-02-08 19:16:03'), 11: Timestamp('2019-02-08 19:16:34'), 12: Timestamp('2019-02-08 19:17:07'), 13: Timestamp('2019-02-08 19:17:42'), 14: Timestamp('2019-02-08 19:18:04'), 15: Timestamp('2019-02-08 19:18:34'), 16: Timestamp('2019-02-08 19:19:04'), 17: Timestamp('2019-02-08 19:19:34'), 18: Timestamp('2019-02-08 19:20:04'), 19: Timestamp('2019-02-08 19:20:34')}}

Answer 1

添加序列比较方法时，可以使用rolling()。你知道吗

df['class_name'] = pd.factorize(df['class_name'])[0]

def custom_func(frame):
    frame['match']=frame['class_name'].rolling(3).apply(lambda x: np.array_equal(x, [0, 1, 2]), raw=True)
    frame['start_time'] = frame['created_at'].shift(2)
    frame = frame[frame['match']==1].agg({'match':'count','start_time':'min','created_at':'max'})
    return frame

df = df.groupby('id').apply(lambda frame:custom_func(frame)).rename(columns={'match':'count','created_at':'end_time'})
print(df)

    count          start_time            end_time
id                                               
1       2 2019-02-08 19:13:35 2019-02-08 19:16:03
2       1 2019-02-08 19:17:42 2019-02-08 19:18:34

Answer 2

一个简单的解决方案可以是：

df.groupby("id").apply(lambda x : len(re.findall("foo bar baz", ' '.join(x['class_name']))))

Answer 3

走了几步，但最终还是达到了目的。。你知道吗

初始化数据：

import pandas as pd
from pandas import Timestamp
import numpy as np


dict_ ={'id': {0: 1, 1: 1, 2: 1, 3: 1, 4: 1, 5: 1, 6: 1, 7: 1, 8: 1, 9: 1, 10: 1, 11: 2, 12: 2, 13: 2, 14: 2, 15: 2, 16: 2, 17: 2, 18: 2, 19: 2}, 'class_name': {0: 'foo', 1: 'bar', 2: 'foo', 3: 'baz', 4: 'bar', 5: 'foo', 6: 'bar', 7: 'baz', 8: 'foo', 9: 'bar', 10: 'baz', 11: 'foo', 12: 'bar', 13: 'foo', 14: 'bar', 15: 'baz', 16: 'baz', 17: 'bar', 18: 'bar', 19: 'foo'}, 'created_at': {0: Timestamp('2019-02-08 19:11:04'), 1: Timestamp('2019-02-08 19:11:34'), 2: Timestamp('2019-02-08 19:12:04'), 3: Timestamp('2019-02-08 19:12:35'), 4: Timestamp('2019-02-08 19:13:05'), 5: Timestamp('2019-02-08 19:13:35'), 6: Timestamp('2019-02-08 19:14:04'), 7: Timestamp('2019-02-08 19:14:35'), 8: Timestamp('2019-02-08 19:15:05'), 9: Timestamp('2019-02-08 19:15:35'), 10: Timestamp('2019-02-08 19:16:03'), 11: Timestamp('2019-02-08 19:16:34'), 12: Timestamp('2019-02-08 19:17:07'), 13: Timestamp('2019-02-08 19:17:42'), 14: Timestamp('2019-02-08 19:18:04'), 15: Timestamp('2019-02-08 19:18:34'), 16: Timestamp('2019-02-08 19:19:04'), 17: Timestamp('2019-02-08 19:19:34'), 18: Timestamp('2019-02-08 19:20:04'), 19: Timestamp('2019-02-08 19:20:34')}}
df=pd.DataFrame(dict_)

我把结束日期向后移了两个点，这样每3步就有一个开始和结束。我在小组内这样做是为了保持连续性：

df['end_time'] = df.groupby('id')['created_at'].shift(-2)

为了找到我们有序列['foo', 'bar', 'baz']的点，我把df['class_name']和shift(-1)和shift(-2)一起压缩

[[x,y,z] for x,y,z in zip(df['class_name'], df['class_name'].shift(-1), df['class_name'].shift(-2))]
[['foo', 'bar', 'foo'],
 ['bar', 'foo', 'baz'],
 ['foo', 'baz', 'bar'],
 ['baz', 'bar', 'foo'],
 ['bar', 'foo', 'bar'],
 ['foo', 'bar', 'baz'],
 ['bar', 'baz', 'foo'],
 ['baz', 'foo', 'bar'],
 ['foo', 'bar', 'baz'],
 ['bar', 'baz', 'foo'],
 ['baz', 'foo', 'bar'],
 ['foo', 'bar', 'foo'],
 ['bar', 'foo', 'bar'],
 ['foo', 'bar', 'baz'],
 ['bar', 'baz', 'baz'],
 ['baz', 'baz', 'bar'],
 ['baz', 'bar', 'bar'],
 ['bar', 'bar', 'foo'],
 ['bar', 'foo', nan],
 ['foo', nan, nan]]

然后我将其转换为numpy数组，并将其与我们要查找的内容进行比较。你知道吗

matches = np.array([[x,y,z] for x,y,z in zip(df['class_name'], df['class_name'].shift(-1), df['class_name'].shift(-2))]) == ['foo', 'bar', 'baz']
array([[ True,  True, False],
       [False, False,  True],
       [ True, False, False],
       [False,  True, False],
       [False, False, False],
       [ True,  True,  True],
       [False, False, False],
       [False, False, False],
       [ True,  True,  True],
       [False, False, False],
       [False, False, False],
       [ True,  True, False],
       [False, False, False],
       [ True,  True,  True],
       [False, False,  True],
       [False, False, False],
       [False,  True, False],
       [False,  True, False],
       [False, False, False],
       [ True, False, False]])

然后为了得到子集向量，我只.all()比较数组。这将为我们提供出发点

vec = [x.all() == True for x in x]
[False,
 False,
 False,
 False,
 False,
 True,
 False,
 False,
 True,
 False,
 False,
 False,
 False,
 True,
 False,
 False,
 False,
 False,
 False,
 False]

现在我们来检查一下

subset = df.loc[vec]
id class_name          created_at            end_time
5    1        foo 2019-02-08 19:13:35 2019-02-08 19:14:35
8    1        foo 2019-02-08 19:15:05 2019-02-08 19:16:03
13   2        foo 2019-02-08 19:17:42 2019-02-08 19:18:34

因为我们需要分组的版本，所以我们只需要groupby和agg就可以得到最终的结果。你知道吗

subset.groupby('id').agg({'class_name':'count', 'created_at':'min', 'end_time':'max'})
    class_name          created_at            end_time
id                                                    
1            2 2019-02-08 19:13:35 2019-02-08 19:16:03
2            1 2019-02-08 19:17:42 2019-02-08 19:18:34