Convert an uneven nested dictioary into tabular form-CodePudding

res = {'Head': {'Ide': 'GLE', 'ID': '7b', 'Source': 'CARS', 'Target': 'TULUM', 'Country': 'GL'},
        'Load': {'Stat': {'Code': '21', 'Reason': 'invalid'}, 'SrcFilePath': '/path.xls'}}

res is the nested dictionary that needs to be converted into a tabular form. With the following columns and respective values:

Ide ID Source Target Country Code Reason SrcFilePath

Code:

for col,data in res.items():
    final_data = dict(data.items())
    df = pd.DataFrame(final_data)
    print(df)

Error:

ValueError: If using all scalar values, you must pass an index

CodePudding user response：

You can try:

pd.DataFrame.from_dict(res, orient='index')

CodePudding user response：

You could try using:

pd.json_normalize(res)

Although the output can be a bit "ugly", but it actually works.

CodePudding user response：

I assume that res isn't the only record and there's data like:

data = [
    {'Head': {'Ide': 'GLE', 'ID': '7b', 'Source': 'CARS', 'Target': 'TULUM', 'Country': 'GL'}, 'Load': {'Stat': {'Code': '21', 'Reason': 'invalid'}, 'SrcFilePath': '/path.xls'}}
    , {'Head': {'Ide': 'ABC', 'ID': '8b', 'Source': 'CARS', 'Target': 'TULUM', 'Country': 'AB'}, 'Load': {'Stat': {'Code': '21', 'Reason': 'invalid'}, 'SrcFilePath': '/path.xls'}}
    , {'Head': {'Ide': 'EFG', 'ID': '9b', 'Source': 'CARS', 'Target': 'TULUM', 'Country': 'EF'}, 'Load': {'Stat': {'Code': '21', 'Reason': 'invalid'}, 'SrcFilePath': '/path.xls'}}
]

So we have to write a procedure to flatten records and apply it by map to the data before transforming records into a frame:

def flatten_dict(d:dict) -> dict:
    res = {}
    for k, v in d.items():
        if type(v) is dict:
            res.update(flatten_dict(v))
        else:
            res[k] = v  
    return res

output = pd.DataFrame(map(flatten_dict, data))

The output:

   Ide  ID Source Target Country Code   Reason SrcFilePath
0  GLE  7b   CARS  TULUM      GL   21  invalid   /path.xls
1  ABC  8b   CARS  TULUM      AB   21  invalid   /path.xls
2  EFG  9b   CARS  TULUM      EF   21  invalid   /path.xls