Pandas re-arange flat hierarchy from bottom up to top down

Question

I am stuck with a challenge to re-arange a flat unbalanced hierarchy that is build bottom up, i.e. mapping a child element to parent and the parent's parent and so on, to a top down structure, i.e. starting from root and populating the structure downwards. Because the tree is unbalanced some end with a lower hierarchy level than others.

Example:

Source:

Child|Parent+0|Parent+1|Parent+2|Parent+3|Parent+4
Julia|Peter|Alice|Paul|Sara|Bianca  
Chris|Jen|Bob|Fred|Bianca|NaN  
Ben|John|Bianca|NaN|NaN

Target:

Parent-0|Parent-1|Parent-2|Parent-3|Parent-4|Child  
Bianca|Sara|Paul|Alice|Peter|Julia  
Bianca|Fred|Bob|Jen|NaN|Chris  
Bianca|John|NaN|NaN|NaN|Ben

I've tried different ideas but so far had no luck. Appreciate your help or ideas.

I tried a for loop in combination with a df.apply(lambda x:) and helper variables counting the number of parent columns and populated parent columns. — Tom67, Oct 10 '19 at 09:27

score 0 · Accepted Answer · answered Oct 09 '19 at 19:33

set_index and flip the values. Then make use of the justify function that cs95 modified from Divakar.

df = df.set_index('Child').loc[:, ::-1]

pd.DataFrame(justify(df.to_numpy(), invalid_val=np.NaN), 
             index=df.index, 
             columns=[f'Parent-{i}' for i in range(0, df.shape[1])])

      Parent-0 Parent-1 Parent-2 Parent-3 Parent-4
Child                                             
Julia   Bianca     Sara     Paul    Alice    Peter
Chris   Bianca     Fred      Bob      Jen      NaN
Ben     Bianca     John      NaN      NaN      NaN

Pandas re-arange flat hierarchy from bottom up to top down

1 Answers1