convert nested json to dataframe using json_normalize in python

Question

I would like to convert nested json to dataframe.

The result i want to get :

| securityFullTypeCode | securityTypeDesc |
| - - - - - - - - - - -| - - - - - - - - -|
| 0101                 |  Ordinary Share  |
| 0102                 |  Preferred  Share|

I tried json_normalize but unsuccessfully.

Json file looks like this :

{
    "securitiesTypes": {
        "result": [
            {
                "securityFullTypeCode": "0101",
                "securityTypeDesc": "Ordinary Share"
            },
            {
                "securityFullTypeCode": "0102",
                "securityTypeDesc": "Preferred  Share"
            },
            {
                "securityFullTypeCode": "0103",
                "securityTypeDesc": "Preferr & Cum  share"
            }
        ],
        "total": 215
    }
}

Thanks

try : pd.DataFrame(pd.read_json(file.json').loc['result','securitiesTypes']) — anky
– anky, Commented Feb 26, 2021 at 15:07

Loic RW · Accepted Answer · 2021-02-26 15:10:52Z

1

A similar question was asked in: JSON to pandas DataFrame

The following seems to do the trick:

file = {
    "securitiesTypes": {
        "result": [
            {
                "securityFullTypeCode": "0101",
                "securityTypeDesc": "Ordinary Share"
            },
            {
                "securityFullTypeCode": "0102",
                "securityTypeDesc": "Preferred  Share"
            },
            {
                "securityFullTypeCode": "0103",
                "securityTypeDesc": "Preferr & Cum  share"
            }
        ],
        "total": 215
    }
}

df = pd.json_normalize(file['securitiesTypes']['result'])

What problems are you running into exactly?

answered Feb 26, 2021 at 15:10

Loic RW

4443 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alexey Over a year ago

I got the following error: TypeError: string indices must be integers

Loic RW Over a year ago

I would check out the following in that case: stackoverflow.com/questions/6077675/…

Quang Hoang · Accepted Answer · 2021-02-26 15:12:14Z

1

Use json_normalize with record_path option:

import json

with open('file.json') as f:
    df = pd.json_normalize(json.load(f), 
                           record_path=['securitiesTypes','result']
                          )

answered Feb 26, 2021 at 15:12

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Collectives™ on Stack Overflow

convert nested json to dataframe using json_normalize in python

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related