How to flatten a hierarchy with Pandas

user_10776453 0
Viewed 1917

I have a 6 level deep Parent / child hierarchy df which is as follows

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

I want to flatten this into the following dataframe for all the paths from the bottom level to the top e.g.

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

My method which works is as follows,

Step 1 add a column of parent and child

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

step 2 get the path of all relationships, I found this code online for printing out the full path of a parent /child tree

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)))

Add the path to a dataframe

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

Split the path column into each of the level node ids

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)

Which gives the following 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

I then created a dictionary from original df to map the node id and description e.g.

{'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',
...}}

I then map new columns for the description in df3 for each Level using the dictionary

e.g.

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

This gives me the flat hierarchy I am after but it just seems a lot of work to get to it and I expect there is a much simpler / more efficient way to do it.

Any advice to do this in simpler way?

pandas hierarchical-data python-3.x python
1 Answers

I would first identify all terminal items, meaning items with no child. Then for every terminal item, I would build the list of its parents. Code could be:

# 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)

With your sample data I get:

   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

If you only want the last line, it is enough to change last to:

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