使用Pandas统计聊天数据集中三方对话的数量

三方对话定义 #

red_0x向green_0x发送消息，green_0x回复red_0x，red_0x再回复green_0x，此类对话在数据集中计为1次三方对话。

需求：在一对一对话系统中统计数据集中符合该规则的三方对话总数。当前数据已按timestamp排序，但仅保留red用户前两条、green用户第一条消息的方式，无法正确匹配red_0x发消息→green_0x回复→red_0x再回复的完整序列。

示例数据集：

方案1：SQL实现 #

利用窗口函数为每个对话内的消息生成顺序编号，再通过关联查询匹配符合序列的对话：

1. 为对话内消息生成序号

WITH ordered_messages AS (
    SELECT 
        conversation_id,
        user_id,
        ROW_NUMBER() OVER (PARTITION BY conversation_id ORDER BY timestamp) AS msg_seq
    FROM your_dataset_table
)

2. 统计符合规则的对话总数

SELECT COUNT(DISTINCT conversation_id) AS three_party_conversation_count
FROM ordered_messages om1
JOIN ordered_messages om2 
    ON om1.conversation_id = om2.conversation_id 
    AND om1.msg_seq + 1 = om2.msg_seq
    AND om1.user_id LIKE 'red_%'
    AND om2.user_id LIKE 'green_%'
JOIN ordered_messages om3 
    ON om2.conversation_id = om3.conversation_id 
    AND om2.msg_seq + 1 = om3.msg_seq
    AND om3.user_id LIKE 'red_%';

方案2：Python（Pandas）实现 #

基于已排序的数据集，通过分组遍历检查序列模式：

1. 准备数据并生成消息序号

import pandas as pd

# 假设df是已按conversation_id和timestamp排序的数据集
df = df.sort_values(by=['conversation_id', 'timestamp'])
# 为每个对话内的消息分配递增序号
df['msg_seq'] = df.groupby('conversation_id').cumcount() + 1

2. 遍历匹配符合规则的对话

valid_conv_ids = set()

# 按对话分组遍历
for conv_id, group in df.groupby('conversation_id'):
    user_list = group['user_id'].tolist()
    # 检查连续三条消息的用户模式
    for i in range(len(user_list) - 2):
        if (user_list[i].startswith('red_') 
            and user_list[i+1].startswith('green_') 
            and user_list[i+2].startswith('red_')):
            valid_conv_ids.add(conv_id)
            break  # 单个对话只要符合一次即计数，无需重复检查

# 输出结果
print(f"符合规则的三方对话总数：{len(valid_conv_ids)}")

内容的提问来源于stack exchange，提问作者n3a5p7s9t1e3r