I have a dataframe like this:
df = pd.DataFrame(id:{1,2,1,4,4},
course:{math,math,sci,art,math},
result:{pass,pass,fail,fail,fail}}
I want to create a nested dictionary like this: for every ID, I want to make a nested dictionary of passed courses and failed courses.
{id:{pass:{courses},fail:{courses}}}
{1:{pass:{math},fail:{sci}},2:{pass:{math}},4:{fail:{art,math}}}
Try this:
df = pd.DataFrame({'id':[1,2,1,4,4],
'course':['math','math','sci','art','math'],
'result':['pass','pass','fail','fail','fail']})
df.groupby(['result', 'id'])['course'].agg(list).unstack().to_dict()
Output:
{1: {'fail': ['sci'], 'pass': ['math']},
2: {'fail': nan, 'pass': ['math']},
4: {'fail': ['art', 'math'], 'pass': nan}}
Well, yes, peaking at @mozway solution, use set instead of list:
df.groupby(['result', 'id'])['course'].agg(set).unstack().to_dict()
Output:
{1: {'fail': {'sci'}, 'pass': {'math'}},
2: {'fail': nan, 'pass': {'math'}},
4: {'fail': {'art', 'math'}, 'pass': nan}}
Assuming this input:
id course result
0 1 math pass
1 2 math pass
2 1 sci fail
3 4 art fail
4 4 math fail
You can use nested groupby:
out = (df.groupby('id')
.apply(lambda g: g.groupby('result')['course']
.agg(set).to_dict())
.to_dict()
)
output:
{1: {'fail': {'sci'}, 'pass': {'math'}},
2: {'pass': {'math'}},
4: {'fail': {'art', 'math'}}}
Or a pivot table:
(df.pivot_table(columns='id', index='result', values='course', aggfunc=set)
.to_dict()
)
output:
{1: {'fail': {'sci'}, 'pass': {'math'}},
2: {'fail': nan, 'pass': {'math'}},
4: {'fail': {'art', 'math'}, 'pass': nan}}
