如何用PYTHON编写分层查询

Question

给定输入如下：

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


Employee     Cycle      LEVEL Path
   101         A         101/10/1
   102         B         102/11/1
   10          C         10/1
   11          D         11/1
   1           E         1

如果能用python“pandas”库来解决这个问题，那就太好了。我不确定用熊猫能不能实现。其他解决办法也受到欢迎。在

Answer 1

dictionary带有EMPLOYEE_ID和MANAGER_ID：

dct = dict(zip(df.EMPLOYEE_ID.values, df.MANAGER_ID.values))

function创建层次结构字符串

^{pr2}$

apply

df['LEVEL'] = df.EMPLOYEE_ID.apply(heirarchy)

# Result

   EMPLOYEE_ID NAME MANAGER_ID     LEVEL
0          101    A         10  101/10/1
1          102    B         11  102/11/1
2           10    C          1      10/1
3           11    D          1      11/1
4            1    E       null         1

Answer 2

您可以创建一个将子项映射到父项的字典。在

然后使用pd.Series.apply通过while循环构造路径字符串。在

注意：我假设null实际上是NaN，这对数值列更有意义。在

child_parent_dict = df.set_index('EMPLOYEE_ID')['MANAGER_ID'].to_dict()

def get_all_parents(child):
    """Get all parents from hierarchy structure"""
    while child == child:
        child = child_parent_dict[child]
        if child == child:
            yield int(child)

def get_path(x):
    """Calculate path and construct string"""
    return '/'.join(list(map(str, [x]+list(get_all_parents(x)))))

df['Path'] = df['EMPLOYEE_ID'].apply(get_path)

print(df)

#    EMPLOYEE_ID NAME  MANAGER_ID      Path
# 0          101    A          10  101/10/1
# 1          102    B          11  102/11/1
# 2           10    C           1      10/1
# 3           11    D           1      11/1
# 4            1    E         NaN         1