Convert a pandas dataframe into a dictionary of dictionaries

Question

I have a data frame in which I have attributes and values for two products.

PRODUCT ATTRIBUTE   VALUES
prod1   Attr1         A
prod1   Attr2         B
prod1   Attr3         C
prod1   Attr4         D
prod2   Attr1         E
prod2   Attr2         F
prod2   Attr3         G
prod2   Attr4         H

How can I convert it into a dictionary of a list of dictionaries that looks like the following?

{'prod1':[{'Attr1':A, 'Attr2':B, 'Attr3':C, 'Attr4':D}], 'prod2':[{'Attr1':E, 'Attr2':F, 'Attr3':G, 'Attr4':H}]}

Possible duplicate of [python pandas dataframe to dictionary](http://stackoverflow.com/questions/18695605/python-pandas-dataframe-to-dictionary) — Chris Beard, Dec 09 '16 at 16:34
@ChrisBeard my question is different, I know how to use to_dict() function but this problem requires some kind of pivot first. — Ujjwal Roy, Dec 09 '16 at 16:38

score 5 · Accepted Answer · answered Dec 09 '16 at 16:36

You can use groupby with apply:

d = df.groupby('PRODUCT').apply(lambda x: [dict(zip(x.ATTRIBUTE, x.VALUES))]).to_dict()
print (d)
{'prod1': [{'Attr1': 'A', 'Attr2': 'B', 'Attr3': 'C', 'Attr4': 'D'}],
'prod2': [{'Attr1': 'E', 'Attr2': 'F', 'Attr3': 'G', 'Attr4': 'H'}]}

Roman Pekar · Answer 2 · 2016-12-09T16:50:04.057

2

You can use pandas.DataFrame.pivot to prepare your data and then call pandas.DataFrame.to_dict:

>>> df.pivot(columns='PRODUCT',index='ATTRIBUTE', values='VALUES').to_dict()
{'prod1': {'Attr4': 'D', 'Attr2': 'B', 'Attr3': 'C', 'Attr1': 'A'}, 'prod2': {'Attr4': 'H', 'Attr2': 'F', 'Attr3': 'G', 'Attr1': 'E'}}

I also assume that you don't actually need one-element lists there. In this case you can have really simple solution. If you need those lists then it's probably better to go with @jezrael answer

edited Dec 09 '16 at 16:50

answered Dec 09 '16 at 16:43

Roman Pekar

107,110
28
195
197

Very Cool! Thanks a lot for this answer. Working amazing! – Ujjwal Roy Dec 09 '16 at 16:48

score 2 · Answer 3 · answered Dec 09 '16 at 17:04

comprehension

{k: [v.to_dict()] for k, v in df.set_index(['PRODUCT', 'ATTRIBUTE']).VALUES.unstack(0).iteritems()}

{'prod1': [{'Attr1': 'A', 'Attr2': 'B', 'Attr3': 'C', 'Attr4': 'D'}],
 'prod2': [{'Attr1': 'E', 'Attr2': 'F', 'Attr3': 'G', 'Attr4': 'H'}]}

score 0 · Answer 4 · answered Dec 09 '16 at 16:46

0

Pivot and use the to_dict method and then wrap the inner dict in a list.

d= df.pivot(index='PRODUCT', columns='ATTRIBUTE', values='VALUES').to_dict()
{key:[value] for key,value in d.items()}

answered Dec 09 '16 at 16:46

Ted Petrou

59,042
19
131
136

Convert a pandas dataframe into a dictionary of dictionaries

4 Answers4