CODEWITHSUNDEEP
jsonpython-3.xpandas
Vasilis
Vasilis

Reputation: 85

Nested JSON to DataFrame

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)

Upvotes: 2

Views: 81

Answers (3)

G.G
G.G

Reputation: 765

data={
    "AAPL":{
        "quote":{
            "symbol":"AAPL",
            "companyName":"Apple, Inc.",
            "primaryExchange":"SQDNAA",
            "calculationPrice":"close"
        }
    },
    "MSFT":{
        "quote":{
            "symbol":"MSFT",
            "companyName":"Microsoft Corp.",
            "primaryExchange":"QSAAND",
            "calculationPrice":"close",
        }
    }
}
pd.json_normalize(data,max_level=1).T.squeeze().apply(pd.Series)

：

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

Upvotes: 0

sammywemmy
sammywemmy

Reputation: 28644

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

Upvotes: 1

Umar.H
Umar.H

Reputation: 23099

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

Upvotes: 1

Related Questions