如何在Python中编写层级查询？优先采用Pandas库实现

问题描述 #

我们需要处理一组员工数据，生成包含员工基本信息、层级（LEVEL，即员工所在的组织层级深度）以及路径（Path，从员工ID向上到顶层老板的ID拼接字符串）的结果集。优先使用Python的Pandas库实现，若有其他可行方案也可补充。

输入数据 #

原始员工数据如下：

EMPLOYEE_ID NAME MANAGER_ID
101 A 10
102 B 11
10 C 1
11 D 1
1 E null

期望输出 #

最终需要生成的结果集（补充了原期望输出中缺失的层级数值）：

Employee ID NAME LEVEL Path
101 A 3 101/10/1
102 B 3 102/11/1
10 C 2 10/1
11 D 2 11/1
1 E 1 1

Pandas解决方案 #

我们可以通过迭代遍历每个员工的上级关系，手动构建路径和层级。具体代码如下：

import pandas as pd

# 1. 构造原始数据的DataFrame，处理空值
data = [
    [101, 'A', 10],
    [102, 'B', 11],
    [10, 'C', 1],
    [11, 'D', 1],
    [1, 'E', None]
]
df = pd.DataFrame(data, columns=['EMPLOYEE_ID', 'NAME', 'MANAGER_ID'])

# 2. 创建员工ID到上级ID的映射字典，方便快速查找
emp_manager_map = df.set_index('EMPLOYEE_ID')['MANAGER_ID'].to_dict()

# 3. 定义函数，获取单个员工的路径和层级
def get_emp_path_and_level(emp_id):
    path_components = [str(emp_id)]
    current_manager_id = emp_manager_map.get(emp_id)
    
    # 循环向上查找上级，直到没有上级（Manager_ID为None/NaN）
    while current_manager_id is not None and pd.notna(current_manager_id):
        path_components.append(str(current_manager_id))
        current_manager_id = emp_manager_map.get(current_manager_id)
    
    # 路径是从员工到顶层的ID拼接，层级是路径的长度
    return '/'.join(path_components), len(path_components)

# 4. 对每个员工应用函数，生成新列
df[['Path', 'LEVEL']] = df['EMPLOYEE_ID'].apply(
    lambda x: pd.Series(get_emp_path_and_level(x))
)

# 5. 调整列顺序，匹配期望输出格式
result_df = df[['EMPLOYEE_ID', 'NAME', 'LEVEL', 'Path']].rename(columns={'EMPLOYEE_ID': 'Employee ID'})

# 打印结果
print(result_df.to_string(index=False))

代码说明 #

• 首先将原始数据转换为DataFrame，并处理MANAGER_ID的空值（用None表示顶层老板）。
• 构建映射字典是为了避免每次查找上级都遍历DataFrame，提升效率。
• 自定义函数get_emp_path_and_level通过循环向上追溯每个员工的上级，收集路径组件，最终拼接成路径字符串，层级就是路径组件的数量。
• 最后调整列顺序并输出，得到符合要求的结果。

其他可行方案 #

如果数据量极大，迭代方式效率不足，可以考虑使用**递归CTE（Common Table Expression）**通过SQL实现（比如用SQLite或PostgreSQL），示例SQL语句如下：

WITH RECURSIVE emp_hierarchy AS (
    SELECT 
        EMPLOYEE_ID,
        NAME,
        MANAGER_ID,
        1 AS LEVEL,
        CAST(EMPLOYEE_ID AS TEXT) AS Path
    FROM employees
    WHERE MANAGER_ID IS NULL
    UNION ALL
    SELECT 
        e.EMPLOYEE_ID,
        e.NAME,
        e.MANAGER_ID,
        eh.LEVEL + 1 AS LEVEL,
        CAST(e.EMPLOYEE_ID AS TEXT) || '/' || eh.Path AS Path
    FROM employees e
    JOIN emp_hierarchy eh ON e.MANAGER_ID = eh.EMPLOYEE_ID
)
SELECT EMPLOYEE_ID AS "Employee ID", NAME, LEVEL, Path FROM emp_hierarchy;

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