Convert nested dictionary to a dataframe

ALain 0
Viewed 44

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.

Desired Output df

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.

Current Output

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

dictionary pandas python
1 Answers

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.

JNevill0
Related