Turn this JSON into dataframe-CodePudding

I am calling a function that returns this JSON:

{
  "symbol" : "ADM",
  "historical" : [ {
    "date" : "2022-02-18",
    "close" : 76.39
  }, {
    "date" : "2022-02-17",
    "close" : 76.65
  }, {
    "date" : "2022-02-16",
    "close" : 76.90
  }, {
    "date" : "2022-02-15",
    "close" : 76.80
  }, 
  ...

I use this code to try to parse this into a Dataframe but it isn't satisfactory in that it shows something that **doesn't look like a Dataframe**:

    historical symbol
0  [{'close': 76.39, 'date': '2022-02-18'}, {'close': 76.65, 'date': '2022-02-17'}, {'close': 76.9, 'date': '2022-02-16'}, {'close': 76.8, 'date': '2022-02-15'}, {'close': 76.12, 'date': '2022-02-14'}, {'close': 76.47, 'date': '2022-02-11'}, {'close': 76.44, 'date': '2022-02-10'}, {'close': 77.28, 'date': '2022-02-09'}, {'close': 76.69, 'date': '2022-02-08'}, {'close': 75.980003, 'date': '2022-02-07'}, {'close': 75.709999, 'date': '2022-02-04'}, {'close': 75.669998, 'date': '2022-02-03'}, {'close': 76.690002, 'date': '2022-02-02'}, {'close': 75.529999, 'date': '2022-02-01'}, {'close': 75.0, 'date': '2022-01-31'}, {'close': 75.919998, 'date': '2022-01-28'}, {'close': 74.199997, 'date': '2022-01-27'}, {'close': 72.269997, 'date': '2022-01-26'}, {'close': 68.370003, 'date': '2022-01-25'}, {'close': 68.589996, 'date': '2022-01-24'}, {'close': 68.940002, 'date': '2022-01-21'}, {'close': 68.889999, 'date': '2022-01-20'}, {'close': 71.0, 'date': '2022-01-19'}, {'close': 71.269997, 'date': '2022-01-18'}, {'close': 71.550003, 'date': '2022-01-14'}, {'close': 70.900002, 'date': '2022-01-13'}, {'close': 70.419998, 'date': '2022-01-12'}, {'close': 70.480003, 'date': '2022-01-11'}, {'close': 69.93, 'date': '2022-01-10'}, {'close': 69.809998, 'date': '2022-01-07'}, {'close': 69.220001, 'date': '2022-01-06'}, {'close': 68.610001, 'date': '2022-01-05'}, {'close': 69.150002, 'date': '2022-01-04'}, {'close': 67.889999, 'date': '2022-01-03'}, {'close': 67.589996, 'date': '2021-12-31'}, {'close': 66.860001, 'date': '2021-12-30'}, {'close': 67.010002, 'date': '2021-12-29'}, {'close': 66.93, 'date': '2021-12-28'},

...

This is the code:

import json       
    
result_bar = requests.get(requestUri, verify=False)

if result_bar == None:
   return None

if result_bar.text == None:
   print("No json")
   return None

raw_symbol_bar = result_bar.json()
symbol_bar_list = json.dumps(raw_symbol_bar,indent=4,sort_keys=True)
decoded_json_bar = unidecode(symbol_bar_list)
loaded_json_bar = json.loads(decoded_json_bar)
df_underlying_bar = pd.json_normalize(loaded_json_bar)
print(df_underlying_bar)

CodePudding user response：

Try pd.json_normalize:

df = pd.json_normalize(j, record_path='historical', meta='symbol')

Output:

>>> df
         date   close symbol
0  2022-02-18  167.30   AAPL
1  2022-02-17  168.88   AAPL
2  2022-02-16  172.55   AAPL
3  2022-02-15  172.79   AAPL
4  2022-02-14  168.88   AAPL