Creating a dictionary with dictionaries from a Pandas dataframe without loop

Question

I'm facing a problem in Python to create in an efficient way a dictionary of dictionaries from a Pandas dataframe. This is my DF.

             User-ID  Book-Rating
ISBN                            
0553297627   230402     1
0553297627   124942     7
0553297627   238120     0
0553297627   227705     2
0553297627   234623     10
0553297627   172742     5

And I want a structure like this:

{
'0553297627': {
                '230402': 1, 
                '124942': 7, 
                '238120': 0, 
                '227705': 2, 
                '234623': 10
                '172742': 5,
             }
... more books here
}

I'm doing this with a loop, and it is very time-consuming. My code is:

...
isbn = '0553297627'
df_values = df.values
d = {key: value for (key, value) in df_values}  <--- I want to avoid!
dict[isbn] = d

Answer 1

dictionary comprehension based off of set_index + groupby + xs

{name: group.xs(name).to_dict()
 for name, group in df.set_index('User-ID', append=True).groupby(level=0)}

{553297627: {'Book-Rating': {124942: 7,
   172742: 5,
   227705: 2,
   230402: 1,
   234623: 10,
   238120: 0}}}

---

using defaultdict + iterrows

from collections import defaultdict

d = defaultdict(dict)
for i, row in df.iterrows():
    d[i][row['User-ID']] = row['Book-Rating']

dict(d)

---

time tests

Answer 2

You can use groupby with zip, last convert to_dict:

print (df.groupby(level='ISBN')
         .apply(lambda x: dict(zip(x['User-ID'], x['Book-Rating'])))
         .to_dict())
{
    553297627:
    {230402: 1, 172742: 5, 238120: 0, 227705: 2, 124942: 7, 234623: 10}
}