'Convert nested JSON to Dataframe with columns referencing nested paths

I am trying to convert a nested JSON into a CSV file with three columns: the level 0 key, the branch, and the lowest level leaf.

For example, in the JSON below:

{
    "protein": {
        "meat": {
            "chicken": {},
            "beef": {},
            "pork": {}
        },
        "powder": {
            "^ISOPURE": {},
            "substitute": {}
        }
    },
    "carbs": {
        "_vegetables": {
            "veggies": {
                "lettuce": {},
                "carrots": {},
                "corn": {}
            }
        },
        "bread": {
            "white": {},
            "multigrain": {
                "whole wheat": {}
            },
            "other": {}
        }
    },
    "fat": {
        "healthy": {
            "avocado": {}
        },
        "unhealthy": {}
    }
}

I want to create an output like this (didn't include entire tree example just to get point across):

level 0 branch leaf
protein protein.meat chicken
protein protein.meat beef

I tried using json normalize but the actual file will not have paths that I can use to identify the nested fields as each dictionary is unique.

This returns the level 0 field but I need to have these as rows, not columns. Any help would be very much appreciated.

I created a function that pcan unnest the json based on key values like this:

import json

with open('path/to/json') as m:
    my_json = json.load(m)


def unnest_json(data):
    for key, value in data.items():
    print(str(key)+'.'+str(value))
    if isinstance(value, dict):
        unnest_json(value)
    elif isinstance(value, list):
        for val in value:
            if isinstance(val, str):
                pass
            elif isinstance(val, list):
                pass
            else:
                unnest_json(val)

unnest_json(my_json)


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source