How to produce hierarchical JSON tree from pandas dataframe in Python

Question

I want to write a function that returns a tree based on JSON data.

This is a pandas dataframe:

employee_id     designation     department      name      manager_emp_id
1                co-founder      co-founder      john         2
2                ceo             co-founder      rocky       
3                cto             tech            alfred       2
4                sde3            tech            bruce        3
5                sde1            tech            tony         4
6                cmo             marketing       steve        2
7                sde1            tech            bucky        3

This is an example of the tree I'm looking for:

       ceo 
    /   |  \
   /    |   \
 cto   cmo   coo 
  |      |     \
  |      |      \
 sde3  sales   operative executives
  |
  |
 sde1

Output format:

Desired Output

{
    employee_id : 2, 
    name : rocky, 
    reportees : [
                    {
                        employee_id : 3, 
                        name : alfred, 
                        reportees:[....]
                    }
                ]
}

Ajax1234 · Accepted Answer · 2021-04-04 18:26:16Z

1

You can use recursion:

d = [{'employee_id': 1, 'designation': 'co-founder', 'department': 'co-founder', 'name': 'john', 'manager_emp_id': 2}, {'employee_id': 2, 'designation': 'ceo', 'department': 'co-founder', 'name': 'rocky', 'manager_emp_id': ''}, {'employee_id': 3, 'designation': 'cto', 'department': 'tech', 'name': 'alfred', 'manager_emp_id': 2}, {'employee_id': 4, 'designation': 'sde3', 'department': 'tech', 'name': 'bruce', 'manager_emp_id': 3}, {'employee_id': 5, 'designation': 'sde1', 'department': 'tech', 'name': 'tony', 'manager_emp_id': 4}, {'employee_id': 6, 'designation': 'cmo', 'department': 'marketing', 'name': 'steve', 'manager_emp_id': 2}, {'employee_id': 7, 'designation': 'sde1', 'department': 'tech', 'name': 'bucky', 'manager_emp_id': 3}]
def get_tree(_id = ''):
   return [{'employee_id':i['employee_id'], 
            'name':i['name'], 
            **({} if not (rp:=get_tree(i['employee_id'])) else {'reportees':rp})} 
            for i in d if i['manager_emp_id'] == _id]

print(get_tree())

Output:

[{'employee_id': 2, 'name': 'rocky', 'reportees': [{'employee_id': 1, 'name': 'john'}, {'employee_id': 3, 'name': 'alfred', 'reportees': [{'employee_id': 4, 'name': 'bruce', 'reportees': [{'employee_id': 5, 'name': 'tony'}]}, {'employee_id': 7, 'name': 'bucky'}]}, {'employee_id': 6, 'name': 'steve'}]}]

edited Apr 4, 2021 at 18:26

answered Apr 3, 2021 at 17:52

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dharmveer Singh Over a year ago

Thanks working , but i want one update if there is no 'reportees' then 'reportees' key should not be added.

Dharmveer Singh Over a year ago

please elaborate the newly added lines, i can not understand the code

Ajax1234 Over a year ago

@DharmveerSingh The code uses a list comprehension to iterate over d, only including employees where the entity's manager_emp_id equals the id that is being searched for. An assignment expression (:=) is used to see if the employee has any reportees, and if so, a reportees key is added.

Collectives™ on Stack Overflow

How to produce hierarchical JSON tree from pandas dataframe in Python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related