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, Feb 26 '21 at 15:07

score 1 · Answer 1 · answered Feb 26 '21 at 15:10

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?

I got the following error: TypeError: string indices must be integers — Alexey, Feb 28 '21 at 08:24
I would check out the following in that case: https://stackoverflow.com/questions/6077675/why-am-i-seeing-typeerror-string-indices-must-be-integers — Loic RW, Feb 28 '21 at 12:04

score 1 · Answer 2 · answered Feb 26 '21 at 15:12

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 '21 at 15:12

Quang Hoang

146,074
10
56
74

convert nested json to dataframe using json_normalize in python

2 Answers2

Linked