Finding a specific sequential pattern

Question

I have a dataframe like this:

Id  Seq Event
1     2    A 
1     3    B 
1     5    c 
1     6    A 
2     1    A 
2     2    B 
2     4    A 
2     6    B

I want to find how many times a specific pattern appears. Let's say "AB" . The output should be.

Id  Pattern_Count
1    1
2    2

I tried using Event + Event.shift() and searching for the specific pattern. It's a tedious task when I have to search for a longer pattern like "ABCDE" and I don't want to shift it 4 times. Is there any alternative way to do this?

cs95 · Accepted Answer · 2019-01-21 20:39:19Z

4

You can do this with groupby, agg, and str.count:

(df.groupby('Id')['Event']
   .agg(''.join)
   .str.count('AB')
   .reset_index(name='Pattern_Count'))

   Id  Pattern_Count
0   1              1
1   2              2

Note that str.count will work for simple substring matches only, regex patterns are not supported directly.

answered Jan 21, 2019 at 20:39

cs95

406k106 gold badges744 silver badges797 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

R. Wayne Over a year ago

I've been studying your answer. It amazes me that so many things can be strung together in this dense fashion. I would not have thought of using .join as a function to .agg. It does not show up in the documentation I have read. How did you know you could use that in .agg? I tried dir(groupby.agg) to list all functions, but it did not work.

cs95 Over a year ago

@R.Wayne You can pass any function to agg, as long as it results in a single, aggregated value. In this case, I used str.join. See this post on string concatenation. agg(''.join) can also be specified as agg(lambda x: ''.join(x)) but is more verbose and less performant.

busybear · Accepted Answer · 2019-01-21 20:44:58Z

2

You can use groupby to isolate your groups then concatenate your strings with sum to count the occurrences of your substring.

result = df.groupby('Id')['Event'].sum().str.count('AB')

answered Jan 21, 2019 at 20:44

busybear

10.7k1 gold badge29 silver badges44 bronze badges

2 Comments

cs95 Over a year ago

concatenating strings with sum is not very efficient since strings are immutable.

busybear Over a year ago

Good point. Seemed more intuitive to use sum, but join should be the faster approach.

Collectives™ on Stack Overflow

Finding a specific sequential pattern

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related