Pandas: nested json/dict to json_normalize DataFrame and json_normalize DataFrame to nested json/dict

Question

I have a large amount of JSON data and I want to perform some tasks. so I choose pandas for this.

I have a nested json like this :

json_data = [
    {
        "item": "Item1",
        "lowestPrice": {
            "price": 11.00,
            "currency": "EUR",
        },
    },
    {
        "item": "Item2",
        "lowestPrice": {
            "price": 12.00,
            "currency": "EUR",
        }
    },
    {
        "item": "Item3",
        "lowestPrice": {
            "price": 13.00,
            "currency": "EUR",
        }
    }
]

and i used json_normalize() to normalize nested json like:

df = pd.json_normalize(json_data, max_level=2)

        item  lowestPrice.price lowestPrice.currency
0  Item1               11.0                  EUR
1  Item2               12.0                  EUR
2  Item3               13.0                  EUR

#do something

now I need data back as a nested JSON or dict like:

json_data = [
    {
        "item": "Item1",
        "lowestPrice": {
            "price": 11.00,
            "currency": "EUR",
        },
        "annotatePrice": 15.00
    },
    {
        "item": "Item2",
        "lowestPrice": {
            "price": 12.00,
            "currency": "EUR",
        },
        "annotatePrice": 15.00
    },
    {
        "item": "Item3",
        "lowestPrice": {
            "price": 13.00,
            "currency": "EUR",
        },
        "annotatePrice": 15.00
    }
]

Does this answer your question? [Pandas convert Dataframe to Nested Json](https://stackoverflow.com/questions/23576869/pandas-convert-dataframe-to-nested-json). You can create the multi-index using `df.columns = df.columns.str.split('.', expand=True)` — Peter, Jan 24 '22 at 10:09
Can you upvote/accept my answer if it was helpful? If something can be improved do let know. — Kabilan Mohanraj, Jan 25 '22 at 09:46
hello @KabilanMohanraj, I upvoted your answer, and yes it is helpful somehow, but my data is very nested. so can you help me more with this??. — Nikunj, Jan 27 '22 at 04:47
I think new data will be outside the scope of this question. My current answer is based on the input and output that has been provided. So, can you accept my answer of it addressed this question and for the new data please create a new question? We can discuss there. — Kabilan Mohanraj, Jan 27 '22 at 08:13

Kabilan Mohanraj · Accepted Answer · 2022-01-24T11:11:16.223

First, I added the column annotatePrice to the dataframe. Then constructed the inner dictionary for lowestPrice, followed by the outer dictionary. I sourced my solution from this stack answer.

Below is the dataframe after adding annotatePrice column.

Conversion code:

df = pd.json_normalize(json_data, max_level=2)
df['annotatePrice'] = 15

json_data = (df.groupby(['item', 'annotatePrice'])
       .apply(lambda x: x[['lowestPrice.price', 'lowestPrice.currency']].rename(columns={"lowestPrice.price":'price', "lowestPrice.currency":'currency'}).to_dict('records')[0])
       .reset_index()
       .rename(columns={0:'lowestPrice'})
       .to_dict(orient='records'))

json_data

Output:

[
  {
        'annotatePrice': 15,
        'item': 'Item1',
        'lowestPrice': {
            'currency': 'EUR',
            'price': 11.0
        }
    },
    {
        'annotatePrice': 15,
        'item': 'Item2',
        'lowestPrice': {
            'currency': 'EUR',
            'price': 12.0
        }
    },
    {
        'annotatePrice': 15,
        'item': 'Item3',
        'lowestPrice': {
            'currency': 'EUR',
            'price': 13.0
        }
    }
]

Pandas: nested json/dict to json_normalize DataFrame and json_normalize DataFrame to nested json/dict

1 Answers1