Importing JSON into Pandas

user_1860317 0
Viewed 26183

I have to following JSON that is coming from an API (e.g. my_json). The array of entities is stored in a key called entities:

{
    "action" : "get",
    "application" : "4d97323f-ac0f-11e6-b1d4-0eec2415f3df",
    "params" : {
      "limit" : [ "2" ]
    },
    "path" : "/businesses",
    "entities" : [
        {
            "uuid" : "508d56f1-636b-11e7-9928-122e0737977d",
            "type" : "business",
            "size" : 730 },
        {
            "uuid" : "2f3bd4dc-636b-11e7-b937-0ad881f403bf",
            "type" : "business",
            "size" : 730 
        } ],
  "timestamp" : 1499469891059,
  "duration" : 244,
  "count" : 2
}

I am trying to load them into a data frame as follows:

import pandas as pd

pd.read_json(my_json['entities'], orient='split')

I get the following error:

ValueError: Invalid file path or buffer object type: <type 'list'>

I have tried records orientation and still doesn't work.

pandas dataframe python json
5 Answers

According to the official documenation orient is suppose to be 'records'

df = pd.read_json(json.dumps(b_j['entities']) , orient='records')
user_41406610

It raises the error because type of your variable is list, as stated at the end of the error.

Actually this makes it easier to stack into dataframe, because you can stack lists into a dataframe directly. Hence you can use:

df = pd.DataFrame(my_json['entities'])
user_111056630
Related