format dataframe with nested json elements-CodePudding

I am currently getting a dataframe which looks like this:

  Date        Open    Close   High    Low     Volume    Security
0 2023-01-27  155.75  155.69  156.96  154.81   646223   {'Symbol': 'A'}
1 2023-01-27   51.39   52.75   53.46   50.98  4772525   {'Symbol': 'AA'}

Seen in the browser, the results look like this:

[{"Date":"2023-01-27","Open":155.75,"Close":155.69,"High":156.96,"Low":154.81,"Volume":646223.0,"Security":{"Symbol":"A"}},{"Date":"2023-01-27","Open":51.39,"Close":52.75,"High":53.46,"Low":50.98,"Volume":4772525.0,"Security":{"Symbol":"AA"}}]

I would like it to look like this:

  Date        Open    Close   High    Low     Volume    Symbol
0 2023-01-27  155.75  155.69  156.96  154.81   646223   A
1 2023-01-27   51.39   52.75   53.46   50.98  4772525   AA

My code with api call is this (with token changed)

import pandas as pd

LIST1 = ["A.XNYS,AA.XNYS,AAC.XNYS"]

for ls in LIST1:

    df = pd.read_json(f'https://globalquotes.xignite.com/v3/xGlobalQuotes.json/GetGlobalDelayedQuotes?IdentifierType=Symbol&Identifiers={ls}&_fields=Security.Symbol,Date,Open,Close,High,Low,Volume&_token=089BErre4D63439499FFE73CA64905F3')

    print(df.head(5))
    if not df.empty:

    df = df.dropna(thresh=7)
   

    #x = df['Symbol'].astype(str)
    #Date = df['date']
    #Open = df['open']
    #Close = df['close']
    #High = df['high']
    #Low = df['low']
    #Volume = df['volume']
    #Symbol = x.str.slice(0, 38)

Can anyone show me how to get it the way I want? maybe using pd.json_normalize Thanks

CodePudding user response：

import json

import pandas as pd
import requests


LIST1 = ["A.XNYS, AA.XNYS, AAC.XNYS"]

for ls in LIST1:
    url = ("https://globalquotes.xignite.com/v3/xGlobalQuotes.json/GetGlobalDelayedQuotes?"
           "IdentifierType=Symbol&"
           f"Identifiers={ls}&"
           "_fields=Security.Symbol,Date,Open,Close,High,Low,Volume&"
           "_token=089BErre4D63439499FFE73CA64905F3")
    
    with requests.Session() as request:
        response = request.get(url)
    if response.status_code != 200:
        print(response.raise_for_status())
    
    data = json.loads(response.text)
    
    df = pd.json_normalize(data=data).dropna(thresh=7)
    print(df)