Dataframe Columns To Key Value Dictionary Pair

I have following DataFrame product count Id 175 '409' 41 175

Solution 1:

In[14]: df.set_index('product').T.to_dict('r')
Out[14]: [{'0.5L': 4, '1.5L': 4, '407': 8, '409': 41, 'SCHWEPPES': 6, 'TONIC 1L': 4}]

Solution 2:

Seems like you need groupby the index

df.groupby(level='Id').apply(lambda x : x.set_index('product').T.to_dict(orient='records'))
Out[124]: 
Id
175    [{''409'': 41, ''407'': 8, ''0.5L'': 4, ''1.5L...
177                 [{''SCHWEPPES'': 6, ''TONIC1L'': 4}]
dtype: object

Output as list

df.groupby(level='Id').apply(lambda x : x.set_index('product').T.to_dict(orient='records')).tolist()
Out[128]: 
[[{"'0.5L'": 4, "'1.5L'": 4, "'407'": 8, "'409'": 41}],
 [{"'SCHWEPPES'": 6, "'TONIC1L'": 4}]]

Making a flat list

ll=df.groupby(level='Id').apply(lambda x : x.set_index('product').T.to_dict(orient='records')).tolist()


importoperatorimport functools
functools.reduce(operator.concat, ll)

Out[130]: 
[{"'0.5L'": 4, "'1.5L'": 4, "'407'": 8, "'409'": 41},
 {"'SCHWEPPES'": 6, "'TONIC1L'": 4}]