Creating dataframe from Nested Dictionary

Question

I am calling an API that returns a batch request of multiple stock tickers in JSON format. It is a nested dictionary, with 2 levels of keys and then a list of dictionaries. Here is the script:

import json
import requests
import pandas as pd

r = requests.get('https://api.iextrading.com/1.0/stock/market/batch?symbols=aapl,wpx,mnro,twnk,labl,plnt,fsct,qyls,vrns,tree&types=chart&range=3m')

x = r.json()

llist = [*x.keys()]

df=[]
for l in llist:
    df.append(pd.DataFrame.from_dict({(i,j): x[i][j] 
                               for i in x.keys() 
                               for j in x[i].keys()},
                           orient='index'))
df_concat = pd.concat(df, axis=1)

Here is the sample format:

{'AAPL': {'chart': [{'change': 0.129548,
    'changeOverTime': 0,
    'changePercent': 0.06,
    'close': 217.6107,
    'date': '2018-09-19',
    'high': 218.8564,
    'label': 'Sep 19, 18',
    'low': 214.5514,
    'open': 217.7403,
    'unadjustedVolume': 27123833,
    'volume': 27123833,
    'vwap': 216.6509}

I would like to create a dataframe from this where everything is grouped by Ticker and ordered by Date with the other variables, such as: 'open', 'close', 'volume' used as the columns with the associated values in the row. So far the sample output looks like this:

                          0                           1              
(AAPL, chart)   {'volume': 27123833,        {'volume': 26608794,
                 'close': 118.21,...         'close': 120.11,...
(WPX, chart)    {'volume': 1098766,         {'volume': 993465,
                 'close': 13.23,...          'close': 14.68,...

What is the best and most efficient way about cleaning up this data and "unlocking" the list of dictionaries? Thanks in advance!

Can you give an example of the output you want from the data? — zafrin, Dec 21 '18 at 23:23

score 0 · Accepted Answer · answered Dec 22 '18 at 00:16

This will do the job, not sure if it's the cleanest way:

import json 
import requests 
import pandas as pd

r = requests.get('https://api.iextrading.com/1.0/stock/market/batch?symbols=aapl,wpx,mnro,twnk,labl,plnt,fsct,qyls,vrns,tree&types=chart&range=3m')

x = r.json()

output = pd.DataFrame()

for ticker, chart in x.items():   
    for  k, v in chart.items():
        for dictionary in v:
            data = dictionary
            data['ticker'] = ticker
            output = output.append(data, ignore_index=True)

This will have the output:

    change  changeOverTime  changePercent   close   date    high    label   low     open    ticker  unadjustedVolume    volume  vwap
0   0.129548    0.000000    0.060   217.6107    2018-09-19  218.8564    Sep 19, 18  214.5514    217.7403    AAPL    27123833.0  27123833.0  216.6509
1   1.654200    0.007602    0.760   219.2650    2018-09-20  221.5071    Sep 20, 18  218.3880    219.4742    AAPL    26608794.0  26608794.0  219.9999
2   -2.361800   -0.003251   -1.077  216.9032    2018-09-21  220.5903    Sep 21, 18  216.5345    220.0123    AAPL    96246748.0  96246748.0  217.7347
3   3.119100    0.011082    1.438   220.0223    2018-09-24  220.4907    Sep 24, 18  215.8768    216.0661    AAPL    27693358.0  27693358.0  218.6857

Creating dataframe from Nested Dictionary

1 Answers1

Linked