How to extract nested json using json_normalize?

Question

I have a nested json, but I can't understand how to work with them.

{
    "return": {
        "status_processing": "3",
        "status": "OK",
        "order": {
            "id": "872102042",
            "number": "123831",
            "date_order": "dd/mm/yyyy",
            "items": [
                {
                    "item": {
                        "id_product": "684451795",
                        "code": "VPOR",
                        "description": "Product 1",
                        "unit": "Un",
                        "quantity": "1.00",
                        "value": "31.76"
                    }
                },
                {
                    "item": {
                        "id_product": "684451091",
                        "code": "VSAP",
                        "description": "Product 2",
                        "unit": "Un",
                        "quantity": "1.00",
                        "value": "31.76"
                    }
                }
            ]
        }
    }
}

I searched on stackoverflow questions, and try some resolutions that people passed, but don't work for me.

Here an sample that I used to accessing the data from json:

df = pd.json_normalize(
    order_list,
    record_path=["return", "order", "itens"],
    meta=[
        ["return", "order", "id"],
        ["return", "order", "date_order"],
        ["return", "order", "number"],
    ],
)

But don't work, they duplicating the data when I send to dataframe.

Anyone can help me?

EDIT

Here an example that I used:

Convert nested JSON to pandas DataFrame

And what I expected:

if you flatten it then it may have to repeat some data. What other solutions did you try? You could add links in question (not in comments). What result do you expect? You could show it in question. It could explain what you really need. — furas
– furas, Commented Mar 30 at 14:09
maybe it would be simpler to write normal code instead of using json_normalize — furas
– furas, Commented Mar 30 at 14:10
first you should show expected result. If you want every item in new row then it may need to use for-loop or expand instead normalize — furas
– furas, Commented Mar 30 at 14:22
What I want is every item in new row. And thank you, I'll try using expand — Cesar Augusto
– Cesar Augusto, Commented Mar 30 at 14:27

chitown88 · Accepted Answer · 2025-03-31 15:41:31Z

You're code is fine. You are getting the data, perhaps you just wanted to specify which columns to keep (or maybe rename)?

import json
import pandas as pd



data = '''   { "return": {
        "status_processing": "3",
        "status": "OK",
        "order": {
            "id": "872102042",
            "number": "123831",
            "date_order": "dd/mm/yyyy",
             "itens": [
                {
                    "item": {
                        "id_product": "684451795",
                        "code": "VPOR",
                        "description": "Product 1",
                        "unit": "Un",
                        "quantity": "1.00",
                        "value": "31.76"
                    }
                },
                {
                    "item": {
                        "id_product": "684451091",
                        "code": "VSAP",
                        "description": "Product 2",
                        "unit": "Un",
                        "quantity": "1.00",
                        "value": "31.76"
                    }
                }
            ]
        }
    }
}'''

order_list = json.loads(data)


df = pd.json_normalize(order_list, 
                       record_path= ["return", "order", "itens"],
                       meta=[["return", "order", "id"], ["return", "order", "number"], ["return", "order", "date_order"]])



df = df[['return.order.id', 'return.order.number', 'return.order.date_order', 'item.id_product']]

Output:

print(df)
  return.order.id return.order.number return.order.date_order item.description
0       872102042              123831              dd/mm/yyyy        Product 1
1       872102042              123831              dd/mm/yyyy        Product 2

furas · Accepted Answer · 2025-03-30 15:59:54Z

0

I don't know what exactly you expect in output but if you want every item in new row then you could use normal code with for-loop for this.

order_list = {
    "return": {
        "status_processing": "3",
        "status": "OK",
        "order": {
            "id": "872102042",
            "number": "123831",
            "date_order": "dd/mm/yyyy",
             "itens": [
                {
                    "item": {
                        "id_product": "684451795",
                        "code": "VPOR",
                        "description": "Product 1",
                        "unit": "Un",
                        "quantity": "1.00",
                        "value": "31.76"
                    }
                },
                {
                    "item": {
                        "id_product": "684451091",
                        "code": "VSAP",
                        "description": "Product 2",
                        "unit": "Un",
                        "quantity": "1.00",
                        "value": "31.76"
                    }
                }
            ]
        }
    }
}

import pandas as pd

data = []

order = order_list['return']['order']

for iten in order['itens']:
    for key, val in iten.items():
        row = {
            #'key': key, 
            'id': order['id'], 
            'date_order': order['date_order'], 
            'number': order['number'], 
            'id_product': val['id_product'],
            #'code': val['code'],
            #'description': val['description'],
            #'quantity': val['quantity'],
            #'value': val['value'],
        }
        data.append(row)

df = pd.DataFrame(data)
print(df)

Result:

          id  date_order  number id_product
0  872102042  dd/mm/yyyy  123831  684451795
1  872102042  dd/mm/yyyy  123831  684451091

If you need other information in rows then you should show it in question.

edited Mar 30 at 15:59

answered Mar 30 at 14:40

furas

149k12 gold badges121 silver badges171 bronze badges

7 Comments

Cesar Augusto Mar 30 at 15:15

It's works, but have some ids that have two itens, and here for key, val in order['itens'][0].items(): it's getting the first item, how I use slicing to get all data ? I Tried using [:] to get all, but dont work

furas Mar 30 at 15:26

if you have two items then (first) you should show it in JSON in question, (second) you can use external for-loop to work with every 'itens' (but this depends on how exactly looks JSON data). BTW: if you show example JSON then show result exactly for this JSON data - it helps to see what is moved to rows.

Cesar Augusto Mar 30 at 15:40

I updated the json on description @furas

Cesar Augusto Mar 30 at 15:59

as you say, I added a for-loop external and works. Thank you for your help @furas

furas Mar 30 at 16:00

I added code with external for-loop

|

Collectives™ on Stack Overflow

How to extract nested json using json_normalize?

2 Answers 2

Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related