Dict in List in Dict to pandas dataframe

Question

I think I am receiving a dict inside of a list inside of a dict from an API. How do I convert this to a pandas dataframe?

When I use pandas.dataframe.from_dict() the output is just one of the dictionaries in each of the elements, instead of splitting up the dictionaries.

a= {'count': 10465,
 'status': 'DELAYED',
 'tickers': [{'day': {'c': 48.11,
    'h': 49.61,
    'l': 48.11,
    'o': 48.36,
    'v': 2018543,
    'vw': 48.6329},
   'lastQuote': {'P': 48.07,
    'S': 50,
    'p': 48.02,
    's': 3,
    't': 1619195328041448704},
   'lastTrade': {'c': None,
    'i': '71683813093722',
    'p': 48.04,
    's': 100,
    't': 1619195306417034752,
    'x': 4},
   'min': {'av': 2018540,
    'c': 48.11,
    'h': 48.19,
    'l': 48.11,
    'o': 48.19,
    'v': 8842,
    'vw': 48.1468},
   'prevDay': {'c': 47.83,
    'h': 49.54,
    'l': 47.07,
    'o': 47.9,
    'v': 4938809,
    'vw': 48.3369},
   'ticker': 'FTCH',
   'todaysChange': 0.21,
   'todaysChangePerc': 0.439,
   'updated': 1619195306417034752},
  {'day': {'c': 4.01,
    'h': 4.09,
    'l': 4.01,
    'o': 4.03,
    'v': 12077,
    'vw': 4.0342},
   'lastQuote': {'P': 4.09, 'S': 3, 'p': 4, 's': 7, 't': 1619195239632140363},
   'lastTrade': {'c': [14, 41],
    'i': '4',
    'p': 4.01,
    's': 100,
    't': 1619195239632141482,
    'x': 21},
   'min': {'av': 12077,
    'c': 4.01,
    'h': 4.01,
    'l': 4.01,
    'o': 4.01,
    'v': 100,
    'vw': 4.01},
   'prevDay': {'c': 4.03,
    'h': 4.07,
    'l': 4.01,
    'o': 4.05,
    'v': 39628,
    'vw': 4.038},
   'ticker': 'METC',
   'todaysChange': -0.02,
   'todaysChangePerc': -0.496,
   'updated': 1619195280000000000}
]}

Expected result:

Ticker  day_c   min_c
FTCH     48.11   48.11
METC      4.01    4.01 

Answer 1

If data is your dictionary from the question:

df = pd.DataFrame(
    [
        {
            "Ticker": t["ticker"],
            "day_c": t["day"]["c"],
            "min_c": t["min"]["c"],
        }
        for t in data["tickers"]
    ]
)
print(df)

Prints:

  Ticker  day_c  min_c
0   FTCH  48.11  48.11
1   METC   4.01   4.01

Answer 2

Using json_normalize with df.join and df.pop

For earlier versions:

out  = pd.DataFrame(a)
out = out.join(pd.io.json.json_normalize(out.pop('tickers')))

For latest versions:

out  = pd.DataFrame(a)
out = out.join(pd.json_normalize(out.pop('tickers')))

---

Output:

   count   status ticker  todaysChange  todaysChangePerc              updated  \
0  10465  DELAYED   FTCH          0.21             0.439  1619195306417034752   
1  10465  DELAYED   METC         -0.02            -0.496  1619195280000000000   

   day.c  day.h  day.l  day.o  ...  min.l  min.o  min.v   min.vw  prevDay.c  \
0  48.11  49.61  48.11  48.36  ...  48.11  48.19   8842  48.1468      47.83   
1   4.01   4.09   4.01   4.03  ...   4.01   4.01    100   4.0100       4.03   

   prevDay.h  prevDay.l prevDay.o prevDay.v  prevDay.vw  
0      49.54      47.07     47.90   4938809     48.3369  
1       4.07       4.01      4.05     39628      4.0380