how to convert this nested JSON in columnar form into Pandas dataframe

Question

I could read this nested JSON format in columnar format into pandas.

JSON scheme format

enter image description here

Python script

    req = requests.get(REQUEST_API)
    returned_data = json.loads(req.text)
    # status
    print("status: {0}".format(returned_data["status"]))
    # api version
    print("version: {0}".format(returned_data["version"]))
    data_in_columnar_form = pd.DataFrame(returned_data["data"])
    data = data_in_columnar_form["data"]

UPDATE

I want to convert the following JSON scheme into the tabular format as the table, how to ?

inline

JSON Scheme

     "data":[  
        {  
           "year":"2009",
           "values":[  
              {  
                 "Actual":"(0.2)"
              },
              {  
                 "Upper End of Range":"-"
              },
              {  
                 "Upper End of Central Tendency":"-"
              },
              {  
                 "Lower End of Central Tendency":"-"
              },
              {  
                 "Lower End of Range":"-"
              }
           ]
        },
        {  
           "year":"2010",
           "values":[  
              {  
                 "Actual":"2.8"
              },
              {  
                 "Upper End of Range":"-"
              },
              {  
                 "Upper End of Central Tendency":"-"
              },
              {  
                 "Lower End of Central Tendency":"-"
              },
              {  
                 "Lower End of Range":"-"
              }
           ]
        },...
        ]

Maybe this is what you are looking for? Construct pandas DataFrame from items in nested dictionary — Janne Karila
– Janne Karila, Commented Feb 4, 2015 at 12:43

Andy Hayden · Accepted Answer · 2015-02-04 17:47:25Z

8

Pandas has a JSON normalization function (as of 0.13), straight out of the docs:

In [205]: from pandas.io.json import json_normalize

In [206]: 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}]}]
   .....: 

In [207]: json_normalize(data, 'counties', ['state', 'shortname', ['info', 'governor']])
Out[207]: 
         name  population info.governor    state shortname
0        Dade       12345    Rick Scott  Florida        FL
1     Broward       40000    Rick Scott  Florida        FL
2  Palm Beach       60000    Rick Scott  Florida        FL
3      Summit        1234   John Kasich     Ohio        OH
4    Cuyahoga        1337   John Kasich     Ohio        OH

answered Feb 4, 2015 at 17:47

Andy Hayden

378k110 gold badges640 silver badges546 bronze badges

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

4 Comments

DSM Over a year ago

I always forget this exists and so I've reimplemented it a few times. :-(

Andy Hayden Over a year ago

@DSM me too... to be fair, it's "recent". :)

user3675188 Over a year ago

@AndyHayden thanks for your reply, could you give me some hint for the new question stackoverflow.com/questions/28335321/…

Datacrawler Over a year ago

@Andy Hayden What if 'counties' is a child of the 'info'? What would the meta be? I am still testing that to parse the 'info':['counties'] values.

Collectives™ on Stack Overflow

how to convert this nested JSON in columnar form into Pandas dataframe

JSON scheme format

Python script

UPDATE

JSON Scheme

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

JSON scheme format

Python script

UPDATE

JSON Scheme

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related