Question: I wanted to count the number of three way conversations that have occured in a dataset. A chat group_x can consist of multiple members. What is a three way conversation?  1st way - red_x sends a message in the group_x. 2nd way - green_x replies in the same group_x. 3rd way - red_x sends a reply in the same group_x.  This can be called a three way conversation. The sequence has to be exactly red_#, green_#, red_#. What is touchpoint?  Touchpoint 1 - red_x's first message. Touchpoint 2 - green_x's first message. Touchpoint 3 - red_x's second message.  Code to easily generate a sample dataset I'm working with. import pandas as pd from pandas import Timestamp t1_df = pd.DataFrame({'from_red': [True, False, True, True, True, True, True, True, True, True, True, True, True, True, True, True, True, True, False, True], 'sent_time': [Timestamp('2021-05-01 06:26:00'), Timestamp('2021-05-04 10:35:00'), Timestamp('2021-05-07 12:16:00'), Timestamp('2021-05-07 12:16:00'), Timestamp('2021-05-09 13:39:00'), Timestamp('2021-05-11 10:02:00'), Timestamp('2021-05-12 13:10:00'), Timestamp('2021-05-12 13:10:00'), Timestamp('2021-05-13 09:46:00'), Timestamp('2021-05-13 22:30:00'), Timestamp('2021-05-14 14:14:00'), Timestamp('2021-05-14 17:08:00'), Timestamp('2021-06-01 09:22:00'), Timestamp('2021-06-01 21:26:00'), Timestamp('2021-06-03 20:19:00'), Timestamp('2021-06-03 20:19:00'), Timestamp('2021-06-09 07:24:00'), Timestamp('2021-05-01 06:44:00'), Timestamp('2021-05-01 08:01:00'), Timestamp('2021-05-01 08:09:00')], 'w_uid': ['w_000001', 'w_112681', 'w_002516', 'w_002514', 'w_004073', 'w_005349', 'w_006803', 'w_006804', 'w_008454', 'w_009373', 'w_010063', 'w_010957', 'w_066840', 'w_071471', 'w_081446', 'w_081445', 'w_106472', 'w_000002', 'w_111906', 'w_000003'], 'user_id': ['red_00001', 'green_0263', 'red_01071', 'red_01071', 'red_01552', 'red_01552', 'red_02282', 'red_02282', 'red_02600', 'red_02854', 'red_02854', 'red_02600', 'red_00001', 'red_09935', 'red_10592', 'red_10592', 'red_12292', 'red_00002', 'green_0001', 'red_00003'], 'group_id': [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1], 'touchpoint': [1, 2, 1, 3, 1, 3, 1, 3, 1, 1, 3, 3, 3, 1, 1, 3, 1, 1, 2, 1]}, columns = ['from_red', 'sent_time', 'w_uid', 'user_id', 'group_id', 'touchpoint']) t1_df['sent_time'] = pd.to_datetime(t1_df['sent_time'], format = "%d-%m-%Y") t1_df  The dataset looks like this:     from_red sent_time w_uid user_id group_id touchpoint     True 2021-05-01 06:26:00 w_000001 red_00001 0 1   False 2021-05-04 10:35:00 w_112681 green_0263 0 2   True 2021-05-07 12:16:00 w_002516 red_01071 0 1   True 2021-05-07 12:16:00 w_002514 red_01071 0 3   True 2021-05-09 13:39:00 w_004073 red_01552 0 1   True 2021-05-11 10:02:00 w_005349 red_01552 0 3   True 2021-05-12 13:10:00 w_006803 red_02282 0 1   True 2021-05-12 13:10:00 w_006804 red_02282 0 3   True 2021-05-13 09:46:00 w_008454 red_02600 0 1   True 2021-05-13 22:30:00 w_009373 red_02854 0 1   True 2021-05-14 14:14:00 w_010063 red_02854 0 3   True 2021-05-14 17:08:00 w_010957 red_02600 0 3   True 2021-06-01 09:22:00 w_066840 red_00001 0 3   True 2021-06-01 21:26:00 w_071471 red_09935 0 1   True 2021-06-03 20:19:00 w_081446 red_10592 0 1   True 2021-06-03 20:19:00 w_081445 red_10592 0 3   True 2021-06-09 07:24:00 w_106472 red_12292 0 1   True 2021-05-01 06:44:00 w_000002 red_00002 1 1   False 2021-05-01 08:01:00 w_111906 green_0001 1 2   True 2021-05-01 08:09:00 w_000003 red_00003 1 1     Here is what I have tried, but the query is taking too long. Is there a faster way to achieve the same? test_df = pd.DataFrame() for i in range(len(t1_df['sent_time'])-1): if t1_df.query(f"group_id == {i}")['from_red'].nunique() == 2: y = t1_df.query(f"group_id == {i} & touchpoint == 2").loc[:, ['sent_time']].values[0][0] x = t1_df.query(f"group_id == {i} & sent_time > @y & (touchpoint == 3)").sort_values('sent_time') test_df = pd.concat([test_df, x]) test_df.merge(x, how = "outer") else: pass test_df 

