如何基于两列生成配对唯一ID，构建正确的有向网络？

原始数据 #

用于构建有向网络的初始数据如下：

问题说明 #

Order列代表节点的连接顺序，正确的有向连接应为相邻Order的节点配对（如AA→BB、BB→CC），但当前网络错误统计了AA→CC这类跨序连接。需要生成包含唯一配对ID的表格，仅保留正确的相邻连接（含单个节点的自身配对）。

目标表格 #

期望生成的配对表格如下：

实现建议 #

方案1：Python Pandas 处理 #

通过分组排序+生成相邻配对的方式实现，代码示例如下：

import pandas as pd

# 加载原始数据
df = pd.DataFrame({
    'ID': [22,22,22,33,33,44,55,55],
    'Order': [1,2,3,1,2,1,1,2],
    '名称（Name）': ['AA','BB','CC','AA','GG','AA','AA','BB']
})

pair_list = []
current_pair_id = 1

# 按ID分组处理每个序列
for _, group in df.groupby('ID'):
    # 确保组内按Order排序
    sorted_group = group.sort_values('Order')['名称（Name）'].tolist()
    group_length = len(sorted_group)
    
    if group_length == 1:
        # 单个节点生成自身配对
        pair_list.extend([
            {'ID': current_pair_id, '名称（Name）': sorted_group[0]},
            {'ID': current_pair_id, '名称（Name）': sorted_group[0]}
        ])
        current_pair_id += 1
    else:
        # 生成相邻节点的配对
        for i in range(group_length - 1):
            pair_list.extend([
                {'ID': current_pair_id, '名称（Name）': sorted_group[i]},
                {'ID': current_pair_id, '名称（Name）': sorted_group[i+1]}
            ])
            current_pair_id += 1

# 转换为目标格式的DataFrame
result_df = pd.DataFrame(pair_list)
print(result_df)

逻辑说明：

• 按ID分组保证每个序列独立处理
• 组内排序确保Order顺序正确
• 单个节点生成自身配对，多个节点仅生成相邻Order的节点配对
• 自动为每个配对分配唯一ID

方案2：SQL 处理 #

通过窗口函数LAG()获取相邻节点，再拆分配对生成目标表格，代码示例如下：

WITH ordered_nodes AS (
    SELECT 
        ID,
        "Order",
        "名称（Name）",
        LAG("名称（Name）") OVER (PARTITION BY ID ORDER BY "Order") AS prev_name
    FROM your_table_name
),
valid_pairs AS (
    -- 处理有相邻节点的配对
    SELECT 
        ROW_NUMBER() OVER (ORDER BY ID, "Order") AS pair_id,
        prev_name AS node,
        "名称（Name）" AS next_node
    FROM ordered_nodes
    WHERE prev_name IS NOT NULL
    
    UNION ALL
    
    -- 处理单个节点的自身配对
    SELECT 
        (SELECT MAX(pair_id) FROM valid_pairs) + ROW_NUMBER() OVER (ORDER BY ID) AS pair_id,
        "名称（Name）" AS node,
        "名称（Name）" AS next_node
    FROM your_table_name
    WHERE ID IN (
        SELECT ID FROM your_table_name GROUP BY ID HAVING COUNT(*) = 1
    )
)
-- 拆分配对为两行，生成目标格式
SELECT pair_id AS ID, node AS "名称（Name）" FROM valid_pairs
UNION ALL
SELECT pair_id AS ID, next_node AS "名称（Name）" FROM valid_pairs
ORDER BY ID;

逻辑说明：

• LAG()函数获取同ID内前一个Order的节点，筛选出有效相邻配对
• 单独处理仅含单个节点的ID，生成自身配对
• 最后将每个配对拆分为两行，得到目标表格结构

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