Flattened data frame with hierarchical structure to nested dictionary

Question

I have a Pandas data frame that looks like this:

Parent_id    Child_id Descr
1            2        Tom
1            3        Mark
1            4        Tony
2            5        Eddy
2            6        Lisa
3            7        Anna
3            8        Maria
7            9        Ali

Desired Output is a nested list-dict combo format like this (I need to pass it as JSON to a treeview builder). I have no clue on how to create this because I don't know the depth of the structure beforehand.

What I know is that id 1 is the parent of all other ids and I don't need to include it. The data frame rows are not necessarily orded by hierarchy level.

[{ id: 2,
   descr: Tom,
   nodes: [{id: 5,
            descr: Eddy},
           {id: 6,
            descr: Lisa}
          ]
 },
 { id: 3,
   descr: Mark,
   nodes: [{id: 7,
            descr: Anna,
            nodes: [{id: 9,
                     descr: Ali
                    }]
           },
           {id: 8,
            descr: Maria}
           ]
 { id: 4,
   descr: Tony}
]

Thanks for any help in advance!

Answer 1

Try:

def tree(df, parent=1):
    out = []
    for _, row in df[df.Parent_id == parent].iterrows():
        out.append(
            {
                "id": row["Child_id"],
                "descr": row["Descr"],
                "nodes": tree(df, parent=row["Child_id"]),
            }
        )
        if not out[-1]["nodes"]:
            del out[-1]["nodes"]
    return out


print(tree(df))

Prints:

[
    {
        "id": 2,
        "descr": "Tom",
        "nodes": [{"id": 5, "descr": "Eddy"}, {"id": 6, "descr": "Lisa"}],
    },
    {
        "id": 3,
        "descr": "Mark",
        "nodes": [
            {"id": 7, "descr": "Anna", "nodes": [{"id": 9, "descr": "Ali"}]},
            {"id": 8, "descr": "Maria"},
        ],
    },
    {"id": 4, "descr": "Tony"},
]