How can Pandas flatten a dict with dynamic keys

HapiDaze 0
Viewed 70

Supposing a dict looked like this:

example_1 = {
    'products': [
        {
            'p_code': 'AP001',
            'description': 'Product 1',
            'dimensions': {
                'height': 20,
                'width': 30
            }
        },
        {
            'p_code': 'AP002',
            'description': 'Product 2',
            'dimensions': {
                'height': 15,
                'width': 25
            }
        }
    ]
}

It would be fairly trivial to flatten it like this:

df_ex_1 = pd.json_normalize(example_1, record_path=['products'])

Result:

p_code  description dimensions.height   dimensions.width
0   AP001   Product 1              20                 30
1   AP002   Product 2              15                 25

But what if the dict looks like this:

example_2 = {
    'AP001': {
        'description': 'Product 1',
        'dimensions': {
            'height': 20,
            'width': 30
        }        
    },
    'AP002': {
        'description': 'Product 2',
        'dimensions': {
            'height': 15,
            'width': 25
        }        
    }
}

Is there a way to specify that record_path needs to drop down into each root-level key that it finds at runtime?

I'm not wedded to using json_normalize, so any other approach, that efficiently achieves the same result, would be fine.

I've tried:

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

Result:

      description                    dimensions
AP001   Product 1   {'height': 20, 'width': 30}
AP002   Product 2   {'height': 15, 'width': 25}

but, this doesn't flatten the data nested in dimensions.

If necessary, I can write code to make a transformed version of the input dict, before I pull it into Pandas, but I suspect Pandas can already do what I want, and I just don't know how.

Any ideas please?

dataframe pandas python-3.x python
2 Answers

Using pandas.concat and list comprehension, you can combine each DataFrame object created by pandas.json_normalize with something like this:

df = pd.concat([pd.json_normalize(example_2[product]) for product in example_2], keys=example_2.keys())

The index of this method is not exactly what you expected, you can do

df.index = example_2.keys()

to obtain what you want.

Bijay Regmi0

Solution presented by Bijay Regmi is the best, however, you can also do this:

import pandas as pd

dict1 = {}
for key, value in example_2.items():
    if 'index' not in dict1.keys():
        dict1['index'] = []
    dict1['index'].append(key)
    for key2, value2 in value.items():
        if isinstance(value2, dict):
            for key3, value3 in value2.items():
                if key2 + '.' + key3 not in dict1.keys():
                    dict1[key2 + '.' + key3] = []
                dict1[key2 + '.' + key3].append(value3)
        else:
            if key2 not in dict1.keys():
                dict1[key2] = []
            dict1[key2].append(value2)

            
df = pd.DataFrame.from_dict(dict1)
df

   index description  dimensions.height  dimensions.width
0  AP001   Product 1                 20                30
1  AP002   Product 2                 15                25
Anoushiravan R0
Related