Convert a nested list of strings into a data frame-CodePudding

I have JSON file containing something like this.

[7500, 
'29-Dec-2022', 
{'strikePrice': 7500, 'expiryDate': '29-Dec-2022', 'underlying': 'NIFTY', 'identifier': 'OPTIDXNIFTY29-12-2022PE7500.00', 'openInterest': 21, 'changeinOpenInterest': 0, 'pchangeinOpenInterest': 0, 'totalTradedVolume': 0, 'impliedVolatility': 0, 'lastPrice': 8.6, 'change': 0, 'pChange': 0, 'totalBuyQuantity': 1800, 'totalSellQuantity': 0, 'bidQty': 1800, 'bidprice': 3.05, 'askQty': 0, 'askPrice': 0, 'underlyingValue': 17287.05},
8300, 
'30-Jun-2022', 
{'strikePrice': 8300, 'expiryDate': '30-Jun-2022', 'underlying': 'NIFTY', 'identifier': 'OPTIDXNIFTY30-06-2022PE8300.00', 'openInterest': 3, 'changeinOpenInterest': 0, 'pchangeinOpenInterest': 0, 'totalTradedVolume': 0, 'impliedVolatility': 0, 'lastPrice': 4.7, 'change': 0, 'pChange': 0, 'totalBuyQuantity': 1050, 'totalSellQuantity': 0, 'bidQty': 750, 'bidprice': 0.35, 'askQty': 0, 'askPrice': 0, 'underlyingValue': 17287.05}, 
8500, 
'29-Jun-2023', {'strikePrice': 8500, 'expiryDate': '29-Jun-2023', 'underlying': 'NIFTY', 'identifier': 'OPTIDXNIFTY29-06-2023CE8500.00', 'openInterest': 319.5, 'changeinOpenInterest': 0, 'pchangeinOpenInterest': 0, 'totalTradedVolume': 0, 'impliedVolatility': 0, 'lastPrice': 1775, 'change': 0, 'pChange': 0, 'totalBuyQuantity': 0, 'totalSellQuantity': 50, 'bidQty': 0, 'bidprice': 0, 'askQty': 50, 'askPrice': 9970, 'underlyingValue': 17287.05}, 
8500, 
'29-Dec-2022', 
{'strikePrice': 8500, 'expiryDate': '29-Dec-2022', 'underlying': 'NIFTY', 'identifier': 'OPTIDXNIFTY29-12-2022PE8500.00', 'openInterest': 2254, 'changeinOpenInterest': 0, 'pchangeinOpenInterest': 0, 'totalTradedVolume': 0, 'impliedVolatility': 0, 'lastPrice': 22.9, 'change': 0, 'pChange': 0, 'totalBuyQuantity': 2700, 'totalSellQuantity': 0, 'bidQty': 1800, 'bidprice': 3.15, 'askQty': 0, 'askPrice': 0, 'underlyingValue': 17287.05}]

Code:

read_cont = []
new_list1 = []
new_list2 = [] 

for i in rjson:
  for j in rjson[i]:
      read_cont.append(rjson[i][j])

      data_filter = read_cont[1]
      for item in data_filter:
      for j in item:
            new_list1.append(item[j])


      new_list1 =  map(str,new_list1)

     for i in new_list1:
        if len(i) > 100:
             new_list2.append(i)

        header_names = ["STRIKE PRICE","EXPIRY","underlying", "identifier","OPENINTEREST","changeinOpenInterest","pchangeinOpenInterest", "totalTradedVolume","impliedVolatility","lastPrice","change","pChange", "totalBuyQuantity","totalSellQuantity","bidQty","bidprice","askQty","askPrice","underlyingValue"]
        df = pd.DataFrame(columns=header_names)

In order to separate the strikePrice entries from the nested list, I had converted all the items to string

["{'strikePrice': 7500, 'expiryDate': '29-Dec-2022', 'underlying': 'NIFTY', 'identifier': 'OPTIDXNIFTY29-12-2022PE7500.00', 'openInterest': 21, 'changeinOpenInterest': 0, 'pchangeinOpenInterest': 0, 'totalTradedVolume': 0, 'impliedVolatility': 0, 'lastPrice': 8.6, 'change': 0, 'pChange': 0, 'totalBuyQuantity': 1800, 'totalSellQuantity': 0, 'bidQty': 1800, 'bidprice': 3.05, 'askQty': 0, 'askPrice': 0, 'underlyingValue': 17287.05}", 
"{'strikePrice': 8300, 'expiryDate': '30-Jun-2022', 'underlying': 'NIFTY', 'identifier': 'OPTIDXNIFTY30-06-2022PE8300.00', 'openInterest': 3, 'changeinOpenInterest': 0, 'pchangeinOpenInterest': 0, 'totalTradedVolume': 0, 'impliedVolatility': 0, 'lastPrice': 4.7, 'change': 0, 'pChange': 0, 'totalBuyQuantity': 1050, 'totalSellQuantity': 0, 'bidQty': 750, 'bidprice': 0.35, 'askQty': 0, 'askPrice': 0, 'underlyingValue': 17287.05}"

Now I want to transfer the content to a data frame containing the below column mention in the code

CodePudding user response：

result_dict = []
result_values = []

for i in range(2, len(input_list), 3):
    result_dict.append(input_list[i])
    result_values.append(input_list[i].values())

col_names = list(result_dict[0].keys())
result_df = pd.DataFrame(result_values, columns = col_names)

CodePudding user response：

rjson = response.json()
read_cont = []
new_list1 = []
new_list2 = [] 



for i in rjson:
     for j in rjson[i]:
      read_cont.append(rjson[i][j])

data_filter = read_cont[1]
for item in data_filter:
      for j in item:
          new_list1.append(item[j])


 for j in new_list1:
       if type(j) == dict:
    new_list2.append(j)
 df = pd.DataFrame(new_list2)