python - 如何使用 Pandas 展平层次结构

Question

我有一个 6 级深的父/子层次结构 df，如下所示

Hierarchy Name,Hierarchy Node ID,Hierarchy Level,Hierarchy Node Desc,Node Higher

0,L1,1,1,Top level,#
1,L110,1072,2,Level 2,1
2,L1100,992,3,Level 3 A,1072
3,L1101,994,3,Level 3 B,1072
4,L1102,997,3,Level 3 C,1072
5,L1103,1013,4,Level 4 1,992
6,L1104,1014,5,Level 5 A,1013

我想将其扁平化为以下数据框，用于从底层到顶层的所有路径，例如

NodeID, NodeDesc, Lvl1, lvl1desc, lvl2, lvl2desc, ...lvl5, lvl5desc

1,Top Level, 1072, Level 2, 992, Level 3 A, 1013, Level 4 1, 1014, Level 5 A

我的方法如下，

第一步添加父子列

df2['Dictionery'] = list(zip(df2['Hierarchy Node ID'], df2['Node ID of the 
Highe']))
ancestry = df2['Dictionery']

step 2 获取所有关系的路径，我在网上找到这段代码打印出父/子树的完整路径

l=[]
parents = set()
children = {}
for c,p,cd in ancestry:
    parents.add(p)
    children[c] = p
# recursively determine parents until child has no parent
def ancestors(p):
    return (ancestors(children[p]) if p in children else []) + [p]

# for each child that has no children print the geneology
for k in (set(children.keys()) - parents):
   l.append('/'.join(ancestors(k)))

将路径添加到数据框

df3 = pd.DataFrame(l, columns = ['Path']) 

将路径列拆分为每个级别节点 id

new = df3["Path"].str.split("/", expand = True) 
df3["Level1"]= new[0] 
df3["Level2"]= new[1]
df3["Level3"]= new[2] 
df3["Level4"]= new[3] 
df3["Level5"]= new[4] 
df3["Level6"]= new[5] 
df3["Level7"]= new[6]
df3.fillna(value=0, inplace=True)   

给出以下 df3

path,  Level1,  Level2 , Level3, Level4, Level5, Level 6

0   #/1/1071/1249/1504/1505/1546, #, 1, 1071 , 1249, 1504, 1505 , 1546

1   #/1/1071/1249/1250/1269/1275, #, 1, 1071, 1249, 1250, 1269, 1275

然后我从原始 df 创建了一个字典来映射节点 ID 和描述，例如

{'Hierarchy Node Desc': {0: '0.0',
  1: 'Top Level',
  1072: 'Level 2',
  992: 'Level 3 A',
  994: 'Level 3 B',
  997: 'Level 3 C',
  1013: 'Level 4 1',
...}}

然后我使用字典为每个级别映射 df3 中描述的新列

例如

df['Level2desc'] = df['Level2'].map(dict)

这为我提供了我所追求的扁平层次结构，但要实现它似乎需要做很多工作，我希望有一种更简单/更有效的方法来做到这一点。

有什么建议可以用更简单的方式做到这一点吗？

Answer 1

我会首先确定所有终端项，即没有子项的项。然后对于每个终端项目，我会建立其 parent 的名单。代码可以是:

# find max hierarchy level
mx = df['Hierarchy Level'].max()

# identify terminal items
last = df[~df['Hierarchy Node ID'].isin(pd.to_numeric(df['Node Higher'],
                                                      errors='coerce'))]

# build a list for any terminal items with all of its parents
data = []
for _, row in last.iterrows():
    # initialize row
    hrow= {'lvl'+str(i+1)+ext: '' for i in range(mx) for ext in ['', 'desc']}
    # populate lvli and lvlidesc for the item and its parents
    for lvl in range(row['Hierarchy Level'], 0, -1):
        hrow['lvl'+str(lvl)] = row['Hierarchy Node ID']
        hrow['lvl'+str(lvl) + 'desc'] = row['Hierarchy Node Desc']
        # process parent until top level
        try:
            row = df[df['Hierarchy Node ID']==int(row['Node Higher'])].iloc[0]
        except:
            break
    data.append(hrow)

# build the resulting dataframe
df2 = pd.DataFrame(data)

根据您的样本数据，我得到:

   lvl1   lvl1desc  lvl2 lvl2desc  lvl3   lvl3desc  lvl4   lvl4desc  lvl5   lvl5desc
0     1  Top level  1072  Level 2   994  Level 3 B                                  
1     1  Top level  1072  Level 2   997  Level 3 C                                  
2     1  Top level  1072  Level 2   992  Level 3 A  1013  Level 4 1  1014  Level 5 A

如果你只想要最后一行，把last改成:

last = df[df['Hierarchy Level']==mx]