Python: How to create DataFrame columns from a dict inside a list

Question

I have a dict with output in this form:

response['candles'][0:2]

[{'complete': True,
  'mid': {'c': '0.91535', 'h': '0.91535', 'l': '0.91535', 'o': '0.91535'},
  'time': '2002-05-06T21:00:00.000000000Z',
  'volume': 1},
 {'complete': True,
  'mid': {'c': '0.90435', 'h': '0.90435', 'l': '0.90435', 'o': '0.90435'},
  'time': '2002-05-07T21:00:00.000000000Z',
  'volume': 1}]

I can create a DataFrame easily that looks like this:

res = pd.DataFrame(response['candles'])

complete    mid time    volume
0   True    {'o': '0.91535', 'h': '0.91535', 'l': '0.91535...   2002-05-06T21:00:00.000000000Z  1
1   True    {'o': '0.90435', 'h': '0.90435', 'l': '0.90435...   2002-05-07T21:00:00.000000000Z  1

I can access 'mid' dict items like this:

response['candles'][0]['mid']['c']

'0.91535'

But how do I add columns in my DataFrame for each 'mid' item, instead of having a dict inside of the DataFrame? I want a column for ['mid']['o'], ['mid']['h'], and so on.

Thanks

Psidom · Accepted Answer · 2017-10-08T20:13:57.087

1

Use pandas.io.json.json_normalize:

d = [{'complete': True,
  'mid': {'c': '0.91535', 'h': '0.91535', 'l': '0.91535', 'o': '0.91535'},
  'time': '2002-05-06T21:00:00.000000000Z',
  'volume': 1},
 {'complete': True,
  'mid': {'c': '0.90435', 'h': '0.90435', 'l': '0.90435', 'o': '0.90435'},
  'time': '2002-05-07T21:00:00.000000000Z',
  'volume': 1}]

pd.io.json.json_normalize(d)
#complete     mid.c   mid.h   mid.l   mid.o                           time  volume
#0  True    0.91535 0.91535 0.91535 0.91535 2002-05-06T21:00:00.000000000Z  1
#1  True    0.90435 0.90435 0.90435 0.90435 2002-05-07T21:00:00.000000000Z  1

edited Oct 08 '17 at 20:13

answered Oct 08 '17 at 20:12

Psidom

209,562
33
339
356

Could one use ```apply(pd.Series)```? – liam Oct 08 '17 at 20:13
@LiamHealy Yes. That works as well. – Psidom Oct 08 '17 at 20:14
Awesome, thanks!!! Didn't realize it was JSON format... – cadig Oct 08 '17 at 20:16
Thanks for the info. I was going to provide an alternative answer but turns out this question was a duplicate - as marked by @cᴏʟᴅsᴘᴇᴇᴅ – liam Oct 08 '17 at 20:16

Python: How to create DataFrame columns from a dict inside a list

1 Answers1