Exploding deeply nested JSON Pandas JSON_Normalize

Viewed 41

I have the below code:

import pandas as pd

df = pd.json_normalize(indict, max_level=5)
n_dict = df.to_dict()

for key, value in n_dict.items():
    print(key, value)

...where indict looks like this:

[
    {
        "make": "Williams-Renault",
        "model": "FW14B", 
        "year": "1992", 
        "specs": 
            {
                "engine": "Renault V10", 
                "gearbox": "Six speed, semi-automatic", 
                "suspension": "Active", 
                "drivers": 
                    [
                        {
                            "name": "Nigel Mansell", 
                            "nationality": "British",
                            "championships": "0"
                        }, 
                        {
                            "name": "Riccardo Patrese", 
                            "nationality": "Italian",
                            "championships": "0"
                        }, 
                        {
                            "name": "Damon Hill", 
                            "nationality": "British",
                            "championships": "0"
                        }
                    ]
            }
    },
    {
        "make": "Mclaren-Honda",
        "model": "MP4/7", 
        "year": "1992", 
        "specs": 
            {
                "engine": "Honda V12", 
                "gearbox": "Six speed, semi-automatic", 
                "suspension": "Passive", 
                "drivers": 
                    [
                        {
                            "name": "Ayrton Senna", 
                            "nationality": "Brazilian",
                            "championships": "3"
                        }, 
                        {
                            "name": "Gerhard Berger", 
                            "nationality": "Austrian",
                            "championships": "0"
                        }, 
                        {
                            "name": "Mark Blundell", 
                            "nationality": "British",
                            "championships": "0"
                        }
                    ]
            }
    },
    {
        "make": "Benetton-Ford",
        "model": "B192", 
        "year": "1992", 
        "specs": 
            {
                "engine": "Ford V8", 
                "gearbox": "Six speed, manual", 
                "suspension": "Passive", 
                "drivers": 
                    [
                        {
                            "name": "Michael Schumacher", 
                            "nationality": "German",
                            "championships": "0"
                        }, 
                        {
                            "name": "Martin Brundle", 
                            "nationality": "British",
                            "championships": "0"
                        }, 
                        {
                            "name": "Alessandro Zanardi", 
                            "nationality": "Italian",
                            "championships": "0"
                        }
                    ]
            }
    },
]

...n_dict then looks like this:

{
    "make": 
        {
            0: "Williams-Renault", 
            1: "Mclaren-Honda", 
            2: "Benetton-Ford"
        }, 
    "model": 
        {
            0: "FW14B", 
            1: "MP4/7", 
            2: "B192"
        }, 
    "year": 
        {
            0: "1992", 
            1: "1992", 
            2: "1992"
        }, 
    "specs": 
        {
            "engine": 
                {
                    0: "Renault V10", 
                    1: "Honda V12", 
                    2: "Ford V8"
                }
            "gearbox": 
                {
                    0: "Six speed, semi-automatic", 
                    1: "Six speed, semi-automatic", 
                    2: "Six speed, manual"
                }
            "suspension": 
                {
                    0: "Active", 
                    1: "Passive", 
                    2: "Passive"
                }
            "drivers": 
                {
                0: 
                    [
                        {"name": "Nigel Mansell", "nationality": "British", "championships": "0"}, 
                        {"name": "Riccardo Patrese", "nationality": "Italian", "championships": "0"}, 
                        {"name": "Damon Hill", "nationality": "British", "championships": "0"}
                    ],
                1:  
                    [
                        {"name": "Ayrton Senna", "nationality": "Brazilian", "championships": "3"}, 
                        {"name": "Gerhard Berger", "nationality": "Austrian", "championships": "0"}, 
                        {"name": "Mark Blundell", "nationality": "British", "championships": "0"}
                    ],
                2: 
                    [
                        {"name": "Michael Schumacher", "nationality": "German", "championships": "0"}, 
                        {"name": "Martin Brundle", "nationality": "British", "championships": "0"}, 
                        {"name": "Alessandro Zanardi", "nationality": "Italian", "championships": "0"}
                    ]
                }
        }
}

...here you can see the drivers JSON has not been exploded/flattened. What am I missing to flatten this last bit as per the rest of the JSON?

I have tried:

df = pd.normalize(indict, record_path=['specs', 'drivers']

...not only does this give me a subset result only for drivers, but the indexing doesnt match the rest of the results from my original query, so I cannot join it back to my first dateframe...

1 Answers

Here is one way to flatten the drivers part of indict using Pandas explode method, after the normalization:

df = pd.json_normalize(indict, max_level=5)

df = df.explode("specs.drivers").reset_index(drop=True)

df = pd.concat([df, pd.DataFrame(df["specs.drivers"].to_list())], axis=1).drop(
    columns="specs.drivers"
)
print(df)
# Output
               make  model  year specs.engine              specs.gearbox  \
0  Williams-Renault  FW14B  1992  Renault V10  Six speed, semi-automatic   
1  Williams-Renault  FW14B  1992  Renault V10  Six speed, semi-automatic   
2  Williams-Renault  FW14B  1992  Renault V10  Six speed, semi-automatic   
3     Mclaren-Honda  MP4/7  1992    Honda V12  Six speed, semi-automatic   
4     Mclaren-Honda  MP4/7  1992    Honda V12  Six speed, semi-automatic   
5     Mclaren-Honda  MP4/7  1992    Honda V12  Six speed, semi-automatic   
6     Benetton-Ford   B192  1992      Ford V8          Six speed, manual   
7     Benetton-Ford   B192  1992      Ford V8          Six speed, manual   
8     Benetton-Ford   B192  1992      Ford V8          Six speed, manual   

  specs.suspension                name nationality championships  
0           Active       Nigel Mansell     British             0  
1           Active    Riccardo Patrese     Italian             0  
2           Active          Damon Hill     British             0  
3          Passive        Ayrton Senna   Brazilian             3  
4          Passive      Gerhard Berger    Austrian             0  
5          Passive       Mark Blundell     British             0  
6          Passive  Michael Schumacher      German             0  
7          Passive      Martin Brundle     British             0  
8          Passive  Alessandro Zanardi     Italian             0  
Related