如何在Pandas中基于两列构建组织层级关系列

问题描述 #

现有如下Pandas数据框，其中supervisory_org是子组织，Superior_org是对应的上级组织，记录了组织间的层级关系：

需要将这种层级关系展开为多列，最终得到如下格式的结果，每一列对应一个层级，从顶层到底层：

生成原始数据的代码：

import pandas as pd

df = pd.DataFrame({
    "supervisory_org": ["org_2","org_7","org_4","org_6","org_9","org_3","org_5","org_8"],
    "Superior_org": ["org_1","org_3","org_2","org_3","org_5","org_1","org_3","org_5"]
})

解决方案 #

步骤1：构建层级映射字典 #

创建字典存储子组织与上级组织的对应关系，方便快速回溯路径：

org_map = df.set_index('supervisory_org')['Superior_org'].to_dict()

步骤2：筛选叶子节点 #

叶子节点是没有下属的组织（即未出现在supervisory_org列中的组织），排除顶层节点org_1后，这些节点就是我们需要生成完整路径的起点：

all_orgs = set(org_map.keys()).union(set(org_map.values()))
leaf_nodes = [org for org in all_orgs if org not in org_map.keys() and org != 'org_1']

步骤3：回溯完整层级路径 #

对每个叶子节点，从自身开始向上遍历直到顶层组织，再反转路径得到从顶层到底层的顺序：

paths = []
for node in leaf_nodes:
    path = []
    current = node
    while current is not None:
        path.append(current)
        current = org_map.get(current)  # 顶层组织无上级，返回None终止循环
    path.reverse()
    paths.append(path)

步骤4：转换为层级列格式的DataFrame #

将路径列表转为DataFrame，给列命名为Level_1、Level_2等，缺失的层级自动填充为NaN：

result_df = pd.DataFrame(paths)
result_df.columns = [f'Level_{i+1}' for i in result_df.columns]

完整代码 #

import pandas as pd

# 生成原始数据
df = pd.DataFrame({
    "supervisory_org": ["org_2","org_7","org_4","org_6","org_9","org_3","org_5","org_8"],
    "Superior_org": ["org_1","org_3","org_2","org_3","org_5","org_1","org_3","org_5"]
})

# 构建层级映射
org_map = df.set_index('supervisory_org')['Superior_org'].to_dict()

# 筛选叶子节点（排除顶层节点org_1）
all_orgs = set(org_map.keys()).union(set(org_map.values()))
leaf_nodes = [org for org in all_orgs if org not in org_map.keys() and org != 'org_1']

# 回溯每个叶子节点的完整路径
paths = []
for node in leaf_nodes:
    path = []
    current = node
    while current is not None:
        path.append(current)
        current = org_map.get(current)
    path.reverse()
    paths.append(path)

# 转换为结果DataFrame
result_df = pd.DataFrame(paths)
result_df.columns = [f'Level_{i+1}' for i in result_df.columns]

print(result_df)

运行代码后即可得到符合预期的层级结构数据框。

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