Flattening nested JSON with LIST values in it using Pandas

Question

I am trying to flatten this nested JSON file:

{
    "1": {
        "name": "Treez #0001",
        "description": "Treez #0001",
        "image": "",
        "atributes": [
            {
                "trait_type": "Apple Count",
                "value": "3"
            },
            {
                "trait_type": "Body Type",
                "value": "demeter"
            },
            {
                "trait_type": "Background",
                "value": "RiverLand"
            },
            {
                "trait_type": "Body Texture",
                "value": "TMac"
            },
            {
                "trait_type": "Stone Type",
                "value": "TeenSpirit"
            },
            {
                "trait_type": "Leaf Texture",
                "value": "Parisian"
            },
            {
                "trait_type": "Apple Texture",
                "value": "Pastel"
            },
            {
                "trait_type": "Animal",
                "value": "Monkey"
            },
            {
                "trait_type": "Rarity Score ",
                "value": "1271"
            }
        ]
    },
    "2": {
        "name": "Treez #0002",
        "description": "Treez #0002",
        "image": "",
        "atributes": [
            {
                "trait_type": "Apple Count",
                "value": "3"
            },
            {
                "trait_type": "Body Type",
                "value": "Naked"
            },
            {
                "trait_type": "Background",
                "value": "Heaven"
            },
            {
                "trait_type": "Body Texture",
                "value": "WcWall"
            },
            {
                "trait_type": "Stone Type",
                "value": "Fairy"
            },
            {
                "trait_type": "Leaf Texture",
                "value": "Haze"
            },
            {
                "trait_type": "Apple Texture",
                "value": "SummerTime"
            },
            {
                "trait_type": "Rarity Score ",
                "value": "767"
            }
        ]
    },
    "3": {
        "name": "Treez #0003",
        "description": "Treez #0003",
        "image": "",
        "atributes": [
            {
                "trait_type": "Apple Count",
                "value": "1"
            },
            {
                "trait_type": "Body Type",
                "value": "Naked"
            },
            {
                "trait_type": "Background",
                "value": "FutureCaveMens"
            },
            {
                "trait_type": "Body Texture",
                "value": "CottonCandy"
            },
            {
                "trait_type": "Stone Type",
                "value": "TeenSpirit"
            },
            {
                "trait_type": "Leaf Texture",
                "value": "Energy"
            },
            {
                "trait_type": "Apple Texture",
                "value": "Slushy"
            },
            {
                "trait_type": "Rarity Score ",
                "value": "517"
            }
        ]
    },
    "4": {
        "name": "Treez #0004",
        "description": "Treez #0004",
        "image": "",
        "atributes": [
            {
                "trait_type": "Apple Count",
                "value": "1"
            },
            {
                "trait_type": "Body Type",
                "value": "Naked"
            },
            {
                "trait_type": "Background",
                "value": "ColorfulSkies"
            },
            {
                "trait_type": "Body Texture",
                "value": "CottonCandy"
            },
            {
                "trait_type": "Stone Type",
                "value": "Poison"
            },
            {
                "trait_type": "Leaf Texture",
                "value": "LemonHaze"
            },
            {
                "trait_type": "Apple Texture",
                "value": "Wilderness"
            },
            {
                "trait_type": "Rarity Score ",
                "value": "502"
            }
        ]
    }
}

Pandas.io.json.read_json return without parsing the list:

and

when I try json_normalize:

f = open("file.t", "r")
data = json.load(f)
pd.json_normalize(data)

it looks like this:

and I could't find any parameters to work.

My expected output is columns = ['name','Apple Count', 'Body Type', 'Background', 'Body Texture', 'Stone Type', 'Leaf Texture', 'Apple Texture', 'Animal','Rarity Score']

try : pd.json_normalize(data['1'], 'atributes', ['name']), you can then pivot the data into the form u want — sammywemmy
– sammywemmy, Commented Oct 1, 2021 at 0:24
yes, did data.pivot(index= 'name', columns='trait_type', values='value') after your code worked like a charm :)) thank you. — erikci
– erikci, Commented Oct 1, 2021 at 0:33

erikci · Accepted Answer · 2021-10-01 00:36:59Z

1

as @sammywemmy suggested,

I first did;

pd.json_normalize(data['1'], 'atributes', ['name'])

gave me this:

then pivoted my table by:

data.pivot(index= 'name', columns='trait_type', values='value')

and this is the result as expected;

answered Oct 1, 2021 at 0:36

erikci

15910 bronze badges

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

Collectives™ on Stack Overflow

Flattening nested JSON with LIST values in it using Pandas

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