converting dictionary into dataframe as expected output in python

Question

let's say i have a dictionary as

dj=  {
        "totalrecords": 2,
        "data": [
            {
                "stateCd": "U.K",
                "stateName": "uttarakhand",
                "details": {
                    "id": [
                        "2312-k",
                        "2312-k"
                    ],
                    "date": [
                        "10-OCT-2019",
                        "11-OCT-2019"
                    ],
                    "icp": [
                        2233,
                        6443
                    ],
                    "icpr": [
                        3.434,
                        23.232
                    ]
                }
            },
            {
                "stateCd": "U.P",
                "stateName": "uttar pradesh",
                "details": {
                    "id": [
                        "2712-k",
                        "5412-k"
                    ],
                    "date": [
                        "10-OCT-2019",
                        "11-OCT-2019"
                    ],
                    "icp": [
                        2233,
                        6443
                    ],
                    "icpr": [
                        32.434,
                        31.232
                    ]
                }
            }
        ]
    }
    }

I want to convert this json/dictionary into data frame which would be like this using python:

but I am having no clue how to perform this action

i have also tried pandas.json_normalize() but didn't get my expected column in output i.e. date,icp,icpr

data_trunc=dj['data']
pd.json_normalize(data_trunc,record_path=['details','id'],meta=['stateCd','stateName'])

enter image description here

You listed pandas as one of your tags, can you show us what you have tried? If you have no idea how to start, perhaps you can look at a tutorial first for converting dict to dataframe, e.g. datatofish.com/dictionary-to-dataframe — user5305519
– user5305519, Commented Jun 10, 2021 at 3:01
what i have tried print(response) print(type(response)) dict_trunc = response['data'][0] new = pd.DataFrame.from_dict(dict_trunc) print(new) — Anil Tiwari
– Anil Tiwari, Commented Jun 10, 2021 at 3:23
along with it i have also tried "flatten-json" but it didn't get the expeted output — Anil Tiwari
– Anil Tiwari, Commented Jun 10, 2021 at 3:25

Someshwaran S · Accepted Answer · 2021-06-10 03:12:59Z

2

You can try something like this

Reference


data = [{'state': 'Florida',
             'shortname': 'FL',
             'info': {'governor': 'Rick Scott'},
             'counties': [{'name': 'Dade', 'population': 12345},
                                {'name': 'Broward', 'population': 40000},
                                {'name': 'Palm Beach', 'population': 60000}]},
            {'state': 'Ohio',
             'shortname': 'OH',
             'info': {'governor': 'John Kasich'},
             'counties': [{'name': 'Summit', 'population': 1234},
                                {'name': 'Cuyahoga', 'population': 1337}]}]
result = pd.json_normalize(data, 'counties', ['state', 'shortname',
                                               ['info', 'governor']])

Output:

0        Dade       12345   Florida    FL    Rick Scott
1     Broward       40000   Florida    FL    Rick Scott
2  Palm Beach       60000   Florida    FL    Rick Scott
3      Summit        1234   Ohio       OH    John Kasich
4    Cuyahoga        1337   Ohio       OH    John Kasich

answered Jun 10, 2021 at 3:12

Someshwaran S

1021 silver badge6 bronze badges

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

4 Comments

Arya Over a year ago

This is very nice! Pandas is useful. From only 1 line of code you did it wow! I was not knowing anything about pandas

Someshwaran S Over a year ago

Checkout panda’s related articles from realpython. There must be other better resources to learn pandas. This is the one I used

Anil Tiwari Over a year ago

@SomeshwaranS i have tried pandas.json_normalize() as pd.json_normalize(data_trunc,record_path=['details','id'],meta=['stateCd','stateName']) but didn't get my expected output can you pls help into this

Someshwaran S Over a year ago

Is it possible to edit the details object to a list of dict like

details = [{'id': '2312-k','icp':'2233','icpr':'3.434','date':'10-OCT-2019'},{{'id': '2312-k','icp':'6443','icpr':'23.232','date':'11-OCT-2019'}]

Arya · Accepted Answer · 2021-06-10 03:17:24Z

Maybe this can help you:-

import json

f = open('1.json')
file = json.load(f)

for data in file['data']:
  stCd = data['stateCd']
  stateN = data['stateName']
  date = data['details']['date']
  icp = data['details']['icp']
  icpr = data['details']['icpr']
  full = f"StCd    StateName       Date          ICP          ICPR"
  whole = f"{stCd}     {stateN}    {date[0]}    {icp[0]}      {icpr[0]}"
  whole2 = f"{stCd}     {stateN}    {date[1]}    {icp[1]}      {icpr[1]}"
  print()
  print(full)
  print(whole)
  print(whole2)

Output:-

StCd    StateName       Date          ICP          ICPR
U.K     uttarakhand    10-OCT-2019    2233      3.434
U.K     uttarakhand    11-OCT-2019    6443      23.232

StCd    StateName       Date          ICP          ICPR
U.P     uttar pradesh    10-OCT-2019    2233      32.434
U.P     uttar pradesh    11-OCT-2019    6443      31.232

Collectives™ on Stack Overflow

converting dictionary into dataframe as expected output in python

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related