在R语言中基于两列按用户消息交互对排列数据行

嘿，我来帮你搞定这个数据表的重新排列需求！下面我会分Excel和SQL两种常用场景来讲解，你可以根据自己的工具选择对应的方法：

方法一：用Excel实现 #

步骤1：创建统一的对话对标识 #

首先，我们需要让同一个对话的双向记录（比如A→B和B→A）拥有相同的标识，方便后续分组。在数据表旁边新增一列（比如D列），输入公式：

=MIN(A2,B2)&"-"&MAX(A2,B2)

下拉填充整列，这样不管是A发消息给B，还是B发消息给A，都会生成相同的对话标识（比如A-B）。

步骤2：生成完整的双向用户对 #

1. 复制D列的所有唯一对话对（可以用「数据」→「删除重复值」提取），粘贴到新的工作表里。
2. 对每个对话对拆分出两个用户，生成两行记录：比如对话对A-B，生成一行Sender:A, Receiver:B，另一行Sender:B, Receiver:A。

步骤3：填充Messages_sent数值 #

用SUMIF函数匹配原表数据，填充新生成的记录：

=IFERROR(SUMIFS(原表!C:C,原表!A:A,新表!A2,原表!B:B,新表!B2),0)

这个公式会在原表中找到对应Sender和Receiver的Messages_sent总和，找不到的话就返回0，完美满足你的需求。

步骤4：排序并计算对话长度 #

1. 按对话对标识列排序，让同一个对话的两行记录挨在一起。
2. 新增一列计算对话长度，在第一行对话的第一行输入公式：

=C2+C3

然后隔行下拉填充，或者用条件判断只在奇数行计算：

=IF(MOD(ROW(),2)=1,C2+C3,"")

方法二：用SQL实现（以MySQL为例） #

如果你的数据存在数据库里，可以用SQL语句直接生成目标表：

步骤1：生成双向用户对并填充Messages_sent #

WITH unique_users AS (
    SELECT DISTINCT sender FROM your_table
    UNION
    SELECT DISTINCT receiver FROM your_table
),
user_pairs AS (
    SELECT 
        u1.user AS sender,
        u2.user AS receiver
    FROM unique_users u1
    CROSS JOIN unique_users u2
    WHERE u1.user != u2.user
)
SELECT 
    up.sender,
    up.receiver,
    IFNULL(t.messages_sent, 0) AS messages_sent
FROM user_pairs up
LEFT JOIN your_table t 
    ON up.sender = t.sender AND up.receiver = t.receiver
ORDER BY CONCAT(LEAST(up.sender, up.receiver), '-', GREATEST(up.sender, up.receiver));

这段代码会先提取所有唯一用户，生成所有非自身的双向用户对，再左连接原表填充消息数，没有的就填0，最后按统一的对话对排序。

步骤2：计算对话长度 #

如果要直接计算每个对话对的总长度，可以在上面的SQL基础上加上窗口函数：

WITH unique_users AS (
    SELECT DISTINCT sender FROM your_table
    UNION
    SELECT DISTINCT receiver FROM your_table
),
user_pairs AS (
    SELECT 
        u1.user AS sender,
        u2.user AS receiver
    FROM unique_users u1
    CROSS JOIN unique_users u2
    WHERE u1.user != u2.user
),
filled_data AS (
    SELECT 
        up.sender,
        up.receiver,
        IFNULL(t.messages_sent, 0) AS messages_sent,
        CONCAT(LEAST(up.sender, up.receiver), '-', GREATEST(up.sender, up.receiver)) AS pair_id
    FROM user_pairs up
    LEFT JOIN your_table t 
        ON up.sender = t.sender AND up.receiver = t.receiver
)
SELECT 
    sender,
    receiver,
    messages_sent,
    SUM(messages_sent) OVER (PARTITION BY pair_id) AS conversation_length
FROM filled_data
ORDER BY pair_id;

这样就能直接得到每个对话的总长度啦！

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