python的pandas模块将带有上下级关系表转为树状children结构

一、目标

使用python的pandas模块，利用groupby功能，将带有上下级关系表转为树状children结构，用于echarts的树状展示。

二、数据库

##（一）使用sqlite3的数据库，建表及插入字段语句如下：

DROP TABLE if exists t_上下级数据;
-- 创建层级数据表
CREATE TABLE t_上下级数据(
    `id` integer primary key autoincrement,
    `name` text,
    `p_id` integer,
    `value` float
);

INSERT INTO t_上下级数据(`id`,`name`,`p_id`,`value`) values(1,'b1',Null,1.1);
INSERT INTO t_上下级数据(`id`,`name`,`p_id`,`value`) values(2,'b2',1,1.2);
INSERT INTO t_上下级数据(`id`,`name`,`p_id`,`value`) values(3,'b3',1,1.3);
INSERT INTO t_上下级数据(`id`,`name`,`p_id`,`value`) values(4,'b4',2,1.4);
INSERT INTO t_上下级数据(`id`,`name`,`p_id`,`value`) values(5,'b5',3,1.5);
INSERT INTO t_上下级数据(`id`,`name`,`p_id`,`value`) values(6,'b6',4,1.6);

（二）使用pandas读取文件

import pandas as pd
import sqlite3
file = "层级数据.db"
con = sqlite3.connect(file)
sql_str = "select * from t_上下级数据 limit 6"
df = pd.read_sql(sql=sql_str,con=con

三、使用pandas

（一）python程序

df['a']=df.apply(lambda x:{'name':x['name'],'id':x['id'],'p_id':x['p_id']},axis=1)

out_1=[]
for k1,e_l in df[df['p_id'] == df_root.loc[0,'id']].iterrows():
#     print(e_l)
    out_2 = []
    for k2,e_2 in df[df['p_id'] == e_l['id']].iterrows():
#         print(e_2)
        out_3 = []
        for k3,e_3 in df[df['p_id'] == e_2['id']].iterrows():
#             print(e_3)
            out_4=[]
            for k4,e_4 in df[df['p_id'] == e_3['id']].iterrows():
#                 print(e_4)
                out_5=[]
                for k5,e_5 in df[df['p_id'] == e_4['id']].iterrows():
#                     print(e_5)
                    out_6=[]
                    for k6,e_6 in df[df['p_id'] == e_5['id']].iterrows():
#                         print(e_6)
                        out_6.append(e_6['a'])
    
                    if len(out_6)>0: e_5['a']['children']=out_6
                    out_5.append(e_5['a'])
            
                if len(out_5)>0:e_4['a']['children']=out_5
                out_4.append(e_4['a'])
                
            if len(out_4)>0:e_3['a']['children']=out_4
            out_3.append(e_3['a'])
            
#             print('out_3',out_3)
        if len(out_3)>0:e_2['a']['children']=out_3
        out_2.append(e_2['a'])
#         print('out_2',out_2)
    if len(out_2)>0:e_l['a']['children']=out_2
    out_1.append(e_l['a'])
#     print('out_1',out_1)
root_dict['children']=out_1
root_dict

(二）运行结果

{'name': 'b1',
 'id': 1,
 'p_id': 0,
 'children': [{'name': 'b2',
   'id': 2,
   'p_id': 1,
   'children': [{'name': 'b4',
     'id': 4,
     'p_id': 2,
     'children': [{'name': 'b6', 'id': 6, 'p_id': 4}]}]},
  {'name': 'b3',
   'id': 3,
   'p_id': 1,
   'children': [{'name': 'b5', 'id': 5, 'p_id': 3}]}]}