creating dataframe from nested dictionary using python

Question

The following is the json data available to me

{
  "status": "success",
  "message": "Transactions Details",
  "TxnArray": [    
    {
      "transactionAmount": {"0": 3500},
      "createdAt": {"0": "17/04/2020"}
    },    
    {
      "transactionAmount": {"1": 4500},
      "createdAt": {"1": "19/04/2020"}
    }
  ]
}

Want to convert the above data in pandas Dataframe like

      transactionAmount createdAt
    0 3500               17/4/2020
    1 4500               19/4/2020

Mark Wang · Accepted Answer · 2020-05-19 13:29:13Z

1

A slightly different yet compact approach,

pd.concat(pd.DataFrame.from_dict(d) for d in data['TxnArray'])

answered May 19, 2020 at 13:29

Mark Wang

2,7579 silver badges18 bronze badges

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

Comments

Panagiotis Simakis · Accepted Answer · 2020-05-19 12:46:20Z

1

A more compact approach:

import pandas as pd

df = pd.DataFrame([
    {k: v[str(i)] for k, v in list_item.items()} for i, list_item in enumerate(json_data['TxnArray'])
])

edited May 19, 2020 at 12:46

answered May 19, 2020 at 12:33

Panagiotis Simakis

1,2671 gold badge20 silver badges48 bronze badges

3 Comments

Pritesh Choksi Over a year ago

That was really helpful and compact solution. Thank you so much..

Pritesh Choksi Over a year ago

I am getting started with all this. Could you help me what "_" is used for? It would be a great help.

Panagiotis Simakis Over a year ago

you have a point @PriteshChoksi . _ is not human friendly. I edited my answer in order to increase readability. So... _ was every list's item

Karol Żak · Accepted Answer · 2020-05-19 13:03:50Z

0

import pandas as pd
import json

json_str = json.loads('''{
  "status": "success",
  "message": "Transactions Details",
  "TxnArray": [    
    {
      "transactionAmount": {"0": 3500},
      "createdAt": {"0": "17/04/2020"}
    },    
    {
      "transactionAmount": {"1": 4500},
      "createdAt": {"1": "19/04/2020"}
    }
  ]
}''')

df = pd.DataFrame(json_str['TxnArray'])

def clean_function(x):
    return list(x.values())[0]

df = df.applymap(lambda x: clean_function(x))

answered May 19, 2020 at 13:03

Karol Żak

2,44626 silver badges26 bronze badges

Collectives™ on Stack Overflow

creating dataframe from nested dictionary using python

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related