CODEWITHSUNDEEP
python-3.xpandas
Pritesh Choksi
Pritesh Choksi

Reputation: 51

creating dataframe from nested dictionary using python

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

Upvotes: 1

Views: 47

Answers (3)

Mark Wang
Mark Wang

Reputation: 2757

A slightly different yet compact approach,

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

Upvotes: 1

Karol Żak
Karol Żak

Reputation: 2406

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'])

enter image description here

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

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

enter image description here

Upvotes: 0

Panagiotis Simakis
Panagiotis Simakis

Reputation: 1257

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'])
])

Upvotes: 1

Related Questions