Add Hierarchic level based on reporting

user_14078956 0
Viewed 79

data df

child parent
b     a
c     a
d     b
e     c
f     c
g     f

output:

child   parent  level
b       a       1
c       a       1
d       b       2
e       c       2
f       c       2
g       f       3

based on this parent child reporting, 'a' is the main parent as it is not reporting to anyone. 'b' and 'c' are reporting to 'a' so they are level = 1. 'd' and 'e' are reporting to level 1 (b,c) so they are level =2. 'g' is reporting to 'f'(which is level 2), so level = 3 for 'g'. please let me know how to achieve this

i was trying this below code but its not working

df['Level'] = np.where(df['parent'] == 'a',"level 1",np.nan)
dfm1 = pd.Series(np.where(df['Level'] == 'level 1', df['parent'],None))
df.loc[df['parent'].isin(dfm1),'Level'] = "level 2"
pandas networkx python
2 Answers

Here is a way using networkx where we can find no of ancestors and get length of the same

import networkx as nx

G = nx.from_pandas_edgelist(df,"parent","child",create_using=nx.DiGraph())
f = lambda x: len(nx.ancestors(G,x))
df['level'] = df['child'].map(f)

print(df)

  child parent  level
0     b      a      1
1     c      a      1
2     d      b      2
3     e      c      2
4     f      c      2
5     g      f      3
user_98406370

Here is a solution from first principles:

# We will build the tree of relationships, using a helper node class
class Node:
    def __init__(self, value, parent=None, level=0):
        self.value = value
        self.parent = parent
        self.level = level
        self.children = []
    
    def set_child(self, child):
        child.level = self.level + 1
        self.children.append(child)

# Helper function to insert nodes
def insert(node, new_node):
    if new_node.parent == node.value:
        # if the new node is a child, insert it
        node.set_child(new_node)
    else:
        # otherwise, iterate over the children until you find its parent
        if node.children:
            for child in node.children:
                insert(child, new_node)

# gather the level information for the tree
def node_print(node, values=[]):
    if node.parent:
        values.append((node.value, node.parent, node.level))
    for child in node.children:
        values = node_print(child, values=values)
    return values

# Now get the data and build the tree
data = """b     a
c     a
d     b
e     c
f     c
g     f"""


rows = [y.split() for y in data.split("\n")]

for index, (child, parent) in enumerate(rows):
    if index == 0:
        node = Node(value=parent)
    
    child_node = Node(value=child, parent=parent)
    insert(node, child_node)

output = pd.DataFrame(data=node_print(node, values=[]), columns=['child', 'parent', 'level']).sort_values(by='level')

print(output)

  child parent  level
0     b      a      1
2     c      a      1
1     d      b      2
3     e      c      2
4     f      c      2
5     g      f      3
user_86710530
Related