Answer: For me it's not clear how you define the "three way conversation". Within on group, if you have the input messages what option(s) do you consider as "three way conversation"? There are several options: Input : red_0, red_2, green_0, red_1, red_0, red_2, red_1 Option1: red_2, green_0, red_1 Option2: red_0, green_0, red_0 + : red_2, green_0, red_2  and many more. Your code example returns the second msg of a user when sent after green: OptionX: green_0, red_0 + : green_0, red_2 + : green_0, red_1  without keeping track if some red user sent a msg before green. Another question is, what happens if green is sending multiple times within one group. Input : red_0, red_2, green_0, green_0, red_1, red_0, green_1, red_1  Based on your description "The sequence has to be exactly red_#, green_#, red_#." I guess, Option1 is what you are looking for and maybe that it's even independent from the color: color0_#, color1_#, color0_#. Correct me if I'm wrong ;). Prepare the DataFrame To get the operation more generic, I would first prepare the DataFrame, e.g. extract the color of the user and get a integer represenation for the color # extract the user color and id t1_df[['color', 'id']] = t1_df.pop('user_id').str.split('_', expand=True) # get the dtypes right, also it is not needed here t1_df.id = t1_df.id.astype(int) t1_df.color = t1_df.color.astype('category') # get color as intager t1_df['color_as_int'] =pd.factorize(t1_df.color)[0]  Detect the sequence color0_#, color1_#, color0_# # a three way conversation is where color_as_int is [...,a,b,a,...] # expressed as difference it's color_as_int.diff() is [...,c,-c,...] # get the difference with tracking the group, therefore first sort t1_df.sort_values(['group_id', 'sent_time'], inplace=True) d_color = t1_df.groupby(['group_id']).color_as_int.diff() m = (d_color != 0) & (d_color == -d_color.shift(-1)) # detect [...,c,-c,...] # count up for each three way conversation m[m] = m[m].cumsum() m = m.astype(int) # get the labels for the dataframe [...,a,b,a,...] t1_df['three_way_conversation'] = m + m.shift(1, fill_value=0) + m.shift(-1, fill_value=0)  which returns and works for any color columns = ['sent_time', 'group_id', 'color', 'id', 'touchpoint'] print(t1_df.loc[t1_df['three_way_conversation']>0, columns]) sent_time group_id color id touchpoint 0 2021-05-01 06:26:00 0 red 1 1 1 2021-05-04 10:35:00 0 green 263 2 2 2021-05-07 12:16:00 0 red 1071 1 17 2021-05-01 06:44:00 1 red 2 1 18 2021-05-01 08:01:00 1 green 1 2 19 2021-05-01 08:09:00 1 red 3 1  Bonus with the DataFrame preparation you can easily count the msg per color or user within a group or get the first and last time of a msg from a color or user. cumcount is faster as count and pd.merg() afterwards. t1_df['color_msg_count'] = t1_df.groupby(['group_id', 'color']).cumcount() + 1 t1_df['user_msg_count'] = t1_df.groupby(['group_id', 'color', 'id']).cumcount() + 1 t1_df['user_sent_time_min'] = t1_df.sort_values('sent_time').groupby(['group_id', 'color', 'id']).sent_time.cummin() t1_df['user_sent_time_max'] = t1_df.sort_values('sent_time', ascending=False).groupby(['group_id', 'color', 'id']).sent_time.cummax()