Convert dataframe to dictionary in Python

Question

I have a csv file that I converted into dataframe using Pandas. Here's the dataframe:

Customer ProductID Count

John     1         50
John     2         45
Mary     1         75
Mary     2         10
Mary     5         15

I need an output in the form of a dictionary that looks like this:

{ProductID:1, Count:{John:50, Mary:75}},
{ProductID:2, Count:{John:45, Mary:10}},
{ProductID:5, Count:{John:0, Mary:15}}

I read the following answers:

python pandas dataframe to dictionary and

Convert dataframe to dictionary

This is the code that I'm having:

df = pd.read_csv('customer.csv') 
dict1 = df.set_index('Customer').T.to_dict('dict') 
dict2 = df.to_dict(orient='records')

and this is my current output:

dict1 = {'John': {'Count': 45, 'ProductID': 2}, 'Mary': {'Count': 15, 'ProductID': 5}}

dict2 = [{'Count': 50, 'Customer': 'John', 'ProductID': 1},
 {'Count': 45, 'Customer': 'John', 'ProductID': 2},
 {'Count': 75, 'Customer': 'Mary', 'ProductID': 1},
 {'Count': 10, 'Customer': 'Mary', 'ProductID': 2},
 {'Count': 15, 'Customer': 'Mary', 'ProductID': 5}]

score 4 · Accepted Answer · answered Jun 11 '16 at 21:39

IIUC you can use:

d = df.groupby('ProductID').apply(lambda x: dict(zip(x.Customer, x.Count)))
      .reset_index(name='Count')
      .to_dict(orient='records')

print (d)
[{'ProductID': 1, 'Count': {'John': 50, 'Mary': 75}}, 
 {'ProductID': 2, 'Count': {'John': 45, 'Mary': 10}}, 
 {'ProductID': 5, 'Count': {'Mary': 15}}]

Convert dataframe to dictionary in Python

1 Answers1