Parse JSON into Dataframe

Question

Any idea how to normalize the following JSON output from an API:

{
   "AA":{
      "country":"US",
      "currency":"USD",
      "exchange":"NEW YORK STOCK EXCHANGE, INC.",
      "finnhubIndustry":"Metals & Mining",
      "ipo":"2016-10-18",
      "logo":"",
      "marketCapitalization":4348.893,
      "name":"Alcoa Corp",
      "phone":"14123152900",
      "shareOutstanding":185.929586,
      "ticker":"AA",
      "weburl":"https://www.alcoa.com/global/en/home.asp"
   },
   "AAA":{
      
   },

With pd.DataFrame.from_dict(json_normalize(df1), orient='columns') I get:

    AA.country  AA.currency AA.exchange AA.finnhubIndustry  AA.ipo  AA.logo AA.marketCapitalization AA.name AA.phone    AA.shareOutstanding ... AAPL.exchange   AAPL.finnhubIndustry    AAPL.ipo    AAPL.logo   AAPL.marketCapitalization   AAPL.name   AAPL.phone  AAPL.shareOutstanding   AAPL.ticker AAPL.weburl
0   US  USD NEW YORK STOCK EXCHANGE, INC.   Metals & Mining 2016-10-18      4348.893    Alcoa Corp  14123152900 185.929586  ... NASDAQ NMS - GLOBAL MARKET  Technology  1980-12-12  https://static.finnhub.io/logo/87cb30d8-80df-1...   2103973 Apple Inc   14089961010 17001.802   AAPL    https://www.apple.com/
1 rows × 144 columns

But I need a format there AA.XXX is a row and new row is added for AAA.XXX

halfer · Accepted Answer · 2020-12-18 23:48:06Z

You don't need to call pd.json_normalize; just use pd.DataFrame.from_dict and specify the orient argument as index.

Note, I've just duplicated your json into a new node, so the values will be the same.

j = {
   "AA":{
      "country":"US",
      "currency":"USD",
      "exchange":"NEW YORK STOCK EXCHANGE, INC.",
      "finnhubIndustry":"Metals & Mining",
      "ipo":"2016-10-18",
      "logo":"",
      "marketCapitalization":4348.893,
      "name":"Alcoa Corp",
      "phone":"14123152900",
      "shareOutstanding":185.929586,
      "ticker":"AA",
      "weburl":"https://www.alcoa.com/global/en/home.asp"
   },
   "AAA":{
      "country":"US",
      "currency":"USD",
      "exchange":"NEW YORK STOCK EXCHANGE, INC.",
      "finnhubIndustry":"Metals & Mining",
      "ipo":"2016-10-18",
      "logo":"",
      "marketCapitalization":4348.893,
      "name":"Alcoa Corp",
      "phone":"14123152900",
      "shareOutstanding":185.929586,
      "ticker":"AA",
      "weburl":"https://www.alcoa.com/global/en/home.asp"} }

df = pd.DataFrame.from_dict(j,orient='index')

print(df)

user6386471 · Accepted Answer · 2020-12-10 11:26:58Z

1

If you want to use json_normalize, you could get straight to a dataframe by calling it like this:

from pandas import json_normalize

json_normalize([d for d in j.values()])

answered Dec 10, 2020 at 11:26

user6386471

1,2631 gold badge9 silver badges18 bronze badges

1 Comment

user6386471 Over a year ago

I changed a few field values of the second node in the json just to make sure it was doing what it was supposed to :)

Collectives™ on Stack Overflow

Parse JSON into Dataframe

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related