How to normalize a JSON file into a Pandas dataframe-CodePudding

I have a JSON file named stocks.json that looks as follows (note the lack of square brackets in the source file):

{"MSFT": {"exchange": "Nasdaq", "price": 275.79}, "FB": {"exchange": "Nasdaq", "price": 320.22}, "TSLA": {"exchange": "Nasdaq", "price": 990.83}, "GE": {"exchange": "Nasdaq", "price": 83.20}}

I would like to transform this data into a Pandas dataframe that looks as follows:

symbol     exchange     price
MSFT       Nasdaq       275.79
FB         Nasdaq       320.22
TSLA       Nasdaq       990.83
GE         NYSE          83.20

My attempt is:

import pandas as pd

stock_data = pd.read_json('stocks.json', lines=True)
stock_data_normalized = pd.json_normalize(stock_data)

Unfortunately, I get the following when calling stock_data_normalized:

Any assistance would be most appreciated. Thanks!

CodePudding user response：

You can just using the pd.DataFrame() constructor, and then transpose and reset the index:

df = pd.DataFrame(d).T.reset_index().rename({'index': 'symbol'}, axis=1)

Output:

>>> df
  symbol exchange   price
0   MSFT   Nasdaq  275.79
1     FB   Nasdaq  320.22
2   TSLA   Nasdaq  990.83
3     GE   Nasdaq    83.2