How to normalise a deeply nested JSON-like object with pandas

October 24, 2024

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.json_normalize.html
each parameter may take a nested list of lists to represent the necessary hierachy

Example:

data = [
    {
        "state": "Florida",
        "shortname": "FL",
        "info": {"governor": "Rick Scott"},
        "counties": [
            {"name": "Dade", "population": 12345},
            {"name": "Broward", "population": 40000},
            {"name": "Palm Beach", "population": 60000},
        ],
    }
]
result = pd.json_normalize(
    data, "counties", ["state", "shortname", ["info", "governor"]]
)

Result:

         name  population    state shortname info.governor
0        Dade       12345   Florida    FL    Rick Scott
1     Broward       40000   Florida    FL    Rick Scott
2  Palm Beach       60000   Florida    FL    Rick Scott