python -- pandas dataframe to nested json with hierarchy level

Question

To be able to generate a checkboxes, I need to convert pandas DataFrame to a JSON format.

First, I have a pandas Dataframe:

cast	title	type
Daniel Craig	Sky Fall	Movie
Ahmed Bakare	Bad Habits	Music Video
Leonardo Dicaprio	Titanic	Movie
Judi Dench	Sky Fall	Movie
Kate Winslet	Titanic	Movie
Emily Ratajkowski	Blurred Lines	Music Video
Elle Evans	Blurred Lines	Music Video

I would like to convert it same like the format below:

{
 "Movie": {
    "label": "Movie",
    "children": {
      "Sky Fall": {
        "label": "Sky Fall",
        "children": {
          "Daniel Craig": {
            "label": "Daniel Craig"
          },
          "Judi Dench": {
            "label": "Judi Dench"
          }
        }
      },
      "Titanic": {
        "label": "Titanic",
        "children": {
          "Leonardo Dicaprio": {
            "label": "Leonardo Dicaprio"
          },
          "Kate Winslet": {
            "label": "Kate Winslet"
          }
        }
      }
    }
  },
 "Music Video": {
    "label": "Music Video",
    "children": {
      "Bad Habits": {
        "label": "Bad Habits",
        "children": {
          "Ahmed Bakare": {
            "label": "Ahmed Bakare"
          }
        }
      },
      "Blurred Lines": {
        "label": "Blurred Lines",
        "children": {
          "Emily Ratajkowski": {
            "label": "Emily Ratajkowski"
          },
          "Elle Evans": {
            "label": "Elle Evans"
          }
        }
      }
    }
  }
};  

My current approach is:

menu = []
groupDict = df.groupby('type').apply(lambda g: g.drop('type', axis=1).to_dict(orient='records')).to_dict()
for key, value in groupDict.items():
    menu.append(dict(type=key,children=str(value)))

However, the result doest not come as I expected.

[{'type': 'Movie',
  'children': "[{'cast': 'Daniel Craig', 'title': 'Sky Fall'}, {'cast': 'Leonardo Dicaprio', 'title': 'Titanic'}, {'cast': 'Judi Dench', 'title': 'Sky Fall'}, {'cast': 'Kate Winslet', 'title': 'Titanic'}]"},
 {'type': 'Music Video',
  'children': "[{'cast': 'Ahmed Bakare', 'title': 'Bad Habits'}, {'cast': 'Emily Ratajkowski', 'title': 'Blurred Lines'}, {'cast': 'Elle Evans', 'title': 'Blurred Lines'}]"}]

Answer 1

I made slight modification from this post: https://stackoverflow.com/a/43765794/19042045

It takes csv input so you can easily convert from dataframe to csv first

1. test.csv

type,title ,cast
Movie,Sky Fall ,Daniel Craig
Music Video,Bad Habits ,Ahmed Bakare
Movie,Titanic ,Leonardo Dicaprio
Movie,Sky Fall ,Judi Dench
Movie,Titanic ,Kate Winslet
Music Video,Blurred Lines ,Emily Ratajkowski
Music Video,Blurred Lines ,Elle Evans

2. test.py

import csv
from collections import defaultdict


def ctree():
    """ One of the python gems. Making possible to have dynamic tree structure.

    """
    return defaultdict(ctree)


def build_leaf(name, leaf):
    """ Recursive function to build desired custom tree structure

    """
    res = {"label": name}

    # add children node if the leaf actually has any children
    if len(leaf.keys()) > 0:
        res["children"] = {k:build_leaf(k, v) for k, v in leaf.items()}

    return res


def main():
    """ The main thread composed from two parts.

    First it's parsing the csv file and builds a tree hierarchy from it.
    Second it's recursively iterating over the tree and building custom
    json-like structure (via dict).

    And the last part is just printing the result.

    """
    tree = ctree()
    # NOTE: you need to have test.csv file as neighbor to this file
    with open('test.csv') as csvfile:
        reader = csv.reader(csvfile)
        for rid, row in enumerate(reader):

            # skipping first header row. remove this logic if your csv is
            # headerless
            if rid == 0:
                continue

            # usage of python magic to construct dynamic tree structure and
            # basically grouping csv values under their parents
            leaf = tree[row[0]]
            for cid in range(1, len(row)):
                leaf = leaf[row[cid]]

    # building a custom tree structure
    res = {}
    for name, leaf in tree.items():
        res[name] = build_leaf(name, leaf)

    # printing results into the terminal
    import json
    print(json.dumps(res, indent=4, sort_keys=True))


# so let's roll
main()

3. execute test

python test.py

4. result

{
    "Movie": {
        "children": {
            "Sky Fall ": {
                "children": {
                    "Daniel Craig ": {
                        "label": "Daniel Craig "
                    }, 
                    "Judi Dench ": {
                        "label": "Judi Dench "
                    }
                }, 
                "label": "Sky Fall "
            }, 
            "Titanic ": {
                "children": {
                    "Kate Winslet ": {
                        "label": "Kate Winslet "
                    }, 
                    "Leonardo Dicaprio ": {
                        "label": "Leonardo Dicaprio "
                    }
                }, 
                "label": "Titanic "
            }
        }, 
        "label": "Movie"
    }, 
    "Music Video": {
        "children": {
            "Bad Habits ": {
                "children": {
                    "Ahmed Bakare ": {
                        "label": "Ahmed Bakare "
                    }
                }, 
                "label": "Bad Habits "
            }, 
            "Blurred Lines ": {
                "children": {
                    "Elle Evans": {
                        "label": "Elle Evans"
                    }, 
                    "Emily Ratajkowski ": {
                        "label": "Emily Ratajkowski "
                    }
                }, 
                "label": "Blurred Lines "
            }
        }, 
        "label": "Music Video"
    }
}