Nested JSON to DataFrame

user_6131860 0
Viewed 69

I have a JSON file that looks like this 

{
   "AAPL":{
      "quote":{
         "symbol":"AAPL",
         "companyName":"Apple, Inc.",
         "primaryExchange":"SQDNAA",
         "calculationPrice":"close"
      }
   },
   "MSFT":{
      "quote":{
         "symbol":"MSFT",
         "companyName":"Microsoft Corp.",
         "primaryExchange":"QSAAND",
         "calculationPrice":"close",
      }
   }
}

How do I convert this to a Pandas DataFrame with columns as in the nested quote? (symbol, companyName, primaryExchange and calculationPrice)

pandas python-3.x json
2 Answers

you can just loop over the dict and add it to a list. 

d = yourjsonobject

dfs = []
for k, _ in d.items():
    for quote,v in _.items():
        dfs.append(pd.DataFrame(v,index=[k]))

df = pd.concat(dfs)

print(df)

     symbol      companyName primaryExchange calculationPrice
AAPL   AAPL      Apple, Inc.          SQDNAA            close
MSFT   MSFT  Microsoft Corp.          QSAAND            close
user_93751020

you could give jmespath a try, as it has a nice way of traversing JSON data :

 import jmespath
 #create a compiled expression
 #of the data path
 #similar to re.compile
 #it looks for the quote key and returns its contents
 expression = jmespath.compile('*.quote')
 A = expression.search(data)

#read into a dataframe
 pd.DataFrame(A)

    symbol  companyName primaryExchange calculationPrice
0   AAPL    Apple, Inc.       SQDNAA      close
1   MSFT    Microsoft Corp.    QSAAND     close
user_71757130
Related