Convert nested dictionary to a dataframe-CodePudding

I am trying to convert a nested dictionary into a pandas df for some stock data I have. The data I am scraping comes in the dictionary as shown below. Apologies it is a very long snippet, I wanted to include all the fields to avoid cutting it incorrectly, plus I may want to include more later.

Data = {'data': [{'heading': {'key': 'history.instrument', 'context': {'quantityPrecision': 8, 'baseCode': None, 'prettyName': 'Coca-Cola', 'precision': 2, 'treeType': 'STOCK', 'instrument': 'KO', 'instrumentBadge': None, 'instrumentCode': 'KO_US_EQ'}, 'meta': None}, 'subHeading': {'key': 'history.order.filled.buy', 'context': {'quantityPrecision': 8, 'amount': 10.0, 'quantity': 0.245264, 'amountPrecision': 2}, 'meta': None}, 'mainInfo': {'key': 'history.currency-amount', 'context': {'amount': 10.0, 'currency': 'GBP'}, 'meta': None}, 'additionalInfo': {'key': 'history.order.status.filled', 'context': None, 'meta': {'colour': 'ACCENT', 'uppercase': True, 'multiline': False}}, 'date': '2021-08-26T16:45:10 03:00', 'detailsPath': '/orders/1514731569'}, {'heading': {'key': 'history.instrument', 'context': {'quantityPrecision': 8, 'baseCode': None, 'prettyName': 'First Solar', 'precision': 2, 'treeType': 'STOCK', 'instrument': 'FSLR', 'instrumentBadge': None, 'instrumentCode': 'FSLR_US_EQ'}, 'meta': None}, 'subHeading': {'key': 'history.order.filled.buy', 'context': {'quantityPrecision': 8, 'amount': 11.0, 'quantity': 0.160216, 'amountPrecision': 2}, 'meta': None}, 'mainInfo': {'key': 'history.currency-amount', 'context': {'amount': 11.0, 'currency': 'GBP'}, 'meta': None}, 'additionalInfo': {'key': 'history.order.status.filled', 'context': None, 'meta': {'colour': 'ACCENT', 'uppercase': True, 'multiline': False}}, 'date': '2021-08-26T16:45:09 03:00', 'detailsPath': '/orders/1514731500'}, 'hasNext': True, 'footer': None}

I want the output to look like this below in a dataframe.

I am getting stuck with attempting to convert the outputted data from a nested dictionary into a pandas dataframe so it is more useable for me.

If I try to put the data straight into a dataframe using:

data_2 = pd.DataFrame(Data)

I get this output.

Clearly I need to tidy the data when converting to a dataframe but I cannot get the setup correct for it.

CodePudding user response：

You can loop through every item in the main list in your Data object and then pick out the pieces you need:

data_list=[]

for item in Data:
        print(item)
        data_list.append([item['heading']['context']['prettyName'], item['heading']['context']['instrument'], item['subHeading']['context']['quantity']])

df = pd.DataFrame(data_list, columns=['prettyName','instrument','quantity'])

Just add onto that logic to get your remaining columns.