convert json data to pandas dataframe in python (dictionary inside list )

Question

I have json data like below:

{"name": "Monkey", "image": "https://media.npr.org/assets/img/2017/09/12/macaca_nigra_self-portrait-3e0070aa19a7fe36e802253048411a38f14a79f8-s800-c85.webp", "attributes": [{"trait_type": "Bones", "value": "Zombie"}, {"trait_type": "Clothes", "value": "Striped"}, {"trait_type": "Mouth", "value": "Bubblegum"}, {"trait_type": "Eyes", "value": "Black Sunglasses"}, {"trait_type": "Hat", "value": "Sushi"}, {"trait_type": "Background", "value": "Purple"}]}

I want to convert this json data as pandas dataframe only selecting the attributes as filter it as below:

Bones   Clothes    Mouth      Eyes    Hat      Background
zombie   striped    bubblegum  black   sushi    purple

Can any expert please help me to get the output as i mentioned Thank you

Denote your original dictionary by orig, then: df = pd.DataFrame({att['trait_type']: [att['value']] for att in orig['attributes']}) — Nir H.
– Nir H., Commented Sep 19, 2021 at 9:06

Bernardo · Accepted Answer · 2021-09-19 09:09:22Z

1

There is probably a prettier solution but this does the job:

import json 
import pandas as pd

with open('file.json') as f:
    trait_types= []
    values = []
    data = json.load(f)
    df = pd.DataFrame(data)
    for key in data['attributes']:
        trait_types.append(key['trait_type'])
        values.append(key['value'])
df = pd.DataFrame({
    'trait type': trait_types,
    'value' : values})
print(df)

answered Sep 19, 2021 at 9:09

Bernardo

868 bronze badges

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

3 Comments

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Zac Over a year ago

you code is correct but only issue now is that i want the "trait type" values to be columns headers and "value" values to be the data.can you make that happen

Bernardo Over a year ago

Use dict(zip(trait_types,values) to convert the two lists into one dictionary. Then use pd.Dataframe.from_dict to put it into your layout

Collectives™ on Stack Overflow

convert json data to pandas dataframe in python (dictionary inside list )

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related