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. You can create the multi-index using df.columns = df.columns.str.split('.', expand=True) — Peter
– Peter, Commented Jan 24, 2022 at 10:09
Can you upvote/accept my answer if it was helpful? If something can be improved do let know. — Kabilan Mohanraj
– Kabilan Mohanraj, Commented Jan 25, 2022 at 9: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
– Nikunj, Commented Jan 27, 2022 at 4: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
– Kabilan Mohanraj, Commented Jan 27, 2022 at 8:13

Kabilan Mohanraj · Accepted Answer · 2022-01-24 11:11:16Z

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
        }
    }
]

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related