Array of Json values to Dataframe in Python

Question

I have a Json array which has key value pairs like below.

[
    [
        {
            "value": "Dimension1",
            "formattedValue": "Dimension1"
        },      
        {
            "value": "Amount1",
            "formattedValue": "Amount1"
        },      
        {
            "value": "17253512.430000003",
            "formattedValue": "17,253,512.43"
        }
    ],
    [
        {
            "value": "Dimension1",
            "formattedValue": "Dimension1"
        },      
        {
            "value": "Amount2",
            "formattedValue": "Amount2"
        },      
        {
            "value": "-143721131.88999993",
            "formattedValue": "-143,721,131.89"
        }
    ],
    [
        {
            "value": "Dimension1",
            "formattedValue": "Dimension1"
        },      
        {
            "value": "Amount3",
            "formattedValue": "Amount3"
        },      
        {
            "value": "160974644.31999999",
            "formattedValue": "160,974,644.32"
        }
    ]   
]

I wanted to convert this into dataframe output like below. How can I achieve this please?

Dimension	Amount1	Amount2	Amount3
Dimension1	17,253,512.43	-143,721,131.89	160,974,644.32

Anurag Dabas · Accepted Answer · 2021-08-09 12:24:59Z

2

Since you are interested in 'formattedValue' so try:

df=(pd.DataFrame(data).stack()
      .map(lambda x:x.get('formattedValue'))
      .unstack().pivot(0,1,2)
      .rename_axis(columns=None,index='Dimension').reset_index())

Note: here data variable is your json data

output of df:

    Dimension   Amount1         Amount2             Amount3
0   Dimension1  17,253,512.43   -143,721,131.89     160,974,644.32

answered Aug 9, 2021 at 12:24

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

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

1 Comment

Muthu Over a year ago

Thanks! It worked as expected for my Json

Karl · Accepted Answer · 2021-08-09 12:29:31Z

1

Try this


import pandas as pd  


data = 'your_json.json'
with open(data) as trainer:
    trainee= json.load(trainer)
df = pd.json_normalize(data['results'])

answered Aug 9, 2021 at 12:29

Karl

154 bronze badges

Collectives™ on Stack Overflow

Array of Json values to Dataframe in Python

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related