求编写函数实现Pandas分类父子关系的层级结构化汇总

首先定义原始数据：

import pandas as pd

df = pd.DataFrame(data={
    'SON' : ['Category1','Category2','Category3','Category4','Category5','Category6','Category7'],
    'PARENT' : [None,'Category1','Category1','Category2','Category4','Category4','Category6']
})

核心思路 #

先定位所有叶子节点（没有子分类的节点），再为每个叶子节点回溯完整的层级路径，最后将路径拆分为对应列，补全空缺位置。

实现步骤 #

1. 构建映射与识别叶子节点

   • 建立子节点到父节点的快速映射字典
   • 叶子节点是那些从未作为父节点出现的分类

   # 子→父映射字典
   child_to_parent = df.set_index('SON')['PARENT'].to_dict()
   
   # 找出所有作为父节点的分类
   parent_categories = df[df['PARENT'].notna()]['PARENT'].unique()
   
   # 筛选叶子节点
   leaf_nodes = [son for son in df['SON'] if son not in parent_categories]
   

2. 回溯完整层级路径
   对每个叶子节点，向上遍历父节点直到根节点，反转路径得到从大到小的层级顺序：

   def get_full_path(node):
       path = []
       current = node
       while current is not None:
           path.append(current)
           current = child_to_parent.get(current)
       # 反转路径，得到根→叶子的层级顺序
       return path[::-1]
   
   # 生成所有叶子节点的完整路径
   paths = [get_full_path(leaf) for leaf in leaf_nodes]
   

3. 转换为目标DataFrame
   统一所有路径的长度，空缺位置用[]填充，再命名对应列：

   # 确定最长路径的深度（即列数）
   max_depth = max(len(p) for p in paths)
   
   # 生成层级列名
   column_names = []
   for i in range(max_depth):
       if i == 0:
           column_names.append('Biggest_category')
       else:
           column_names.append(f'{i+1}th_Biggest_category')
   
   # 补全路径长度
   padded_paths = [path + [[]]*(max_depth - len(path)) for path in paths]
   
   # 生成最终DataFrame
   result_df = pd.DataFrame(padded_paths, columns=column_names)
   

输出结果 #

运行后result_df与期望输出完全一致：

Biggest_category Second_Biggest_category Third_Biggest_category Fourth_Biggest_category Fifth_Biggest_category
0        Category1               Category2              Category4               Category5                      []
1        Category1               Category2              Category4               Category6               Category7
2        Category1               Category3                     []                      []                      []

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