Filter dataframe to get the leaves of tree structure

user_1276022 0
Viewed 46

I have a dataframe with columns for levels of hierarchy (similar to directory path). I am trying to keep only the records with the latest generation in the levels (leaves of the hierarchy tree). I tried couple ways with transform and groupby but unable to get the desired output

Code

import numpy as np
import pandas as pd

df = pd.DataFrame({'lvl1':['aa','aa','aa','aa','bb','bb','bb','bb','cc','aa'],
                   'lvl2':[np.nan,'xx','xx','xx',np.nan,'yy','yy','zz',np.nan,'sa'],
                   'lvl3':[np.nan,np.nan,'ww','qq',np.nan,np.nan,'rr',np.nan,np.nan,'jj'],
                   'value':[12,4,7,22,76,0,18,47,10,2]})
result = pd.DataFrame({'lvl1':['aa','aa','bb','bb','cc','aa'],
                       'lvl2':['xx','xx','yy','zz',np.nan,'sa'],
                       'lvl3':['ww','qq','rr',np.nan,np.nan,'jj'],
                       'value':[7,22,18,47,10,2]})

Figure

figure

Appreciate your help

pandas dataframe python
1 Answers

If need filter rows by maximum unique values per rows and for each category not maximum only last row use:

s = df[["lvl1", "lvl2", "lvl3"]].nunique(axis=1)
#if need test number of non missing values use count
#s = df[["lvl1", "lvl2", "lvl3"]].count(axis=1)

df = df[~s.duplicated(keep='last') | s.eq(s.max())]
print (df)
  lvl1 lvl2 lvl3  value
2   aa   xx   ww      7
3   aa   xx   qq     22
6   bb   yy   rr     18
7   bb   zz  NaN     47
8   cc  NaN  NaN     10
9   aa   sa   jj      2
user_29010020
Related