How to convert multi level json file to csv using python?-CodePudding

I need to convert this json to pandas dataframe.

"""
{
    "col": [
        {
            "desc": {
                "cont": "Asia",
                "country": "China",
                "Sports": "TT"
            },
            "geo": {
                "col": [
                    [
                        [
                            34,
                            92
                        ],
                     ]
                   ],
                  "c_t": "matic"
            },
            "d_t": "fli"
        }
    ],
    "game": "outdoor"
}

"""

df_output:

col_desc_cont  col_desc_country  col_desc_Sports col_geo_col1  col_geo_co2  col_geo_c_t col_geo_d_t  game
Asia                 China         TT               34          92           matic      fli          outdoor

I want to loop every column value and column header, so that i can get the above result...

CodePudding user response：

That's not actually a valid json (but I fixed it below).

.json_normlaize() is what you are looking for. I'll let you split the geo.col column though.

data = """
{
    "col": [
        {
            "desc": {
                "cont": "Asia",
                "country": "China",
                "Sports": "TT"
            },
            "geo": {
                "col": [
                    [
                        [
                            34,
                            92
                        ]
                     ]
                   ],
                  "c_t": "matic"
            },
            "d_t": "fli"
        }
    ],
    "game": "outdoor"
}

"""

import pandas as pd
import json

jsonData = json.loads(data)
df = pd.json_normalize(jsonData, 
                       record_path=['col'], 
                       meta=['game'] )

Output:

print(df)
   d_t desc.cont desc.country desc.Sports       geo.col geo.c_t     game
0  fli      Asia        China          TT  [[[34, 92]]]   matic  outdoor