在Pandas中基于tpMessage列拆分timestamp，实现问答时间匹配

原始DataFrame #

timestamp   conversationId   UserId  MessageId      tpMessage   Message 
1614578324  ceb9004ae9d3    1c376ef 5bbd34859329    question    Where do you live?
1614578881  ceb9004ae9d3    1c376ef d3b5d3884152    answer      Brooklyn
1614583764  ceb9004ae9d3    1c376ef 0e4501fcd61f    question    What's your name?
1614590885  ceb9004ae9d3    1c376ef 97d841b79ff7    answer      Phill
1614594952  ceb9004ae9d3    1c376ef 11ed3fd24767    question    What's your gender?
1614602036  ceb9004ae9d3    1c376ef 601538860004    answer      Male
1614602581  ceb9004ae9d3    1c376ef 8bc8d9089609    question    How old are you?
1614606219  ceb9004ae9d3    1c376ef a2bd45e64b7c    answer      35
1614606240  loi90zj8q0qv    1c890r9 o2bd10ex4b8u    question    Where do you live?
1614606240  jto9034pe0i5    1c489rl o6bd35e64b5j    question    What's your name?
1614606250  jto9034pe0i5    1c489rl 96jd89i55b72    answer      Robert
1614606267  jto9034pe0i5    1c489rl 33yd1445d6ut    answer      Brandom
1614606267  loi90zj8q0qv    1c890r9 o2bd10ex4b8u    answer      London
1614606287  jto9034pe0i5    1c489rl b7q489iae77t    answer      Connor

需求说明 #

需要基于tpMessage列将timestamp拆分为ts_question和ts_answer两列，实现：

• 每个回答关联对应会话中的对应问题时间戳
• 支持一个问题对应多个回答的场景
• 支持同一时间多会话并行的场景

此前尝试用np.where实现，但会在不满足条件的位置填充0，无法达到预期效果。

预期输出 #

ts_question ts_answer   conversationId   UserId
1614578324  1614578881  ceb9004ae9d3    1c376ef
1614583764  1614590885  ceb9004ae9d3    1c376ef
1614594952  1614602036  ceb9004ae9d3    1c376ef
1614602581  1614606219  ceb9004ae9d3    1c376ef
1614606240  1614606250  jto9034pe0i5    1c489rl
1614606240  1614606267  jto9034pe0i5    1c489rl
1614606240  1614606267  loi90zj8q0qv    1c890r9
1614606240  1614606287  jto9034pe0i5    1c489rl

解决方案 #

使用pandas的分组、拆分、关联操作实现，代码如下：

import pandas as pd

# 构造原始DataFrame（如果已有数据可跳过此步）
data = {
    'timestamp': [1614578324, 1614578881, 1614583764, 1614590885, 1614594952, 1614602036, 1614602581, 1614606219, 1614606240, 1614606240, 1614606250, 1614606267, 1614606267, 1614606287],
    'conversationId': ['ceb9004ae9d3', 'ceb9004ae9d3', 'ceb9004ae9d3', 'ceb9004ae9d3', 'ceb9004ae9d3', 'ceb9004ae9d3', 'ceb9004ae9d3', 'ceb9004ae9d3', 'loi90zj8q0qv', 'jto9034pe0i5', 'jto9034pe0i5', 'jto9034pe0i5', 'loi90zj8q0qv', 'jto9034pe0i5'],
    'UserId': ['1c376ef', '1c376ef', '1c376ef', '1c376ef', '1c376ef', '1c376ef', '1c376ef', '1c376ef', '1c890r9', '1c489rl', '1c489rl', '1c489rl', '1c890r9', '1c489rl'],
    'MessageId': ['5bbd34859329', 'd3b5d3884152', '0e4501fcd61f', '97d841b79ff7', '11ed3fd24767', '601538860004', '8bc8d9089609', 'a2bd45e64b7c', 'o2bd10ex4b8u', 'o6bd35e64b5j', '96jd89i55b72', '33yd1445d6ut', 'o2bd10ex4b8u', 'b7q489iae77t'],
    'tpMessage': ['question', 'answer', 'question', 'answer', 'question', 'answer', 'question', 'answer', 'question', 'question', 'answer', 'answer', 'answer', 'answer'],
    'Message': ['Where do you live?', 'Brooklyn', "What's your name?", 'Phill', "What's your gender?", 'Male', 'How old are you?', '35', 'Where do you live?', "What's your name?", 'Robert', 'Brandom', 'London', 'Connor']
}
df = pd.DataFrame(data)

# 1. 按会话分组，为每个问题创建唯一分组标识
df['question_group'] = df.groupby('conversationId')['tpMessage'].apply(lambda x: x.eq('question').cumsum())

# 2. 拆分问题和回答数据集
questions = df[df['tpMessage'] == 'question'][['conversationId', 'UserId', 'question_group', 'timestamp']].rename(columns={'timestamp': 'ts_question'})
answers = df[df['tpMessage'] == 'answer'][['conversationId', 'UserId', 'question_group', 'timestamp']].rename(columns={'timestamp': 'ts_answer'})

# 3. 关联问题与回答，自动处理一对多场景
result = pd.merge(answers, questions, on=['conversationId', 'UserId', 'question_group'], how='left')

# 4. 调整输出列顺序
result = result[['ts_question', 'ts_answer', 'conversationId', 'UserId']]

print(result)

逻辑说明 #

• question_group：按conversationId分组后，统计每个会话中的问题次数，每个新问题会让分组号递增，确保同一问题下的所有回答归属同一分组。
• 拆分与关联：将问题和回答数据拆分后，通过会话ID、用户ID和问题分组三个维度关联，保证每个回答匹配到对应的问题时间戳。
• 兼容性：天然支持一个问题对应多个回答、同一时间多会话并行的场景，分组计算是会话独立的。

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