I have a table with 2 fields like so:
The IDs fields are both in int64 format. The first field represents accounts which could be controlled by a parent account which could be itself controlled by another parent account. For example: A controls Z who controls Y who controls X.
Not sure if this is relevant but for full context, it is possible for a parent to not actually use their own accounts for personal purpose and only use it to manage their children accounts. For example: Y falls under this scenario because it does not have a row relating it to itself like Z & A.
I am trying to create a field like in the table below that would easily highlight the structure of that hierarchy:
The one & two tiered ones are fairly simple with a concatenate, but I was wondering if there is an elegant way to populate the multi-tiered ones like “x/y/z/a”?
This does the trick. Advantageously it only relies on pandas and a small function.
def add_hierarchy(df, s, tier): df['Hierarchy'] = df['Account_ID'] for i in range(tier): next_tier = s.apply(lambda x: df[df['Account_ID'] == x].iloc) df['Hierarchy'] += '/' + next_tier['Account_ID'] s = next_tier['Parent_ID'] add_hierarchy(df, df['Parent_ID'], tier=3)
Probably you may want to add some sort of nice format afterwards. However, it returns the correct hierarchies
Account_ID Parent_ID Hierarchy 0 x y x/y/z/a 1 x1 y x1/y/z/a 2 x2 y x2/y/z/a 3 y z y/z/a/a 4 y1 z y1/z/a/a 5 y2 z y2/z/a/a 6 z a z/a/a/a 7 z2 a z2/a/a/a 8 a a a/a/a/a 9 b b b/b/b/b
(Please check your output. z has two parents given. I assumed z to have only one parent to be deterministic as is also indicated in your example output.)