Need to Convert following to a dataframe-CodePudding

This is a nested return I receive from a API function. I need to convert this to a dataframe. Could you help please

{'net': [{'tradingsymbol': 'NIFTY21NOVFUT', 'exchange': 'NFO', 'instrument_token': 13720834, 'product': 'NRML', 'quantity': -150, 'overnight_quantity': -150, 'multiplier': 1, 'average_price': 17894.833333, 'close_price': 17913.1, 'last_price': 17710, 'value': 2684224.9999499996, 'pnl': 27724.99994999962, 'm2m': 30465, 'unrealised': 27724.99994999962, 'realised': 0, 'buy_quantity': 0, 'buy_price': 0, 'buy_value': 0, 'buy_m2m': 0, 'sell_quantity': 150, 'sell_price': 17894.833333, 'sell_value': 2684224.9999499996, 'sell_m2m': 2686965.0, 'day_buy_quantity': 0, 'day_buy_price': 0, 'day_buy_value': 0, 'day_sell_quantity': 0, 'day_sell_price': 0, 'day_sell_value': 0}, {'tradingsymbol': 'NIFTY21N0316500PE', 'exchange': 'NFO', 'instrument_token': 11018242, 'product': 'MIS', 'quantity': 0, 'overnight_quantity': 0, 'multiplier': 1, 'average_price': 0, 'close_price': 0, 'last_price': 2.4, 'value': -397.5, 'pnl': -397.5, 'm2m': -397.5, 'unrealised': -397.5, 'realised': 0, 'buy_quantity': 100, 'buy_price': 7.875, 'buy_value': 787.5, 'buy_m2m': 787.5, 'sell_quantity': 100, 'sell_price': 3.9, 'sell_value': 390, 'sell_m2m': 390, 'day_buy_quantity': 100, 'day_buy_price': 7.875, 'day_buy_value': 787.5, 'day_sell_quantity': 100, 'day_sell_price': 3.9, 'day_sell_value': 390}, {'tradingsymbol': 'NIFTY21N0317600CE', 'exchange': 'NFO', 'instrument_token': 11086850, 'product': 'MIS', 'quantity': 0, 'overnight_quantity': 0, 'multiplier': 1, 'average_price': 0, 'close_price': 0, 'last_price': 195.35, 'value': -7960.000000000015, 'pnl': -7960.000000000015, 'm2m': -7960.000000000015, 'unrealised': -7960.000000000015, 'realised': 0, 'buy_quantity': 300, 'buy_price': 343.3833333333334, 'buy_value': 103015.00000000001, 'buy_m2m': 103015.00000000001, 'sell_quantity': 300, 'sell_price': 316.85, 'sell_value': 95055, 'sell_m2m': 95055, 'day_buy_quantity': 300, 'day_buy_price': 343.3833333333334, 'day_buy_value': 103015.00000000001, 'day_sell_quantity': 300, 'day_sell_price': 316.85, 'day_sell_value': 95055}, {'tradingsymbol': 'NIFTY21N0317600PE', 'exchange': 'NFO', 'instrument_token': 11087106, 'product': 'MIS', 'quantity': 0, 'overnight_quantity': 0, 'multiplier': 1, 'average_price': 0, 'close_price': 0, 'last_price': 91.95, 'value': 537.5, 'pnl': 537.5, 'm2m': 537.5, 'unrealised': 537.5, 'realised': 0, 'buy_quantity': 100, 'buy_price': 102.55, 'buy_value': 10255, 'buy_m2m': 10255, 'sell_quantity': 100, 'sell_price': 107.925, 'sell_value': 10792.5, 'sell_m2m': 10792.5, 'day_buy_quantity': 100, 'day_buy_price': 102.55, 'day_buy_value': 10255, 'day_sell_quantity': 100, 'day_sell_price': 107.925, 'day_sell_value': 10792.5}, {'tradingsymbol': 'NIFTY21N0317900CE', 'exchange': 'NFO', 'instrument_token': 11094274, 'product': 'MIS', 'quantity': 0, 'overnight_quantity': 0, 'multiplier': 1, 'average_price': 0, 'close_price': 0, 'last_price': 59.9, 'value': -1512.5, 'pnl': -1512.5, 'm2m': -1512.5, 'unrealised': -1512.5, 'realised': 0, 'buy_quantity': 400, 'buy_price': 155.51875, 'buy_value': 62207.50000000001, 'buy_m2m': 62207.50000000001, 'sell_quantity': 400, 'sell_price': 151.7375, 'sell_value': 60695.00000000001, 'sell_m2m': 60695.00000000001, 'day_buy_quantity': 400, 'day_buy_price': 155.51875, 'day_buy_value': 62207.50000000001, 'day_sell_quantity': 400, 'day_sell_price': 151.7375, 'day_sell_value': 60695.00000000001}, {'tradingsymbol': 'NIFTY21N0317900PE', 'exchange': 'NFO', 'instrument_token': 11094530, 'product': 'MIS', 'quantity': 0, 'overnight_quantity': 0, 'multiplier': 1, 'average_price': 0, 'close_price': 0, 'last_price': 255.1, 'value': 1415.000000000001, 'pnl': 1415.000000000001, 'm2m': 1415.000000000001, 'unrealised': 1415.000000000001, 'realised': 0, 'buy_quantity': 50, 'buy_price': 136.95, 'buy_value': 6847.499999999999, 'buy_m2m': 6847.499999999999, 'sell_quantity': 50, 'sell_price': 165.25, 'sell_value': 8262.5, 'sell_m2m': 8262.5, 'day_buy_quantity': 50, 'day_buy_price': 136.95, 'day_buy_value': 6847.499999999999, 'day_sell_quantity': 50, 'day_sell_price': 165.25, 'day_sell_value': 8262.5}, {'tradingsymbol': 'NIFTY21N0318500CE', 'exchange': 'NFO', 'instrument_token': 11135746, 'product': 'MIS', 'quantity': 0, 'overnight_quantity': 0, 'multiplier': 1, 'average_price': 0, 'close_price': 0, 'last_price': 5.85, 'value': 137.5, 'pnl': 137.5, 'm2m': 137.5, 'unrealised': 137.5, 'realised': 0, 'buy_quantity': 100, 'buy_price': 8.55, 'buy_value': 855.0000000000001, 'buy_m2m': 855.0000000000001, 'sell_quantity': 100, 'sell_price': 9.925, 'sell_value': 992.5000000000001, 'sell_m2m': 992.5000000000001, 'day_buy_quantity': 100, 'day_buy_price': 8.55, 'day_buy_value': 855.0000000000001, 'day_sell_quantity': 100, 'day_sell_price': 9.925, 'day_sell_value': 992.5000000000001}, {'tradingsymbol': 'NIFTY21NOVFUT', 'exchange': 'NFO', 'instrument_token': 13720834, 'product': 'MIS', 'quantity': 0, 'overnight_quantity': 0, 'multiplier': 1, 'average_price': 0, 'close_price': 17913.1, 'last_price': 17710, 'value': -27877.5, 'pnl': -27877.5, 'm2m': -27877.5, 'unrealised': -27877.5, 'realised': 0, 'buy_quantity': 1850, 'buy_price': 17825.048648648648, 'buy_value': 32976340.0, 'buy_m2m': 32976340.0, 'sell_quantity': 1850, 'sell_price': 17809.97972972973, 'sell_value': 32948462.5, 'sell_m2m': 32948462.5, 'day_buy_quantity': 1850, 'day_buy_price': 17825.048648648648, 'day_buy_value': 32976340.0, 'day_sell_quantity': 1850, 'day_sell_price': 17809.97972972973, 'day_sell_value': 32948462.5}], 'day': [{'tradingsymbol': 'NIFTY21N0316500PE', 'exchange': 'NFO', 'instrument_token': 11018242, 'product': 'MIS', 'quantity': 0, 'overnight_quantity': 0, 'multiplier': 1, 'average_price': 0, 'close_price': 0, 'last_price': 2.4, 'value': -397.5, 'pnl': -397.5, 'm2m': -397.5, 'unrealised': -397.5, 'realised': 0, 'buy_quantity': 100, 'buy_price': 7.875, 'buy_value': 787.5, 'buy_m2m': 787.5, 'sell_quantity': 100, 'sell_price': 3.9, 'sell_value': 390, 'sell_m2m': 390, 'day_buy_quantity': 100, 'day_buy_price': 7.875, 'day_buy_value': 787.5, 'day_sell_quantity': 100, 'day_sell_price': 3.9, 'day_sell_value': 390}, {'tradingsymbol': 'NIFTY21N0317600CE', 'exchange': 'NFO', 'instrument_token': 11086850, 'product': 'MIS', 'quantity': 0, 'overnight_quantity': 0, 'multiplier': 1, 'average_price': 0, 'close_price': 0, 'last_price': 195.35, 'value': -7960.000000000015, 'pnl': -7960.000000000015, 'm2m': -7960.000000000015, 'unrealised': -7960.000000000015, 'realised': 0, 'buy_quantity': 300, 'buy_price': 343.3833333333334, 'buy_value': 103015.00000000001, 'buy_m2m': 103015.00000000001, 'sell_quantity': 300, 'sell_price': 316.85, 'sell_value': 95055, 'sell_m2m': 95055, 'day_buy_quantity': 300, 'day_buy_price': 343.3833333333334, 'day_buy_value': 103015.00000000001, 'day_sell_quantity': 300, 'day_sell_price': 316.85, 'day_sell_value': 95055}, {'tradingsymbol': 'NIFTY21N0317600PE', 'exchange': 'NFO', 'instrument_token': 11087106, 'product': 'MIS', 'quantity': 0, 'overnight_quantity': 0, 'multiplier': 1, 'average_price': 0, 'close_price': 0, 'last_price': 91.95, 'value': 537.5, 'pnl': 537.5, 'm2m': 537.5, 'unrealised': 537.5, 'realised': 0, 'buy_quantity': 100, 'buy_price': 102.55, 'buy_value': 10255, 'buy_m2m': 10255, 'sell_quantity': 100, 'sell_price': 107.925, 'sell_value': 10792.5, 'sell_m2m': 10792.5, 'day_buy_quantity': 100, 'day_buy_price': 102.55, 'day_buy_value': 10255, 'day_sell_quantity': 100, 'day_sell_price': 107.925, 'day_sell_value': 10792.5}, {'tradingsymbol': 'NIFTY21N0317900CE', 'exchange': 'NFO', 'instrument_token': 11094274, 'product': 'MIS', 'quantity': 0, 'overnight_quantity': 0, 'multiplier': 1, 'average_price': 0, 'close_price': 0, 'last_price': 59.9, 'value': -1512.5, 'pnl': -1512.5, 'm2m': -1512.5, 'unrealised': -1512.5, 'realised': 0, 'buy_quantity': 400, 'buy_price': 155.51875, 'buy_value': 62207.50000000001, 'buy_m2m': 62207.50000000001, 'sell_quantity': 400, 'sell_price': 151.7375, 'sell_value': 60695.00000000001, 'sell_m2m': 60695.00000000001, 'day_buy_quantity': 400, 'day_buy_price': 155.51875, 'day_buy_value': 62207.50000000001, 'day_sell_quantity': 400, 'day_sell_price': 151.7375, 'day_sell_value': 60695.00000000001}, {'tradingsymbol': 'NIFTY21N0317900PE', 'exchange': 'NFO', 'instrument_token': 11094530, 'product': 'MIS', 'quantity': 0, 'overnight_quantity': 0, 'multiplier': 1, 'average_price': 0, 'close_price': 0, 'last_price': 255.1, 'value': 1415.000000000001, 'pnl': 1415.000000000001, 'm2m': 1415.000000000001, 'unrealised': 1415.000000000001, 'realised': 0, 'buy_quantity': 50, 'buy_price': 136.95, 'buy_value': 6847.499999999999, 'buy_m2m': 6847.499999999999, 'sell_quantity': 50, 'sell_price': 165.25, 'sell_value': 8262.5, 'sell_m2m': 8262.5, 'day_buy_quantity': 50, 'day_buy_price': 136.95, 'day_buy_value': 6847.499999999999, 'day_sell_quantity': 50, 'day_sell_price': 165.25, 'day_sell_value': 8262.5}, {'tradingsymbol': 'NIFTY21N0318500CE', 'exchange': 'NFO', 'instrument_token': 11135746, 'product': 'MIS', 'quantity': 0, 'overnight_quantity': 0, 'multiplier': 1, 'average_price': 0, 'close_price': 0, 'last_price': 5.85, 'value': 137.5, 'pnl': 137.5, 'm2m': 137.5, 'unrealised': 137.5, 'realised': 0, 'buy_quantity': 100, 'buy_price': 8.55, 'buy_value': 855.0000000000001, 'buy_m2m': 855.0000000000001, 'sell_quantity': 100, 'sell_price': 9.925, 'sell_value': 992.5000000000001, 'sell_m2m': 992.5000000000001, 'day_buy_quantity': 100, 'day_buy_price': 8.55, 'day_buy_value': 855.0000000000001, 'day_sell_quantity': 100, 'day_sell_price': 9.925, 'day_sell_value': 992.5000000000001}, {'tradingsymbol': 'NIFTY21NOVFUT', 'exchange': 'NFO', 'instrument_token': 13720834, 'product': 'MIS', 'quantity': 0, 'overnight_quantity': 0, 'multiplier': 1, 'average_price': 0, 'close_price': 17913.1, 'last_price': 17710, 'value': -27877.5, 'pnl': -27877.5, 'm2m': -27877.5, 'unrealised': -27877.5, 'realised': 0, 'buy_quantity': 1850, 'buy_price': 17825.048648648648, 'buy_value': 32976340.0, 'buy_m2m': 32976340.0, 'sell_quantity': 1850, 'sell_price': 17809.97972972973, 'sell_value': 32948462.5, 'sell_m2m': 32948462.5, 'day_buy_quantity': 1850, 'day_buy_price': 17825.048648648648, 'day_buy_value': 32976340.0, 'day_sell_quantity': 1850, 'day_sell_price': 17809.97972972973, 'day_sell_value': 32948462.5}]}

CodePudding user response：

It seems that you have JSON where key net indicates list of 8 x JSON with 29 key-value pars each. So it means that you should have finally pandas DataFrame with 8 rows and 29 columns.

So all you need is:

import pandas as pd

df = pd.json_normalize(d, record_path="net")

df.shape

# output
(8, 29)

Where d is your dict you posted.

Your output will look like this (I show only 5 rows & columns to make it more readable):

df.iloc[:5, :5]

# output
  average_price   buy_m2m   buy_price  buy_quantity  buy_value
0   17894.833333       0.0    0.000000             0        0.0
1       0.000000     787.5    7.875000           100      787.5
2       0.000000  103015.0  343.383333           300   103015.0
3       0.000000   10255.0  102.550000           100    10255.0
4       0.000000   62207.5  155.518750           400    62207.5