Convert delimited string and value into hierarchical JSON with Python

Question

I have data in the following format:

[['Director', 9010],
['Director - Product Manager', 9894],
['Director - Product Manager - Project Manager', 9080],
['Director - Product Manager - Project Manager - Staff', 5090],
['Director - Product Manager - Project Manager - Staff 2', 5087],
['Director - Product Manager - Project Manager 2', 9099],...]

and want an output that'll look something like this:

{
    'title': 'Director',
    'id': 9010,
    'children': [
        {
            'title': 'Product Manager',
            'id': 9894,
            'children': [
                {
                    'title': 'Project Manager',
                    'id': 9080,
                    'children': [
                        ...
                    ]
                },{
                    'title': 'Project Manager 2',
                    'id': 9099,
                    'children': [
                        ...
                    ]
                }],
                ...
            ]
        },
        ...
    ]
}

I've been toying around with dictionaries, but struggling to match the ids with the titles. Any suggestions are appreciated.

Show us what you tried please. – FcoRodr Nov 07 '17 at 09:30 — FcoRodr, Nov 07 '17 at 09:30

score 3 · Accepted Answer · answered Nov 07 '17 at 10:32

An efficient way to do this is to make the outermost layer a list rather than a dict. As we iterate over each title in the string of titles we look for a dict with that title in the current list. If the title doesn't exist in the current list then we need to create a new dict. If it does exist, then we make the children list of that dict the new current list and proceed to look for the next title.

I've also written a recursive prune function which removes all empty children lists, once we've finished entering all the data, in case you want to do that.

import json

lst = [
    ['Director', 9010],
    ['Director - Product Manager', 9894],
    ['Director - Product Manager - Project Manager', 9080],
    ['Director - Product Manager - Project Manager - Staff', 5090],
    ['Director - Product Manager - Project Manager - Staff 2', 5087],
    ['Director - Product Manager - Project Manager 2', 9099],
]

# Search for a matching name in the current list.
# If it doesn't exist, create it.
def insert(lst, name, idnum):
    for d in lst:
        if d['title'] == name:
            break
    else:
        d = {'title': name, 'id': idnum, 'children': []}
        lst.append(d)
    return d['children']

# Remove empty child lists
def prune(lst):
    for d in lst:
        if d['children']:
            prune(d['children'])
        else:
            del d['children']

# Insert the data into the master list
master = []
for names, idnum in lst:
    lst = master
    for name in [s.strip() for s in names.split(' - ')]:
        lst = insert(lst, name, idnum)

prune(master)

# Get the top level dict from the master list
data = master[0]
print(json.dumps(data, indent=4))

output

{
    "title": "Director",
    "id": 9010,
    "children": [
        {
            "title": "Product Manager",
            "id": 9894,
            "children": [
                {
                    "title": "Project Manager",
                    "id": 9080,
                    "children": [
                        {
                            "title": "Staff",
                            "id": 5090
                        },
                        {
                            "title": "Staff 2",
                            "id": 5087
                        }
                    ]
                },
                {
                    "title": "Project Manager 2",
                    "id": 9099
                }
            ]
        }
    ]
}

score 0 · Answer 2 · answered Nov 07 '17 at 14:35

With d as your input, iterate through your input list with.

Since there are two elements in every sublists, hold the position and id in the iteration variable as p and id

Example, you are processing list ['Director - Product Manager - Project Manager - Staff', 5090],

To get the title of each position, you can split your the position separated by - and strip the leading and trailing white spaces. Eg.,

>>> d[3][0]
'Director - Product Manager - Project Manager - Staff'
>>> map(str.strip,d[3][0].split('-'))
['Director', 'Product Manager', 'Project Manager', 'Staff']

The output dict, along with the immediately previous position of Staff is passed to the recursive search method and it fetches the all matches of the finding value, i.e. Project Manager and returns a list. Fetch the last match.

>>> recursive_search([data,],'Product Manager')[-1]
{'children': [{'children': [{'id': 5090, 'title': 'Staff'}, {'id': 5087, 'title': 'Staff 2'}], 'id': 9080, 'title': 'Project Manager'}, {'id': 9099, 'title': 'Project Manager 2'}], 'id': 9894, 'title': 'Product Manager'}

You need to append the new id to the children key of the above result!

Combining all the above,

d=[['Director', 9010],['Director - Product Manager', 9894],['Director - Product Manager - Project Manager', 9080],['Director - Product Manager - Project Manager - Staff', 5090],['Director - Product Manager - Project Manager - Staff 2', 5087],['Director - Product Manager - Project Manager 2', 9099],]

from pprint import pprint    
def recursive_search(items, key):
        found = []
        for item in items:
                if isinstance(item, list):
                        found += recursive_search(item, key)
                elif isinstance(item, dict):
                        if key in item.values():
                                found.append(item)
                        found += recursive_search(item.values(), key)
        return found
data={}
for p,id in d:
        desig = map(str.strip,p.split('-'))
        if len(desig)>1:
                res = recursive_search([data,],desig[-2])[-1]
                if res:
                        res['children']=res.get('children',[])
                        res['children'].append({'id':id,'title':desig[-1]})
        else:
                data = {'id':id,'title':p}

pprint.pprint(data)

Output:

{'children': [{'children': [{'children': [{'id': 5090, 'title': 'Staff'},
                                          {'id': 5087,
                                           'title': 'Staff 2'}],
                             'id': 9080,
                             'title': 'Project Manager'},
                            {'id': 9099, 'title': 'Project Manager 2'}],
               'id': 9894,
               'title': 'Product Manager'}],
 'id': 9010,
 'title': 'Director'}

Ref: The recursive_search function used here is the slightly modified version of searching through a dict mentioned here

Convert delimited string and value into hierarchical JSON with Python

2 Answers2